Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 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).

share|improve this answer
    
Thanks for your feedback. But am I right to assume that if streaming replication isn't working (because of network issues), log shipping wouldn't work either? And, if keep_segments is high enough, it will be able to get back on track as long as I still have the segments for it? Because the archive would take up at least the same amount of space as the segments would do? –  jishi Jul 6 '13 at 9:12
    
If the network was severely impacted, yes, log shipping would experience the same problem as streaming replication (no data goes over the wire), but I should have been more explicit. The network connectivity problems I was referring to would be enough to cause streaming replication to fall behind and fail, but this is where the log shipping comes into play. Assuming you have set wal_keep_segments high enough, once the network is okay, the WAL segments will ship to your slave and your db will be brought up to date again. When I experienced this in 9.0, SR did not restart automatically (IIRC). –  bma Jul 7 '13 at 1:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.