Skip site navigation (1) Skip section navigation (2)

Re: Copying pg_control file

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Deepa K" <kdeepa(at)midascomm(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Copying pg_control file
Date: 2004-12-18 17:55:22
Message-ID: 2680.1103392522@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
"Deepa K" <kdeepa(at)midascomm(dot)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

In response to

pgsql-novice by date

Next:From: Tomka GergelyDate: 2004-12-18 21:38:09
Subject: version 8
Previous:From: George WeaverDate: 2004-12-18 11:43:20
Subject: Re: how to echo SQL commands in the output of a script

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group