X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id DAA00D8F98 for ; Sun, 2 Oct 2005 06:44:39 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 23028-09 for ; Sun, 2 Oct 2005 09:44:36 +0000 (GMT) Received: from smtp.nildram.co.uk (smtp.nildram.co.uk [195.112.4.54]) by svr1.postgresql.org (Postfix) with ESMTP id 13280D8EF0 for ; Sun, 2 Oct 2005 06:44:36 -0300 (ADT) Received: from 192.168.0.3 (unknown [84.12.188.233]) by smtp.nildram.co.uk (Postfix) with ESMTP id 808C624FEA8; Sun, 2 Oct 2005 10:44:36 +0100 (BST) Subject: Re: archive_command From: Simon Riggs To: Jeff Frost Cc: pgsql-admin@postgresql.org In-Reply-To: References: <20031115132118.V74587@holywar.net> <433AC059.1050300@musicrebellion.com> <433ACE02.3040208@musicrebellion.com> <1128071627.19345.256.camel@localhost.localdomain> <1128113968.4045.32.camel@localhost.localdomain> Content-Type: text/plain Organization: 2nd Quadrant Date: Sun, 02 Oct 2005 10:44:55 +0100 Message-Id: <1128246295.3717.76.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.0.2 (2.0.2-3) Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.101 required=5 tests=[AWL=0.051, FORGED_RCVD_HELO=0.05] X-Spam-Level: X-Archive-Number: 200510/4 X-Sequence-Number: 19076 On Sat, 2005-10-01 at 21:43 -0700, Jeff Frost wrote: > On Fri, 30 Sep 2005, Simon Riggs wrote: > > > If I follow your example, yes. But that assumes there is only one > > timeline's WAL files in your pg_xlog. It could get more complex in that > > situation because you could be in any of the timelines. > > > > But, if you know which timeline you're in, then yes, the file after the > > latest file in archive_status is the current WAl file. > > > > It might be interesting to submit your script to put into contrib? > > So, actually, my script is probably doing things incorrectly at the moment. > What effect would the different timelines create? In my client's case there > is only one timeline, but I'd love to generalize the script so we can put it > in contrib, but first I'd like to make sure it actually works properly. :-) The initial 000....0001 prefix of the WAL filename is the timeline number. If you recover the database from backup, specifying a target before the end of WAL (with recovery_target_time or recovery_target_xid), then you will generate a new timeline. If you then recover the database *again* but this time specify a recovery_target_timeline less than the highest, *but* this time *don't* specify a recovery_target_time or recovery_target_xid then you'll end up with the current timeline being less than the highest timeline number. (All of that seeming complexity is absolutely necessary when you are trying to save your business data from the skip. Thank Tom for having the insight to create it.) It's possible that you would have files of the higher timeline numbers still in the directory, that might confuse your program. Probably the best idea is to backup the last WAL file for each timeline seen. Keep track of that, so when the current file changes you'll know which timeline you're on and stick to that from then on. Or more simply, put some notes with your program saying "if you ever use a complex recovery situation, make sure to clear all archive_status files for higher timeline ids before using this program". This would not be necessary had I completed my logswitch patch in time for 8.1 freeze, but I regret that I was unable to do that. Best Regards, Simon Riggs