When do I have to use archive_command and when not

Posted on

Question :

I have some doubts about PostgreSQL replication command. I am doing some research and I found severals configurations.

first one is:

archive_mode = on
archive_command = 'cd .'

second one is :

cp -i %p /var/lib/postgresql/9.1/archive/%f'

Then my questions are:

  1. What is the different?
  2. The first one option ( command ) is really strange to me. What do it
    do?

Answer :

The first one (cd .) does nothing. It’s a placeholder. It would be better to write true in my view, so it’s more obviously intended as a no-op.

Turning archive_mode on but setting a no-op archive_command is a hacky workaround for the fact that changing archive_command only requires a server reload, but changing archive_mode requires a full server restart that can be disruptive in production.

The purpose of the archive_command, and all the rest, is covered in the documentation so I won’t repeat all that material there.

I am hitting the same question as you.

When using streaming replication, it is in theory not needed to ship the archived Xlogs to the standby.

But the archived Xlogs are also needed for normal recovery and they can also be needed if the standby cannot catch up with the master (for example if it was stopped for a while). Note that I am not 100% sure that archives are needed in this case because there is also the concept of replication slot by which the master is aware of what has been consumed by the slave (my understanding). With replication slots I think the master will accumulate the XLOGS as long as they are needed by the slave (and stop operating if the slave does not come back and the file system is full ?)

What I will do is ship them to the slave and even keep a copy on the master.

So, on the master

archive_command = rsync -ac %p postgres@other-pg-server:/u01/archive/%f

However, I think I will use a shell script that will copy the file locally and rsync it to the slave. This shell script can also check if it is running on the slave and if yes do nothing (this way the config file can be symetric between the two).

On the slave, in recovery.config,

restore_command = 'cp /u02/archive/%f %p'

I am using a replication slot but I will still need to do the cleanup of the archived Xlogs, via a cron jobs that delete files older than x days. I did not investigate the pg_archivecleanup program yet (https://www.postgresql.org/docs/9.2/static/pgarchivecleanup.html)

Leave a Reply

Your email address will not be published. Required fields are marked *