Postgresql - restore database from physical storage

[Anja Karlstädt]

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:

***@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

"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.

"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

[Lukasz Brodziak]

Post by Anja Karlstädt
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
/var/lib/postgresql/8.4/main/ -l
/var/log/postgresql/postgresql-8.4-main.log start

server starting

postgres
template 0
template 1

"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.

"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
--
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"