Replication and High Availability

Running PostgreSQL in production means planning for two things: what happens when the primary server fails, and how you handle more read traffic than one server can serve. Replication solves both. A standby server that stays in sync with the primary can take over in seconds after a failure, and in the meantime it can serve read-only queries to offload the primary. Connection pooling solves a separate but equally important problem — the overhead of short-lived application connections at scale.

Setup

Everything below runs on PostgreSQL 17.11 in Docker (postgres:17): a primary, a streaming standby cloned with pg_basebackup, a logical-replication subscriber, and PgBouncer, all on one Docker network. Container IDs, IP addresses, LSNs and timings will differ on your machine. Run the commands from a directory that holds the .sql files. Each file is piped into psql inside the right container with docker exec -i <container> psql -U postgres < file.sql. On Git Bash for Windows, set MSYS_NO_PATHCONV=1 first so container paths aren’t rewritten.

Start with the network and the primary. The until loops wait for the server to be ready. The image runs a temporary server during first-time initialization, so the first loop waits for that to finish.

docker network create pr-sql-net
docker run -d --name pr-sql-primary --network pr-sql-net -e POSTGRES_PASSWORD=secret postgres:17
until docker logs pr-sql-primary 2>&1 | grep -q "PostgreSQL init process complete"; do sleep 1; done
until docker exec pr-sql-primary pg_isready -q; do sleep 1; done

$ docker network create pr-sql-net
f72fc19ca664ed6f6033986fe6a5ae24a7ac30041f76b77826b44abd4e9ede49
$ docker run -d --name pr-sql-primary --network pr-sql-net -e POSTGRES_PASSWORD=secret postgres:17
98ab2fa6f4d89f77ea1e5da24460109b8d6d0b2a232abe48bbedf8b1845ead39
$ until docker logs pr-sql-primary 2>&1 | grep -q "PostgreSQL init process complete"; do sleep 1; done
$ until docker exec pr-sql-primary pg_isready -q; do sleep 1; done

The two long hex strings are the IDs of the new network and container. No configuration flags were passed, so the primary starts with PostgreSQL’s defaults. That matters below.

The Write-Ahead Log (WAL)

Every change in PostgreSQL — INSERT, UPDATE, DELETE, DDL — is written to the Write-Ahead Log before it is applied to the actual data files. This “write ahead” guarantee means that if the server crashes mid-write, PostgreSQL replays the WAL on restart to recover a consistent state without data loss.

WAL is also the foundation of replication: ship the WAL records to another server, and it can replay the same changes. Each position in the WAL is a log sequence number (LSN). This file creates the orders table used for the rest of the lesson, loads 10,000 rows, and measures how far the LSN moved:

-- wal.sql
SELECT pg_current_wal_lsn() AS before_lsn \gset

CREATE TABLE orders (
    id         SERIAL PRIMARY KEY,
    total      NUMERIC(10, 2) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT now()
);
INSERT INTO orders (total) SELECT n % 100 FROM generate_series(1, 10000) AS n;

SELECT :'before_lsn' AS before_lsn,
       pg_current_wal_lsn() AS after_lsn,
       pg_size_pretty(pg_current_wal_lsn() - :'before_lsn') AS wal_written,
       pg_walfile_name(pg_current_wal_lsn()) AS wal_file;

$ docker exec -i pr-sql-primary psql -U postgres < wal.sql
CREATE TABLE
INSERT 0 10000
 before_lsn | after_lsn | wal_written |         wal_file         
------------+-----------+-------------+--------------------------
 0/14EF878  | 0/168E910 | 1660 kB     | 000000010000000000000001
(1 row)

\gset is a psql command that stores the first query’s result in the variable before_lsn. Subtracting two LSNs gives a byte count, so loading 10,000 small rows (and their index) wrote about 1.6 MB of WAL. That is the stream a standby has to receive. WAL lives in 16 MB segment files under pg_wal/, and wal_file names the segment the current position falls in.

Streaming Replication (Physical)

Streaming replication sends WAL bytes from the primary to one or more standby servers in near real time. The standby is a byte-for-byte copy of the primary — same schema, same data, same PostgreSQL major version. It’s the standard approach for high availability and disaster recovery.

Setting up a primary

Three settings matter: wal_level must be at least replica (enough WAL detail for a standby), max_wal_senders must allow a connection per standby, and wal_keep_size keeps extra WAL around for a standby that falls behind. Check the current values before changing anything, and change them with ALTER SYSTEM, which writes postgresql.auto.conf for you:

-- primary.sql
SELECT name, setting FROM pg_settings
WHERE name IN ('wal_level', 'max_wal_senders', 'wal_keep_size', 'listen_addresses');

ALTER SYSTEM SET wal_keep_size = '1GB';  -- keep WAL for a standby that falls behind
SELECT pg_reload_conf();

CREATE ROLE replicator WITH LOGIN REPLICATION PASSWORD 'rep-pass';

$ docker exec -i pr-sql-primary psql -U postgres < primary.sql
       name       | setting 
------------------+---------
 listen_addresses | *
 max_wal_senders  | 10
 wal_keep_size    | 0
 wal_level        | replica
(4 rows)

ALTER SYSTEM
 pg_reload_conf 
----------------
 t
(1 row)

CREATE ROLE

The defaults already suit streaming replication: wal_level is replica and up to 10 WAL senders are allowed. The postgres image also sets listen_addresses = '*'. Only wal_keep_size needed changing, and it takes effect on a reload. wal_level is not something to change for streaming. You only need to raise it to logical for logical replication, later in this lesson.

Next, let the replicator role connect with the replication protocol. A pg_hba.conf line whose database column is replication matches physical replication connections. samenet means any address on the server’s own subnets, which here is the Docker network.

-- hba.sql
SELECT pg_reload_conf();
SELECT line_number, type, database, user_name, address, auth_method
FROM pg_hba_file_rules
WHERE type = 'host';

$ docker exec pr-sql-primary sh -c 'echo "host  replication  replicator  samenet  scram-sha-256" >> "$PGDATA/pg_hba.conf"'
$ docker exec -i pr-sql-primary psql -U postgres < hba.sql
 pg_reload_conf 
----------------
 t
(1 row)

 line_number | type |   database    |  user_name   |  address  |  auth_method  
-------------+------+---------------+--------------+-----------+---------------
         119 | host | {all}         | {all}        | 127.0.0.1 | trust
         121 | host | {all}         | {all}        | ::1       | trust
         125 | host | {replication} | {all}        | 127.0.0.1 | trust
         126 | host | {replication} | {all}        | ::1       | trust
         128 | host | {all}         | {all}        | all       | scram-sha-256
         129 | host | {replication} | {replicator} | samenet   | scram-sha-256
(6 rows)

pg_hba_file_rules shows the file as PostgreSQL parsed it. The new rule is line 129. Line 128, the image’s own host all all all rule, does not cover it: all does not match physical replication connections.

Setting up a standby

A standby starts life as a copy of the primary’s data directory, taken with pg_basebackup. This container runs pg_basebackup the first time it starts, then starts PostgreSQL on the copy:

docker run -d --name pr-sql-replica --network pr-sql-net --user postgres \
  -e PGPASSWORD=rep-pass postgres:17 bash -c '
    D=/var/lib/postgresql/data
    [ -f $D/PG_VERSION ] || pg_basebackup -h pr-sql-primary -U replicator -D $D \
      -R -X stream -C -S replica_slot
    chmod 0700 $D
    exec postgres -D $D -c cluster_name=standby1'
until docker exec pr-sql-replica pg_isready -q 2>/dev/null; do sleep 1; done

$ docker run -d --name pr-sql-replica --network pr-sql-net --user postgres \
  -e PGPASSWORD=rep-pass postgres:17 bash -c '
    D=/var/lib/postgresql/data
    [ -f $D/PG_VERSION ] || pg_basebackup -h pr-sql-primary -U replicator -D $D \
      -R -X stream -C -S replica_slot
    chmod 0700 $D
    exec postgres -D $D -c cluster_name=standby1'
03268f63fa5cead183d0e7b5100af3f8e77f1399abaee82b3f671432585e1020
$ until docker exec pr-sql-replica pg_isready -q 2>/dev/null; do sleep 1; done

-X stream copies the WAL generated during the backup, -C -S replica_slot creates a replication slot for this standby (see “Replication Slots” below), and cluster_name=standby1 becomes the name the standby reports to the primary. -R writes the standby configuration. Look at what it wrote:

$ docker exec pr-sql-replica cat /var/lib/postgresql/data/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
wal_keep_size = '1GB'
primary_conninfo = 'user=replicator password=''rep-pass'' channel_binding=prefer host=''pr-sql-primary'' port=5432 sslmode=prefer sslnegotiation=postgres sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'
primary_slot_name = 'replica_slot'
$ docker exec pr-sql-replica ls /var/lib/postgresql/data/standby.signal
/var/lib/postgresql/data/standby.signal

The copy brought the primary’s postgresql.auto.conf along, including wal_keep_size. -R appended primary_conninfo, which says how to reach the primary, and primary_slot_name. It also created the empty standby.signal file. When PostgreSQL 12 or later finds standby.signal in the data directory, it starts as a standby and keeps applying WAL from primary_conninfo instead of finishing recovery.

Monitoring lag

Run this on the primary. pg_stat_replication has one row per connected standby:

-- lag.sql
SELECT
  client_addr,
  state,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  (sent_lsn - replay_lsn) AS replication_lag_bytes
FROM pg_stat_replication;

$ docker exec -i pr-sql-primary psql -U postgres < lag.sql
 client_addr |   state   | sent_lsn  | write_lsn | flush_lsn | replay_lsn | replication_lag_bytes 
-------------+-----------+-----------+-----------+-----------+------------+-----------------------
 172.21.0.3  | streaming | 0/3000000 | 0/3000000 | 0/3000000 | 0/3000000  |                     0
(1 row)

The primary has sent (sent_lsn) the same LSN that the standby has written, flushed to disk and replayed (replay_lsn), so the lag is 0 bytes. On a loaded system these four drift apart: sent_lsn minus replay_lsn is how much WAL the standby still has to apply. pg_wal_lsn_diff(sent_lsn, replay_lsn) computes the same number.

Synchronous vs Asynchronous

By default replication is asynchronous — the primary confirms a commit before the standby has applied it. This maximizes write performance but means a crash could lose the last few transactions that hadn’t yet reached the standby.

Synchronous replication makes the primary wait for at least one standby to confirm WAL before acknowledging the commit. This gives zero data loss but increases write latency proportionally to the network round-trip time between primary and standby.

-- sync.sql
SHOW synchronous_standby_names;
SELECT application_name, sync_state FROM pg_stat_replication;

$ docker exec -i pr-sql-primary psql -U postgres < sync.sql
 synchronous_standby_names 
---------------------------
 
(1 row)

 application_name | sync_state 
------------------+------------
 standby1         | async
(1 row)

synchronous_standby_names is empty, so no standby is synchronous and standby1 (the cluster_name from above) is async. Naming it in synchronous_standby_names makes it sync. Practice task 4 does that and shows what happens to commits when the synchronous standby goes away.

Using Standbys for Read Scaling

A standby in hot standby mode accepts read-only queries, offloading reporting and analytics workloads from the primary. This is one of the most cost-effective ways to scale read capacity — you’re already running the standby for HA, so serving reads from it costs nothing extra.

-- standby_read.sql
SELECT pg_is_in_recovery();
SELECT count(*) FROM orders WHERE created_at >= '2024-01-01';

$ docker exec -i pr-sql-replica psql -U postgres < standby_read.sql
 pg_is_in_recovery 
-------------------
 t
(1 row)

 count 
-------
 10000
(1 row)

pg_is_in_recovery() returns true only on a standby. The orders table and its 10,000 rows were created on the primary before the backup, and every later change arrives through WAL. Point read-heavy queries at the standby and send all writes to the primary. A standby rejects writes, as Practice task 2 shows.

Logical Replication

Logical replication decodes WAL into row-level changes (INSERT/UPDATE/DELETE) and streams them as logical messages. This is more flexible than physical replication because it doesn’t require an identical copy — you can replicate a subset of tables, replicate between different PostgreSQL major versions, or stream changes to non-PostgreSQL systems like Kafka or Debezium.

Publications and Subscriptions

The publisher defines what to replicate. The subscriber consumes it, much like a consumer reading a message queue. Publish orders on the primary. The primary still has the default wal_level:

-- pub.sql
SHOW wal_level;
CREATE PUBLICATION my_pub FOR TABLE orders;
GRANT SELECT ON orders TO replicator;  -- the initial copy reads the table as replicator

$ docker exec -i pr-sql-primary psql -U postgres < pub.sql
 wal_level 
-----------
 replica
(1 row)

WARNING:  "wal_level" is insufficient to publish logical changes
HINT:  Set "wal_level" to "logical" before creating subscriptions.
CREATE PUBLICATION
GRANT

PostgreSQL creates the publication but warns that it can’t publish anything yet. You could also publish every table with FOR ALL TABLES. Now start a third server for the subscriber, create a matching table there, and subscribe:

$ docker run -d --name pr-sql-sub --network pr-sql-net -e POSTGRES_PASSWORD=secret postgres:17
d1c98e4baaee00eb62bfec3db17a6cd058f99ef69168336c944aa05846e248c4
$ until docker logs pr-sql-sub 2>&1 | grep -q "PostgreSQL init process complete"; do sleep 1; done
$ until docker exec pr-sql-sub pg_isready -q; do sleep 1; done

