PostgreSQL 17 Replication on Rocky Linux 10 [Step-by-Step]

PostgreSQL streaming replication continuously ships Write-Ahead Log (WAL) records from a primary server to one or more standby servers. The standby replays these records in near real-time, providing high availability and read scaling. If the primary fails, a standby can be promoted to take over.

This guide covers setting up PostgreSQL 17 streaming replication on Rocky Linux 10 / AlmaLinux 10 with one primary and one standby server. For the official documentation, see the PostgreSQL replication docs.

Prerequisites

2 servers running Rocky Linux 10 or AlmaLinux 10
Primary server: 10.0.1.10
Standby server: 10.0.1.11
Root or sudo access on both servers
Port 5432/tcp open between both servers
PostgreSQL 17 installed on both servers

Step 1: Install PostgreSQL 17 on Both Servers

Run these commands on both the primary and standby servers. Install the PGDG repository and PostgreSQL 17.

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-10-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf install -y postgresql17-server postgresql17

Step 2: Initialize and Configure the Primary Server

On the primary server (10.0.1.10), initialize the database cluster.

sudo /usr/pgsql-17/bin/postgresql-17-setup initdb

Start and enable PostgreSQL.

sudo systemctl enable --now postgresql-17

Create a Replication User

Create a dedicated user for replication with the REPLICATION privilege.

sudo -u postgres psql

Run the following SQL:

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'StrongReplPass2026';
\q

Configure postgresql.conf

Edit the main configuration file.

sudo vi /var/lib/pgsql/17/data/postgresql.conf

Set these replication parameters:

# Listen on all interfaces
listen_addresses = '*'

# WAL settings for replication
wal_level = replica
max_wal_senders = 5
wal_keep_size = 512MB

# Optional: synchronous replication (uncomment for sync mode)
# synchronous_standby_names = 'standby1'

Configure pg_hba.conf

Allow the standby server to connect for replication.

sudo vi /var/lib/pgsql/17/data/pg_hba.conf

Add this line at the end:

# Replication connection from standby
host    replication     replicator      10.0.1.11/32        scram-sha-256

Restart PostgreSQL to apply the changes.

sudo systemctl restart postgresql-17

Step 3: Configure Firewall on Both Servers

Open port 5432/tcp on both servers to allow PostgreSQL traffic.

sudo firewall-cmd --permanent --add-port=5432/tcp
sudo firewall-cmd --reload

Step 4: Set Up the Standby Server

On the standby server (10.0.1.11), stop PostgreSQL if it’s running and remove any existing data directory.

sudo systemctl stop postgresql-17
sudo rm -rf /var/lib/pgsql/17/data/*

Take a base backup from the primary server using pg_basebackup. This copies the entire database cluster and sets up the standby configuration automatically.

sudo -u postgres pg_basebackup -h 10.0.1.10 -U replicator -D /var/lib/pgsql/17/data -Fp -Xs -P -R

The flags:

-h 10.0.1.10 – primary server address
-U replicator – replication user
-D /var/lib/pgsql/17/data – target data directory
-Fp – plain format (not tar)
-Xs – stream WAL during backup
-P – show progress
-R – create standby.signal and write connection info to postgresql.auto.conf

Enter the replication password when prompted. The -R flag is the key – it automatically creates the standby.signal file and adds the primary_conninfo to postgresql.auto.conf.

Verify the standby configuration was created.

$ cat /var/lib/pgsql/17/data/postgresql.auto.conf
primary_conninfo = 'user=replicator password=StrongReplPass2026 channel_binding=prefer host=10.0.1.10 port=5432 sslmode=prefer'

$ ls /var/lib/pgsql/17/data/standby.signal
/var/lib/pgsql/17/data/standby.signal

Start PostgreSQL on the standby.

sudo systemctl enable --now postgresql-17

Step 5: Verify Replication Status

On the primary server, check the replication status.

$ sudo -u postgres psql -c "SELECT client_addr, state, sent_lsn, replay_lsn FROM pg_stat_replication;"
 client_addr |   state   |  sent_lsn  | replay_lsn
-------------+-----------+------------+------------
 10.0.1.11   | streaming | 0/3000148  | 0/3000148
(1 row)

The state should show streaming and sent_lsn should match replay_lsn (meaning the standby is caught up).

On the standby server, confirm it’s in recovery mode.

$ sudo -u postgres psql -c "SELECT pg_is_in_recovery();"
 pg_is_in_recovery
--------------------
 t
(1 row)

Step 6: Test Replication

On the primary, create a test database and table.

sudo -u postgres psql -c "CREATE DATABASE testdb;"
sudo -u postgres psql -d testdb -c "CREATE TABLE test_repl (id serial PRIMARY KEY, data text); INSERT INTO test_repl (data) VALUES ('replication works');"

On the standby, verify the data appears (standby is read-only).

$ sudo -u postgres psql -d testdb -c "SELECT * FROM test_repl;"
 id |       data
----+-------------------
  1 | replication works
(1 row)

Clean up the test database on the primary.

sudo -u postgres psql -c "DROP DATABASE testdb;"

Promoting a Standby to Primary

If the primary fails, promote the standby to become the new primary.

sudo -u postgres pg_ctl promote -D /var/lib/pgsql/17/data

Or use SQL on the standby.

sudo -u postgres psql -c "SELECT pg_promote();"

After promotion, the standby exits recovery mode and accepts writes. The standby.signal file is automatically removed.

Conclusion

PostgreSQL 17 streaming replication is running between the primary and standby on Rocky Linux 10 / AlmaLinux 10. For automated failover in production, consider tools like Patroni or repmgr. Monitor replication lag with Prometheus and Grafana using the postgres_exporter, and manage your databases with pgAdmin.