Postgresql – postgres: what is the archive_command and when to use it

archive-logbackuppostgresqlreplication

I have a master/slave postgresql configuration where the slave is a hot standby and is used as a readonly backup. Currently, the archive_command is set to "cd ." (a noop as I've read). My understanding is that the archive_command can copy wal segments to a specific location; usually I see examples using the cp command to copy it somewhere else on disk. I have wal_keep_segments set to 256, so if I understand correctly, the slave can fall behind by about 4GB (16MB * 256) worth of changes to the wal. Is this possibly why I've never needed to use the archive_command because I have enough wal segments to account for some lag?

So is the purpose of the optional archive_command to save more wal segments than the wal_keep_segments settings? So in my case, 256 wal segments would be in the database directory (pg_xlog), and postgres would automatically rotate out and clean up those wal segments as the limit was reached.

And, if I put an archive_command in place, would it just keep saving the wal segments to wherever the command copied the files to (and keep adding more and more archived wal segments unless I added my own cleanup process)? Is this thinking correct?

Then, assuming my understanding of the archive_command is correct, how does the slave take advantage of those wal files? Does the slave first try to retrieve the 256 wal files from the master data directory, but then fall back to the recovery_command to retrieve wal files older than that? Does the recovery_command run on the slave or somehow get proxied through the master; many of the examples of the recovery_command show it being used with cp, so I'm not sure how the files are being transferred over to a different server?

I've tried reading numerous guides on this, including the following.
When do I have to use archive_command and when not
https://www.postgresql.org/docs/current/static/continuous-archiving.html

I'm still a bit hazy on the concepts and am trying to reach some clarity. Can someone bring some clarity as to whether my thinking above is correct and why I might use the archive_command? Thanks for reading.

Here is my config:

Master: postgresql.conf

listen_addresses = '*' 

wal_level = hot_standby
archive_mode = on 
max_wal_senders = 2
archive_command = 'cd .'
wal_keep_segments = 256 
hot_standby = on

Slave: postgresql.conf

listen_addresses = '*' 

wal_level = hot_standby
archive_mode = on 
max_wal_senders = 2
archive_command = 'cd .'
wal_keep_segments = 256 
hot_standby = on

Slave: recovery.conf

standby_mode = 'on'
primary_conninfo = 'host=IP-of-master port=5432 user=rep password=****'
trigger_file = '/tmp/postgresql.trigger.5432'

Best Answer

The archive_command is useful when:

  • You want to do point-in-time recovery from a base backup to some past state in case of disaster, like accidentally dropping a database.

  • You're using streaming replication without a replication slot. In this case if the replica gets behind by more than wal_keep_segments and hot_standby_feedback is off or the replica gets disconnected temporarily, the master will remove resources it needs and it'll break. If you have WAL archiving in use, the replica can fall back to using its restore_command to fetch WAL to catch up and recover. Otherwise you'd have to re-create it from a new pg_basebackup.