Thread: 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:
> 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?
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
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 > 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)
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
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/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 > >---------------------------(end of broadcast)--------------------------- >TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/users-lounge/docs/faq.html > > -- Charles H. Woloszynski ClearMetrix, Inc. 115 Research Drive Bethlehem, PA 18015 tel: 610-419-2210 x400 fax: 240-371-3256 web: www.clearmetrix.com