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

* 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:

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