Skip site navigation (1) Skip section navigation (2)

Re: Proposed doc-patch: Identifying the Current WAL file

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Stephen Frost <sfrost(at)snowman(dot)net>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Richard Huxton <dev(at)archonet(dot)com>, pgsql-docs(at)postgresql(dot)org
Subject: Re: Proposed doc-patch: Identifying the Current WAL file
Date: 2006-04-18 03:36:31
Message-ID: 200604180336.k3I3aVA20637@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-docspgsql-patches
Stephen Frost wrote:
-- Start of PGP signed section.
> * Bruce Momjian (pgman(at)candle(dot)pha(dot)pa(dot)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. +

In response to

pgsql-docs by date

Next:From: Alvaro HerreraDate: 2006-04-18 03:45:24
Subject: Re: Proposed doc-patch: Identifying the Current WAL file
Previous:From: Stephen FrostDate: 2006-04-17 14:18:11
Subject: Re: Proposed doc-patch: Identifying the Current WAL file

pgsql-patches by date

Next:From: Bruce MomjianDate: 2006-04-18 03:40:33
Subject: Re: schema-qualified SET CONSTRAINTS
Previous:From: Qingqing ZhouDate: 2006-04-18 01:54:08
Subject: Re: [Win32] Problem with rename()

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group