Postrgres data restoration problem

[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.

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 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.
I suspect that the data may be unrecoverable. Is my pessimism justified or
might there be a way (without spending days, that is) to recover the old
data?


Thanks very much!
Jim Cochrane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Sai Hertz And Control Systems]

Dear Jim Cochrane,

Post by 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

Post by Jim Cochrane
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

I presume while you were doing the all above said
1. PostgreSQL was stoped while cp etc
2. The new cluster has been initialise initdb i..e the original
database and not the copied direcrory namely /var/lib/pgsql

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


Regards,
V Kashyap



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

[Jim Cochrane]

Thanks, V Kashyap, for your help.

Post by Sai Hertz And Control Systems
Dear Jim Cochrane,

Post by 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

Post by Jim Cochrane
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

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.

Post by Sai Hertz And Control Systems
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

Post by Sai Hertz And Control Systems
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.

Post by Sai Hertz And Control Systems
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.)

Post by Sai Hertz And Control Systems
3) Have you try to set : export PGDATA=/oldpgsqlpath/data and pg_ctl start ?

Post by Jim Cochrane
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

Post by Sai Hertz And Control Systems
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]

Post by Sai Hertz And Control Systems
If this help kindly shoot back for more
For next time keep a backup

Yep.

Thanks,
Jim

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

Post by Jim Cochrane
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.

Post by Jim Cochrane
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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Jim Cochrane]

Thanks, Tom, for the reply.

Post by Tom Lane

Post by Jim Cochrane
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.)

Post by Tom Lane

Post by Jim Cochrane
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

Post by Tom Lane
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.

Post by Tom Lane
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.)

Post by Tom Lane
3) Have you try to set : export PGDATA=/oldpgsqlpath/data and pg_ctl start ?

Post by Jim Cochrane
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

Post by Tom Lane
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.]

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

Post by Jim Cochrane

Post by Tom Lane
How old? We need to know the exact PG version number.

cat PG_VERSION
7.2

That's not exact, it only tells the major release number.
"postmaster --version" was what I was looking for.

Post by Jim Cochrane
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

This is ungood :-(. Your only hope at this point is to run pg_resetxlog
(which is not a standard part of the 7.2 distribution, but is available
as a contrib utility). If you are lucky, that will let you into the
database, but you should be aware of the possibility that you've lost
parts of the last few transactions and therefore have a
not-completely-consistent database.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Jim Cochrane]

Post by Tom Lane

Post by Jim Cochrane

Post by Tom Lane
How old? We need to know the exact PG version number.

cat PG_VERSION
7.2

That's not exact, it only tells the major release number.
"postmaster --version" was what I was looking for.

It outputs:

postmaster (PostgreSQL) 7.2.1

Post by Tom Lane

Post by Jim Cochrane
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, segm

ent 0) failed: No such file or directory

Post by Tom Lane

Post by Jim Cochrane
DEBUG: invalid primary checkpoint record
DEBUG: open of /home/pgsql/data/pg_xlog/0000000000000000 (log file 0, segm

ent 0) failed: No such file or directory

Post by Tom Lane

Post by Jim Cochrane
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

This is ungood :-(. Your only hope at this point is to run pg_resetxlog
(which is not a standard part of the 7.2 distribution, but is available
as a contrib utility). If you are lucky, that will let you into the
database, but you should be aware of the possibility that you've lost
parts of the last few transactions and therefore have a
not-completely-consistent database.

I guess I'm unlucky. I shut the server down and ran pg_resetxlog, which
gave the error message:

The database was not shut down cleanly.
Resetting the xlog may cause data to be lost!
If you want to proceed anyway, use -f to force reset.

So I used the -f option and started the server up, which started successfully:

postmaster successfully started
DEBUG: database system was shut down at 2003-12-10 12:31:46 MST
DEBUG: checkpoint record is at 0/2000010
DEBUG: redo record is at 0/2000010; undo record is at 0/2000010; shutdown TRUE
DEBUG: next transaction id: 158; next oid: 16556
DEBUG: database system is ready

However, the same problem occurs as before - When I use psql to connect to
a database and then use \d to list the tables, there are not tables:
No relations found.

It appears to me that the metadata for the database tables got corrupted or
blown away. It looks like it's time to give up.

In case anyone's concerned, this data was important, but not critical - It
hurts to loose it, but I'm not going to end up in jail because of it :-)

Anyway, I appreciate you guys' help. I've learned some new things about
postgres.


Jim Cochrane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[Tom Lane]

Post by Jim Cochrane
Anyway, I appreciate you guys' help. I've learned some new things about
postgres.

One thing I hope you learned is to run something newer than 7.2.1.
If you still want to use the 7.2 series then 7.2.4 is the thing to
be using; although really 7.3.5 is the most stable release at this
instant. There is very seldom any good reason to be running anything
but the latest dot-release of whichever release series you are
using. In the case of 7.2 I know we fixed some critical bugs in
the dot-releases (though I don't have enough info to guess whether
one of them was what bit you).

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly