Simple, safe hot backup and recovery

Hi,

On Fri, Jun 5, 2009 at 4:18 PM, Yoshinori Sano <yoshinori.sano@gmail.com> wrote:

Hi, all

I posted this message to the pgsql-general mailing list, however there was
no response. So, I repost the mail to pgsql-hackers.

I want to achieve a simple, safe hot backup and recovery using PostgreSQL 8.3
or later.

The standalone hot backup script listed in "24.3.5.1. Standalone hot backups"
(http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html)
seems to be very helpful to me because it's simple and it matches my needs.
I don't need the timeline feature provided by PITR.  However, the recovery
procedure is somewhat complex, as the documentation shows.  So, I want to
rely on the PostgreSQL's crush recovery mechanism.  Is this a bad idea?

I wrote a prototype script for that reason.  The script's first part is based
on the standalone hot backup script taken from the documentation.  The last part
is my idea. The archived WAL segment files are stored into the backup's pg_xlog/
and remake the backup file.  The script works for me, but I want to know whether
this approach is really safe or not.  If it's not safe, I want to know
the reason.

Anybody has good idea? Is there another solution?

A crash recovery from standalone hot backup might not redo the latest
transaction (generated after backup). It seems to be only guaranteed that
a database is recovered up to the state just after pg_stop_backup.

Does this meet your requirements?

psql $DB_NAME -c "SELECT pg_stop_backup();"
sleep 10 # Why we need this?
rm /var/lib/pgsql/backup_in_progress
tar -rf /var/lib/pgsql/backup.tar /var/lib/pgsql/archive/

Since all WAL files generated during backup have to be added into backup.tar,
I guess that "sleep 10" waits until they are archived.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

Hi,

On Fri, Jun 5, 2009 at 5:10 PM, Fujii Masao <masao.fujii@gmail.com> wrote:

psql $DB_NAME -c "SELECT pg_stop_backup();"
sleep 10 # Why we need this?
rm /var/lib/pgsql/backup_in_progress
tar -rf /var/lib/pgsql/backup.tar /var/lib/pgsql/archive/

Since all WAL files generated during backup have to be added into backup.tar,
I guess that "sleep 10" waits until they are archived.

In v8.4, pg_stop_backup waits until all WAL files used during backup
are archived.
So, "sleep" is already unnecessary for standalone hot backup.

Here is one-line patch to get rid of the description of that needless
operation from
backup.sgml.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

Fujii Masao wrote:

Hi,

On Fri, Jun 5, 2009 at 5:10 PM, Fujii Masao <masao.fujii@gmail.com> wrote:

psql $DB_NAME -c "SELECT pg_stop_backup();"
sleep 10 # Why we need this?
rm /var/lib/pgsql/backup_in_progress
tar -rf /var/lib/pgsql/backup.tar /var/lib/pgsql/archive/

Since all WAL files generated during backup have to be added into backup.tar,
I guess that "sleep 10" waits until they are archived.

In v8.4, pg_stop_backup waits until all WAL files used during backup
are archived.
So, "sleep" is already unnecessary for standalone hot backup.

Here is one-line patch to get rid of the description of that needless
operation from
backup.sgml.

Good point, applied. Thanks.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Hi Sano-san,

On Fri, Jun 5, 2009 at 7:02 PM, Yoshinori Sano<yoshinori.sano@gmail.com> wrote:

In v8.4, pg_stop_backup waits until all WAL files used during backup
are archived.
So, "sleep" is already unnecessary for standalone hot backup.

Oh, it's a great news!  We don't need to use the unsafe approach (the
sleep command) anymore if we use v8.4, do we?

Yes in upcoming v8.4. Of course, in or before v8.3, you still need to
make up the safe mechanism. For that, XLogArchiveIsBusy() function
which was added in v8.4 may be a good reference to you.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center