Re: PostgreSQL 9.3 PITR from remote server failing

This line
scp: /bkp/wal/f%: No such file or directory
indicates that something's wrong with your recovery command, it should be
/bkp/wal/%f

2014/1/2 Kido Kouassi <jjkido(at)gmail(dot)com>

> Hello,
>
> I used WAL continuous Archiving to a remote backup server using the
> following command:
>
> archive_command ='scp %p pgu(at)backup_server:/bkp/wal/%f && ssh
> pgu(at)backup_server "gzip /bkp/wal/%f"'
>
> The wal is getting archived fine but the recovery fails when trying to
> restore using the following command:
>
> restore_command ='ssh pgu(at)backup_server "gunzip /bkp/wal%f" && scp
> pgu(at)backup_server:/bkp/wal/%f %p'
>
> The log file shows:
>
> < 2014-01-02 10:36:07.375 EST >LOG: database system was interrupted; last
> known up at 2013-12-31 13:09:55 EST
> < 2014-01-02 10:36:07.377 EST >LOG: creating missing WAL directory
> "pg_xlog/archive_status"
> < 2014-01-02 10:36:07.379 EST >LOG: starting point-in-time recovery to
> 2013-12-31 13:15:00-05
> scp: /bkp/wal/f%: No such file or directory
> gzip: /bkp/wal/0000000200000000000000F7: unknown suffix -- ignored
> gzip: /bkp/wal/00000002.history: unknown suffix -- ignored
> < 2014-01-02 10:36:18.924 EST >LOG: invalid checkpoint record
> < 2014-01-02 10:36:18.924 EST >FATAL: could not locate required
> checkpoint record
> < 2014-01-02 10:36:18.924 EST >HINT: If you are not restoring from a
> backup, try removing the file "/var/lib/pgsql/9.3/data/backup_label".
> < 2014-01-02 10:36:18.930 EST >LOG: startup process (PID 30246) exited
> with exit code 1
> < 2014-01-02 10:36:18.930 EST >LOG: aborting startup due to startup
> process failure
>
> Here is the content of backup_server:/bkp/wal/ after I run the recovery. 00000002.history
> was 00000002.history.gz before.
>
> [pgu(at)backup_server ~]$ ls -l /bkp/wal/
> total 18456
> -rw-------. 1 pgu pgu 229 Dec 31 11:59
> 00000001000000000000000B.00000028.backup.gz
> -rw-------. 1 pgu pgu 230 Dec 31 11:59
> 0000000200000000000000D4.00000028.backup.gz
> -rw-------. 1 pgu pgu 16777216 Dec 31 13:10 0000000200000000000000F7
> -rw-------. 1 pgu pgu 231 Dec 31 13:10
> 0000000200000000000000F7.00000028.backup.gz
> -rw-------. 1 pgu pgu 37896 Dec 31 13:12 0000000200000000000000F8.gz
> -rw-------. 1 pgu pgu 16468 Dec 31 13:16 0000000200000000000000F9.gz
> -rw-------. 1 pgu pgu 2000673 Dec 31 13:20 0000000200000000000000FA.gz
> -rw-------. 1 pgu pgu 37598 Dec 31 13:26 0000000200000000000000FB.gz
> -rw-------. 1 pgu pgu 50 Dec 31 16:43 00000002.history
>
>
>
> restoring from the local server works consistently using:
>
> restore_command ='gunzip /bkp/wal/%f && cp /bkp/wal/%f %p'
>
> Could you please help me with the appropriate restore command to recover
> from a remote server?
>
> Thank you,
>
> Kido Kouassi
>