I have some doubts about how the workflow proccess in PostgreSQL replication using archiving logs.

If I have this configuration in Master postgresql.conf:

archive_command = 'test ! -f /path/to/archive/%f && cp %p /path/to/archive/%f'

After the %p and %f parameters have been replaced, the actual command executed might look like this:

test ! -f /path/to/archive/00000001000000A900000065 && cp pg_xlog/00000001000000A900000065 /path/to/archive/00000001000000A900000065

Ok, if the file isn't into archive, then it will be copied.

If I have this configuration in Slave recovery.conf:

restore_command = 'cp /path/to/archive/%f %p'

Then if I don't configure anything like this :

scp /master/path/to/archive/* /slave/path/to/archive/

How is possible that the xlogs files in /path/to/archive/ travels from Master to Slave, and then the Slave copy them to its pg_xlog/ directory?

  • Well, the assumption in that example archive_command you have copied out of the docs is that /path/to/archive is a shared archive accessible by BOTH the master and the slave. The master copies its WAL segments into the archive, and the slave copies them out. If that's not applicable for your environment e.g. you don't have a shared storage location accessible by your master and slave, then you'll need to e.g. adjust archive_command to scp the files to somewhere readable by your slave(s). – Josh Kupershmidt Sep 25 '14 at 17:55
  • @JoshKupershmidt Why it is necessary to create a archive directory and the copy doesn't execute directly into pg_xlog? – Robert Sep 25 '14 at 19:08

You're expected to ensure that /path/to/archive is a shared volume, like an NFS fileshare, that both servers can read. Or you're expected to use an appropriate command to copy the files to/from a shared storage location.

I think this might need to be made more obvious in the documentation.

Re your comment:

Why it is necessary to create a archive directory and the copy doesn't execute directly into pg_xlog?

the reason is that the master's pg_xlog is generally on a different server to the replica(s). The replicas cannot access it. You really don't want to put pg_xlog on shared storage because it's performance critical.

It also does you no good to have a backup that requires access to the master server's pg_xlog when the master server just melted in a fire.

By archiving WALs, PostgreSQL can also reduce filesystem fragmentation. It recycles WAL archives once they're no longer required, renaming them and using them as if they were new files. This is a significant performance improvement that would not be possible if it had to keep all the old WAL files around.

Finally, it creates a logical separation between "transaction logs the master server still needs to operate" and "transaction logs that are now only required for backup/archival".

| improve this answer | |

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.