-- sub.sql
CREATE TABLE orders (
    id         SERIAL PRIMARY KEY,
    total      NUMERIC(10, 2) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE SUBSCRIPTION my_sub
  CONNECTION 'host=pr-sql-primary dbname=postgres user=replicator password=rep-pass'
  PUBLICATION my_pub;

$ docker exec -i pr-sql-sub psql -U postgres < sub.sql
CREATE TABLE
ERROR:  could not create replication slot "my_sub": ERROR:  logical decoding requires "wal_level" >= "logical"

A subscription needs a logical replication slot on the publisher, and that needs wal_level = logical. replica only logs enough to replay pages byte for byte, while logical adds the information needed to decode WAL back into row changes. The failed CREATE SUBSCRIPTION rolled back, so only the table exists on the subscriber.

Raising wal_level

wal_level only changes when the server starts. Set it with ALTER SYSTEM and restart:

-- wal_level.sql
ALTER SYSTEM SET wal_level = 'logical';
SELECT pg_reload_conf();

-- wal_check.sql
SELECT name, setting, pending_restart, source, sourcefile
FROM pg_settings WHERE name = 'wal_level';

$ docker exec -i pr-sql-primary psql -U postgres < wal_level.sql
ALTER SYSTEM
 pg_reload_conf 
----------------
 t
(1 row)

$ docker exec -i pr-sql-primary psql -U postgres < wal_check.sql
   name    | setting | pending_restart | source  | sourcefile 
-----------+---------+-----------------+---------+------------
 wal_level | replica | t               | default | 
(1 row)

$ docker restart pr-sql-primary
pr-sql-primary
$ until docker exec pr-sql-primary pg_isready -q; do sleep 1; done
$ docker exec -i pr-sql-primary psql -U postgres < wal_check.sql
   name    | setting | pending_restart |       source       |                  sourcefile                   
-----------+---------+-----------------+--------------------+-----------------------------------------------
 wal_level | logical | f               | configuration file | /var/lib/postgresql/data/postgresql.auto.conf
(1 row)

After the reload, the setting is still replica with pending_restart = t: the new value is waiting in the file. After the restart it is logical, read from postgresql.auto.conf. The standby reconnects to the primary by itself after the restart (its slot shows active again in “Replication Slots” below). logical includes everything replica provides, so streaming replication is unaffected. It costs somewhat more WAL, which is why it isn’t the default.

Watch out for one trap. A setting passed on the server’s command line, as postgres -c or as arguments after the image name in docker run, outranks postgresql.auto.conf. ALTER SYSTEM then appears to succeed and is silently ignored after the restart. This throwaway server is started with -c wal_level=replica, and the same two files are run against it:

$ docker run -d --name pr-sql-demo -e POSTGRES_PASSWORD=secret postgres:17 -c wal_level=replica
3ef5414b09d0c78e81079b5ad60affe9d999ed939634862e67bc008779cb5652
$ until docker logs pr-sql-demo 2>&1 | grep -q "PostgreSQL init process complete"; do sleep 1; done
$ until docker exec pr-sql-demo pg_isready -q; do sleep 1; done
$ docker exec -i pr-sql-demo psql -U postgres < wal_level.sql
ALTER SYSTEM
 pg_reload_conf 
----------------
 t
(1 row)

$ docker exec -i pr-sql-demo psql -U postgres < wal_check.sql
   name    | setting | pending_restart |    source    | sourcefile 
-----------+---------+-----------------+--------------+------------
 wal_level | replica | t               | command line | 
(1 row)

$ docker restart pr-sql-demo
pr-sql-demo
$ until docker exec pr-sql-demo pg_isready -q; do sleep 1; done
$ docker exec -i pr-sql-demo psql -U postgres < wal_check.sql
   name    | setting | pending_restart |    source    | sourcefile 
-----------+---------+-----------------+--------------+------------
 wal_level | replica | f               | command line | 
(1 row)

$ docker rm -f pr-sql-demo
pr-sql-demo

source is command line, and the restart kept replica even though ALTER SYSTEM wrote logical to the file. If a server is started with -c options, change those settings where the server is started, not with ALTER SYSTEM. The Practice tasks below pass -c wal_level=logical when the primary starts. wal_level only changes at startup, so that sets it without the restart shown above. As this demo shows, it also outranks any later ALTER SYSTEM.

Now the subscription works. Create it again, wait until the initial copy has arrived, and check its status:

-- sub2.sql
CREATE SUBSCRIPTION my_sub
  CONNECTION 'host=pr-sql-primary dbname=postgres user=replicator password=rep-pass'
  PUBLICATION my_pub;

-- sub_check.sql
SELECT count(*) FROM orders;

-- subenabled lives in pg_subscription, not pg_stat_subscription
SELECT subname, received_lsn IS NOT NULL AS receiving FROM pg_stat_subscription;
SELECT subname, subenabled FROM pg_subscription;

$ docker exec -i pr-sql-sub psql -U postgres < sub2.sql
NOTICE:  created replication slot "my_sub" on publisher
CREATE SUBSCRIPTION
$ until docker exec pr-sql-sub psql -U postgres -Atc 'SELECT count(*) FROM orders' | grep -qx 10000; do sleep 1; done
$ docker exec -i pr-sql-sub psql -U postgres < sub_check.sql
 count 
-------
 10000
(1 row)

 subname | receiving 
---------+-----------
 my_sub  | t
(1 row)

 subname | subenabled 
---------+------------
 my_sub  | t
(1 row)

CREATE SUBSCRIPTION created the slot my_sub on the publisher, copied the existing 10,000 rows, and then switched to streaming changes. The two status views hold different things: pg_stat_subscription has the live worker state, such as received_lsn, and pg_subscription has the definition, such as whether it is enabled. New rows follow within moments:

$ docker exec pr-sql-primary psql -U postgres -c "INSERT INTO orders (total) VALUES (12.34);"
INSERT 0 1
$ until docker exec pr-sql-sub psql -U postgres -Atc 'SELECT count(*) FROM orders' | grep -qx 10001; do sleep 1; done
$ docker exec pr-sql-sub psql -U postgres -c "SELECT id, total FROM orders ORDER BY id DESC LIMIT 1;"
  id   | total 
-------+-------
 10001 | 12.34
(1 row)

The subscriber must have the same table definitions (columns, types) as the publisher. Schema changes are not replicated: apply DDL on both sides yourself. Practice task 5 shows what breaks when you forget.

Replication Slots

A replication slot makes the primary keep WAL until the slot’s consumer has received it. That is what lets a standby or subscriber fall behind and catch up without losing data. It also means WAL piles up for as long as a slot’s consumer stays away. This file creates a slot that nothing uses, writes more rows, and compares the slots:

-- slots.sql
-- a slot nobody is using, as if its standby had gone away
SELECT pg_create_physical_replication_slot('my_slot', true) IS NOT NULL AS created;
INSERT INTO orders (total) SELECT n % 100 FROM generate_series(1, 10000) AS n;

SELECT slot_name, slot_type, active, restart_lsn,
       pg_size_pretty(pg_current_wal_lsn() - restart_lsn) AS retained
FROM pg_replication_slots;

SELECT pg_drop_replication_slot('my_slot');
SELECT slot_name FROM pg_replication_slots;

$ docker exec -i pr-sql-primary psql -U postgres < slots.sql
 created 
---------
 t
(1 row)

INSERT 0 10000
  slot_name   | slot_type | active | restart_lsn | retained 
--------------+-----------+--------+-------------+----------
 replica_slot | physical  | t      | 0/30085B8   | 1461 kB
 my_sub       | logical   | t      | 0/3006468   | 1470 kB
 my_slot      | physical  | f      | 0/3006380   | 1470 kB
(3 rows)

 pg_drop_replication_slot 
--------------------------
 
(1 row)

  slot_name   
--------------
 replica_slot
 my_sub
(2 rows)

There are three slots: the standby’s physical replica_slot, the subscriber’s logical my_sub, and the unused my_slot. true in pg_create_physical_replication_slot reserves WAL from the moment the slot is created. Straight after the INSERT, all three still hold back about 1.4 MB, because the active slots’ restart_lsn hasn’t moved forward yet. The difference is active. An active slot’s restart_lsn advances as its consumer makes progress. my_slot has no consumer, so its restart_lsn never moves, and everything written after it has to stay on disk. Nothing limits this unless you set max_slot_wal_keep_size. Monitor active and the retained WAL, and drop slots you no longer need with pg_drop_replication_slot. An inactive slot holding back WAL can fill your disk. Practice task 3 shows the same thing with a real standby that is down.

Connection Pooling with PgBouncer

Each PostgreSQL connection is a separate OS process. Each process has its own memory, which grows with the work it does and with settings such as work_mem, and every new connection pays for process start-up and authentication. Hundreds of mostly idle application connections add up to a lot of memory and CPU. PgBouncer sits between the application and PostgreSQL, maintaining a smaller pool of actual server connections and multiplexing many client connections onto them.

Pooling Modes

Session mode — one server connection per client session. Lowest compatibility risk. Barely reduces connection count.

Transaction mode — a server connection is held only for the duration of a transaction, then returned to the pool. Most efficient. Incompatible with SET, advisory locks, and LISTEN/NOTIFY that persist across transactions.

Statement mode — connection returned after every statement. Rarely used; incompatible with multi-statement transactions.

This configuration puts PgBouncer in front of the primary in transaction mode, with at most 5 server connections. This first version has a comment at the end of the pool_mode line:

; pgbouncer.ini
[databases]
postgres = host=pr-sql-primary port=5432 dbname=postgres

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
admin_users = postgres
pool_mode = transaction    ; return the server connection after each transaction
; how many client connections PgBouncer accepts
max_client_conn = 1000
; real PostgreSQL connections per database/user pair
default_pool_size = 5

$ cat userlist.txt
"postgres" "secret"

userlist.txt holds the passwords PgBouncer accepts from clients, and PgBouncer also uses them to log in to PostgreSQL. Start PgBouncer with both files mounted, and wait for it:

$ docker run -d --name pr-sql-bouncer --network pr-sql-net \
  -v "$PWD/pgbouncer.ini":/etc/pgbouncer/pgbouncer.ini:ro \
  -v "$PWD/userlist.txt":/etc/pgbouncer/userlist.txt:ro edoburu/pgbouncer
8191df51ee6e04ab0c4e3caa3f26b06c7d8ae3ffa00ab4d70c5a8f8520ca5c4e
$ until [ "$(docker inspect -f '{{.State.Status}}' pr-sql-bouncer)" = exited ]; do sleep 1; done
$ docker logs pr-sql-bouncer
Starting /usr/bin/pgbouncer /etc/pgbouncer/pgbouncer.ini...
2026-10-11 16:53:52.815 UTC [1] ERROR invalid value "transaction    ; return the server connection after each transaction" for parameter pool_mode in configuration (/etc/pgbouncer/pgbouncer.ini:11)
2026-10-11 16:53:52.815 UTC [1] FATAL cannot load config file
$ docker rm -f pr-sql-bouncer
pr-sql-bouncer

PgBouncer exited straight away. Its config format only allows a comment on a line of its own, so everything after the = on line 11, the spaces and the ; comment included, became the value of pool_mode. Move the comment to its own line:

; pgbouncer.ini
[databases]
postgres = host=pr-sql-primary port=5432 dbname=postgres

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
admin_users = postgres
; return the server connection to the pool after each transaction
pool_mode = transaction
; how many client connections PgBouncer accepts
max_client_conn = 1000
; real PostgreSQL connections per database/user pair
default_pool_size = 5

$ docker run -d --name pr-sql-bouncer --network pr-sql-net \
  -v "$PWD/pgbouncer.ini":/etc/pgbouncer/pgbouncer.ini:ro \
  -v "$PWD/userlist.txt":/etc/pgbouncer/userlist.txt:ro edoburu/pgbouncer
3e4b4e6044e96440e272a13e4641f546aec0a6c8b2f7e11e750194cbf6be084e
$ until docker exec pr-sql-sub pg_isready -h pr-sql-bouncer -p 6432 -q; do sleep 1; done

Now load the standard pgbench tables on the primary and send 40 concurrent clients through PgBouncer on port 6432. Each client runs 250 read-only transactions. Then count the connections the primary actually served:

-- pool_check.sql
SELECT client_addr, state, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend' AND client_addr IS NOT NULL
GROUP BY client_addr, state;

$ docker exec pr-sql-primary pgbench -i -q -U postgres postgres
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
100000 of 100000 tuples (100%) of pgbench_accounts done (elapsed 0.04 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 0.13 s (drop tables 0.00 s, create tables 0.01 s, client-side generate 0.06 s, vacuum 0.04 s, primary keys 0.03 s).
$ docker exec -e PGPASSWORD=secret pr-sql-sub pgbench -h pr-sql-bouncer -p 6432 -U postgres -n -S -c 40 -j 4 -t 250 postgres
pgbench (17.11 (Debian 17.11-1.pgdg13+2))
transaction type: <builtin: select only>
scaling factor: 1
query mode: simple
number of clients: 40
number of threads: 4
maximum number of tries: 1
number of transactions per client: 250
number of transactions actually processed: 10000/10000
number of failed transactions: 0 (0.000%)
latency average = 2.388 ms
initial connection time = 73.177 ms
tps = 16748.062249 (without initial connection time)
$ docker exec -i pr-sql-primary psql -U postgres < pool_check.sql
 client_addr | state | count 
-------------+-------+-------
 172.21.0.5  | idle  |     5
(1 row)

All 10,000 transactions from 40 clients went through. Afterwards the primary holds just 5 connections, all from PgBouncer’s address (172.21.0.5), sitting idle in the pool for the next clients. That is default_pool_size. Pointed straight at the primary, pgbench -c 40 would open 40 connections, and so 40 server processes. The latency and tps figures depend on your machine.

Applications connect to PgBouncer on port 6432 exactly as they would connect to PostgreSQL on 5432. The change is transparent to application code.

Connection String Best Practices

Production connection strings should always include SSL mode, a connection timeout, and an application name, which shows up in pg_stat_activity for monitoring. For a primary and standby, list both hosts, and say which kind of server you want with target_session_attrs. This file reports where the session landed:

-- conn.sql
\conninfo
SELECT pg_is_in_recovery(), current_setting('application_name') AS app;

$ docker exec -i pr-sql-sub psql "postgresql://postgres:secret@pr-sql-replica:5432,pr-sql-primary:5432/postgres?target_session_attrs=read-write&connect_timeout=5&application_name=web-api" < conn.sql
You are connected to database "postgres" as user "postgres" on host "pr-sql-primary" (address "172.21.0.2") at port "5432".
 pg_is_in_recovery |   app   
-------------------+---------
 f                 | web-api
(1 row)

The replica is listed first, but target_session_attrs=read-write made the driver check each server and skip the one that only allows reads. The session landed on pr-sql-primary, tagged web-api. For read-only traffic, ask for a standby instead. This time the primary is listed first:

$ docker exec -i pr-sql-sub psql "postgresql://postgres:secret@pr-sql-primary:5432,pr-sql-replica:5432/postgres?target_session_attrs=prefer-standby&connect_timeout=5&application_name=reports" < conn.sql
You are connected to database "postgres" as user "postgres" on host "pr-sql-replica" (address "172.21.0.3") at port "5432".
 pg_is_in_recovery |   app   
-------------------+---------
 t                 | reports
(1 row)

prefer-standby (PostgreSQL 14 and later) skipped the primary and landed on the replica, but it would still fall back to the primary if no standby answered. any, the default, doesn’t check: it takes the first listed server that accepts the connection, standby or not. The other values are read-only, primary and standby. These options are part of libpq, the C library behind psql and most PostgreSQL drivers. JDBC and some other drivers have their own equivalents. Now add sslmode=require:

$ docker exec -i pr-sql-sub psql "postgresql://postgres:secret@pr-sql-primary:5432/postgres?sslmode=require&connect_timeout=5" < conn.sql
psql: error: connection to server at "pr-sql-primary" (172.21.0.2), port 5432 failed: server does not support SSL, but SSL was required

require refuses to fall back to an unencrypted connection, and the stock postgres image has no certificate configured, so the connection fails. In production, configure SSL on the server and use sslmode=require or, better, verify-full, which also checks the server’s certificate.

Automatic Failover with Patroni

When the primary fails, a standby has to be promoted to take over writes. Here is that done by hand: stop the primary, try the read-write connection string from above, promote the standby, and try again.

-- promote.sql
SELECT pg_promote();
SELECT pg_is_in_recovery();
INSERT INTO orders (total) VALUES (99.99);

$ docker stop pr-sql-primary
pr-sql-primary
$ docker exec -i pr-sql-sub psql "postgresql://postgres:secret@pr-sql-replica:5432,pr-sql-primary:5432/postgres?target_session_attrs=read-write&connect_timeout=5&application_name=web-api" < conn.sql
psql: error: connection to server at "pr-sql-replica" (172.21.0.3), port 5432 failed: session is read-only
could not translate host name "pr-sql-primary" to address: Name or service not known
$ docker exec -i pr-sql-replica psql -U postgres < promote.sql
 pg_promote 
------------
 t
(1 row)

 pg_is_in_recovery 
-------------------
 f
(1 row)

INSERT 0 1
$ docker exec -i pr-sql-sub psql "postgresql://postgres:secret@pr-sql-replica:5432,pr-sql-primary:5432/postgres?target_session_attrs=read-write&connect_timeout=5&application_name=web-api" < conn.sql
You are connected to database "postgres" as user "postgres" on host "pr-sql-replica" (address "172.21.0.3") at port "5432".
 pg_is_in_recovery |   app   
-------------------+---------
 f                 | web-api
(1 row)

With the primary gone and the standby still read-only, there was no writable server, so the connection failed. pg_promote() ended recovery on the standby. It accepted an INSERT straight away, and the unchanged connection string now finds it as the read-write server. Clients with both hosts in their connection string follow a failover without a config change.

Doing this by hand is slow and error-prone, and in a real outage there is a trap: if the old primary comes back, two servers accept writes (split-brain). Patroni is the standard tool that automates promotion. It runs next to each PostgreSQL server and uses a distributed consensus store (etcd, Consul or ZooKeeper) to decide which node is the leader. How fast it reacts depends on its leader-key ttl, which defaults to 30 seconds: a standby can only take over after the old leader’s key expires.

Key concepts:

• Leader election via etcd/Consul — only one node can hold the leader key at a time
• Fencing — the old primary is blocked from accepting writes before promotion to prevent split-brain
• HAProxy or DNS-based routing in front of the cluster routes writes to the current leader

patronictl list shows the cluster’s members and which one leads. patronictl switchover performs a planned, graceful version of the promotion above, for example before maintenance. Setting up Patroni and etcd is beyond this lesson. What it automates is the pg_promote() step you just ran by hand, plus the fencing that keeps the old primary from coming back as a second writer.

Clean up

Remove the containers and the network before starting the Practice tasks, which build their own pair with the same names:

$ docker rm -f pr-sql-bouncer pr-sql-sub pr-sql-replica pr-sql-primary
pr-sql-bouncer
pr-sql-sub
pr-sql-replica
pr-sql-primary
$ docker network rm pr-sql-net
pr-sql-net

Practice

These tasks run a real primary and standby on PostgreSQL 17.11 in Docker (postgres:17). As in the lesson, the hosts are containers on a Docker network, reached by name. Each .sql file is copied into the right container and run there with psql -U postgres -f. The $ psql lines show which file ran, and the task says which server it ran on. LSNs, PIDs and IP addresses will differ on your machine.

1. Build the pair: a primary configured as in “Setting up a primary”, and a standby cloned with pg_basebackup. Then run the lesson’s lag query (lag.sql) on the primary, and SELECT pg_is_in_recovery(); on both servers.

#!/usr/bin/env bash
# setup.sh — a primary + streaming standby pair
set -e
export MSYS_NO_PATHCONV=1   # only matters on Git Bash for Windows

docker network create pr-sql-net

docker run -d --name pr-sql-primary --network pr-sql-net -e POSTGRES_PASSWORD=secret \
  postgres:17 -c wal_level=logical -c max_wal_senders=5 -c wal_keep_size=1GB

# the image runs a temporary server during init, so wait for the real one
until docker logs pr-sql-primary 2>&1 | grep -q "PostgreSQL init process complete"; do sleep 1; done
until docker exec pr-sql-primary pg_isready -q; do sleep 1; done

docker exec pr-sql-primary psql -U postgres \
  -c "CREATE ROLE replicator WITH LOGIN REPLICATION PASSWORD 'rep-pass';"
docker exec pr-sql-primary sh -c \
  'echo "host  replication  replicator  samenet  scram-sha-256" >> "$PGDATA/pg_hba.conf"'
docker exec pr-sql-primary psql -U postgres -c "SELECT pg_reload_conf();"

# clone the primary once (-R writes standby.signal + primary_conninfo, -C -S creates a slot)
docker run -d --name pr-sql-replica --network pr-sql-net --user postgres \
  -e PGPASSWORD=rep-pass postgres:17 bash -c '
    D=/var/lib/postgresql/data
    [ -f $D/PG_VERSION ] || pg_basebackup -h pr-sql-primary -U replicator -D $D \
      -R -X stream -C -S replica_slot
    chmod 0700 $D
    exec postgres -D $D -c cluster_name=standby1'

until docker exec pr-sql-replica pg_isready -q 2>/dev/null; do sleep 1; done

$ bash setup.sh
<container id>
<container id>
CREATE ROLE
 pg_reload_conf 
----------------
 t
(1 row)

<container id>

$ psql -U postgres -f lag.sql
 client_addr |   state   | sent_lsn  | write_lsn | flush_lsn | replay_lsn | replication_lag_bytes 
-------------+-----------+-----------+-----------+-----------+------------+-----------------------
 172.21.0.3  | streaming | 0/3000000 | 0/3000000 | 0/3000000 | 0/3000000  |                     0
(1 row)

$ docker exec pr-sql-replica psql -U postgres -c "SELECT pg_is_in_recovery();"
 pg_is_in_recovery 
-------------------
 t
(1 row)

$ docker exec pr-sql-primary psql -U postgres -c "SELECT pg_is_in_recovery();"
 pg_is_in_recovery 
-------------------
 f
(1 row)

2. Create an orders table on the primary and insert three rows. On the standby, check that they arrived, then try to insert a row there.

-- write.sql, run on the primary
CREATE TABLE orders (
    id         SERIAL PRIMARY KEY,
    total      NUMERIC(10, 2) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT now()
);

INSERT INTO orders (total) VALUES (19.99), (5.00), (42.50);

-- standby_write.sql, run on the standby
SELECT pg_is_in_recovery();
SELECT count(*) FROM orders;
INSERT INTO orders (total) VALUES (1.00);

$ psql -U postgres -f write.sql
CREATE TABLE
INSERT 0 3
$ psql -U postgres -f standby_write.sql
 pg_is_in_recovery 
-------------------
 t
(1 row)

 count 
-------
     3
(1 row)

psql:standby_write.sql:4: ERROR:  cannot execute INSERT in a read-only transaction

3. See what a replication slot does while its standby is down. Stop the standby, insert 100000 rows on the primary, and query pg_replication_slots together with pg_current_wal_lsn(). Then start the standby again and repeat the query.

-- slot.sql, run on the primary (slot_after.sql is the same SELECT without the INSERT)
INSERT INTO orders (total) SELECT n % 100 FROM generate_series(1, 100000) AS n;

SELECT slot_name, slot_type, active, restart_lsn,
       pg_current_wal_lsn(),
       pg_current_wal_lsn() - restart_lsn AS retained_bytes
FROM pg_replication_slots;

$ docker stop pr-sql-replica
pr-sql-replica
$ psql -U postgres -f slot.sql
INSERT 0 100000
  slot_name   | slot_type | active | restart_lsn | pg_current_wal_lsn | retained_bytes 
--------------+-----------+--------+-------------+--------------------+----------------
 replica_slot | physical  | f      | 0/3033088   | 0/3E77048          |       14958528
(1 row)

$ docker start pr-sql-replica
pr-sql-replica
$ psql -U postgres -f slot_after.sql
  slot_name   | slot_type | active | restart_lsn | pg_current_wal_lsn | retained_bytes 
--------------+-----------+--------+-------------+--------------------+----------------
 replica_slot | physical  | t      | 0/3E77048   | 0/3E77048          |              0
(1 row)

4. Make the standby synchronous. setup.sh started it with cluster_name=standby1, so it connects with that application_name. Set synchronous_standby_names on the primary and check sync_state. Then stop the standby and, in one terminal, run an INSERT with statement_timeout = '3s'. From a second terminal, look at what that session is waiting on, and cancel it.

-- sync.sql, on the primary
ALTER SYSTEM SET synchronous_standby_names = 'standby1';
SELECT pg_reload_conf();
SELECT pg_sleep(1);
SELECT application_name, sync_state FROM pg_stat_replication;

-- sync_down.sql, terminal 1, on the primary
SET statement_timeout = '3s';
INSERT INTO orders (total) VALUES (777.77);

-- sync_wait.sql, terminal 2, on the primary
SELECT pid, wait_event_type, wait_event, state, query
FROM pg_stat_activity
WHERE wait_event = 'SyncRep';

SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE wait_event = 'SyncRep';

-- sync_check.sql, terminal 2, on the primary
SELECT count(*) FROM orders WHERE total = 777.77;

$ psql -U postgres -f sync.sql
ALTER SYSTEM
 pg_reload_conf 
----------------
 t
(1 row)

 pg_sleep 
----------
 
(1 row)

 application_name | sync_state 
------------------+------------
 standby1         | sync
(1 row)

$ docker stop pr-sql-replica
pr-sql-replica

$ psql -U postgres -f sync_wait.sql
 pid | wait_event_type | wait_event | state  |                    query                    
-----+-----------------+------------+--------+---------------------------------------------
 195 | IPC             | SyncRep    | active | INSERT INTO orders (total) VALUES (777.77);
(1 row)

 pg_cancel_backend 
-------------------
 t
(1 row)

$ psql -U postgres -f sync_down.sql
SET
psql:sync_down.sql:3: WARNING:  canceling wait for synchronous replication due to user request
DETAIL:  The transaction has already committed locally, but might not have been replicated to the standby.
INSERT 0 1

$ psql -U postgres -f sync_check.sql
 count 
-------
     1
(1 row)

5. With synchronous_standby_names reset as at the end of task 4 (otherwise the inserts below hang waiting for a synchronous standby), set up logical replication from the primary to a third server, pr-sql-sub. Publish only orders, create the same table on the subscriber, and subscribe. Then break it on purpose: on the primary, insert one row, run ALTER TABLE orders ADD COLUMN note TEXT;, and insert a row that sets note. Check the subscriber and its log.

-- logical_pub.sql, on the primary
CREATE PUBLICATION my_pub FOR TABLE orders;
GRANT SELECT ON orders TO replicator;

-- logical_sub.sql, on the subscriber
CREATE TABLE orders (
    id         SERIAL PRIMARY KEY,
    total      NUMERIC(10, 2) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE SUBSCRIPTION my_sub
  CONNECTION 'host=pr-sql-primary dbname=postgres user=replicator password=rep-pass'
  PUBLICATION my_pub;

-- logical_check.sql, on the subscriber
SELECT count(*), max(id) FROM orders;
SELECT subname, apply_error_count FROM pg_stat_subscription_stats;

-- logical_ddl.sql, on the primary
INSERT INTO orders (total) VALUES (10.00);
ALTER TABLE orders ADD COLUMN note TEXT;
INSERT INTO orders (total, note) VALUES (20.00, 'gift wrap');
SELECT count(*), max(id) FROM orders;

$ psql -U postgres -f logical_pub.sql
CREATE PUBLICATION
GRANT
$ docker run -d --name pr-sql-sub --network pr-sql-net -e POSTGRES_PASSWORD=secret postgres:17
<container id>
$ until docker logs pr-sql-sub 2>&1 | grep -q "PostgreSQL init process complete"; do sleep 1; done
$ until docker exec pr-sql-sub pg_isready -q; do sleep 1; done
$ psql -U postgres -f logical_sub.sql
CREATE TABLE
psql:logical_sub.sql:10: NOTICE:  created replication slot "my_sub" on publisher
CREATE SUBSCRIPTION
$ until docker exec pr-sql-sub psql -U postgres -Atc "SELECT count(*) FROM orders" | grep -qx 100004; do sleep 1; done
$ psql -U postgres -f logical_check.sql
 count  |  max   
--------+--------
 100004 | 100004
(1 row)

 subname | apply_error_count 
---------+-------------------
 my_sub  |                 0
(1 row)

$ psql -U postgres -f logical_ddl.sql
INSERT 0 1
ALTER TABLE
INSERT 0 1
 count  |  max   
--------+--------
 100006 | 100006
(1 row)

$ until docker logs pr-sql-sub 2>&1 | grep -q "missing replicated column"; do sleep 1; done
$ psql -U postgres -f logical_check.sql
 count  |  max   
--------+--------
 100005 | 100005
(1 row)

 subname | apply_error_count 
---------+-------------------
 my_sub  |                 1
(1 row)

$ docker logs pr-sql-sub 2>&1 | grep "missing replicated column" | head -n 1
2026-10-11 16:57:09.607 UTC [86] ERROR:  logical replication target relation "public.orders" is missing replicated column: "note"

-- logical_fix.sql, on the subscriber
ALTER TABLE orders ADD COLUMN note TEXT;
SELECT pg_sleep(6);  -- wait for the apply worker's next retry
SELECT id, total, note FROM orders WHERE id > 100004 ORDER BY id;

$ psql -U postgres -f logical_fix.sql
ALTER TABLE
 pg_sleep 
----------
 
(1 row)

   id   | total |   note    
--------+-------+-----------
 100005 | 10.00 | 
 100006 | 20.00 | gift wrap
(2 rows)