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)
Best Answer
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.
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.