Hello,
Following the PostgreSQL 9.2 official documentation, I have set up
continuous archiving on my postgres.conf file:
wal_level = archive
archive_mode = on
archive_command = 'test ! -f /.../archive/%f && cp %p /.../archive/%f'
After a successful backup and recovery, the archive command fails when
the database is started because PostgreSQL is trying to archive the
last replayed WAL file, and obviously, it'd already been archived. My
recovery.conf file is the following:
restore_command = 'cp /.../archive/%f "%p"'
recovery_target_time = '2013-07-02 15:20:12'
And this is the error I'm getting when I try to start the database:
LOG: database system was interrupted; last known up at 2013-07-02 14:53:37 CEST
LOG: creating missing WAL directory "pg_xlog/archive_status"
LOG: starting point-in-time recovery to 2013-07-02 15:20:12+02
LOG: restored log file "000000010000000000000002" from archive
LOG: redo starts at 0/2000020
LOG: consistent recovery state reached at 0/20023D0
LOG: restored log file "000000010000000000000003" from archive
LOG: recovery stopping before commit of transaction 4063, time
2013-07-02 15:20:12.211559+02
LOG: redo done at 0/306D8B8
LOG: last completed transaction was at log time 2013-07-02 15:20:11.189978+02
cp: cannot stat `/.../archive/00000002.history': No such file or directory
LOG: selected new timeline ID: 2
cp: cannot stat `/.../archive/00000001.history': No such file or directory
LOG: archive recovery complete
LOG: autovacuum launcher started
LOG: database system is ready to accept connections
LOG: archive command failed with exit code 1
DETAIL: The failed archive command was: test ! -f
/.../archive/000000010000000000000003 && cp
pg_xlog/000000010000000000000003 /.../archive/000000010000000000000003
LOG: archive command failed with exit code 1
DETAIL: The failed archive command was: test ! -f
/.../archive/000000010000000000000003 && cp
pg_xlog/000000010000000000000003 /.../archive/000000010000000000000003
LOG: archive command failed with exit code 1
DETAIL: The failed archive command was: test ! -f
/.../archive/000000010000000000000003 && cp
pg_xlog/000000010000000000000003 /.../archive/000000010000000000000003
WARNING: transaction log file "000000010000000000000003" could not be
archived: too many failures
Is this an expected behaviour? Should the last replayed WAL file be
archived after recovery? If so, the archive command should not fail in
case the file is already archived (as mentioned in the documentation).
Cheers,
Daniel