Horton

Thread

  1. Re: Restoring databases using only data/ dir

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-10-03T19:27:52Z

    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

  2. Restoring databases using only data/ dir

    Afra <aa4@cse.buffalo.edu> — 2002-10-03T19:58:26Z

    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

  3. Re: Restoring databases using only data/ dir

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-10-03T20:07:50Z

    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

  4. Re: Restoring databases using only data/ dir

    Afra <aa4@cse.buffalo.edu> — 2002-10-03T20:37:03Z

    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)

  5. Re: Restoring databases using only data/ dir

    Charles H. Woloszynski <chw@clearmetrix.com> — 2002-10-03T22:12:53Z

    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