2

I'm running postgresql 9.3. I've currently got WAL archiving configured, but I'm doubting it's utility based on my configuration. On the master, I've got archive_command set to

rsync -az --ignore-existing %p postgres@slave_replica:/wal_archive

and on the slave replica, I've got restore_command set to

cp /wal_archive/%f %p || exit 0

If the slave server goes offline, it won't be able to receive the WAL files, and then when it comes back up, won't have what it needs.

Should I instead effectively reverse those two commands: archive the WALs locally on the master, and have slave replica rsync them from the master server? Or should I have the master archive the WALs both to a local directory and rsync them to the slave?

  • 1
    You should upgrade to PostgreSQL 10, and use streaming replication. PostgreSQL 9.3 is EOL. – Evan Carroll Dec 18 '17 at 23:47
  • I'm sure we will, but EOL for 9.3 isn't until September 2018, and we've got a big bureaucratic CM process that moves slow. In the meantime, we need our current system to work. So any advice on addressing my original question in 9.3? – John Dec 19 '17 at 0:17
  • I don't believe postgres@:/wal_archive is legal syntax for rsync. In my hands it always returns an error code, meaning that postgres will never remove the file from pg_xlog. – jjanes Dec 19 '17 at 0:26
  • @jjanes: Sorry, that was a typo in the post, not in the config file. I've fixed the post. – John Dec 19 '17 at 15:02
2

If the slave server is offline, the rsync in the archive_command should fail (exit with a non-zero exit value), which means the master will keep retrying it until it succeeds. It will not remove a pg_xlog file until it is successfully archived. So it will still be there when the slave comes back on online.

Also, the || exit 0 at the end of your restore command is just wrong. PostgreSQL will ask for files which do not exist, and has to be told they don't exist (by returning a non-zero exit code)

  • That's why I shouldn't read documentation when I'm tired. I misunderstood what it said about restore_command exit codes. Thanks. I've removed the || exit 0. – John Dec 19 '17 at 22:06
  • So other than the || exit 0, does my configuration look like it will work when the slave goes offline for a few hours? – John Dec 19 '17 at 23:06
0

Restore, afaik, tries to load all WAL logs at the directory you're pointing it to From the docs,

The standby server can read WAL from a WAL archive (see restore_command) or directly from the master over a TCP connection (streaming replication). [...] At startup, the standby begins by restoring all WAL available in the archive location

If you need warm standby under the archive/restore mode, I believe you want to look at the utility pg_standby

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.