From: "Anja Karlstädt" <anja.karlstaedt@charite.de>
To: pgsql-admin@postgresql.org
Subject: Postgresql - restore database from physical storage
Date: Mon, 19 Nov 2012 15:47:49 +0100
Message-ID: <581030c75139182bb216efbd121033da.squirrel@webmail.charite.de> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: anja.karlstaedt@charite.de
Subject: Re: Postgresql - restore database from physical storage
In-Reply-To: <581030c75139182bb216efbd121033da.squirrel@webmail.charite.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox