Re: PITR Base Backup on an idle 8.1 server

On Thu, 31 May 2007, Marco Colombo wrote:

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

That's correct. I don't think you even need the </dev/null in that
command.

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

That's also correct. What you're doing will work for getting a useful
backup.

However, recognize the limitations of the approach: this is a clever way
to make a file-system level snapshot of your database without involving
the archive logging process. You'll get a good backup at that point, but
it won't provide you with any ability to do roll-forward recovery if the
database gets screwed up in the middle of the day. Since that's a
requirement of most PITR setups, I'm not sure your workaround accomplishes
what you really want. More on why that is below.

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

This won't work, and resolving it will require going to grips with the
full archive logging mechanism rather than working around it the way you
suggest above.

Every time the server hits a checkpoint, it recycles old WAL
segments--renames them and then overwrites them with new data. The first
time your database hits a checkpoint after your backup is done, you will
have lost segment files such that it's impossible to recover the current
state of the database anymore. You'll have the first part of the series
(from the base backup), the last ones (from the current pg_xlog), but will
be missing some number in the middle (the recycled files).

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD