Consistent PITR backup

From: Thomas Karcher <thkarcher(at)gmx(dot)de>
To: pgsql-admin(at)postgresql(dot)org
Subject: Consistent PITR backup
Date: 2007-10-11 09:39:09
Message-ID: 20071011093908.GA7988@arche.mine.nu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Malm Paul 2007-10-11 09:59:09 reading a blob from a postggreSQL db
Previous Message Simon Riggs 2007-10-10 19:01:26 Re: Using rsync for base backups for PITR