0

I had replication set up with a simple archive_command in postgresql.conf:

archive_command = 'rsync -a %p _postgresql@serverip:/var/postgresql/wal_archive/%f'

Which worked great for 2+ years until the replica went down. After the replica went down, the archive command failed repeatedly so pg_xlog files on master were never deleted and master ran out of disk space.

In my use case the replica is nice to have but not that important. However ensuring master is always available is very important, so I'd like to use an archive command that will sacrifice the replica before problems arise on master.

I currently went to the other extreme and changed the archive_command to:

archive_command = 'rsync -a %p _postgresql@serverip:/var/postgresql/wal_archive/%f || true'

But this doesn't seem ideal either since even a few seconds of network outage could cause replication to fail.

Ideally I'd like to change the archive_command to something that will try to copy the WAL file to the replica a certain number of times, and if that fails, give up and remove the pg_xlog file. Any suggestions for a new archive_command?

(postgres 10.5)

1

I'd recommend a slightly different approach. Instead of testing for number of failures because a large number might lead to low disk space, test for low disk space directly.

archive_command = 'rsync -a %p _postgresql@serverip:/var/postgresql/wal_archive/%f || (df -P /var/data/pg_wal|awk "NR==2 && \$4< 400000 {exit 0}; NR==2 {exit 1}")'

But be advised that rsync is a dangerous approach in the first place, because it doesn't guarantee a successful fsync of the file written to the remote side (and the directory that that file is in) before it returns success locally.

Or an entirely different approach: set your replica up as a streaming replica, rather than log shipping replica. Then set "wal_keep_segments" to a value a little bit less than the amount of space reserved for pg_wal. Note that this only works if the space is hard reserved for pg_wal, that is, no one can "borrow" some free space for other purposes, like temp files.

And of course, if your replica was down for a while and you didn't notice, then you probably need a monitoring solution as well.

  • It appears I had log shipping AND streaming replication set up at the same time. I changed archive_mode to off in postgresql.conf on primary and removed restore_command and archive_cleanup_command from recovery.conf on replica. I still might need to adjust wal_keep_segments. Current val is only 528 but I have 240+GB of space on the partition. – sheepdog Jan 16 at 4:48

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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