Re: PITR Base Backup on an idle 8.1 server

Greg Smith wrote:
[...]
> -Find something harmless I can execute in a loop that will generate WAL
> activity, run that until the segment gets archived.  Haven't really
> thought of something good to use for that purpose yet.

Some time ago I started a thread about taking on-the-fly backups at file
level on idle servers. Problem was much the same of yours. After posting
that, I'm doing some research on my own (in spare time) now.

Currently, I'm using the following procedure:

1) create a backup lockfile
2) issue pg_start_backup()
3) tar the data directory, excluding pg_xlog
4) issue pg_stop_backup()
5) tar pg_xlog
6) remove the lockfile

Meanwhile, a "fake" WAL archiving is active, which does pretty nothing.

archive_command = 'test ! -f /var/lib/pgsql/backup_lock </dev/null'

Under normal condition (no backup running) this will trick PG into
thinking that segments get archived. If I'm not mistaken, PG should
behave exactly as if no archive_command is configured, and recycle them
ASAP. This saves me the burden of taking care of the archiving at all.

Should a WAL segment fill up during the backup (unlikely as it is, since
the system is mostly idle AND the tar completes withing a minute - but
it's still possible), the test command would report failure in archiving
the segment, and PG would keep it around in pg_xlog, ready to be tar'ed
  at step 5 (mind you - this is speculation since I had no time to
actually test it).

So it ends up with two tar archives: one is the datafiles backup, the
other the wal segments. As an optimization, I should exclude WAL
segments older that the lockfile in step 5), since I know they are older
than the backup.

What I really should do now is kill -STOP the tar at step 3), start some
big write activity and see what exaclty happens to the WAL segment when
it fills up and PG tries to archive it.

Restore would be done the usual way, extracting both the archives, maybe
adding WAL segments from the crashed pg_xlog. Whether I need to
configure a fake restore command I have still to find out.

Hope it helps,
.TM.