Re: Simple, safe hot backup and recovery

Hi,

On Fri, Jun 5, 2009 at 4:18 PM, Yoshinori Sano <yoshinori(dot)sano(at)gmail(dot)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