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.
1 Answer
Set up WAL archiving. If the master no longer retains enough WAL your replica will fall back to its archive_command.
answered Feb 11, 2014 at 11:40
-
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 NoriegaFeb 12, 2014 at 3:11
-
@DavidNoriega Is
/var/lib/pgsql/9.1/walarchiveon NFS, or some other kind of shared file system? If not, how will the replicas read the WAL archives? Feb 12, 2014 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 NoriegaFeb 12, 2014 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. Feb 12, 2014 at 23:59