---
title: "12 — Replication and High Availability"
uid: replication-high-availability
tags: ["streaming", "roadmap:postgresql-dba", "patroni", "haproxy", "postgresql", "pgbouncer", "replication", "logical", "high-availability"]
excerpt: "Streaming replication ships WAL bytes for a byte-identical standby; logical replication ships row changes for selective cross-version copying; HA is a separate concern — a failover controller that promotes a replica."
date: 2026-08-13T03:27:51+0000
source: https://www.aveshina.my.id/en/blog/replication-high-availability
---

"Replication just mirrors stuff" was my high-availability model, and it fused two mechanisms with different jobs. The framing that untangled it: **there are two replication mechanisms with different units of transfer — streaming replication ships WAL bytes for a byte-identical standby, logical replication ships row-level changes for selective, cross-version copying — and high availability is a separate concern, solved by a failover controller that watches the primary and promotes a replica when it dies** [1][2][3]. Once I separated the replication mechanism from the HA controller, the tooling map became legible.

## Two replication mechanisms

```figure
<svg viewBox="0 0 740 300" xmlns="http://www.w3.org/2000/svg" class="my-6 w-full max-w-2xl" role="img" aria-label="Streaming vs logical replication. Left side STREAMING: a primary streams WAL bytes to a standby, producing a byte-identical cluster copy — same Postgres version, all databases, read-only standby. Right side LOGICAL: a publisher sends row-level changes (publication/subscription) to a subscriber, which can be a different version, select only some tables, and accept writes.">
  <defs>
    <marker id="rearrow" viewBox="0 0 10 10" refX="9" refY="5" markerWidth="7" markerHeight="7" orient="auto">
      <path d="M0,0 L10,5 L0,10 z" fill="#64748b"/>
    </marker>
  </defs>
  <g font-family="ui-sans-serif, system-ui, sans-serif" text-rendering="geometricPrecision">

    <!-- primary -->
    <rect x="290" y="20" width="160" height="60" rx="8" fill="#dcfce7" stroke="#16a34a" stroke-width="1.5"/>
    <text x="370" y="44" font-size="12" font-weight="700" fill="#052e16" text-anchor="middle">primary</text>
    <text x="370" y="62" font-size="9" fill="#052e16" text-anchor="middle">accepts writes</text>

    <!-- STREAMING branch -->
    <path d="M330,80 L150,140" fill="none" stroke="#6366f1" stroke-width="1.8" marker-end="url(#rearrow)"/>
    <text x="200" y="100" font-size="10" font-weight="700" fill="#1e1b4b">WAL bytes (streaming)</text>
    <rect x="40" y="140" width="220" height="100" rx="8" fill="#e0e7ff" stroke="#6366f1" stroke-width="1.5"/>
    <text x="150" y="162" font-size="11" font-weight="700" fill="#1e1b4b" text-anchor="middle">physical standby</text>
    <text x="150" y="180" font-size="9" fill="#1e1b4b" text-anchor="middle">byte-identical cluster copy</text>
    <text x="150" y="198" font-size="9" fill="#1e1b4b" text-anchor="middle">same major version</text>
    <text x="150" y="214" font-size="9" fill="#1e1b4b" text-anchor="middle">all databases</text>
    <text x="150" y="230" font-size="9" fill="#1e1b4b" text-anchor="middle" font-style="italic">read-only (or hot standby)</text>

    <!-- LOGICAL branch -->
    <path d="M410,80 L590,140" fill="none" stroke="#db2777" stroke-width="1.8" marker-end="url(#rearrow)"/>
    <text x="540" y="100" font-size="10" font-weight="700" fill="#500724" text-anchor="middle">row changes (logical)</text>
    <rect x="480" y="140" width="220" height="100" rx="8" fill="#fce7f3" stroke="#db2777" stroke-width="1.5"/>
    <text x="590" y="162" font-size="11" font-weight="700" fill="#500724" text-anchor="middle">subscriber</text>
    <text x="590" y="180" font-size="9" fill="#500724" text-anchor="middle">publication → subscription</text>
    <text x="590" y="198" font-size="9" fill="#500724" text-anchor="middle">cross-version possible</text>
    <text x="590" y="214" font-size="9" fill="#500724" text-anchor="middle">select specific tables</text>
    <text x="590" y="230" font-size="9" fill="#500724" text-anchor="middle" font-style="italic">can accept its own writes</text>

    <text x="370" y="278" font-size="10" fill="#475569" text-anchor="middle" font-style="italic">streaming = physical, whole-cluster mirror · logical = selective, row-level copy</text>
  </g>
</svg>
```

