Re: Restoring databases using only data/ dir

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