Обсуждение: PostgreSQL 9.2 archiving last replayed WAL after recovery
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
Dear Daniel Blanco, I think you copied same postgres.conf from master to standby, right? if so please remove the following setting from standby before starting it wal_level = archive archive_mode = on archive_command = 'test ! -f /.../archive/%f && cp %p /.../archive/%f' hope it will work :D Regards, Amul Sul -- View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-9-2-archiving-last-replayed-WAL-after-recovery-tp5763207p5764173.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
Hi Amul,
No, I didn't copy my postgres.conf from master to standby. This is always working on a single instance, archiving WAL files to be able to achieve backup and recovery.
The problem arises after recovery. We go back to a certain snapshot (created between pg_start_backup and pg_stop_backup as advised) and then start replaying logs. It reaches a consistent state after the pg_stop_backup point and all the rest of the logs are replayed, and recovery is successful. But after that, when the database starts up and starts recycling WAL files, it always archives the last replayed one, although that file was already archived and has changed because another timeline is started.
For the moment, I solved the problem modifying the archive_command to 'test -f /.../archive/%f || cp %p /.../archive/%f' so if the file exists it doesn't archive it. But that is not the recommended procedure in the documentation, where it says that a the archive command should fail if a fail is tried to be archived twice.
Cheers,
Daniel
On 18 July 2013 04:06, amulsul <sul_amul@yahoo.co.in> wrote:
Dear Daniel Blanco,
I think you copied same postgres.conf from master to standby, right?
if so please remove the following setting from standby before starting ithope it will work :D
wal_level = archive
archive_mode = on
archive_command = 'test ! -f /.../archive/%f && cp %p /.../archive/%f'
Regards,
Amul Sul
--
View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-9-2-archiving-last-replayed-WAL-after-recovery-tp5763207p5764173.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Sorry, in the second paragraph I meant to say "it always archives the last replayed one, although that file was already archived and has not changed because another timeline is started."
Daniel
On 18 July 2013 09:58, Daniel Gomez Blanco <nanodgb@gmail.com> wrote:
Hi Amul,No, I didn't copy my postgres.conf from master to standby. This is always working on a single instance, archiving WAL files to be able to achieve backup and recovery.The problem arises after recovery. We go back to a certain snapshot (created between pg_start_backup and pg_stop_backup as advised) and then start replaying logs. It reaches a consistent state after the pg_stop_backup point and all the rest of the logs are replayed, and recovery is successful. But after that, when the database starts up and starts recycling WAL files, it always archives the last replayed one, although that file was already archived and has changed because another timeline is started.For the moment, I solved the problem modifying the archive_command to 'test -f /.../archive/%f || cp %p /.../archive/%f' so if the file exists it doesn't archive it. But that is not the recommended procedure in the documentation, where it says that a the archive command should fail if a fail is tried to be archived twice.Cheers,DanielOn 18 July 2013 04:06, amulsul <sul_amul@yahoo.co.in> wrote:Dear Daniel Blanco,
I think you copied same postgres.conf from master to standby, right?
if so please remove the following setting from standby before starting ithope it will work :D
wal_level = archive
archive_mode = on
archive_command = 'test ! -f /.../archive/%f && cp %p /.../archive/%f'
Regards,
Amul Sul
--
View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-9-2-archiving-last-replayed-WAL-after-recovery-tp5763207p5764173.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice