pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Change "database system identifier" in pg_control?
2+ messages / 2 participants
[nested] [flat]

* Change "database system identifier" in pg_control?
@ 2006-12-14 23:40  Joshua Colson <joshua.colson@ination.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Joshua Colson @ 2006-12-14 23:40 UTC (permalink / raw)
  To: pgsql-admin

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




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Change "database system identifier" in pg_control?
@ 2006-12-15 00:10  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Joshua Colson <joshua.colson@ination.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2006-12-15 00:10 UTC (permalink / raw)
  To: Joshua Colson <joshua.colson@ination.com>; +Cc: pgsql-admin

Joshua Colson <joshua.colson@ination.com> writes:
> 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?

You'd have to modify pg_resetxlog to force that value into the sysid
rather than whatever is there.  Hand-editing wouldn't work very well
because of (a) the CRC on pg_control and (b) the need to copy the
value into extant WAL segments.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2006-12-15 00:10 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-12-14 23:40 Change "database system identifier" in pg_control? Joshua Colson <joshua.colson@ination.com>
2006-12-15 00:10 ` Tom Lane <tgl@sss.pgh.pa.us>

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