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