Patroni with PostgreSQL High Availability: "No Downtime Without Patroni, No Problem With Patroni!"

"PostgreSQL is an excellent database... but what happens when there's an outage? That’s when Patroni steps in."

PostgreSQL is highly reliable and performant on its own. However, as systems grow—when you need to use the database across multiple servers in a distributed environment with high availability—certain challenges arise:

• What happens if the primary server fails?
• When should replica servers take over?
• Is manual intervention required?
• How can we balance data consistency and availability?

This is precisely where Patroni saves the day.

Patroni is a High Availability (HA) manager for PostgreSQL that automates failover, leader election, health checks, and cluster management. In essence, Patroni is an intelligent orchestrator that brings HA capabilities to PostgreSQL. Simply put, Patroni:

• Maintains cluster state using a distributed key-value store such as Etcd or Consul,
• Monitors the health of servers,
• Automatically elects a new leader and reconfigures the cluster when the current leader fails,
• Dynamically adjusts PostgreSQL replication settings.

In this article, we will not discuss intermediary layers such as HAProxy or PgBouncer. Dedicated articles on these tools will be published soon—stay tuned!

The CAP theorem states that in a distributed system, you can only choose two out of three properties:

1. Consistency

2. Availability

3. Partition Tolerance

In distributed systems, network partitions are inevitable. Therefore, the choice narrows down to balancing consistency and availability. Patroni chooses the "Consistency + Partition Tolerance" side. Why?

• It does not allow multiple primary (writable) servers.
• It prioritizes the consistency of written data.
• However, this may temporarily render the system unavailable for write operations.

Patroni requires a key-value (KV) store to centrally track which server is the leader and the overall health of the cluster. This is typically done via:

• etcd
• or Consul.

These systems:

• Provide distributed consensus using the RAFT protocol,
• Require at least three nodes to ensure high availability,
• Allow each Patroni instance to read from and write to this KV store.

Patroni performs leader election through Etcd. The process works as follows:

1. Each PostgreSQL instance, via its associated Patroni bot, checks whether a specific key in Etcd has expired.

2. If the leader key is vacant, the bot requests Etcd to acquire the key for a limited duration.

3. Etcd, using the RAFT protocol in the background, ensures that only one instance can acquire the key, preventing race conditions.

4. The first instance to acquire the key is recognized as the leader, and Patroni configures that PostgreSQL instance as the primary.

5. Other instances detect this leadership change and automatically switch to replica mode.

Thus, the leader is selected in a distributed and consistent manner without requiring a central control point.

• Single Primary Architecture: Only one server can be writable at any given time.
• Dependence on Etcd/Consul: These systems must themselves be highly available; otherwise, Patroni cannot function.
• Split-Brain Prevention: Complex control mechanisms are required to prevent incorrect leader elections.

For a simple example, we can use three PostgreSQL + Patroni containers and three Etcd containers:

First, obtain the project files from the developer’s GitHub repository. Build the Dockerfile in the project to create the Patroni Docker image we will use.

^{Example related to this topic (created by the author)}

Patroni is a highly effective and flexible solution for transforming PostgreSQL into a high-availability system.

In our next article, we will explain how to route traffic to a Patroni cluster using HAProxy and create a connection pool with PgBouncer. Don’t miss it!

Stay Tuned—Keep Your Data Alive!