Resilient PostgreSQL cluster: PgBouncer, PgPool-II and repmgr

4 min readJan 9, 2021

I was looking for a PostgreSQL resilient cluster solution.

I read about using PgPooI-Il, using PgBouncer, using various replication mechanisms and so on.

As often happens I haven’t found THE solution, but many possible ways to go, so I decided to think of a solution that would suit me.

The important actors in this my scenario are:

1. The pooler

A pooler sits between the database and the clients and speaks the PostgreSQL protocol, emulating a PostgreSQL server. A client connects to the pooler with the exact same syntax it would use when connecting directly to PostgreSQL.

I found this article very interesting: https://scalegrid.io/blog/postgresql-connection-pooling-part-1-pros-and-cons/

Aparajita clarify me that a pooler is mandatory even if its add latency and complexity:

“..in modern web applications, clients tend to open a lot of connections. Developers are often strongly discouraged from holding a database connection while other operations take place. “Open a connection as late as possible, close a connection as soon as possible”. But that causes a problem with PostgreSQL’s architecture — forking a process becomes expensive when transactions are very short, as the common wisdom dictates they should be.”

Aparajita said that PostgreSQL middlewares pooler have these PRO:

Optimized for PostgreSQL and its rather unique architecture among the modern DBMSes.
Provide centralized access control for diverse clients.
Allow you to reap the same rewards as client-side pools, and then some more (we will discuss these more in more detail in our next posts)!

PgBouncer or PgPool-II

I chose PgBouncer as pooler for my PoC because of:

it’s lightweight: it’s one only one process
the mechanism of “connection reuse” is optimized: user+database combination
it has 3 different pooling modes: session, transaction or statement
it supports also the Pass-through authentication

2. The replication mechanism

Also for the replication of the data, you can use a lot of different mechanisms: PgPool-II, repmgr, postgres itself;

I choose the Streaming Replication of PgPool-II.

3. HA: failover mechanism

PgBouncer & PgPool-II fails:
If the active PgBouncer or PgPool-II node fails, the keepalives mechanism notice this and moved the IP to the node in stand-by

PostgreSQL fail:
If the master goes down, repmgr will ensure any of the standby nodes takes the primary role, guaranteeing high availability. In parallel PgPool-II, as a proxy, will understand that the previous master is knocked out and will elect the new master.

In a HA PostgreSQL cluster, you can have one primary and zero or more standby nodes. The primary node is in read-write mode, while the standby nodes are in read-only mode. For best performance its advisable to limit the reads to the standby nodes.

The PoC

Looking for a solution as resilient as possible, and supported by the tests done by scalegrid.io, I decided to create a PoC, meshing various applications and various types of infrastructures.

In this PoC the HA is essential and therefore there must be no SPOF.

As already mention, I decided to use:

PgBouncer as pooler
PgPool-II as proxy and for high-availability and load balancing
Repmgr as replication manager for PostgreSQL

Fails example

- VM fails

If the problem is in the first PgPool VM (the left one), the Keepalived mechanism switch the Virtual IP on the second VM (the right one) and so the active PgBouncer start to talk with it.

Application fails

Otherwise if the problem is in the first PostgreSQL (the one in the left VM), the repmgr and PgPool-II HA mechanisms will elect the new ACTIVE PostgrSQL (the one in the right VM).