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