Top PG Clustering HA Solutions for PostgreSQL

If your system relies on PostgreSQL databases and you are looking for clustering solutions for HA, we want to let you know in advance that it is a complex task, but not impossible to achieve.

We are going to discuss some solutions, from which you will be able to choose taking into account your requirements on fault tolerance.

PostgreSQL does not natively support any multi-master clustering solution, like MySQL or Oracle do. Nevertheless, there are many commercial and community products that offer this implementation, along with others such as replication or load balancing for PostgreSQL.

For a start, let's review some basic concepts:

What is High Availability?

It is the amount of time that a service is available, and is usually defined by the business.

Redundancy is the basis for high availability; in the event of an incident, we can continue to operate without problems.

Continuous Recovery

If and when an incident occurs, we have to restore a backup and then apply the wal logs; The recovery time would be very high and we would not be talking about high availability.

However, if we have the backups and the logs archived in a contingency server, we can apply the logs as they arrive.

If the logs are sent and applied every 1 minute, the contingency base would be in a continuous recovery, and would have an outdated state to the production of at most 1 minute.

Standby databases

The idea of a standby database is to keep a copy of a production database that always has the same data, and that is ready to be used in case of an incident.

There are several ways to classify a standby database:

By the nature of the replication:

• Physical standbys: Disk blocks are copied.
• Logical standbys: Streaming of the data changes.

By the synchronicity of the transactions:

• Asynchronous: There is possibility of data loss.
• Synchronous: There is no possibility of data loss; The commits in the master wait for the response of the standby.

By the usage:

• Warm standbys: They do not support connections.
• Hot standbys: Support read-only connections.

Clusters

A cluster is a group of hosts working together and seen as one.

This provides a way to achieve horizontal scalability and the ability to process more work by adding servers.

It can resist the failure of a node and continue to work transparently.

There are two models depending on what is shared:

• Shared-storage: All nodes access the same storage with the same information.
• Shared-nothing: Each node has its own storage, which may or may not have the same information as the other nodes, depending on the structure of our system.

Let's now review some of the clustering options we have in PostgreSQL.

Distributed Replicated Block Device

DRBD is a Linux kernel module that implements synchronous block replication using the network. It actually does not implement a cluster, and does not handle failover or monitoring. You need complementary software for that, for example Corosync + Pacemaker + DRBD.

Example:

• Corosync: Handles messages between hosts.
• Pacemaker: Starts and stops services, making sure they are running only on one host.
• DRBD: Synchronizes the data at the level of block devices.

ClusterControl

ClusterControl is an agentless management and automation software for database clusters. It helps deploy, monitor, manage and scale your database server/cluster directly from its user interface.

ClusterControl is able to handle most of the administration tasks required to maintain database servers or clusters.

With ClusterControl you can:

• Deploy standalone, replicated or clustered databases on the technology stack of your choice.
• Automate failovers, recovery and day to day tasks uniformly across polyglot databases and dynamic infrastructures.
• You can create full or incremental backups and schedule them.
• Do unified and comprehensive real time monitoring of your entire database and server infrastructure.
• Easily add or remove a node with a single action.

On PostgreSQL, if you have an incident, your slave can be promoted to master status automatically.

It is a very complete tool, that comes with a free community version (which also includes free enterprise trial).

Node Stats View

Cluster Nodes View