From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200604180336.k3I3aVA20637@candle.pha.pa.us>
Subject: Re: Proposed doc-patch: Identifying the Current WAL file
In-Reply-To: <20060417141811.GD4474@ns.snowman.net>
To: Stephen Frost <sfrost@snowman.net>
Date: Mon, 17 Apr 2006 23:36:31 -0400 (EDT)
CC: Tom Lane <tgl@sss.pgh.pa.us>, Richard Huxton <dev@archonet.com>,
	pgsql-docs@postgresql.org
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII

Stephen Frost wrote:
-- Start of PGP signed section.
> * Bruce Momjian (pgman@candle.pha.pa.us) wrote:
> > In the first case, x2 is current, having be just switched to from x1,
> > while in the second case, x1 is current.  In BSD, you can use ls -ltT to
> > see the seconds, but in Linux it is something different, and I am sure
> > there are some operating systems that don't allow you to see the seconds
> > at all.  What general command-line solution can we propose for this
> > process?  And if we can't provide one, should we supply an SQL function
> > to return the current WAL name?
> 
> When we were looking into this we actually thought that it looked like
> multiple WALs were written to concurrently by the DB so we used what I
> suppose might have been something excessive- we just rsync the entire
> directory to a seperate area on the backup server.  Our setup is
> more-or-less like this:

Yep, doing the entire directory seems safest.

---------------------------------------------------------------------------


> Full backups:
> pg_start_backup
> Find the starting checkpoint and WAL from the backup_label
> rsync
> pg_stop_backup
> Find the stopping WAL from the .backup file (using the checkpoint and
> starting WAL to find the correct .backup file)
> Copy all the WALs between (inclusive) the starting WAL and stopping WAL,
> which still exist on the server, to the backup server (seems to be only
> one usually).
> Run a command on the backup server which finds all the WALs necessary
> for restoring the *backup* and copy them into a 'backup_wals' directory
> under the 'base' directory of the rsync'd backup.
> Run a command on the backup server which looks for the oldest 'base'
> backup (we rotate through three base backups), finds the starting WAL
> for that backup (from backup_label) and then deletes all WAL files in
> the 'archived_logs' directory which are before it.
> 
> WAL archival:
> scp the WAL from the server to the backup server into an 'archived_logs'
> directory outside of the base backup directories.  After a 'base' backup
> this will overwrite the partial log file on the backup server which was
> created immediately following the pg_stop_backup.
> 
> Partial WAL copying:
> Every 5 minutes rsync the entire pg_xlog directory to the backup
> server, into a 'pg_xlog_5min' directory that's outside the base backups.
> Since this is using rsync it only copies what has actually changed and
> hasn't seemed to be terribly expensive so far (then again, this is on a
> local gigabit network with some decent systems on both sides).
> 
> All comparisons are done in hex using bc.  Everything is implemented in
> shell scripts.
> 
> We then have three base backups which we rotate through weekly.  We also
> do tape backups of the most recent 'base' backup plus the archived_logs
> and pg_xlog_5min directories each night.
> 
> I'm guessing the reason this question has come up is that people would
> like to do the 'Partial WAL copying' of only the most recent WAL log?  I
> agree with the idea of having a function to find out the most recent
> WAL.  It'd also be really nice to be able to tell Postgres "please log
> even a partial WAL every 5 minutes, unless nothing has changed" or 
> similar.  I think one or both of those may be on the TODO.
> 
> I'd certainly like to know if anyone can see any problems with this
> setup or any reason it'd be less than perfect...  If this is a
> reasonable way to set things up then I could try to write up some docs
> outlining it as an example setup and/or provide the various shell
> scripts we use.
> 
> 	Thanks!
> 
> 		Stephen
-- End of PGP section, PGP failed!

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +