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@gmail.com>

Hello,

I used WAL continuous Archiving to a remote backup server using the following command:

archive_command ='scp %p pgu@backup_server:/bkp/wal/%f && ssh pgu@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@backup_server "gunzip /bkp/wal%f" && scp pgu@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@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