pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Simon Riggs <simon@2ndquadrant.com>
To: Jeff Frost <jeff@frostconsultingllc.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: archive_command
Date: Tue, 04 Oct 2005 11:09:30 +0100
Message-ID: <1128420570.8603.279.camel@localhost.localdomain> (raw)
In-Reply-To: <Pine.LNX.4.63.0510031950290.10454@discord.dyndns.org>
References: <20031115132118.V74587@holywar.net>
	<433AC059.1050300@musicrebellion.com>
	<Pine.LNX.4.63.0509280925070.28080@discord.dyndns.org>
	<433ACE02.3040208@musicrebellion.com>
	<1128071627.19345.256.camel@localhost.localdomain>
	<Pine.LNX.4.63.0509300926340.1280@discord.dyndns.org>
	<1128113968.4045.32.camel@localhost.localdomain>
	<Pine.LNX.4.63.0510012142210.13614@discord.dyndns.org>
	<1128246295.3717.76.camel@localhost.localdomain>
	<Pine.LNX.4.63.0510031950290.10454@discord.dyndns.org>

On Mon, 2005-10-03 at 20:00 -0700, Jeff Frost wrote:
> On Sun, 2 Oct 2005, Simon Riggs wrote:
> 
> > 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".
> 
> Tell me if you think this is the most reasonable way to determine the in use 
> WAL file:
> 
> ls -rt $PGDATA/pg_xlog/ | grep -v "backup\|archive\|RECOVERY" | tail -1

If you trust the times your filesystem hands you. PostgreSQL doesn't use
the file times it uses the sequential number naming, so for paranoid
accuracy, this should too so perhaps take the t off.

> > 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.
> 
> What's the logswitch patch going to accomplish? 

Nothing until I finish it. :-)

> Also, I have an interesting and unrelated question...this past weekend, we had 
> a failure in which we had to restore from backup.  I have created a script 
> which makes a base backup every two weeks and we keep the last two.  Also, we 
> use PITR for replication, making a base backup between the primary and 
> secondary server every 8 hours, and running my rsynclastlog script once/minute 
> to keep as up to date as possible.  

Sounds like a good setup.

> Now here's the problem...the servers 
> switched roles on Sep 21.  We switched them back a little while after that. 
> When I went to replay the WAL files using the Sep 15 base backup, it happily 
> played back the WAL files to Sep 21 and stopped.  I presume that this means 
> whenever the servers switch roles I need to create a fresh base backup?

Yes, but more generally if I lost one node I would always be inclined to
fully backup the remaining one just in case.

> Is there any possible way to replay the other WAL files after the Sep 21 
> switchover/switchback?  I'm going to guess this is similar to having another 
> timeline.

That may be your exact case, it depends upon how you did recovery. Both
systems think they are the same one, so you need to be careful.

Look for the last log file of the went-down node. That file should exist
twice, once as finally written by the went-down node and once as
continued to be written to by the stayed-up node after switchover. It
sounds like the wrong file was used to recover with and as a result
stopped recovery on Sep 21. But if you're running with the system now,
you'll need to do an intermediate rebuild and recover the data manually.

(I recommend testing recovery procedures before you go live and then at
least every 6 months, to ensure that they actually still work when you
need 'em. Trying to do a recovery with any confidence is not easy at 4am
on a Sunday morning under maximum stress, from experience.)

> We restored from a nightly pg_dump which we have kept doing, but that means we 
> lost about 20 hrs of data. :-(

But sounds recoverable.

Best Regards, Simon Riggs




view thread (17+ messages)  latest in thread

Message-ID: <1128420570.8603.279.camel@localhost.localdomain>
Permalink:  ../1128420570.8603.279.camel@localhost.localdomain/
Also on:    postgresql.org/message-id/1128420570.8603.279.camel@localhost.localdomain

 · 

reply

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: simon@2ndquadrant.com, jeff@frostconsultingllc.com
  Subject: Re: archive_command
  In-Reply-To: <1128420570.8603.279.camel@localhost.localdomain>
  References: <20031115132118.V74587@holywar.net> <433AC059.1050300@musicrebellion.com> <Pine.LNX.4.63.0509280925070.28080@discord.dyndns.org> <433ACE02.3040208@musicrebellion.com> <1128071627.19345.256.camel@localhost.localdomain> <Pine.LNX.4.63.0509300926340.1280@discord.dyndns.org> <1128113968.4045.32.camel@localhost.localdomain> <Pine.LNX.4.63.0510012142210.13614@discord.dyndns.org> <1128246295.3717.76.camel@localhost.localdomain> <Pine.LNX.4.63.0510031950290.10454@discord.dyndns.org> <1128420570.8603.279.camel@localhost.localdomain>

* 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