I am a DBA at the University of South Australia. For PostgreSQL High Availability, we currently have setup a Master/Slave across two datacenters using PostgreSQL (WAL) streaming replication. We use an LTM (load balancer) server that sits between the application servers and the PostgreSQL server that directs connections to the Master (and the Slave if failover occurs). We also have watchdog processes on the PostgreSQL servers that polls the LTM to determine who is Master and perform automatic failover if required. I am looking at options to improve our high availability.
I would like to know how other organizations in different industries (other than education) setup High Availability on their PostgreSQL environments.
What tools do you use. Are they commercial licensed products? How is the architecture setup and how do you do recovery of new slave.
Your information is greatly appreciated.
An efficient High Availability setup for PostgreSQL would depend on various factors like Application, Infrastructure and other Business Continuity requirements. In your case, you have already mentioned that the Load Balancer continuously polls to check the master status and fails over to slave when the former is down. If you are looking at improving this setup, then, it is important for us to know how the slave promotion is happening ? is that done by some tools like pgPool-II ? Well, those are the open-source tools available if you wish to automate the slave promotion when the master is down. If you are looking at an highly efficient High Availability setup would depend on how a) Application failover and b) slave promotion are going hand-in-hand. Following are some of the factors to consider which can help improve the efficiency in PostgreSQL High Availability -
- Application requirements for continued / uninterrupted data operations on slave post the fail-over
- How fast the slave gets promoted when master fails
- You need to ensure Master and Slave are in absolute sync all the time (importantly just before fail-over)
- Various other factors related to infrastructure like Network, database load etc.
