repmgr 17

Option A: AWS Marketplace console

• Open the SecureAnyCloud Hardened Adminer & Database Admin and Backup Tools AMIs listing on AWS Marketplace. Choose View purchase options, then Subscribe. One subscription covers every version in the listing.
• When the subscription is active, choose Launch your software.
• Choose One-click launch from AWS Marketplace, or Launch from EC2 Console for the full launch wizard. In Version, choose repmgr 17 on Ubuntu 24.04, then choose your Region.
• Pick an instance type and your key pair. m5.large is recommended, and the software charge is $0.04 per vCPU per hour.
• Set the security group. The listing's default group allows SSH (22) only, so restrict it to your own IP address. Step 6 adds the PostgreSQL rule for your cluster.
• Choose your VPC and subnet, leave the metadata defaults (the image already requires IMDSv2), then choose Launch.

Option B: AWS CLI

Run the commands below on your own workstation. Replace the AMI ID with the one shown for your Region on the Marketplace launch page. Replace the VPC, subnet, key pair and CIDR with your own.

Launch one instance for a single primary, or use --count 2 for a primary and a standby:

SG=$(aws ec2 create-security-group --group-name repmgr-cluster \
  --description "repmgr PostgreSQL cluster" --vpc-id vpc-0123456789abcdef0 \
  --query GroupId --output text)
aws ec2 authorize-security-group-ingress --group-id "$SG" --protocol tcp --port 22 --cidr <your-mgmt-cidr>
aws ec2 run-instances \
  --image-id ami-0123456789abcdef0 \
  --instance-type m5.large \
  --count 2 \
  --key-name my-key-pair \
  --security-group-ids "$SG" \
  --subnet-id subnet-0123456789abcdef0 \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=repmgr-node}]'

You do not need --metadata-options, because the AMI already requires IMDSv2.

Connect over SSH as ubuntu with the key pair you chose:

ssh -i ~/.ssh/my-key-pair.pem ubuntu@<public-ip>

First boot finishes within a minute of launch. It creates this instance's TLS certificate, sets the passwords, creates the repmgr role and database, registers this node as the primary, and then writes the credentials file.

Show the connection details with the command below. It leaves out the two password lines. Read them with sudo cat /root/repmgr-credentials.txt and keep them in your secrets manager:

sudo grep -vE '^(POSTGRES_PASSWORD|REPMGR_PASSWORD)=' /root/repmgr-credentials.txt

# repmgr 5.5 + PostgreSQL 17 - generated on first boot by repmgr-firstboot.service.
# These values are unique to this instance. Store them somewhere safe. POSTGRES_HOST=<private-ip> POSTGRES_PORT=5432 POSTGRES_USER=postgres POSTGRES_SSLMODE=require REPMGR_USER=repmgr REPMGR_DATABASE=repmgr REPMGR_NODE_ID=1 REPMGR_NODE_NAME=ip-172-31-80-193 TLS_CERTIFICATE=/etc/postgresql/17/main/tls/server.crt TLS_CERTIFICATE_SHA256=<certificate-sha256-fingerprint>
# PostgreSQL listens on 5432 on every interface but admits network clients over TLS (scram-sha-256) only.
# The security group opens SSH 22 only: open 5432 to your standby and application subnets yourself.
# Standbys and the other cluster nodes must also be listed in /etc/sac/repmgr-egress-allow.conf
# (then:
sudo systemctl reload repmgr-egress-guard).
# Cluster:
sudo -u postgres repmgr -f /etc/repmgr.conf cluster show
# Self-test:
sudo /usr/local/sbin/repmgr-selftest.sh
# User guide: https://secureanycloud.com/stacks/repmgr-17/

POSTGRES_PASSWORD is the postgres superuser's password for network logins. REPMGR_PASSWORD is the replication and management account that repmgr uses between nodes.

On the instance itself, sudo -u postgres psql logs in without a password.

Check the installed versions, the service states and the listening ports:

psql --version sudo -u postgres repmgr --version systemctl is-active postgresql@17-main.service repmgrd.service repmgr-egress-guard.service
sudo ss -Hltn

psql (PostgreSQL) 17.11 (Ubuntu 17.11-1.pgdg24.04+2) repmgr 5.5.0 active active active LISTEN 0 200 0.0.0.0:5432 0.0.0.0:* LISTEN 0 4096 0.0.0.0:22 0.0.0.0:* LISTEN 0 4096 127.0.0.53%lo:53 0.0.0.0:* LISTEN 0 4096 127.0.0.54:53 0.0.0.0:* LISTEN 0 200 [::]:5432 [::]:* LISTEN 0 4096 [::]:22 [::]:*

PostgreSQL listens on 5432 and SSH on 22. Port 53 on 127.0.0.53 and 127.0.0.54 is the operating system's local DNS stub. repmgrd opens no port of its own.

Run repmgr as the postgres system user. cluster show lists every registered node. service status adds whether repmgrd is running on each node. node check runs repmgr's health checks on this node:

sudo -u postgres repmgr -f /etc/repmgr.conf cluster show
sudo -u postgres repmgr -f /etc/repmgr.conf service status
sudo -u postgres repmgr -f /etc/repmgr.conf node check

ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string ----+------------------+---------+-----------+----------+----------+----------+----------+------------------------------------------------------------------------------------------ 1 | ip-172-31-80-193 | primary | * running | | default | 100 | 1 | host=172.31.80.193 port=5432 user=repmgr dbname=repmgr connect_timeout=5 sslmode=require ID | Name | Role | Status | Upstream | repmgrd | PID | Paused? | Upstream last seen ----+------------------+---------+-----------+----------+---------+-------+---------+-------------------- 1 | ip-172-31-80-193 | primary | * running | | running | 13308 | no | n/a Node "ip-172-31-80-193": Server role: OK (node is primary) Replication lag: OK (N/A - node is primary) WAL archiving: OK (0 pending archive ready files) Upstream connection: OK (N/A - node is primary) Downstream servers: OK (this node has no downstream nodes) Replication slots: OK (node has no physical replication slots) Missing physical replication slots: OK (node has no missing physical replication slots) Configured data directory: OK (configured "data_directory" is "/var/lib/postgresql/17/main")

The node name is the instance's hostname, and the connection string uses its private IP address. repmgr's own connections use sslmode=require.

Log in to PostgreSQL as postgres over the instance's private address. The command reads the password from the credentials file at run time, so it never appears on screen:

export PGPASSWORD="$(sudo sed -n 's/^POSTGRES_PASSWORD=//p' /root/repmgr-credentials.txt)" H="$(sudo sed -n 's/^POSTGRES_HOST=//p' /root/repmgr-credentials.txt)" psql "host=$H port=5432 dbname=postgres user=postgres sslmode=require" \
  -c "SELECT current_user, current_setting('server_version') AS server_version, pg_is_in_recovery();" \
  -c '\conninfo' unset PGPASSWORD

current_user | server_version | pg_is_in_recovery --------------+------------------------------------+------------------- postgres | 17.11 (Ubuntu 17.11-1.pgdg24.04+2) | f (1 row) You are connected to database "postgres" as user "postgres" on host "172.31.80.193" at port "5432". SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql)

pg_hba.conf rejects a connection without TLS before any password is checked, and a wrong password is refused:

H="$(sudo sed -n 's/^POSTGRES_HOST=//p' /root/repmgr-credentials.txt)" for mode in disable require; do OUT="$(PGPASSWORD=<set-at-first-boot> psql "host=$H port=5432 dbname=postgres user=postgres sslmode=$mode" -c 'SELECT 1' 2>&1 || true)" echo "${OUT#*failed: }" done

FATAL: pg_hba.conf rejects connection for host "172.31.80.193", user "postgres", database "postgres", no encryption FATAL: password authentication failed for user "postgres"

The shipped self-test checks the whole node in one run. It verifies that:

• the credentials file and .pgpass agree
• both accounts log in over TLS, including a replication connection for repmgr
• wrong passwords and plaintext connections are refused
• node 1 is the running primary and repmgrd is monitoring it

It prints REPMGR_SELFTEST_OK on success:

sudo /usr/local/sbin/repmgr-selftest.sh

== repmgr primary self-test == OK: credentials file 0600 root; ~postgres/.pgpass (0600) holds the same repmgr password OK: PostgreSQL listens on 0.0.0.0:5432 and [::]:5432 OK: postgres: per-VM password logs in over TLS; a wrong password and a plaintext connection are refused (and the right one still works) OK: repmgr: logs in over TLS for a normal and a replication connection; a wrong password is refused OK: repmgr: node 1 is registered as the running primary OK: repmgrd is active and monitoring node 1 REPMGR_SELFTEST_OK: per-VM postgres and repmgr passwords log in over TLS (normal and replication), wrong passwords and plaintext are refused, node 1 is the running primary and repmgrd is monitoring it.

Two layers decide who can reach PostgreSQL, and a standby needs both.

Layer 1: the security group. Allow 5432 between the cluster's nodes only. If every node uses the same security group, a rule that names the group itself does exactly that. Run this on your workstation:

text aws ec2 authorize-security-group-ingress --group-id <your-sg-id> \ --protocol tcp --port 5432 --source-group <your-sg-id>

Add a second rule for your application subnet if applications connect from outside the cluster (--cidr <your-app-cidr>). Never open 5432 to 0.0.0.0/0.

Layer 2: the egress allow-list on every node. The postgres system user may connect only to the addresses listed in /etc/sac/repmgr-egress-allow.conf, so each node must list every other node. A standby connects to its primary to replicate, and repmgr cluster show and repmgrd's failover connect to every node.

On the primary, add the standby's private IP. On the standby, add the primary's:

bash echo '<standby-private-ip>' | sudo tee -a /etc/sac/repmgr-egress-allow.conf sudo systemctl reload repmgr-egress-guard sudo nft list set inet sac_repmgr_egress allow4

text 172.31.93.192 table inet sac_repmgr_egress { set allow4 { type ipv4_addr flags interval auto-merge elements = { 172.31.93.192 } } }

The reload applies the list without restarting PostgreSQL. Until a node is listed, a connection to it from the postgres user is refused (Connection refused), even when the security group allows it.

No pg_hba.conf change is needed. The shipped rules already admit repmgr replication connections from any address over TLS with scram-sha-256:

hostssl replication repmgr 0.0.0.0/0 scram-sha-256 hostssl all all 0.0.0.0/0 scram-sha-256 hostnossl all all 0.0.0.0/0 reject

To narrow them to your VPC, replace 0.0.0.0/0 (and ::/0) with your VPC CIDR in /etc/postgresql/17/main/pg_hba.conf on every node. Then run sudo systemctl reload postgresql@17-main.service.

Launch a second instance of this image in the same VPC and security group, and complete Step 6 on both instances. Every instance boots as its own primary, so you turn the second one into a standby of the first. Give it node ID 2, give repmgr the primary's repmgr password, and clone the primary's data over it.

Run the commands below on the standby instance. First stop repmgrd and PostgreSQL, then set the node ID. The node name and connection string are already this instance's own:

sudo systemctl stop repmgrd.service postgresql@17-main.service
sudo sed -i 's/^node_id=1$/node_id=2/' /etc/repmgr.conf
sudo grep -E '^(node_id|node_name|conninfo)=' /etc/repmgr.conf

node_id=2 node_name='ip-172-31-93-192' conninfo='host=172.31.93.192 port=5432 user=repmgr dbname=repmgr connect_timeout=5 sslmode=require'

Put the primary's REPMGR_PASSWORD into this node's ~postgres/.pgpass. You find it in /root/repmgr-credentials.txt on the primary. The read -rs command keeps it off the screen and out of your shell history:

read -rs -p 'Primary REPMGR_PASSWORD: ' P; echo printf '*:5432:*:repmgr:%s\n' "$P" | sudo -u postgres tee /var/lib/postgresql/.pgpass >/dev/null; unset P
sudo stat -c '%a %U' /var/lib/postgresql/.pgpass

Check that the clone can run, then clone. The -F option replaces this instance's own (empty) database with the primary's. The -c option asks the primary for an immediate checkpoint:

sudo -u postgres repmgr -d 'host=<primary-private-ip> user=repmgr dbname=repmgr sslmode=require' -f /etc/repmgr.conf standby clone --dry-run sudo -u postgres repmgr -d 'host=<primary-private-ip> user=repmgr dbname=repmgr sslmode=require' -f /etc/repmgr.conf standby clone -F -c

INFO: connecting to source node DETAIL: connection string is: user=repmgr dbname=repmgr host=172.31.80.48 sslmode=require DETAIL: current installation size is 29 MB ... INFO: all prerequisites for "standby clone" are met ... NOTICE: starting backup (using pg_basebackup)... NOTICE: standby clone (using pg_basebackup) complete

The preview run also prints "config_directory" set in repmgr.conf, but --copy-external-config-files not provided. This is expected. The standby keeps its own configuration in /etc/postgresql/17/main, including its own TLS certificate.

Start PostgreSQL, register the standby, and start repmgrd:

sudo systemctl start postgresql@17-main.service
sudo -u postgres repmgr -f /etc/repmgr.conf standby register
sudo systemctl start repmgrd.service

INFO: connecting to local node "ip-172-31-93-192" (ID: 2) INFO: connecting to primary database WARNING: --upstream-node-id not supplied, assuming upstream node is primary (node ID: 1) INFO: standby registration complete NOTICE: standby node "ip-172-31-93-192" (ID: 2) successfully registered

The standby now holds a copy of the primary's database, including its roles. The primary's postgres and repmgr passwords therefore apply to the standby, and the passwords in the standby's own /root/repmgr-credentials.txt no longer do.

The shipped self-test checks a primary, so run it on the primary.

On the primary, confirm that both nodes are listed, repmgrd runs on both, and the standby streams over TLS:

sudo -u postgres repmgr -f /etc/repmgr.conf service status
sudo -u postgres psql -X -c "SELECT client_addr, usename, state, sync_state, ssl FROM pg_stat_replication JOIN pg_stat_ssl USING (pid);"

ID | Name | Role | Status | Upstream | repmgrd | PID | Paused? | Upstream last seen ----+------------------+---------+-----------+-----------------+---------+------+---------+-------------------- 1 | ip-172-31-80-48 | primary | * running | | running | 1188 | no | n/a 2 | ip-172-31-93-192 | standby | running | ip-172-31-80-48 | running | 2714 | no | 0 second(s) ago client_addr | usename | state | sync_state | ssl ---------------+---------+-----------+------------+----- 172.31.93.192 | repmgr | streaming | async | t (1 row)

Write a row on the primary and read it on the standby. The standby is read-only:

-- on the primary
sudo -u postgres psql -c "CREATE TABLE repl_check (id int PRIMARY KEY, note text); INSERT INTO repl_check VALUES (1, 'written on the primary');" -- on the standby
sudo -u postgres psql -c "SELECT note, pg_is_in_recovery() FROM repl_check;" note | pg_is_in_recovery ------------------------+------------------- written on the primary | t (1 row)

repmgrd on the standby checks the primary continuously. If the primary stops answering, repmgrd tries to reconnect 6 times, 10 seconds apart, and then promotes the standby.

In SecureAnyCloud's test, stopping PostgreSQL on the primary promoted the standby about 50 seconds later:

[WARNING] unable to reconnect to node "ip-172-31-80-48" (ID: 1) after 6 attempts [NOTICE] this node is the only available candidate and will now promote itself [NOTICE] promoting standby to primary [DETAIL] promoting server "ip-172-31-93-192" (ID: 2) using pg_promote() [DETAIL] server "ip-172-31-93-192" (ID: 2) was successfully promoted to primary

Read the history with sudo -u postgres repmgr -f /etc/repmgr.conf cluster event and journalctl -u repmgrd.service. Point your applications at the new primary, or use a connection string that lists both nodes with target_session_attrs=read-write.

The failed primary must not start again as a primary. To bring it back as a standby of the new primary, run Step 7 on it and clone from the new primary's private IP. Register it with standby register --force. Its node ID stays 1:

sudo systemctl stop repmgrd.service postgresql@17-main.service
sudo -u postgres repmgr -d 'host=<primary-private-ip> user=repmgr dbname=repmgr sslmode=require' -f /etc/repmgr.conf standby clone -F -c sudo systemctl start postgresql@17-main.service
sudo -u postgres repmgr -f /etc/repmgr.conf standby register --force sudo systemctl start repmgrd.service

The image manages PostgreSQL with systemd and configures no service_* commands for repmgr. Use systemctl to start and stop PostgreSQL. Do not use repmgr's node rejoin on this image, because it starts PostgreSQL itself with pg_ctl.

Run the commands below to confirm three things. Every PostgreSQL and repmgr unit carries the metadata block, only postgres and repmgr have passwords, and the egress backstop is loaded:

systemctl show -p IPAddressDeny postgresql@17-main.service repmgrd.service repmgr-firstboot.service
sudo -u postgres psql -tAc "SELECT rolname, rolsuper, rolreplication, rolpassword IS NOT NULL AS has_password FROM pg_authid WHERE rolcanlogin ORDER BY 1;"
sudo nft list chain inet sac_repmgr_egress output | grep -c sac-backstop

IPAddressDeny=169.254.169.254/32 168.63.129.16/32 fd00:ec2::254/128 IPAddressDeny=169.254.169.254/32 168.63.129.16/32 fd00:ec2::254/128 IPAddressDeny=169.254.169.254/32 168.63.129.16/32 fd00:ec2::254/128 postgres|t|t|t repmgr|t|t|t 6