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 ("Log-shipping standby" as described in http://www.postgresql.org/docs/9.1/static/warm-standby.html)
I take frequent backups of the standby server:
1) Stop standby server (fast shutdown). 2) Rsync to another fileserver 3) Start standby server.
I just tried to recover one of these backups which *failed* with the following errors:
2014-02-17 14:27:28 CET LOG: incomplete startup packet 2014-02-17 14:27:28 CET LOG: database system was shut down in recovery at 2013-12-25 18:00:03 CET 2014-02-17 14:27:28 CET LOG: could not open file "pg_xlog/00000001000001E300000061" (log file 483, segment 97): No such file or directory 2014-02-17 14:27:28 CET LOG: invalid primary checkpoint record 2014-02-17 14:27:28 CET LOG: could not open file "pg_xlog/00000001000001E300000060" (log file 483, segment 96): No such file or directory 2014-02-17 14:27:28 CET LOG: invalid secondary checkpoint record 2014-02-17 14:27:28 CET PANIC: could not locate a valid checkpoint record 2014-02-17 14:27:29 CET FATAL: the database system is starting up 2014-02-17 14:27:29 CET FATAL: the database system is starting up 2014-02-17 14:27:30 CET FATAL: the database system is starting up 2014-02-17 14:27:30 CET FATAL: the database system is starting up 2014-02-17 14:27:31 CET FATAL: the database system is starting up 2014-02-17 14:27:31 CET FATAL: the database system is starting up 2014-02-17 14:27:32 CET FATAL: the database system is starting up 2014-02-17 14:27:33 CET FATAL: the database system is starting up 2014-02-17 14:27:33 CET FATAL: the database system is starting up 2014-02-17 14:27:33 CET LOG: startup process (PID 26186) was terminated by signal 6: Aborted 2014-02-17 14:27:33 CET LOG: aborting startup due to startup process failure
So it seems the server is missing some WAL files which are not in the backup? Or is it simply not possible to take a backup of a standby server in recovery?
From version 9.2, you can take backups from standby also using pg_basebackup utility.
Is the WAL file is present in archive folder? if yes, Did you provided the archive command in
recovery.conf file?
I am not sure what happened? During fast shutdown of standby it should create a restart point
for further replay of WAL. Can you please enable log_checkpoints guc and check whether any restart point is