From: Joshua Colson <joshua.colson@ination.com>
To: pgsql-admin@postgresql.org
Subject: Change "database system identifier" in pg_control?
Date: Thu, 14 Dec 2006 16:40:40 -0700
Message-ID: <1166139640.7183.27.camel@s60r.ination.com> (raw)
Hello everyone,
I'm trying to change the SYSID in pg_control (at least I think that is
where it is) due to a catastrophic backup system failure, combined with
a timely administrative "oops". Does anyone know how I might go about
doing that?
The back story, for those interested:
We lost *all* of our recent backups due to massive hardware failure. A
new database was added within the last couple of weeks and yesterday
morning one of our admins toasted it. We've got a pg_dumpall from after
the database was created and all of the archive logs since. I was trying
to recreate the whole system on another machine to attempt restoration
of the data. The first problem that I ran into was the checkpoint was
incorrectly set (due to the system being a new installation). I was able
to fix that (I think) with the pg_resetxlog command but a subsequent
recovery attempt yields the following errors in the log:
2006-12-14 22:42:09 MST LOG: restored log file
"000000010000002300000079" from archive
2006-12-14 22:42:09 MST LOG: WAL file is from different system
2006-12-14 22:42:09 MST DETAIL: WAL file SYSID is
4965793265546637307, pg_control SYSID is 5008561987249897439
2006-12-14 22:42:09 MST LOG: invalid primary checkpoint record
2006-12-14 22:42:09 MST LOG: invalid secondary checkpoint link in
control file
2006-12-14 22:42:09 MST PANIC: could not locate a valid checkpoint
record
2006-12-14 22:42:09 MST LOG: startup process (PID 15145) was
terminated by signal 6
2006-12-14 22:42:09 MST LOG: aborting startup due to startup process
failure
2006-12-14 22:42:09 MST LOG: logger shutting down
So I thought I would try to change the SYSID to 4965793265546637307 and
have another shot at it.
I'm not a PG guru so please let me know, is there any chance that what
I'm doing will be successful, or am I just wasting my time?
Thank you very much for any help you can provide!
--
Joshua Colson <joshua.colson@ination.com>
iNation, LLC
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: joshua.colson@ination.com
Subject: Re: Change "database system identifier" in pg_control?
In-Reply-To: <1166139640.7183.27.camel@s60r.ination.com>
References: <1166139640.7183.27.camel@s60r.ination.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox