Creating High Availability PostgreSQL Clusters to Boost Resiliency

When we first introduced EDB Postgres Distributed (PGD), it went by BDR, short for Bi-Directional Replication. Since writes can occur on any node, there’s an inherent risk of write conflicts if two nodes simultaneously modify the same row. PGD provides several mechanisms to address this, such as custom conflict handling, column-level conflict resolution, Conflict-free Replicated Data Types (CRDT), and eager resolution, among others. Yet, the best way to prevent conflicts is to avoid them.

The easiest way to do this is to prevent writes on multiple PGD nodes. We introduced High Availability Routing for Postgres or HARP, a Patroni-inspired design based on a Distributed Configuration Store (DCS). The idea was simple: each PGD node has a HARP manager service that conveys node status to the DCS, and any HARP router contacts the DCS to obtain the canonical lead master of the database cluster. Clients then connect to the HARP router.

The architecture of this was a bit more complicated:

The illustration above is a simplified view of how this worked, but we color-coded everything to explain what’s happening.

• PGD nodes and the traffic between them are in orange.
• The DCS (etcd) and its internal traffic is in purple.
• The HARP manager and the communication between PGD are in blue.
• The HARP router and traffic to PGD nodes are in green.
• Traffic to and from the DCS is in red.

The HARP manager service continuously updates the status of each node in the DCS, and the HARP router obtains the status of the appropriate lead node from the DCS. We used three nodes in this example because it’s the minimum to maintain a voting quorum.

It’s also convenient for service distribution since we can place one of each necessary item on each node. It’s common to split these components up into designated roles. For example, the DCS may be assigned to dedicated nodes so the service can be shared among several database clusters. HARP router services can likewise be decoupled from their respective nodes, allowing for better conceptual abstraction or integration into container-driven environments.

The best part about this design is that it works exceptionally well. Since we control both router and manager components, we can ensure proper failover based on PostgreSQL replication lag, the state of the Commit At Most Once (CAMO) queue, or any number of requirements to prevent conflicts. The primary concerns about this architecture lay in its moderate complexity and dependence on many moving parts, some of which are external to EDB.

The final improvement related to connection routing derives from Raft itself. Despite PGD making its API available to HARP, clients often choose to use etcd as the consensus layer. Why?

Our most commonly deployed architecture designates two PGD nodes in a lead/shadow arrangement per location. Whether this location is an availability zone, data center, region or otherwise doesn’t matter. What’s important is that each location can have its etcd database cluster, meaning the HARP router will still function even if half of the PGD cluster is unavailable due to a network partition. If PGD is the DCS, losing half of the cluster means the other half will not be routable, as the DCS will refuse to answer queries without a quorum.

That is no longer the case with EDB PGD 5.0, as we’ve added the capability to create Raft sub-groups. We can now construct a database cluster like this:

This isn’t the best cluster design since it uses an even number of nodes, but it illustrates the point. In this scenario, the PGD proxy in AZ1 would continue to route traffic to the lead node there even if AZ2 vanishes. The Raft sub-group ensures local consensus is possible even when global consensus is unknown. Being independent of global consensus also removes remote consensus latency, allowing faster failover within the sub-group (lower RTO).

It may not seem groundbreaking, but combined with DCS change notifications it allows us to abandon our reliance on etcd for certain functionality. Now, there is nothing etcd can do that PGD itself can’t, all without the extra (and sometimes substantial) configuration and maintenance etcd requires.