1

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.

migrated from stackoverflow.com Apr 6 '14 at 7:46

This question came from our site for professional and enthusiast programmers.

1

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