2

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'
4

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.

  • Thank you @craig-ringer. Might the Wal segments live on a shared filesystem, shared between master and slave; is that how the slave would have access to them when running the restore_command? – Joe J Jun 12 '17 at 15:20
  • @JoeJ That's one way. Personally I prefer to have the master archive them away somewhere non-local where a badly written cron job running as root can't rm -rf / them away by accident. I usually have the archive_command use PgBarman and the replica use barman get-wal. Others like to use WAL-E for archiving to S3. You can also write your own to, say, scp to some NAS that the replica can also scp from in its restore_command. – Craig Ringer Jun 13 '17 at 5:27

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.