pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL 9.3 PITR from remote server failing
4+ messages / 3 participants
[nested] [flat]
* PostgreSQL 9.3 PITR from remote server failing
@ 2014-01-02 16:04 Kido Kouassi <jjkido@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Kido Kouassi @ 2014-01-02 16:04 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PostgreSQL 9.3 PITR from remote server failing
@ 2014-01-02 16:25 Rene Romero Benavides <rromero@omnitracs.mx>
parent: Kido Kouassi <jjkido@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Rene Romero Benavides @ 2014-01-02 16:25 UTC (permalink / raw)
To: Kido Kouassi <jjkido@gmail.com>; +Cc: pgsql-admin
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PostgreSQL 9.3 PITR from remote server failing
@ 2014-01-02 16:52 David Johnston <polobo@yahoo.com>
parent: Kido Kouassi <jjkido@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: David Johnston @ 2014-01-02 16:52 UTC (permalink / raw)
To: pgsql-admin
Kido Kouassi wrote
> restore_command ='ssh pgu@backup_server "gunzip /bkp/wal%f" && scp
> pgu@backup_server:/bkp/wal/%f %p'
>
> scp: /bkp/wal/f%: No such file or directory
typo? try: "gunzip /bkp/wal/%f" (note the added "/" after 'wal')
Also the 'scp' error indicates "/bkp/wal/f%" but you claim you are using
"/bkp/wal/%f" - which would substitute the %f with an actual file instead of
passing around the literal 'f%'. Likely the error is correct and you have a
typo there as well.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-9-3-PITR-from-remote-server-failing-tp5785074p578...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PostgreSQL 9.3 PITR from remote server failing
@ 2014-01-02 17:13 Kido Kouassi <jjkido@gmail.com>
parent: Rene Romero Benavides <rromero@omnitracs.mx>
0 siblings, 0 replies; 4+ messages in thread
From: Kido Kouassi @ 2014-01-02 17:13 UTC (permalink / raw)
To: Rene Romero Benavides <rromero@omnitracs.mx>; +Cc: pgsql-admin
Thank you so much for your patience;
I had a typo in my restore command.
On Thu, Jan 2, 2014 at 11:25 AM, Rene Romero Benavides <rromero@omnitracs.mx
> wrote:
> 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
>>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2014-01-02 17:13 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-01-02 16:04 PostgreSQL 9.3 PITR from remote server failing Kido Kouassi <jjkido@gmail.com>
2014-01-02 16:25 ` Rene Romero Benavides <rromero@omnitracs.mx>
2014-01-02 17:13 ` Kido Kouassi <jjkido@gmail.com>
2014-01-02 16:52 ` David Johnston <polobo@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox