PostgreSQL HA Cluster using Patroni setup and hot_standby_feedback impact on Primary node!

PostgreSQL HA Cluster using Patroni - Overview of Architecture:

To prevent "split-brain" scenarios during a failure, As per the best practises and recommendations, this setup utilizes a 3-node architecture to maintain quorum.

• Node 1, Node 2, Node 3: Each runs PostgreSQL (database), Patroni (cluster manager/failover), and Etcd (distributed consensus store).
• HAProxy Node: Acts as the entry point, routing application traffic to the current PostgreSQL Primary.

Step 1: Environment Preparation (Run on all nodes)

1. Update OS and Install Dependencies: (OS: Ubuntu)

sudo apt update && sudo apt install -y curl wget jq net-tools python3-pip python3-dev libpq-dev

2. Configure Host Resolution: Add the following mapping to the /etc/hosts file on every server:

10.0.0.11 node1

10.0.0.12 node2

10.0.0.13 node3

10.0.0.10 haproxy-node

3. Configure the Firewall (Optional but Recommended): Allow traffic on ports 22 (SSH), 5432 (PostgreSQL), 2379 & 2380 (Etcd), and 8008 (Patroni API).

Step 2: Install and Configure Etcd (Run on Nodes 1, 2, 3)

Etcd stores the cluster state and manages leader election.

1. Install Etcd:

sudo apt install -y etcd-server etcd-client

2. Configure Etcd: Edit /etc/default/etcd (or /etc/etcd/etcd.conf). Below is an example for node1; ensure you adjust ETCD_NAME and IP addresses for node2 and node3.

ETCD_NAME="node1"

ETCD_LISTEN_PEER_URLS="http://10.0.0.11:2380"

ETCD_LISTEN_CLIENT_URLS="http://10.0.0.11:2379,http://127.0.0.1:2379"

ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.0.0.11:2380"

ETCD_INITIAL_CLUSTER="node1=http://10.0.0.11:2380,node2=http://10.0.0.12:2380,node3=http://10.0.0.13:2380"

ETCD_INITIAL_CLUSTER_STATE="new"

ETCD_INITIAL_CLUSTER_TOKEN="patroni-cluster"

ETCD_ADVERTISE_CLIENT_URLS="http://10.0.0.11:2379"

3. Start and Verify the Cluster:

sudo systemctl enable etcd && sudo systemctl restart etcd

etcdctl member list  # This should output all 3 nodes

 Step 3: Install PostgreSQL (Run on Nodes 1, 2, 3)

1. Install PostgreSQL 16 (or your preferred version):

sudo apt install -y postgresql-common

sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

sudo apt install -y postgresql-16 postgresql-contrib-16

2. Disable the Default PostgreSQL Service: Because Patroni will take full control over managing the database lifecycle (startup/shutdown/failover), the default systemd service must be disabled.

sudo systemctl stop postgresql

sudo systemctl disable postgresql

