Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 3FB9B9FA2E0 for ; Thu, 14 Dec 2006 19:55:52 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 47706-02 for ; Thu, 14 Dec 2006 19:55:46 -0400 (AST) X-Greylist: delayed 00:15:00.472433 by SQLgrey-1.7.4 Received: from ination.com (ination-69-16-151-6.phx2.puregig.net [69.16.151.6]) by postgresql.org (Postfix) with ESMTP id 308C59FA19B for ; Thu, 14 Dec 2006 19:55:46 -0400 (AST) Received: from [65.118.181.3] (account joshua.colson HELO [192.168.8.103]) by ination.com (CommuniGate Pro SMTP 5.0.9) with ESMTPSA id 2472216 for pgsql-admin@postgresql.org; Thu, 14 Dec 2006 16:40:40 -0700 Subject: Change "database system identifier" in pg_control? From: Joshua Colson To: pgsql-admin@postgresql.org Content-Type: text/plain Organization: iNation, LLC Date: Thu, 14 Dec 2006 16:40:40 -0700 Message-Id: <1166139640.7183.27.camel@s60r.ination.com> Mime-Version: 1.0 X-Mailer: Evolution 2.4.0 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/206 X-Sequence-Number: 23998 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 iNation, LLC