Thread: restore_command ignored in recovery.conf on standby

Rene,

* Rene Romero Benavides (rene.romero.b@gmail.com) wrote:
> restore_command='/bin/tar -xzf /db/wal_archives/%f.tar.gz -C %p'
[...]
> I tested the restore_command replacing variables and it works. Any ideas on
> why it isn't being executed?

Are you sure that it isn't being executed and just immediately returning
'1' (meaning 'false'- aka, done with recovery)?

The -C option to tar is supposed to be "change directory" according to
the tar that I've got, and %p is the complete file name that PG wants
the WAL file to be copied to- it's not a directory (it's something like
pg_xlog/RECOVERY_WAL).

    Thanks,

        Stephen

* Rene Romero Benavides (rene.romero.b@gmail.com) wrote:
> Yep, I checked:
>
> [postgres@uxmal standby_node]$ /bin/tar -xzf
> /db/wal_archives/00000001000001ED000000FB.tar.gz -C
> /db/standby_node/pg_xlog/
> [postgres@uxmal standby_node]$ echo $?
> 0

Err, sure, but that isn't actually what is being passed via %p.  %p will
be something like 'pg_xlog/RECOVERY_WAL', as I said, which *won't* work
for your tar command, eg:

sfrost@tamriel:/home/sfrost> tar -xzf zz.tar.gz -C zz/zz
tar: zz/zz: Cannot open: No such file or directory
tar: Error is not recoverable: exiting now
sfrost@tamriel:/home/sfrost> echo $?
2

> [postgres@uxmal standby_node]$ ls /db/standby_node/pg_xlog/ | grep
> 00000001000001ED000000FB
> 00000001000001ED000000FB

Noooo, PG tells you via %p the *specific* filename to use, do not just
overwrite files in pg_xlog willy-nilly with a tar command.

    Thanks,

        Stephen

Rene,

* Rene Romero Benavides (rene.romero.b@gmail.com) wrote:
> What I did (I bet there's a better way) is this:
> restore_command='/db/standby_node/scripts/wal_restore.sh %f %p'
>
> # wal_restore.sh
> #!/bin/bash
> /bin/tar -xzf /db/wal_archives/$1.tar.gz -C /tmp
> cp /tmp/$1 $2
> rm /tmp/$1

You'll probably want to be more careful here- this script could exit
with 'success' (meaning zero) even if some of the above commands fail.
When writing reliable shell scripts, you really need to check the exit
status of each command.  Note that you can return a high-value (>128,
iirc) from your shell script to indicate 'permanent' failure while
trying to do WAL recovery and PG will give up and stop trying.

Is there any particular reason you're tar'ing up the WAL files in the
first place..?  It'd surely be easier if you simply gzip'd them and then
used something like 'zcat /path/to/wal/archive/%f.gz > %p'.

The other option, if you really want to keep them tar'd, would be to use
tar's -O option, eg:

tar -O -zxf /db/wal_archives/%f.tar.gz %f > %p

There is also a --transform option that you could pass to tar to change
the filenames.

> My best regards to Stephen Frost.

Thanks!

    Stephen

On Apr 13, 2014 7:30 PM, "Rene Romero Benavides" <rene.romero.b@gmail.com> wrote:
>
> - Base backup taken with 9.2.6 (via pg_basebackup command)
> - binaries updated to 9.2.8
> - set up the base backup to replicate from the master and archives, and started
> - the restore_command option is ignored, with the following message:
>
> 2014-04-13 21:07:21.386 CDT,,,22055,,534b42d7.5627,4,,2014-04-13 21:07:19 CDT,1/0,0,LOG,00000,"consistent recovery state reached at 1E6/F9FFE880",,,,,,,,"CheckRecoveryConsistency, xlog.c:7371",""
> 2014-04-13 21:07:21.387 CDT,,,22053,,534b42d6.5625,1,,2014-04-13 21:07:18 CDT,,0,LOG,00000,"database system is ready to accept read only connections",,,,,,,,"sigusr1_handler, postmaster.c:4261",""
>

Are you sure there is actually a problem? "Ready to accept read-only connections" doesn't mean recovery has ended.

Cheers,

Jeff