High Availability Checkpoints

These checkpoints run against the three local nodes from the High Availability Hands-On Tutorial (primary on port 5432, standbys on 5442 and 5452, HTTP on 8080 to 8082). For the container setup in High Availability Cluster with Docker Compose, use ports 15432, 15442 and 15452 for the psql shortcuts and set PGPASSWORD; the HTTP ports of the containers are not published, so check health as shown in Checkpoint 1.

Each checkpoint lists what to run, what to expect and what it shows. Run them in order.

# Shortcuts used below
p()  { psql -h 127.0.0.1 -p 5432 -U postgres -d postgres "$@"; }
s1() { psql -h 127.0.0.1 -p 5442 -U postgres -d postgres "$@"; }
s2() { psql -h 127.0.0.1 -p 5452 -U postgres -d postgres "$@"; }

Checkpoint 1: All nodes are up

p  -c "SELECT role, sync_mode, is_read_only, standby_count FROM pg_replication_status;"
s1 -c "SELECT role, sync_mode, is_read_only FROM pg_replication_status;"
s2 -c "SELECT role, sync_mode, is_read_only FROM pg_replication_status;"
for port in 8080 8081 8082; do curl -s "http://127.0.0.1:$port/health"; echo; done

Expected: the primary reports role = primary, is_read_only = false and standby_count = 2; each standby reports role = standby and is_read_only = true; every /health call returns {"status":"ok"}.

In the Docker Compose setup the HTTP port 8080 of the containers is not published, so the curl loop above cannot reach it from the host. Check health with docker compose ps (each service shows healthy) or inside each container:

for svc in primary standby1 standby2; do
  docker compose exec "$svc" curl -fsS http://127.0.0.1:8080/health; echo
done

What it shows: a standby answers SQL and health checks only after it has verified the primary’s history and caught up.

Checkpoint 2: The primary sees its standbys

p  -c "SELECT node_id, address, state, sync_mode, flush_lsn, apply_lsn, lag_bytes, lag_ms FROM pg_replication_standbys;"
s1 -c "SELECT primary_address, connection_state, primary_lsn, local_lsn, lag_bytes FROM pg_replication_primary;"

Expected: two rows on the primary, one per standby, with flush_lsn and apply_lsn close to the primary’s current_lsn; on the standby, one row describing its connection to the primary.

Checkpoint 3: Changes replicate

p -c "CREATE TABLE IF NOT EXISTS checkpoint_log (id INT PRIMARY KEY, note TEXT);"
p -c "INSERT INTO checkpoint_log VALUES (1, 'written on the primary');"
s1 -c "SELECT * FROM checkpoint_log;"
s2 -c "SELECT * FROM checkpoint_log;"

Expected: the same row on both standbys. Table definitions replicate with the rows.

Checkpoint 4: A standby refuses writes

s1 -c "INSERT INTO checkpoint_log VALUES (2, 'written on a standby');"
p  -c "SELECT count(*) FROM checkpoint_log;"

Expected: ERROR: transparent forwarding is disabled for a history-guarded standby (SQLSTATE 25006) and a count of 1 on the primary.

What it shows: applications send writes to the primary. Forwarding from a standby is planned; HeliosProxy can route writes and reads to different nodes.

Checkpoint 5: A standby restart resumes

Stop standby 2 (Ctrl+C in its terminal) and start it again right away with the same command, without writing on the primary in between.

s2 -c "SELECT count(*) FROM checkpoint_log;"
p  -c "SELECT node_id, state, apply_lsn FROM pg_replication_standbys;"

Expected: standby 2 serves the same count and appears again on the primary.

What it shows: a standby resumes from its stored position. It can do so only while the primary still holds the WAL above that position; after the primary checkpoints past it (every 1,000 entries or 30 seconds of writing by default) the standby is refused with full resync required. See Restart a standby.

Checkpoint 6: Reads continue while the primary is down

Stop the primary (Ctrl+C in its terminal), then:

s1 -c "SELECT count(*) FROM checkpoint_log;"
p  -c "SELECT 1;"

Expected: the standby returns the count; the primary connection is refused.

Start the primary again with its original command and repeat Checkpoint 2.

Expected: both standbys reconnect (they retry after 5 seconds, then with a delay that doubles up to 60 seconds) and appear again in pg_replication_standbys.

What it shows: during a primary outage the standbys keep serving reads and writes wait for the primary. Promotion and automatic failover are planned.

Checkpoint 7: Switchover is refused

STANDBY_ID=$(s1 -At -c "SELECT node_id FROM pg_replication_status;")
p -c "SWITCHOVER CHECK '$STANDBY_ID';"
p -c "SWITCHOVER TO '$STANDBY_ID';"

Expected: SWITCHOVER CHECK reports can_proceed = false; SWITCHOVER TO returns Switchover refused: runtime switchover/promotion is unsupported ... and no role changes. Check with Checkpoint 1.

Checkpoint 8: Clean up

p -c "DROP TABLE checkpoint_log;"

Stop the standbys and then the primary. To start a new setup, remove the three data directories; standbys always need a data directory that does not exist yet.