In last six months I have travelled extensively meeting clients and prospects in South East Asia and India. These include large Telecom Operators, Banks, Insurance firms, Stock Exchanges and Retail Chains. In all these discussions I could sense a need for a sustainable alternative for proprietary RDBMS products viz Oracle, SQL Server etc. In recent years PostgreSQL has emerged as a viable alternative as Database choice and most of these customers were actually using Postgres at some or the other level. Their usage of Postgres ranges from non-critical to business critical to mission critical applications. e.g. a Stock Exchange is using PostgreSQL as a backend of their trading engine. But one of the important aspects, which all of them were evaluating was related availability and scalability of Postgres. This is where our team got engaged with them and helped them design solutions which were right for them.
We have worked with these different teams to help them understand the high availability options in Postgres and come up with architecture that suits them best. Let me list some of the features and tools which helped us build these Highly Available architectures-
1.PostgreSQL Streaming Replication – Streaming Replication, which is built into PostgreSQL core can be used for creating read-only replicas.
2.EnterpriseDB Failover Manager – EnterpriseDB’s Failover Manager is a tool which helps automate the promotion of standby DB servers. From version 2.0 onwards you can have upto a 20 node cluster using EDB FM.
3.Linux HA (corosync and pacemaker) – Linux HA is another way of implementing Postgres cluster. It helps in automating the failover and manage switchover of nodes
4.pgpool-II – pgpool-II is Postgres Swiss knife which can fit into multiple role while acting as a DB middleware. In our setups we primarily used it for Load Balancing the read queries between master and multiple slaves. pgpool can also manage its own high availability with another pgpool node.
Out of various architecture that we discussed with different customers, we saw two architectures attracting most of the attention-
1.Postgres Cluster with pgpool Cluster – In this architecture we setup a Postgres cluster wherein the redundancy of nodes was achieved with Streaming Replication and failover was managed by either Failover Manager or Linux HA components. We used pgpool as Load Balancer. This architecture is quite scalable as you can add more DB server nodes as the load increases. These new standby nodes can be added in both pgpool and Failover Manager configuration without bringing down the running setup. The addition of new node would be transparent to application. Only challenge was to avoid pgpool from becoming Single Point of Failure. We could handle that by tapping into pgpool’s HA capability with Watchdog feature. It allows two pgpool to communicate with each other and keep a tab of their health. If the Primary pgpool goes down, there is a virtual IP which shifts over to the secondary pgpool and application continues to function without any issues.
2.Postgres Cluster working in tandem with Application Cluster – All the customer we were talking with were quite aware that availability of a system cannot be ensured with availability at just DB layer. They all had some of the other mechanism of managing application level high availability and load balancing. We thought that why not build DB HA solution which is in tandem with such application level clustering solution. Since most of the applications today have application level load balancing we thought it would be better to place a pgpool server on the application server itself and let them connect to their local pgpool. Each pgpool in-turn would connect to PostgreSQL cluster (master and standby servers) and do load balancing.
We recently did a Hangout  where we took our audience through various options of High Availability in Postgres. Feel free to get in touch with us , if you are looking for one of these solutions to be implemented or if you feel a need to discuss a different solution which is more suitable for your environment.