Re: File system level backup of shut down standby does not work?

On Mon, Feb 17, 2014 at 7:02 PM, Jürgen Fuchsberger <
juergen(dot)fuchsberger(at)uni-graz(dot)at> wrote:

> 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 ("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
getting created or not during fast shutdown.

Regards,
Hari Babu
Fujitsu Australia