Re: Postgresql - restore database from physical storage

2012/11/19 "Anja Karlstädt" <anja.karlstaedt@charite.de>:
> Dear PGSQL-Admin,
>
> Due to a system crash I had to reinstall PostgreSQL 8.4 without being able
> to make a dump of my database.
> However, I do have a complete backup of the postgresql main directory
> including the physical storage (/var/lib/postgres/8.4/main).
>
> Is it possible to restore the database from this backup?
>
> After reinstallation of postgres (8.4 - 17.11.2012) I moved all files from
> the original main directory back into the new directory and connected to
> the database as following:
>
> postgres@user:/home/user$ /usr/lib/postgresql/8.4/bin/pg_ctl -D
> /var/lib/postgresql/8.4/main/ -l
> /var/log/postgresql/postgresql-8.4-main.log start
>> server starting
>
> After entering psql the list of databases is displayed as following:
> postgres
> template 0
> template 1
>
> The pg_database file shows:
>> "template1" 1 1663 648
>> "template0" 11563 1663 648
>> "postgres" 11564 1663 648
> The "original" pg_database changed from my "original" version including
> all databases (total=6) to the given default version.
> Then I created two different databases as "test" and "test2":
>> "test" 16384 1663 648
>> "test2" 16386 1663 648
>
> The OID 16385 is obtained by one of my "original" databases and obviously
> not included in this pg_database list.
> If I try to set the OID from "test" to 16385 and connect to this database
> the OID entry is changed back to 16384 and the database is empty.
> The permissions (owner, group) for all files in the main directory are set
> to postgres.
>
> Could you please tell me how to restore my database or connect to it in
> order to save the data from the tables?
>
> Thank you very much for reading this email.
>
> Yours sincerely,
>
> Anja Karlstaedt
>
>
>
>
>
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

Hello,

Do you still have the 'untouched' copy of the PGDATA? If so I would do this:
1. Create a new directory eg. /usr/local/pgsql/data and place the
physical files from the backup in that directory(only the files and
subdirectories from the main folder)
2. Give all permissions to the folder to user postgres
3. Stop the server
4. Initiate new cluster pointing the data directory from first step
and start the server
5. Try to connect usin psql and check the pg_database for the
'original' set of DBs

The strange thing is that your procedure didn't work. Given you have
properly copied all the data from the physical backup it should work
just fine. I've been using this method with some of our clients and it
always worked.

Regards

--
Łukasz Brodziak
"Do you bury me when I'm gone
Do you teach me while I'm here
Just as soon I belong
Then it's time I disappear"