Restoring databases using only data/ dir
Hi,
We have run into some problems here which stops us from properly backing
up the databases using pg_dump. We do have a tarred version of the data
directory (/usr/local/pgsql/data) which contains databases that we need
to restore.
I found out that simply replacing the data/ directory does no good. Is
there another method in which I can restore databases with the data/
directory?
Thank you
Afra
Afra <aa4@cse.buffalo.edu> writes:
Now, the old server was running postgres 7.1. So I installed the same
version in house and moved over the data/ directory from the backup tar
file to the newly build data directory:
# mv backup/pgsql/data/global /usr/local/psql/data/
# mv backup/pgsql/data/base /usr/local/psql/data/
# mv backup/pgsql/data/pg_xlog /usr/local/psql/data
Okay, this should have worked given that both machines were the same
architecture --- you could possibly have got burnt by endianness or
alignment issues otherwise.
Postgres starts up fine after the move, but then I cannot actually
retrieve any databases:
$ pg_dump wdcx > wdcx
pg_dump: couldn't find the pg_database entry.
There is no entry in the 'pg_database' table for this database.
Odd thing is, the database is in there and Postgres knows it:
$ createdb wdcx
ERROR: CREATE DATABASE: database "wdcx" already exists
createdb: database creation failed
That is mighty suggestive. I'm thinking that the indexes on pg_database
are corrupt. Are you able to connect to template1 and do "select * from
pg_database"? If so, what do you see? If not, what happens exactly?
regards, tom lane
Import Notes
Reply to msg id not found: 3D9CAA6F.5000409@cse.buffalo.edu
Hi,
Thank you for the quick reply. Here is a more detailed account of what
is happening...
Well, we were hosting our Postgres boxes at a co-host place. The machine
crashed and has no dumps of any databases on there. However, luckily, I
do have a tar backup of the pgsql directory, which contains all the
files in data/.
Now, the old server was running postgres 7.1. So I installed the same
version in house and moved over the data/ directory from the backup tar
file to the newly build data directory:
# mv backup/pgsql/data/global /usr/local/psql/data/
# mv backup/pgsql/data/base /usr/local/psql/data/
# mv backup/pgsql/data/pg_xlog /usr/local/psql/data
# su - postgres
Postgres starts up fine after the move, but then I cannot actually
retrieve any databases:
$ pg_dump wdcx > wdcx
pg_dump: couldn't find the pg_database entry.
There is no entry in the 'pg_database' table for this database.
Odd thing is, the database is in there and Postgres knows it:
$ createdb wdcx
ERROR: CREATE DATABASE: database "wdcx" already exists
createdb: database creation failed
How do I retrieve all the databases from the directory? I hope I am not
doomed...
Thank you very much.
Afra
Show quoted text
Restoring the *entire* data directory should work.
It seems to me that there's a great deal you haven't told us; if you
want useful help it's better to err on the side of too much info,
not too little. For starters, what happens when you try to pg_dump?regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Was the backup of the directory done with the postgresql server running
or stopped? I am going to guess that the server was running and that
the disk image was changing as the backup was made...
Tom Lane wrote:
Afra <aa4@cse.buffalo.edu> writes:
Now, the old server was running postgres 7.1. So I installed the same
version in house and moved over the data/ directory from the backup tar
file to the newly build data directory:# mv backup/pgsql/data/global /usr/local/psql/data/
# mv backup/pgsql/data/base /usr/local/psql/data/
# mv backup/pgsql/data/pg_xlog /usr/local/psql/dataOkay, this should have worked given that both machines were the same
architecture --- you could possibly have got burnt by endianness or
alignment issues otherwise.Postgres starts up fine after the move, but then I cannot actually
retrieve any databases:
$ pg_dump wdcx > wdcx
pg_dump: couldn't find the pg_database entry.
There is no entry in the 'pg_database' table for this database.Odd thing is, the database is in there and Postgres knows it:
$ createdb wdcx
ERROR: CREATE DATABASE: database "wdcx" already exists
createdb: database creation failedThat is mighty suggestive. I'm thinking that the indexes on pg_database
are corrupt. Are you able to connect to template1 and do "select * from
pg_database"? If so, what do you see? If not, what happens exactly?regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
--
Charles H. Woloszynski
ClearMetrix, Inc.
115 Research Drive
Bethlehem, PA 18015
tel: 610-419-2210 x400
fax: 240-371-3256
web: www.clearmetrix.com