Re: Postrgres data restoration problem

Thanks, V Kashyap, for your help.

> Dear Jim Cochrane,
>
> >I'm looking for help restoring old postgres databases on a linux system
> >after a reinstall of Redhat (including posgresql). It's the same version
> >of redhat and of postgres I was using before the resinstall, but I've not
> >been able to get the database server to see the old databases.
> >
> Here we are trying to help
>
> >The old data is stored in the standard postgres format. (No,
> >unfortunately, I did not have a backup.) The postgres data is located
> >on my system under /var/lib/pgsql. So I tried both creating a
> >symbolic link - setting up /var/lib/pgsql/data as a symbolic link to
> >/oldpgsqlpath/data, and, when that didn't work, I just copied the old data
> >directory to /var/lib/pgsql (that is, it ended up residing in
> >/var/lib/pgsql/data), which didn't work either:
> >
> I presume while you were doing the all above said
> 1. PostgreSQL was stoped while cp etc

I think so, but am not certain it was stopped; if it wasn't, it may have
caused a problem, as your question implies.

> 2. The new cluster has been initialise initdb i..e the original
> database and not the copied direcrory namely /var/lib/pgsql

I didn't run initdb. However, let me include the response I sent to
Thierry (who, I think, only responded to me and not the list). I included
this in another reply I just sent, but so you don't have to look for it
I'll include it here, too. I think the results I got after trying his
suggestion indicates that the database has gotten corrupted, but perhaps
I've overlooked something that could fix it. Here's the response to
Thierry:

> 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?

[END RESPONSE]

> If this help kindly shoot back for more
> For next time keep a backup
Yep.

Thanks,
Jim