Re: Postrgres data restoration problem - Mailing list pgsql-admin

Thanks, Tom, for the reply.

> Jim Cochrane <jtc@dim.com> writes:
> > I'm looking for help restoring old postgres databases on a linux system
> > after a reinstall of Redhat (including posgresql).
>
> How old?  We need to know the exact PG version number.

cat PG_VERSION
7.2

(Both the old and new installations are the same version.)

>
> > I was able to connect to an old database (e.g., with psql dbname), but
> > when I listed the tables with \d, it responds with: No relations found.
>
> Do you see anything if you just do "select * from pg_class" or
> "select * from pg_database"?  If there's more than one database
> accessible, do the results change in different databases?
>
>             regards, tom lane

Well, after trying Thierry's suggestion, it appears that my old database
may have gotten corrupted.  Here is my reply to him:

> I have just simple questions :
> 1) After Redhat and Postgres reinstall, did you run initdb ? Don't do it.

I'm pretty sure I didn't run initdb by hand, but it may have been run by
the startup script in /etc/rc?.d.

> 2) What is the value of $PGDATA ?

The database server was being started by the /etc/init.d/postgresql (linked
into the appropriate /etc/rc?.d directory) and I believe it was setting
PGDATA to either /var/lib/pgsql or /var/lib/pgsql/data.  (The old database
files are in /home/pgsql/data.)

> 3) Have you try to set : export PGDATA=/oldpgsqlpath/data and pg_ctl start ?
>
> >From my point of view, after reinstalling Redhat and PG, be sure that the
> filesystem /oldpgsqlpath is mounted, export PGDATA=/oldpgsqlpath/data, pg_ctl
> start.
> psql dbname
> \d  => you should see your tables.

Using your example, I just essentially did the same thing (as the postgres
user):

cd /oldpgsqlpath/data
pg_ctl  -D $PWD start >/tmp/pgr 2>&1

However the server failed to start up, giving the following error messages:

postmaster successfully started
DEBUG:  database system was shut down at 2003-12-07 14:55:22 MST
DEBUG:  open of /home/pgsql/data/pg_xlog/0000000000000000 (log file 0, segment 0) failed: No such file or directory
DEBUG:  invalid primary checkpoint record
DEBUG:  open of /home/pgsql/data/pg_xlog/0000000000000000 (log file 0, segment 0) failed: No such file or directory
DEBUG:  invalid secondary checkpoint record
FATAL 2:  unable to locate a valid checkpoint record
DEBUG:  startup process (pid 31411) exited with exit code 2
DEBUG:  aborting startup due to startup process failure

> Otherwise, something has been broken in /oldpgsqlpath/data.

It looks like that is the case - that, perhaps, the metadata is corrupted.

I suspect that this is not recoverable.  Is that the case?

[In case it matters, I made sure the currently running postmaster was
terminated before starting it with pg_ctl.]