pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgresql - restore database from physical storage
2+ messages / 2 participants
[nested] [flat]
* Postgresql - restore database from physical storage
@ 2012-11-19 14:47 "Anja Karlstädt" <anja.karlstaedt@charite.de>
0 siblings, 1 reply; 2+ messages in thread
From: "Anja Karlstädt" @ 2012-11-19 14:47 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Postgresql - restore database from physical storage
@ 2012-11-19 16:58 Lukasz Brodziak <lukasz.brodziak@gmail.com>
parent: "Anja Karlstädt" <anja.karlstaedt@charite.de>
0 siblings, 0 replies; 2+ messages in thread
From: Lukasz Brodziak @ 2012-11-19 16:58 UTC (permalink / raw)
To: Anja Karlstädt <anja.karlstaedt@charite.de>; +Cc: pgsql-admin
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"
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2012-11-19 16:58 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-11-19 14:47 Postgresql - restore database from physical storage "Anja Karlstädt" <anja.karlstaedt@charite.de>
2012-11-19 16:58 ` Lukasz Brodziak <lukasz.brodziak@gmail.com>
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