**Streaming replication** is physical [1]. The primary ships WAL segments as they're generated; the standby applies them, producing a byte-identical copy of the entire cluster. It's the mechanism behind hot standbys (read-only replicas that serve queries) and the foundation of most HA setups. Constraints: same major version on both ends, entire cluster (can't pick one table), and the standby is read-only (unless promoted to primary).

**Logical replication** is a different unit of transfer — it ships row-level changes (INSERT/UPDATE/DELETE) [2]. The source defines a **publication** (a set of tables and change types); the target subscribes. Because it's row-level, it crosses version boundaries (great for major-version upgrades with near-zero downtime), selects specific tables, and the subscriber can accept its own writes (enabling multi-master-ish patterns). The cost: more overhead per change, and it doesn't replicate schema — the target schema must already exist.

## High availability: the failover problem

Replication gives me a copy; **high availability** is the problem of detecting primary failure and promoting a replica without operator intervention [3]. The challenge is split-brain — two nodes both thinking they're primary — which is why HA needs a consensus layer.

The de facto solution is **Patroni** [4]. Each Postgres instance runs a Patroni sidecar; the sidecars use a distributed key-value store (**etcd**, Consul, or ZooKeeper) to elect a leader and store cluster state. When the primary fails, the consensus store notices, and Patroni promotes the healthiest replica to primary automatically, then reconfigures clients. The roadmap's alternatives — Stolon, repmgr, PAF — solve the same problem with different architectures; Patroni is the most widely deployed.

## Connection pooling: PgBouncer and friends

A separate but adjacent concern is connection pooling [5]. Because Postgres uses a process per connection, thousands of client connections exhaust resources. **PgBouncer** sits between clients and Postgres, maintaining a small pool of real backend connections and multiplexing many client connections through them. The pooling modes trade off correctness for density:

- **Transaction pooling** — a backend is assigned only for the duration of a transaction. Highest density, but breaks session-level state (prepared statements, temporary tables, SET).
- **Session pooling** — a backend is held for the whole client session. Safe, but less efficient.
- **Statement pooling** — per-statement. Only works if transactions are never used across statements.

Alternatives like **Pgpool-II** add load balancing and query routing on top of pooling, and **Odyssey** is a multithreaded option for very high scale [6]. For most setups, PgBouncer in transaction-pooling mode in front of a primary-plus-replicas cluster is the standard.

## Load balancing reads

With a primary and read-only replicas, the next question is routing writes to the primary and reads to replicas [7]. **HAProxy** can health-check each Postgres instance and route connections, sending writes to the primary and distributing reads. **Consul** and **Keepalived** provide service discovery and virtual-IP failover respectively, so clients always reach a healthy node [8][9]. The full stack for a self-hosted HA cluster is often: Patroni (failover) + etcd (consensus) + PgBouncer (pooling) + HAProxy (routing) — each layer with one job.

## How I use this

For anything production, the default is: a primary plus at least one streaming-replica standby, Patroni + etcd automating failover, and PgBouncer in front to protect the backend process count. Reads that tolerate slight staleness go to the standby via HAProxy. Logical replication I reserve for specific jobs — major-version upgrades with minimal downtime, or feeding a subset of tables into a separate analytics database. And I treat HA testing as mandatory: a planned failover exercise (kill the primary, watch Patroni promote) is the only proof the setup actually works. Replication gives you a copy; the failover controller is what makes "available" true.

## References

[1] PostgreSQL Wiki, "Streaming Replication," 2024. [Online]. Available: [https://wiki.postgresql.org/wiki/Streaming_Replication](https://wiki.postgresql.org/wiki/Streaming_Replication)

[2] PostgreSQL Global Development Group, "Logical Replication," 2024. [Online]. Available: [https://www.postgresql.org/docs/current/logical-replication.html](https://www.postgresql.org/docs/current/logical-replication.html)

[3] Kinsta, "PostgreSQL replication," 2024. [Online]. Available: [https://kinsta.com/blog/postgresql-replication/](https://kinsta.com/blog/postgresql-replication/)

[4] Zalando, "Patroni," GitHub, 2024. [Online]. Available: [https://github.com/zalando/patroni](https://github.com/zalando/patroni)

[5] PgBouncer, "PgBouncer," 2024. [Online]. Available: [https://www.pgbouncer.org/](https://www.pgbouncer.org/)

[6] Yandex, "Odyssey," GitHub, 2024. [Online]. Available: [https://github.com/yandex/odyssey](https://github.com/yandex/odyssey)

[7] HAProxy, "HAProxy," 2024. [Online]. Available: [https://www.haproxy.org/](https://www.haproxy.org/)

[8] HashiCorp, "Consul," 2024. [Online]. Available: [https://www.consul.io/](https://www.consul.io/)

[9] Keepalived, "Keepalived," 2024. [Online]. Available: [https://www.keepalived.org/](https://www.keepalived.org/)

```quiz
Q: What is the unit of transfer in streaming (physical) replication?
- Row-level changes (INSERT/UPDATE/DELETE)
- WAL bytes, producing a byte-identical cluster copy on the standby
correct: 1
explain: Streaming replication ships WAL segments, so the standby is a physical mirror of the entire cluster. It's version-locked and cluster-wide, and the standby is normally read-only.

Q: Which replication type lets you replicate only specific tables and cross major PostgreSQL versions?
- Streaming replication
- Logical replication
correct: 1
explain: Logical replication transfers row changes via publication/subscription. Because it's row-level, the subscriber can run a different major version and subscribe to only selected tables.

Q: What problem does Patroni solve?
- It compresses WAL for faster shipping
- It automates cluster failover — detecting primary failure and promoting a replica, using a consensus store like etcd to avoid split-brain
correct: 1
explain: Patroni is a failover controller. Combined with etcd/Consul for consensus, it watches the primary and automatically promotes the healthiest replica, preventing two nodes from both acting as primary.

Q: PgBouncer in transaction pooling mode multiplexes many client connections onto few backend connections. What does it break?
- Nothing — it's fully transparent
- Session-level state like prepared statements, temp tables, and SET, because a backend is held only for the duration of a transaction
correct: 1
explain: Transaction pooling reassigns backends per transaction, so anything that assumes a stable session (server-side prepared statements, temp tables, session SET) breaks. Use session pooling when that state is required.

Q: In a typical self-hosted HA stack, what is the role of HAProxy?
- It replaces Postgres's query planner
- It health-checks nodes and routes client connections — writes to the primary, reads distributed to replicas
correct: 1
explain: HAProxy is the load balancer: it detects which node is primary (often via Patroni's REST API) and routes accordingly, distributing reads across replicas while sending writes to the primary.
```