Обсуждение: Postrgres data restoration problem

Поиск
Список
Период
Сортировка

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

Re: Postrgres data restoration problem

От
Sai Hertz And Control Systems
Дата:
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



Re: Postrgres data restoration problem

От
Tom Lane
Дата:
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

Re: Postrgres data restoration problem

От
Jim Cochrane
Дата:
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.]

Re: Postrgres data restoration problem

От
Jim Cochrane
Дата:
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

Re: Postrgres data restoration problem

От
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.

> 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

Re: Postrgres data restoration problem

От
Jim Cochrane
Дата:
> 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

Re: Postrgres data restoration problem

От
Tom Lane
Дата:
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