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

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. +