Обсуждение: Postrgres data restoration problem
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
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 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
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.
> 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
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.]
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
Jim Cochrane <jtc@dim.com> writes:
>> 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.
> 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
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
> Jim Cochrane <jtc@dim.com> writes: > >> 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 > > > 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, segm ent 0) failed: No such file or directory > > 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 > > 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
Jim Cochrane <jtc@dim.com> writes:
> 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