Обсуждение: Backup of shut down standby does not work?
Hi all, I have a master-slave configuration running the master with WAL archiving enabled and the slave in recovery mode reading back the WAL files from the master (as described in http://www.postgresql.org/docs/9.1/static/continuous-archiving.html#BACKUP-PITR-RECOVERY Now I am doing the following backup strategy: 1) Stop slave (fast shutdown). 2) Rsync to backup fileserver 3) Start slave. I just tried to recover a Backup which *failed* with the following errors: 2014-02-13 08:34:05 CET LOG: starting archive recovery 2014-02-13 08:34:05 CET LOG: incomplete startup packet cp: cannot stat `/var/postgres-wal/00000001000001E300000061': No such file or directory 2014-02-13 08:34:05 CET LOG: could not open file "pg_xlog/00000001000001E300000061" (log file 483, segment 97): No such file or directory 2014-02-13 08:34:05 CET LOG: invalid primary checkpoint record cp: cannot stat `/var/postgres-wal/00000001000001E300000060': No such file or directory 2014-02-13 08:34:05 CET LOG: could not open file "pg_xlog/00000001000001E300000060" (log file 483, segment 96): No such file or directory 2014-02-13 08:34:05 CET LOG: invalid secondary checkpoint record 2014-02-13 08:34:05 CET PANIC: could not locate a valid checkpoint record 2014-02-13 08:34:06 CET FATAL: the database system is starting up 2014-02-13 08:34:06 CET FATAL: the database system is starting up 2014-02-13 08:34:07 CET FATAL: the database system is starting up 2014-02-13 08:34:07 CET FATAL: the database system is starting up 2014-02-13 08:34:07 CET LOG: startup process (PID 16882) was terminated by signal 6: Aborted 2014-02-13 08:34:08 CET LOG: aborting startup due to startup process failure So it seems the server is missing the archived WAL files which are not in the backup. Some time ago there was a discussion about this (http://www.postgresql.org/message-id/CAFwQ8rd+oDkLG4KWkux=2jwNinBnuwhY8DH3W-_58MGO1m3=Lg@mail.gmail.com) and it turned out that the WAL archive is not needed, only files in pg_xlog which I *did* backup. So am I doing something wrong or are the WAL files really needed? Best, Juergen
Вложения
Hi, Do you have recovery.conf and archive command well defined?. Because restore command look for wall files located onslave inside of `/var/postgres-wal/. Dubravko -- Dubravko Sever Sektor za računalne sustave Sveučilište u Zagrebu, Sveučilišni računski centar (Srce), www.srce.unizg.hr Dubravko.Sever@srce.hr, tel: +385 1 616 5807, fax: +385 1 616 5559 > -----Original Message----- > From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin- > owner@postgresql.org] On Behalf Of Jürgen Fuchsberger > Sent: Thursday, February 13, 2014 9:38 AM > To: pgsql-admin@postgresql.org > Subject: [ADMIN] Backup of shut down standby does not work? > > Hi all, > > I have a master-slave configuration running the master with WAL archiving > enabled and the slave in recovery mode reading back the WAL files from the > master (as described in > http://www.postgresql.org/docs/9.1/static/continuous- > archiving.html#BACKUP-PITR-RECOVERY > > Now I am doing the following backup strategy: > > 1) Stop slave (fast shutdown). > 2) Rsync to backup fileserver > 3) Start slave. > > I just tried to recover a Backup which *failed* with the following errors: > > 2014-02-13 08:34:05 CET LOG: starting archive recovery > 2014-02-13 08:34:05 CET LOG: incomplete startup packet > cp: cannot stat `/var/postgres-wal/00000001000001E300000061': No such file > or directory > 2014-02-13 08:34:05 CET LOG: could not open file > "pg_xlog/00000001000001E300000061" (log file 483, segment 97): No such file > or directory > 2014-02-13 08:34:05 CET LOG: invalid primary checkpoint record > cp: cannot stat `/var/postgres-wal/00000001000001E300000060': No such file > or directory > 2014-02-13 08:34:05 CET LOG: could not open file > "pg_xlog/00000001000001E300000060" (log file 483, segment 96): No such file > or directory > 2014-02-13 08:34:05 CET LOG: invalid secondary checkpoint record > 2014-02-13 08:34:05 CET PANIC: could not locate a valid checkpoint record > 2014-02-13 08:34:06 CET FATAL: the database system is starting up > 2014-02-13 08:34:06 CET FATAL: the database system is starting up > 2014-02-13 08:34:07 CET FATAL: the database system is starting up > 2014-02-13 08:34:07 CET FATAL: the database system is starting up > 2014-02-13 08:34:07 CET LOG: startup process (PID 16882) was terminated by > signal 6: Aborted > 2014-02-13 08:34:08 CET LOG: aborting startup due to startup process failure > > So it seems the server is missing the archived WAL files which are not in the > backup. Some time ago there was a discussion about this > (http://www.postgresql.org/message- > id/CAFwQ8rd+oDkLG4KWkux=2jwNinBnuwhY8DH3W- > _58MGO1m3=Lg@mail.gmail.com) > and it turned out that the WAL archive is not needed, only files in pg_xlog > which I *did* backup. > > So am I doing something wrong or are the WAL files really needed? > > Best, > Juergen
On Feb 13, 2014, at 1:37 AM, Jürgen Fuchsberger <juergen.fuchsberger@uni-graz.at> wrote: > So am I doing something wrong or are the WAL files really needed? If you're backing up a live server, and there is activity during the backup... You're missing step 1.5: select pg_start_backup('whatever') on the server. -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice
On 02/13/2014 03:01 PM, Scott Ribe wrote: > On Feb 13, 2014, at 1:37 AM, Jürgen Fuchsberger <juergen.fuchsberger@uni-graz.at> wrote: > >> So am I doing something wrong or are the WAL files really needed? > > If you're backing up a live server, and there is activity during the backup... I know, but I am backing up a shut down server. Juergen > > You're missing step 1.5: select pg_start_backup('whatever') on the server. >
Вложения
On 02/13/2014 10:07 AM, Dubravko Sever wrote: > Hi, > > Do you have recovery.conf and archive command well defined?. Because restore command look for wall files locatedon slave inside of `/var/postgres-wal/. > > Dubravko > Yes, the config is correct. The only problem is the missing files in /var/postgres-wal (which I expected not to be needed). I think it should work without the recovery.conf too (since I expect the backup to be consistent) , which results in this error: 2014-02-13 11:14:49 CET LOG: incomplete startup packet 2014-02-13 11:14:49 CET LOG: database system was shut down in recovery at 2013-12-25 18:00:03 CET 2014-02-13 11:14:49 CET LOG: could not open file "pg_xlog/00000001000001E300000061" (log file 483, segment 97): No such file or directory 2014-02-13 11:14:49 CET LOG: invalid primary checkpoint record 2014-02-13 11:14:49 CET LOG: could not open file "pg_xlog/00000001000001E300000060" (log file 483, segment 96): No such file or directory 2014-02-13 11:14:49 CET LOG: invalid secondary checkpoint record 2014-02-13 11:14:49 CET PANIC: could not locate a valid checkpoint record 2014-02-13 11:14:49 CET FATAL: the database system is starting up 2014-02-13 11:14:50 CET FATAL: the database system is starting up 2014-02-13 11:14:53 CET FATAL: the database system is starting up 2014-02-13 11:14:53 CET FATAL: the database system is starting up 2014-02-13 11:14:54 CET LOG: startup process (PID 8277) was terminated by signal 6: Aborted 2014-02-13 11:14:54 CET LOG: aborting startup due to startup process failure Juergen.