Setting Up High-Availability PostgreSQL with PgPool-II

PgPool-II: What Breaks When a Database Node Fails

The primary database node stops responding at the worst possible moment — for example, at night, when the on-call engineer learns about it from the alerting system.

At that moment, the application holds open connections to the failed node, not to the standby: the pool has not yet been rebuilt, and routing has not been switched. Writes and orders begin to fail with connection errors, while unprocessed requests pile up in the queue.

Then manual work begins. The standby node must be promoted to write mode, traffic must be switched to it, you must make sure the replica has caught up with the primary via the log, and only then bring the application back into operation.

While this is happening, some requests are lost irrevocably — no one will replay them, because the queue has already been distributed across clients and has fallen apart at the connection level.

PgPool-II is placed between the application and the database precisely so that this scenario does not unfold manually.

It maintains its own connection pool, monitors node health with periodic checks, and on failure it automatically excludes the failed node from routing and promotes the standby to write mode.

The application continues to obtain a connection from the pool — for the service, the destination address changes, not the very fact of database availability.

But the presence of PgPool-II in the architecture does not by itself eliminate the point of failure. If it runs as a single instance, without redundancy for the pool itself and without carefully calibrated health-check timeouts, the failure simply moves from the database to it. Below, we examine how this is addressed.

What a Fault-Tolerant Database Cluster Provides

Fault tolerance manifests not during normal operation, but in the first seconds after a failure.

That is why we look not at the declared replication support, but at the timeline: how quickly the balancer notices the failed node, who switches the traffic, and what the application sees at that moment.

The application connects to a single address, and PgPool-II distributes incoming connections across servers. Health checks run at a short interval: after several failed responses, the node is excluded from the pool, and the remaining ones take over its traffic.

There are no connection errors on the application side — only internal routing changes, and recovery time is measured in seconds instead of manual troubleshooting.

Then read/write splitting comes into play: read queries go to replicas, write queries go to the primary node. The application is unaware of this; the connection string and code remain the same.

Under typical load, up to two-thirds of requests go to replicas, and the primary node ceases to be the only point in terms of both load and failures.

Predictability matters more here than peak performance. Degradation of a single node becomes an event with a known scenario: some requests slow down, but the service remains available.

The class of failures where 'a server goes down — the entire product is down' disappears from the risk list, and cluster behavior is verified in drills rather than discovered during a real incident.

PgPool-II Operating Modes and Their Limitations

A fault-tolerant architecture on PgPool-II can be assembled in several ways, and the balancer is not always needed. The key question is what exactly is required: to survive a primary node failure, to distribute read load, or to smooth out bursts of short connections. This determines where PostgreSQL is placed and where the intermediate layer goes.

What to Check Before Choosing

Query replication mode does not remove the limitations of the DBMS itself: queries sensitive to data freshness are routed to the primary node, and extensions must be installed on all nodes.

Compatibility is tested under peak load — replication lag, behavior when the link drops, and the correct return of the old primary node to service.

How We Implement a Fault-Tolerant Cluster: Work Stages

A high-availability cluster is not assembled 'on the fly' but according to a sequence in which each step is confirmed by a check. Below is the work order by which we run the project: from audit to handover to the customer's shift.

At each stage, it is clear what has already been done, how it was verified, and which risks remain open.

1. Audit of the current architecture and points of failure. We analyze the existing setup: where writes go, who holds connections, and how replication is arranged. We list single points — one write node, a balancer without redundancy, manual failover on failure — and estimate the cost of downtime for each.
2. Architecture design. We define node roles (primary, standby, read nodes), the data flow between PgPool-II, database servers, and applications, the routing rules for read and write queries, and the load distribution mode.
3. Node configuration and failover testing. We set up the connection pool, replication, health checks, and automatic role switching. Separately, we verify that the application survives a primary node change without code changes.
4. Failure testing on the staging environment. We take each node and the inter-site link out of service one by one: we look at switchover time, the behavior of the request queue, and which transactions are lost and which are not.
5. Load testing. We apply a profile close to production: peak connections, heavy reports, long transactions. We verify that failover fits within the agreed window and under load.
6. Handover to operations and shift training. We provide configurations, the role and flow diagram, the switchover and incident troubleshooting procedures. We conduct drills with the customer's shift so that node switching is done according to instructions, not from memory.

Configurations, Diagrams, and Procedures: Implementation Artifacts

Implementation is considered complete not when the nodes are up, but when the customer has a set of artifacts that allows them to maintain the architecture on their own. Some files go to the configuration repository, and some to the on-call shift's knowledge base. Below is what exactly we hand over.

