Dear list,
in the documentation
(http://www.postgresql.org/docs/8.2/interactive/continuous-archiving.html)
is written that after doing the filesystem based backup, I have to
invoke pg_stop_backup() which triggers an xlog switch, and in order to
have a consistent backup, I need to have the switched WAL file included
in my backup.
Unfortunately, there is a little problem for me with that: The
archive_command isn't actually doing the backup but just copying the WAL
files to a specific directory on the database server. The actual backup
then comes later and fetches them to the backup server. It is not wished
that there is a more or less persistent connection to the backup server
and that PG itself is doing the actual backup.
So when I'm doing a full PITR backup, I have to include the last WAL
file that was switched in pg_stop_backup(), but the archiver is as far
as I understood more or less asynchronous, so I can't easily be sure
that the archiver really copied the last WAL file to my archive
directory.
I had a look into the source code and found an undocumented (I suppose
...) feature: The xlog switch creates a xxxx.ready file in the
pg_xlog/archive_status directory, and as soon as the archiver finds a
file like this, it copies the corresponding WAL file to the archive
directory.
My idea is now: When doing a consistent PITR backup, extend the
documented steps between 4 (pg_stop_backup()) and 5 (wait for archived
WAL file) with a busy wait on pg_xlog/archive_status/*.ready files - as
soon as no .ready files are there anymore, the archiver has done its
work, and I can safely backup the archived WAL files in order to have a
really consistent backup.
Is that right? Or is there an easier way to do it?
Thanks & best regards,
Thomas