Thread: Consistent PITR backup

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

Thomas Karcher <thkarcher@gmx.de> writes:
> 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.

If the database is busy, you might never see an instant where there are
no .ready files.  Certainly it's possible that this approach will
archive many more segments than you need.

> Is that right? Or is there an easier way to do it?

I believe the intended way to do this is to look at pg_stop_backup's
return value to determine the last segment you need to archive.

            regards, tom lane

Hi Tom,

> > 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.
> If the database is busy, you might never see an instant where there are
> no .ready files.  Certainly it's possible that this approach will
> archive many more segments than you need.

I see - but the risk for never seeing an instant without .ready files is
relatively slow, I hope. And compared to the risk of not having a
consistent backup, I could live with having backed up more WAL files
than I actually would have needed.

> > Is that right? Or is there an easier way to do it?
> I believe the intended way to do this is to look at pg_stop_backup's
> return value to determine the last segment you need to archive.

Thank you - I saw that, but I didn't have a clue about how to determine
the WAL filename in which this segment is to be found ...


Thank you & regards,
Thomas

Thomas Karcher <thkarcher@gmx.de> writes:
>> I believe the intended way to do this is to look at pg_stop_backup's
>> return value to determine the last segment you need to archive.

> Thank you - I saw that, but I didn't have a clue about how to determine
> the WAL filename in which this segment is to be found ...

Well, the transformation is pretty trivial, but you can use
pg_xlogfile_name() to avoid getting your hands dirty.

            regards, tom lane

Hi Tom,

> >> I believe the intended way to do this is to look at pg_stop_backup's
> >> return value to determine the last segment you need to archive.
> > Thank you - I saw that, but I didn't have a clue about how to determine
> > the WAL filename in which this segment is to be found ...
> Well, the transformation is pretty trivial, but you can use
> pg_xlogfile_name() to avoid getting your hands dirty.

That's what I was looking for! Thank you!


Best regards from Germany,
Thomas

On Thu, 2007-10-11 at 16:37 +0200, Thomas Karcher wrote:
> Hi Tom,
>
> > > 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.
> > If the database is busy, you might never see an instant where there are
> > no .ready files.  Certainly it's possible that this approach will
> > archive many more segments than you need.
>
> I see - but the risk for never seeing an instant without .ready files is
> relatively slow, I hope. And compared to the risk of not having a
> consistent backup, I could live with having backed up more WAL files
> than I actually would have needed.

There will be gaps, otherwise the archiver will never catch up over
time, but its hard to say when those might be.

> > > Is that right? Or is there an easier way to do it?
> > I believe the intended way to do this is to look at pg_stop_backup's
> > return value to determine the last segment you need to archive.
>
> Thank you - I saw that, but I didn't have a clue about how to determine
> the WAL filename in which this segment is to be found ...

Look at pg_stop_backup() in the docs.

--
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com