I use postgresql 9.1 replication to a lot of workstations that have postgresql on hot standby mode. However sometimes these workstations go offline and disconnect from the master server. If they go offline for more than say a day they stop replicating, because the wal segments are no longer available. My question is how do I properly configure the master and the standby servers so that they can still replicate even after being disconnected from each other for several days.


Set up WAL archiving. If the master no longer retains enough WAL your replica will fall back to its archive_command.

See the documentation and this post I wrote earlier.

  • This is what I did: 1. Edit postgresql.conf on the master and set wal_level = hot_standy archive_mode = on archive_command = ' cp "%p" /var/lib/pgsql/9.1/walarchive/"%f" ' 2. Edit recovery.conf on each of the standy servers and set: standby_mode = 'on' restore_command = 'cp /var/lib/pgsql/9.1/walarchive/"%f" "%p"' 3. create the directory /var/lib/pgsql/9.1/walarchive and have in read/writable for postgres user 4. Periodically delete old wal segments (older than say 1 month) so the walarchive directory doesn't become bloated. Please confirm is this is right. Thank you. – David Noriega Feb 12 '14 at 3:11
  • @DavidNoriega Is /var/lib/pgsql/9.1/walarchive on NFS, or some other kind of shared file system? If not, how will the replicas read the WAL archives? – Craig Ringer Feb 12 '14 at 3:12
  • That is what is not clear to me. I thought the master will fetch the archive wal segment as per the restore_command from the recovery.conf on the standby server. So I must have a NFS or someway to share the wal segment files with the master and replicas? – David Noriega Feb 12 '14 at 18:37
  • @DavidNoriega The usual solution is to provide shared storage somewhere. Lots of people now use object stores like S3 for this (see github.com/wal-e/wal-e), but you can just as well rsync/scp to a fileserver, or whatever. – Craig Ringer Feb 12 '14 at 23:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy