This runbook deploys coord2 as the PostgreSQL Streaming Replication Standby for the primary Citus coordinator (coord1).
Unlike the workers, coord2 is NOT registered as a Citus worker. Its sole responsibility is to maintain an up-to-date replica of the coordinator database so it can be promoted if coord1 fails.
| Host | Role | WireGuard IP |
|---|---|---|
| coord1 | Primary Coordinator | 92.243.18.209 |
| coord2 | Coordinator Standby | 92.243.18.66 |
| worker1 | Citus Worker | 92.243.18.201 |
| worker2 | Citus Worker | 92.243.18.208 |
This guide assumes:
coord1 has already been fully configuredreplicator role already exists on coord1psql --version
sudo pg_lsclusters
Expected:
Ver Cluster Port Status
16 main 5432 online
Although this server is a standby, it must have the same Citus version as the primary.
sudo apt update
sudo apt install -y postgresql-16-citus-14.1
Verify:
dpkg -l | grep postgresql-16-citus
Check current preload libraries.
sudo -u postgres psql -Atc \
"SHOW shared_preload_libraries;"
If empty:
sudo pg_conftool 16 main set shared_preload_libraries citus
If another extension is already configured:
sudo pg_conftool 16 main set \
shared_preload_libraries 'citus,pg_stat_statements'
Restart PostgreSQL.
sudo systemctl restart postgresql
Verify.
sudo -u postgres psql -Atc \
"SHOW shared_preload_libraries;"
Expected:
citus
The standby will receive a copy of the primary's data directory.
Stop PostgreSQL.
sudo systemctl stop postgresql
Remove the existing cluster.
sudo pg_dropcluster --stop 16 main
Create an empty data directory.
sudo mkdir -p /var/lib/postgresql/16/main
sudo chown postgres:postgres /var/lib/postgresql/16/main
Take a base backup from the primary.
sudo -u postgres pg_basebackup \
-h 92.243.18.209 \
-D /var/lib/postgresql/16/main \
-U replicator \
-P \
-R \
-X stream
During execution, enter the replication password created on coord1.
The -R option automatically creates the standby configuration.
Configure PostgreSQL to listen on localhost and the WireGuard interface.
sudo pg_conftool 16 main set \
listen_addresses '127.0.0.1,92.243.18.66'
Append to /etc/hosts.
92.243.18.209 coord1
92.243.18.66 coord2
92.243.18.201 worker1
92.243.18.208 worker2
Verify.
getent hosts coord1
Locate the active file.
sudo -u postgres psql -Atc \
"SHOW hba_file;"
Create a backup.
HBA=$(sudo -u postgres psql -Atc "SHOW hba_file")
sudo cp "$HBA" "$HBA.bak"
Append:
# ==================================================
# Primary Coordinator
# ==================================================
host all all 92.243.18.209/32 trust
Reload PostgreSQL.
sudo systemctl reload postgresql
Validate.
sudo -u postgres psql -P pager=off -c "
SELECT
line_number,
address,
auth_method,
error
FROM pg_hba_file_rules
WHERE address::text LIKE '92.243.%'
OR error IS NOT NULL;"
The error column should be empty.
If UFW is enabled.
sudo ufw allow in on wg0 \
from 92.243.18.209 \
to 92.243.18.66 \
port 5432 proto tcp
Verify.
sudo ufw status numbered
sudo systemctl start postgresql
Verify.
sudo systemctl status postgresql
On coord2.
sudo -u postgres psql -c "
SELECT
pg_is_in_recovery();
"
Expected:
t
The standby should always report true while replicating.
On coord1.
SELECT
application_name,
client_addr,
state,
sync_state
FROM pg_stat_replication;
Expected:
application_name | coord2
client_addr | 92.243.18.66
state | streaming
echo "=== PostgreSQL ==="
sudo pg_lsclusters
echo
echo "=== Citus Package ==="
dpkg -l | grep postgresql-16-citus
echo
echo "=== Preload ==="
sudo -u postgres psql -Atc \
"SHOW shared_preload_libraries;"
echo
echo "=== Recovery Mode ==="
sudo -u postgres psql -Atc \
"SELECT pg_is_in_recovery();"
echo
echo "=== PostgreSQL Listener ==="
sudo ss -lntp | grep 5432
echo
echo "=== HBA Validation ==="
sudo -u postgres psql -Atc "
SELECT count(*)
FROM pg_hba_file_rules
WHERE error IS NOT NULL;"
Expected results:
shared_preload_libraries = citus127.0.0.1:543292.243.18.66:5432pg_is_in_recovery() returns truecoord1 reports an active streaming replication connectionpg_hba.conf validation returns 0 errorsIf the primary coordinator becomes unavailable, promote the standby.
sudo -u postgres pg_ctlcluster 16 main promote
Verify.
sudo -u postgres psql -c "
SELECT pg_is_in_recovery();
"
Expected:
f
The promoted server is now the active coordinator.
After the standby is successfully replicating:
coord1 appear on coord2.