Thread: Recover an existing database
Apologies if I'm posting this in the wrong forum. I have tried to get help from comp.databases.postgresql.hackers without success.
I had a hard disk crash recently and had to reinstall Linux (RedHat 7.2) and consequently postgres (7.1.3) on the new root disk. My large database (also 7.1.3) was mounted on another volume and I had created it using the "CREATE DATABASE WITH LOCATION=" syntax.
My question is: How can I change the new postgres install to recognise my existing database? I renamed the existing database directory and successfully created a new database area using initlocation and rebooted the postmaster with the PGDATA2 env variable set. I created a new
database using CREATE DATABASE WITH LOCATION='PGDATA2' and that went okay. Then I dropped the postmaster and moved the directories around so that the existing database was renamed to the new one. The postmaster came up okay but psql said the database was "pre 7.0" which it isn't. So I'm sure I'm missing something somewhere (pg_database?) I know what I'm doing is pretty tweeky but I would really like not to have to rebuild this database if at all possible.
If this type of functionality is not available, perhaps it could be considered as a future addition. Using /var as the default location is understandable but typically / is not a large file system and I would assume use of other locations for data storage to be pretty common.
Hoping you can help
Peter
Sydney, Australia
"Peter Bayley" <peterb@homer.com.au> writes:
> I had a hard disk crash recently and had to reinstall Linux (RedHat 7.2) a=
> nd consequently postgres (7.1.3) on the new root disk. My large database (=
> also 7.1.3) was mounted on another volume and I had created it using the "C=
> REATE DATABASE WITH LOCATION=3D" syntax.
> =20
> My question is: How can I change the new postgres install to recognise my =
> existing database?
You can't, if I'm correctly interpreting this scenario to mean that you
lost pg_log. Your transaction history, transaction numbers, and OIDs
are not in sync with the data files. While the OID counter isn't all
that critical, the transaction history definitely is. Sorry.
regards, tom lane