Postgres isn't running the archive_command on my standby

This just came up on the Postgres mailing list, and I spent a long time figuring it out a few months ago, so maybe this blog post will make it a bit more Googleable.

The problem is you have a master archiving to a standby, but you want the standby to run an archive command too, either to replicate downstream to another standby, or to take pressure off the master when running base backups, e.g. with the WAL-E backup tool. For some reason the master’s achive_command runs fine, but the standby’s isn’t even getting used!

The issue is that in 9.5 and 9.6, Postgres will ignore an achive_mode=on setting if it is running in standby mode. Arguably this is kind of useful, so that you can set up the standby as close to the master as possible, and if you fail over it will immediately start running that command.

But if you really do want to do archiving from the standby, the solution is to say archive_mode=always. Once you make that change, Postgres will start running your archive_command.

Btw, if you are using Ansible, as of today the postgresql role does not respect always. If you give it something truthy it will always generate on. I’ve written a pull request to support always, but it is not yet merged.

blog comments powered by Disqus Prev: Temporal Databases Annotated Bibliography Next: Javascript Daylight Savings Time: One Weird Trick Your Application Hates