sudo rm -rf /var/lib/postgresql/16/main/*  # Clear data so Patroni can bootstrap it

Step 4: Install and Configure Patroni (Run on Nodes 1, 2, 3)

1.     Install Patroni:

sudo apt install -y patroni

2. Configure Patroni: Create/edit /etc/patroni/patroni.yml. Below is a baseline configuration for node1 (Update name and connect_address IPs for nodes 2 and 3):

scope: postgres_cluster

namespace: /db/

name: node1

 

restapi:

  listen: 0.0.0.0:8008

  connect_address: 10.0.0.11:8008

 

etcd:

  host: 127.0.0.1:2379

 

bootstrap:

  dcs:

    ttl: 30

    loop_wait: 10

    retry_timeout: 10

    postgresql:

      use_pg_rewind: true

  initdb:

    - auth-host: md5

    - auth-local: trust

    - encoding: UTF8

    - data-checksums

 

 

postgresql:

  listen: 0.0.0.0:5432

  connect_address: 10.0.0.11:5432

  data_dir: /var/lib/postgresql/16/main

  bin_dir: /usr/lib/postgresql/16/bin

  authentication:

    superuser:

      username: postgres

      password: supersecretpassword

    replication:

      username: replicator

      password: repsecretpassword

3.    Start Patroni: Start the service on node1 first, let it initialize as the Leader, and then start nodes 2 and 3 sequentially so they join as Replicas.

sudo systemctl enable patroni && sudo systemctl start patroni

4.     Verify Cluster State:

sudo patronictl -c /etc/patroni/patroni.yml list

Step 5: Install and Configure HAProxy (Run on HAProxy Node)

HAProxy sits between your applications and the database, pinging the Patroni API (port 8008) to dynamically determine which node is the current Primary, routing write traffic exclusively to it.

1. Install HAProxy:

sudo apt install -y haproxy

2. Configure HAProxy: Append the following block to /etc/haproxy/haproxy.cfg:

listen postgres_cluster

    bind *:5432

    mode tcp

    option httpchk OPTIONS /master

    http-check expect status 200

    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions

    server node1 10.0.0.11:5432 maxconn 100 check port 8008

    server node2 10.0.0.12:5432 maxconn 100 check port 8008

    server node3 10.0.0.13:5432 maxconn 100 check port 8008

3. Restart HAProxy:

sudo systemctl restart haproxy

Application servers can now connect to PostgreSQL strictly via the HAProxy IP (10.0.0.10:5432). In the event of a primary node crash, Patroni will orchestrate a failover via Etcd, and HAProxy will transparently redirect the connection stream to the newly promoted leader.

 

 

Have you ever encountered a scenario where a long-running transaction on a PostgreSQL standby node caused performance degradation or table bloat on the primary node?

It seems strange, a secondary node is supposed to be read-only, so querying it shouldn't impact the primary at all, right? It is a common misconception that asynchronous replication protects the primary from everything happening on the secondary. While async mode protects your primary's write speed, it does not protect your primary's disk space and vacuum processes.

However, in PostgreSQL architecture, a long-running transaction (SELECT) on a secondary node can severely impact or even bring down the primary. This happens primarily through three specific parameters designed to keep the databases synchronized and keep the integrity.

Here is exactly why this strange thing happens:

The hot_standby_feedback Trap (Bloating on Primary Node):

If a primary node updates (auto correction mode i.e. delete+insert) or deletes a row, VACUUM will eventually clean up the old "dead" row. But what if another query on the secondary is currently reading that exact row? If the primary deletes it, the secondary query will fail with a "snapshot too old" error. To prevent the query on the secondary from failing, PostgreSQL provides hot_standby_feedback, to be enabled.

• What happens, If we enable hot_standby_feedback: The secondary node now, keep-on messages the primary requesting, "I have a transaction open using transaction ID XXX, hence don’t vacuum anything newer than this XXX".
• What is the Impact on Primary: If some long report running on the secondary (typically runs 1-2 hours), the primary is completely forbidden from cleaning up any dead tuples across the entire database for those 1-2 hours. Hence, primary tables and indexes will get huge bloat, causing disk I/O to spike and overall performance to degrade heavily.

Replication Slots & Disk Space Exhaustion:

If the secondary node is running a massive transaction, it is consuming heavy CPU, Memory, and Disk I/O.

• What happens: The secondary becomes so starved for resources that the startup process (which replays the Write-Ahead Logs from the primary) slows down to a grinding halt or stops entirely.
• The Impact on Primary: If you are using physical replication slots, the primary is forced to hold onto all WAL files until the secondary confirms it has replayed all of  them. If the secondary is bogged down by a large query and stops replaying, the WAL files will pile up on the primary's pg_wal directory until it runs out of disk space. When the primary hits 100% disk usage, the database crashes.

Synchronous Replication Blocking (synchronous_commit):

This is more problematic now. If we have configured our cluster for high availability using synchronous replication (where synchronous_commit is set to on, remote_write, or remote_apply), the primary must wait for the secondary to acknowledge transactions.

• What happens in the Secondary: A huge query on the secondary hogs the disk I/O or locks resources.
• The Impact on Primary: Write transactions (INSERT, UPDATE, DELETE) on the primary will hang. The primary might successfully execute the write locally, but the COMMIT will sit in a waiting state until the sluggish secondary processes the WAL and acknowledges it back to Primary. This causes application connections to pile up on the primary, eventually exhausting max_connections and the database crashes.

How to Mitigate This?

In an OLTP production environment, the ultimate goal is to make sure Primary database is always up, running and available. To protect the primary from secondary-node abuse, we have to establish the cited guardrails:

• Tune max_standby_streaming_delay: Set this to a reasonable limit (e.g., 30s or 1min). If a query on the replica blocks replication for longer than this time, PostgreSQL then forcefully cancel the conflicting query on the Secondary node. The user's query fails, but the primary stays alive.
• Use statement_timeout on the Replica: Enforce a strict time limit for read-only queries on the secondary so long running queries/reports are killed automatically.
• Turn off hot_standby_feedback: If we can tolerate occasional query cancellations on the replica due to the error “snapshot too old”, but, turning hot_standby_feedback off ensures the primary's VACUUM is never blocked.