Not being a DBA as main profession, I would like to know if the following assumptions are true. I have been searching the net for days and I think I have understand it correctly, but I would like to hear the response from people when faced with my exact configuration.
I want to utilize streaming replication, which I have a working setup for.
I have turned off WAL archiving, since I believe that wal_keep_segments will contain enough backlog to get a slave up to date if necessary (700, you might think this high, I just calculated how much "buffer" I could spare for replication backlog, please tell me if this is the wrong approach).
I have the same configuration on master and slave, which includes the following:
wal_level = hot_standby
hot_standby = on #(this is ignored on master, as I understand it?)
Now, the main question is the first entry here, I could theoretically set this to minimal on the slave, correct? However, I'm thinking for ease of failover, I should keep it this way?
Finally, I never specify restore_command in my recovery.conf, because as I have understood it, this is for log shipping (when rsyncing the WAL files between the two nodes or similar), or if I were to bring back a slave node from a long disconnect and archive_mode was on. 12 GB of WAL segments should be good for at least a week (unless I do some big restore of a DB), and I assume that all segments that are in pg_xlog will be streamed to the slave upon reconnect if it is behind?
And finally, what would be a good way to surveillance that the replication works as it should? I know about repmgr, but it would be nice to be aware of how the surveillance would consider the slave to be out of date.
Best Answer
This is a really broad topic, and not one I am going to cover in detail here. One point to note however: in my experience, SR can occasionally break due to network issues between datacenters (or ever servers), so disabling log shipping isn't a good idea unless you truly don't have the bandwidth to spare between master and slave(s).