From: Thomas Karcher <thkarcher@gmx.de>
To: pgsql-admin@postgresql.org
Subject: Consistent PITR backup
Date: Thu, 11 Oct 2007 11:39:09 +0200
Message-ID: <20071011093908.GA7988@arche.mine.nu> (raw)
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
Attachments:
signature.asc (application/pgp-signature, 188B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: thkarcher@gmx.de
Subject: Re: Consistent PITR backup
In-Reply-To: <20071011093908.GA7988@arche.mine.nu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox