Re: Copying pg_control file - Mailing list pgsql-novice

From Tom Lane
Subject Re: Copying pg_control file
Date
Msg-id 2680.1103392522@sss.pgh.pa.us
Whole thread Raw
In response to Re: Copying pg_control file  ("Deepa K" <kdeepa@midascomm.com>)
List pgsql-novice
"Deepa K" <kdeepa@midascomm.com> writes:
> I am running postgresql 7.1.3 on Red Hat Linux 7.2. pg_control file get
> corrupted on abrupt PC reboot. Can I copy the pg_control file of another
> PC (in which also postgresql 7.1.3 is running on Red Hat Linux 7.2)
> here??

It'd destroy your database.  Don't even think about it.

What you're probably going to have to do to recover your data is run
pg_resetxlog.  The tricky part is that pg_resetxlog normally gets some
info it needs (like the next transaction ID to use) from pg_control.
You'll have to work out the correct values by hand and supply them as
switches to pg_resetxlog.  What I'd suggest for 7.1 is:

1. Look at the pg_log file ($PGDATA/global/1269).  Multiply its size
in bytes by 4, and use that as the next transaction ID.

2. Look in the $PGDATA/pg_xlog directory, and note the file with the
highest numeric filename.  Use that plus one as the next WAL position.

3. Once you've managed to get into the database, immediately do a
pg_dumpall, re-initdb, reload.  Don't trust that the database hasn't
been corrupted.  (You might want to think about updating to a PG version
less stone-age than 7.1, while you're at it.)

[ looks back at 7.1 ... ]  Um, wait a minute.  pg_resetxlog back then
didn't have any -x or -l option, so there wasn't any way to tell it what
to use.  Oh dear.  If you're a C programmer you could easily modify
pg_resetxlog.c to add those switches (you could look at 7.2 or later for
inspiration, but don't simply copy the whole program from a later
version, because there are other changes you do NOT want).  Or, since
this is a one-shot use, just hardwire in the values you determined in
steps 1 and 2.

If you're not a C programmer then I'm afraid you're a bit hosed.  How
old is your last backup?

            regards, tom lane

pgsql-novice by date:

Previous
From: George Weaver
Date:
Subject: Re: how to echo SQL commands in the output of a script
Next
From: Tomka Gergely
Date:
Subject: version 8