1

I am seeing many pg_log files full of following errors

2019-07-08 00:04:06 CEST DETAIL:  The failed archive command was: copy "pg_xlog\000000010000002100000025" "D:/Programme/OmniBack/server/db80/pg/pg_xlog_archive/000000010000002100000025"
2019-07-08 00:04:07 CEST LOG:  archive command failed with exit code 1
2019-07-08 00:04:07 CEST DETAIL:  The failed archive command was: copy "pg_xlog\000000010000002100000025" "D:/Programme/OmniBack/server/db80/pg/pg_xlog_archive/000000010000002100000025"
2019-07-08 00:04:07 CEST WARNING:  transaction log file "000000010000002100000025" could not be archived: too many failures
2019-07-08 00:05:07 CEST LOG:  archive command failed with exit code 1**

postgres.conf having settings

checkpoint_segments = 256
wal_buffers = 256MB
wal_level = archive
archive_mode = on
port = 7112
work_mem = 128MB
shared_buffers = 512MB
effective_cache_size = 32719MB
listen_addresses = '*'
logging_collector = on
log_line_prefix = '%t '
archive_command = 'copy "%p" "D:/Programme/OmniBack/server/db80/pg/pg_xlog_archive/%f"'**

I have checked pg_xlog folder for this file. This file is not present in pg_xlog but in pg_xlog\archive_status with following permissions

pg/pg_xlog\archive_status\000000010000002100000025.ready 

KDLF\adminkdlf:(I)(F)
VORDEFINIERT\Administratoren:(I)(F)
NT-AUTORITŽT\SYSTEM:(I)(F)
VORDEFINIERT\Benutzer:(I)(RX)

My disk is getting full. Please tell why this issue is coming because of this others logs are still not archived. I am not sure if I am missing something here.

Improve this question
3

If the file is not in pg_xlog, but there is a .ready file in archive_status, then somebody must have removed that file, which is why archival is failing.

Temporarily set archive_command = '/bin/true' until the view pg_stat_archiver indicates that PostgreSQL is happy again, then set the parameter back to its original value.

Perform a new base backup immediately after that.

You won't be able to recover across the point where you have no WAL archives.

Try to find out which of your administrators is stupid enough to delete files from the data directory and send him or her to a PostgreSQL course.

Improve this answer
  • I think now Standby will not sync up to the point due to archive failed and /bin/true parameter change. is it right? – Adam Mulla Jul 30 '20 at 11:57
  • @AdamMulla Streaming replication is independent of WAL archiving, so I don't see how that should affect a streaming replication standby. – Laurenz Albe Aug 10 '20 at 5:59
  • you are correct technical point of view. what if i want to use that all archives to apply on standby without streaming or for point in time recovery? – Adam Mulla Aug 10 '20 at 12:47
  • Of course, if you don't use streaming, but WAL shipping via archive_command, the standby will be broken and has to be rebuilt. – Laurenz Albe Aug 10 '20 at 13:31
  • That means, If we are using archive_command and doing scp to standby without streaming replication, and we changed archive_command = '/bin/true' meanwhile due to some issues like above , post this standby must be rebuild right? it will not sync with prod. – Adam Mulla Aug 10 '20 at 13:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.