• Production configurations of the fault-tolerant architecture — PgPool-II, replication node, and cluster manager files with parameters for your load profile, not demo ones. They are stored under version control so that settings can be rolled back, not recreated from memory.
• Architecture diagram — the current topology: node roles, traffic routes, points of failure, and the procedure for passing through them. It is updated with every change, rather than living in one engineer's head.
• Access rules and roles — a permission matrix for nodes, balancer operational functions, and monitoring; separate accounts for the on-call engineer and for automation, with separation of read and write access.
• Switchover procedure — a step-by-step scenario for planned and emergency role switching, with expected time, criteria for 'time to intervene,' and the procedure for returning nodes to service.
• Node recovery instructions — what to do when a replica lags or drops out: checking synchronization, conditions for catching up without a full rebuild, and commands for typical failures.
• Observability and failure alerts — a set of metrics (replica lag, node state, routing error rate) with thresholds at which the on-call engineer receives a signal rather than learning about the failure from complaints.
• Drill records — actual recovery time based on training results and the list of amendments made to the procedure after each one.

Case Study: Downtime During Node Failure Was Reduced Severalfold

Initial Architecture and Its Weak Points

A service with a constant stream of writes relied on a single primary node and a hot standby, and a duty engineer performed switching manually.

When the primary node failed, the application received write errors until the standby was promoted and traffic was redirected: downtime reached tens of minutes, and some unfinished transactions were examined and retried by hand.

The weak point was not the standby itself, but the absence of an automatic arbiter — the decision to switch was made by a human based on indirect signs and with delay.

What We Changed and What the Metrics Showed

We placed PgPool-II in front of the cluster: it maintains a connection pool, periodically polls nodes, and initiates failover to the standby itself when the primary stops responding.

The share of automatic switchovers grew from zero to almost all failures recorded by monitoring; manual handling remained only for scenarios with network loss between nodes, where a deliberate decision is required.

Downtime during node failure was reduced severalfold: switchover fits within seconds, and the application reconnects to the new primary node instead of waiting for an engineer.

The connection pool relieved the database under peaks — the application no longer opens an avalanche of new connections on every burst, and the request queue stopped hitting the connection limit.

How Connection Balancing Differs from Replication

Replication and balancing solve different problems, and confusing them is costly. Replication is responsible for ensuring that a copy of the data exists on another node: the primary node streams changes, the standby applies them and is available read-only.

Balancing is responsible for the request route — which node it goes to and what happens to it at the moment of failure.

Having configured streaming replication, you will not distribute read load. The replica receives the same data, but the application accesses the primary node as long as there is no router between it and the databases.

PgPool-II works here: it parses the query, sends changes to the primary node, spreads reads across replicas, and maintains the connection pool.

The architecture is chosen based on the load profile. If writes predominate, replicas will not offload the primary node — they merely repeat changes.

If reads dominate, distributing read queries removes a noticeable share of requests from the primary node, but the application must tolerate replica lag.

Hence the main risk: while nodes diverge, the replica returns stale data — the user does not see a just-saved record. The reverse error is worse: a change goes to a node that the router mistakenly considers primary.

Then the nodes themselves diverge, and this cannot be fixed by a restart. That is why balancing is accompanied by node health checks, automatic failover to the standby, and protection against cluster split-brain.

What to do about lag — wait, exclude the replica from distribution, or move reads to the primary node — is decided in advance.

Technical Doubts Before Implementation: A Breakdown

Before implementing PgPool-II, teams raise the same doubts: will the application need to be modified, is downtime required, will automatic failover work, and what to do if data diverges.

Does implementation on a running cluster require downtime?

Downtime is not required. PgPool-II is brought up alongside the current cluster; first we move reads to it and observe behavior under load, and writes last. This requires a pause of seconds so that open transactions close. Rollback is returning applications to the previous connection string.

How do we test automatic failover?

On a staging environment close to production: we stop the primary node and record the time until the replica is promoted to primary, the number of lost transactions, and the application's behavior when the connection breaks. Then we repeat on a rehearsal of the production architecture — with a report and logs.

What happens if data diverges?

PgPool-II monitors replica lag: when the threshold is exceeded, the node is excluded from read distribution — the application does not receive stale data. A node that dropped after a failure is rebuilt from a copy of the primary.

Replication lag, availability, and role changes are displayed on the monitoring dashboard: an alert arrives before users notice the problem.

Let's Discuss the Task and Come Back with a Work Plan

To ensure the assessment is based on facts rather than assumptions, a description of the current architecture and the nature of the load is enough to start.

We will analyze where the point of failure currently is, how reads and writes are distributed across nodes, and whether a single entry point for applications is ready.

From the customer, little is needed:

• Architecture diagram: the number of PostgreSQL nodes, the presence of replicas, the replication mode, and who currently manages the primary node role switch.
• Load profile: the ratio of reads and writes, peak periods, and request response time requirements.
• Application behavior: how they handle a connection break, whether they use a single connection address, and whether they can correctly retry a request after failover.
• Connection fleet: how many clients connect simultaneously and whether there is a pool on the application side.
• Failure requirements: acceptable downtime for writes and reads, and whether automatic role return to the previous node is needed.
• Procedures: maintenance window, access to servers, and access to monitoring systems.

Based on the discussion, we send an implementation plan: the scope and order of work, phasing (node preparation, enabling read distribution, testing role switchover, load testing), acceptance criteria, and the handover procedure with support. Describe your task — we will come back with an estimate and a plan.