Hi, On Fri, Jun 5, 2009 at 4:18 PM, Yoshinori Sano <yoshinori.s...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers