Thread: Simple, safe hot backup and recovery
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? Thanks in advance, -- Yoshinori Sano <yoshinori.sano@gmail.com>
Attachment
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
Attachment
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