PostgreSQL 18 Streaming Replication (2 Standbys) on Oracle Linux 9

This instruction configures physical streaming replication from a PostgreSQL 18 primary server to two hot standby servers on Oracle Linux 9. Both standbys receive WAL data over SSL-encrypted streaming connections and use replication slots for reliable WAL retention. After completing this document, the primary streams WAL to both standbys in asynchronous mode, with an optional section for enabling synchronous replication.

This is Document 3 in a four-part series:

1. PostgreSQL 18 Installation on Oracle Linux 9 -- server installation with SSL/TLS and SCRAM-SHA-256
2. pgBackRest Backup Configuration for PostgreSQL 18 on Oracle Linux 9 -- backup with WAL archiving on the primary
3. Streaming Replication (2 Standbys) -- this document
4. pgBackRest with Replication -- backup from standby (future document)

Architecture:

+------------------+       WAL Stream (SSL)       +------------------+
|                  | ---------------------------> |                  |
|     PRIMARY      |       standby1_slot          |    STANDBY 1     |
|  {PRIMARY_HOST}  |                              |  {STANDBY1_HOST} |
|  {PRIMARY_IP}    |                              |  {STANDBY1_IP}   |
|                  |       WAL Stream (SSL)       +------------------+
|                  | --------------------------->
|                  |       standby2_slot          +------------------+
|                  |                              |                  |
+------------------+                              |    STANDBY 2     |
                                                  |  {STANDBY2_HOST} |
                                                  |  {STANDBY2_IP}   |
                                                  +------------------+

- Replication slots ensure WAL retention for each standby
- Both standbys are hot standbys (read-only queries allowed)
- Default mode: asynchronous (synchronous is optional)

Assumptions

This instruction assumes:

• Document 1 is complete on all three servers: PostgreSQL 18 is installed from the PGDG repository, SSL/TLS is configured, and SCRAM-SHA-256 authentication is enabled
• Document 2 is complete on the primary: pgBackRest is configured with WAL archiving active (wal_level = replica, archive_mode = on, archive_command set)
• The postgresql-18 service is running on the primary
• The standbys have PostgreSQL 18 RPMs installed from Document 1. If Document 1 was followed completely on the standbys (including initdb), Step 1 of each standby cloning section clears the data directory before pg_basebackup populates it
• SSL/TLS certificates are configured on all three servers (from Document 1)
• The postgres OS user exists on all three servers
• Data directory on all servers: /var/lib/pgsql/18/data
• Network connectivity exists between all three servers on port 5432/tcp
• DNS resolution or /etc/hosts entries exist for all three hostnames on all three servers
• SELinux is in enforcing mode on all three servers
• Firewalld is active on all three servers
• The reader will substitute {PRIMARY_IP}, {STANDBY1_IP}, {STANDBY2_IP}, {PRIMARY_HOST}, {STANDBY1_HOST}, {STANDBY2_HOST}, and {REPL_PASSWORD} with actual values

Placeholder definitions:

• {PRIMARY_IP} -- IP address of the primary server (e.g., 10.0.1.10)
• {PRIMARY_HOST} -- hostname of the primary server (e.g., pgprimary.example.com)
• {STANDBY1_IP} -- IP address of the first standby server (e.g., 10.0.1.11)
• {STANDBY1_HOST} -- hostname of the first standby server (e.g., pgstandby1.example.com)
• {STANDBY2_IP} -- IP address of the second standby server (e.g., 10.0.1.12)
• {STANDBY2_HOST} -- hostname of the second standby server (e.g., pgstandby2.example.com)
• {REPL_PASSWORD} -- a strong password for the replication user (at least 16 characters)

Prerequisites

Automatic setup

No additional packages are required. Physical streaming replication is built into the postgresql18-server package installed in Document 1. The pg_basebackup utility is included in the postgresql18 client package, which is a dependency of postgresql18-server.

Additional setup

1. Open the firewall on the standby servers

The primary server already has port 5432 open from Document 1. The standbys also need port 5432 open so the primary can be reached from the standbys and (for future use) so clients can connect to standbys for read-only queries.

Run on each standby ({STANDBY1_HOST} and {STANDBY2_HOST}):

sudo firewall-cmd --permanent --add-service=postgresql
sudo firewall-cmd --reload

Expected output:

success
success

2. Verify network connectivity from each standby to the primary

Run on each standby:

pg_isready -h {PRIMARY_IP} -p 5432

Expected output:

{PRIMARY_IP}:5432 - accepting connections

If this fails, verify firewall rules on the primary and network routing between the servers.

Configure the Primary Server

All steps in this section run on the primary server ({PRIMARY_HOST}).

Step 1: Create the replication user

Connect to PostgreSQL as the postgres superuser and create a dedicated replication role.

sudo -u postgres psql

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD '{REPL_PASSWORD}';
\q

Expected output:

CREATE ROLE

Verify the role exists and has the REPLICATION attribute:

sudo -u postgres psql -c "SELECT rolname, rolreplication FROM pg_roles WHERE rolname = 'replicator';"

Expected output:

  rolname   | rolreplication
------------+----------------
 replicator | t
(1 row)

Step 2: Configure pg_hba.conf for replication connections

Add entries that allow the replicator user to connect from both standby IP addresses for replication over SSL with SCRAM-SHA-256 authentication.

sudo -u postgres vi /var/lib/pgsql/18/data/pg_hba.conf

Add the following lines before any existing hostssl all entries:

# Streaming replication connections from standbys
hostssl replication  replicator  {STANDBY1_IP}/32  scram-sha-256
hostssl replication  replicator  {STANDBY2_IP}/32  scram-sha-256

The special database name replication in pg_hba.conf controls streaming replication connections. The hostssl type requires SSL for these connections.

Step 3: Configure postgresql.conf for replication

Document 2 already set wal_level = replica and archive_mode = on. Verify these and set the additional replication parameters.

sudo -u postgres vi /var/lib/pgsql/18/data/postgresql.conf

Verify or set the following parameters:

# Already set from Document 2 -- verify these are present:
wal_level = replica
archive_mode = on

# Replication settings:
max_wal_senders = 10
wal_keep_size = 1GB

max_wal_senders = 10 -- maximum number of concurrent WAL sender processes. The default in PostgreSQL 18 is 10, which is sufficient for 2 standbys plus pgBackRest. Verify it is not set lower.

wal_keep_size = 1GB -- minimum size of past WAL files retained in pg_wal as a safety net alongside replication slots. This provides WAL retention even if a slot is accidentally dropped.

hot_standby = on -- allows read-only queries on standbys. This is on by default in PostgreSQL 18 (and has been since PostgreSQL 10). No change is needed unless it was explicitly set to off.

Step 4: Create physical replication slots

Replication slots ensure the primary retains WAL segments until each standby has consumed them. Create one slot per standby.

sudo -u postgres psql

SELECT pg_create_physical_replication_slot('standby1_slot');
SELECT pg_create_physical_replication_slot('standby2_slot');
\q

Expected output:

 pg_create_physical_replication_slot
--------------------------------------
 (standby1_slot,)
(1 row)

 pg_create_physical_replication_slot
--------------------------------------
 (standby2_slot,)
(1 row)

Verify the slots exist:

sudo -u postgres psql -c "SELECT slot_name, slot_type, active FROM pg_replication_slots;"

Expected output:

  slot_name   | slot_type | active
--------------+-----------+--------
 standby1_slot | physical  | f
 standby2_slot | physical  | f
(2 rows)

Both slots show active = f because no standby has connected yet.

Warning: Replication slots retain WAL indefinitely until the consuming standby acknowledges receipt. If a standby is permanently decommissioned, you MUST drop its slot on the primary with SELECT pg_drop_replication_slot('slot_name');. Orphaned slots will cause pg_wal to grow without bound until the primary runs out of disk space.

Step 5: Reload the PostgreSQL configuration

Apply the pg_hba.conf and postgresql.conf changes. A reload is sufficient because wal_level and archive_mode were already set from Document 2 and max_wal_senders is already at its default value of 10. If you needed to increase max_wal_senders above 10 (not required for this setup), a restart would be required instead of a reload.

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

Expected output:

 pg_reload_conf
----------------
 t
(1 row)

If you changed max_wal_senders to a value other than 10, use a restart instead of the reload above:

sudo systemctl restart postgresql-18

Clone Standby 1 from Primary

All steps in this section run on standby 1 ({STANDBY1_HOST}) unless otherwise noted.

Step 1: Verify the data directory is empty

If initdb was previously run on this standby, the data directory must be cleared.

ls -la /var/lib/pgsql/18/data/

If the directory is empty (only . and ..), proceed to Step 2. If files exist, clear the directory:

Warning: This permanently deletes all contents of the data directory on this standby. Do not run this on the primary server.

sudo -u postgres rm -rf /var/lib/pgsql/18/data/*

Verify the directory is empty:

ls -la /var/lib/pgsql/18/data/

Expected output:

total 0
drwx------. 2 postgres postgres 6 ... .
drwx------. 4 postgres postgres 51 ... ..

Step 2: Run pg_basebackup

Clone the primary's data directory to this standby. The -R flag creates standby.signal and writes primary_conninfo to postgresql.auto.conf.

sudo -u postgres pg_basebackup \
  -h {PRIMARY_IP} \
  -U replicator \
  -D /var/lib/pgsql/18/data \
  -Fp -Xs -P -R

Enter the {REPL_PASSWORD} when prompted.

Expected output:

Password:
30318/30318 kB (100%), 1/1 tablespace

The -R flag (short for --write-recovery-conf) does the following:

• Creates the standby.signal file in the data directory
• Appends primary_conninfo to postgresql.auto.conf with the connection parameters used by this pg_basebackup command

Step 3: Configure the replication slot and application name

The pg_basebackup -R command generates primary_conninfo in postgresql.auto.conf, but it does not set primary_slot_name or application_name. Both must be added.

sudo -u postgres vi /var/lib/pgsql/18/data/postgresql.auto.conf

Locate the primary_conninfo line generated by pg_basebackup. It will look similar to:

primary_conninfo = 'user=replicator host={PRIMARY_IP} port=5432 sslmode=prefer ...'

Make the following changes:

1. Change sslmode=prefer to sslmode=require (or sslmode=verify-full if you have a trusted CA) to enforce SSL encryption
2. Add application_name=standby1 to the connection string

The edited primary_conninfo should look like:

primary_conninfo = 'user=replicator host={PRIMARY_IP} port=5432 sslmode=require application_name=standby1'

Add the primary_slot_name parameter below primary_conninfo:

primary_slot_name = 'standby1_slot'

The application_name=standby1 is required for synchronous replication to identify this standby in synchronous_standby_names. Even in asynchronous mode, it makes monitoring output clearer.

Step 4: Verify standby configuration files

Confirm that standby.signal exists and postgresql.auto.conf has the correct settings.

ls -la /var/lib/pgsql/18/data/standby.signal

Expected output:

-rw------- 1 postgres postgres 0 ... /var/lib/pgsql/18/data/standby.signal

sudo -u postgres grep -E 'primary_conninfo|primary_slot_name' /var/lib/pgsql/18/data/postgresql.auto.conf

Expected output:

primary_conninfo = 'user=replicator host={PRIMARY_IP} port=5432 sslmode=require application_name=standby1'
primary_slot_name = 'standby1_slot'

Step 5: Start the standby service

sudo systemctl enable --now postgresql-18

Expected output:

Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-18.service → /usr/lib/systemd/system/postgresql-18.service.

Verify the service is running:

sudo systemctl status postgresql-18 --no-pager

Expected output:

● postgresql-18.service - PostgreSQL 18 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-18.service; enabled; preset: disabled)
     Active: active (running) since ...

Step 6: Verify replication on standby 1

Confirm this server is in standby mode:

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

Expected output:

 pg_is_in_recovery
-------------------
 t
(1 row)

Check the WAL receiver status:

sudo -u postgres psql -c "SELECT status, sender_host, slot_name FROM pg_stat_wal_receiver;"

Expected output:

  status   | sender_host  |   slot_name
-----------+--------------+---------------
 streaming | {PRIMARY_IP} | standby1_slot
(1 row)

Step 7: Verify replication on the primary

Run on the primary ({PRIMARY_HOST}):

sudo -u postgres psql -c "SELECT client_addr, application_name, state, sync_state FROM pg_stat_replication;"

Expected output:

  client_addr  | application_name | state     | sync_state
---------------+------------------+-----------+------------
 {STANDBY1_IP} | standby1         | streaming | async
(1 row)

Clone Standby 2 from Primary

All steps in this section run on standby 2 ({STANDBY2_HOST}) unless otherwise noted. The procedure is identical to standby 1, with different slot and application names.

Step 1: Verify the data directory is empty

ls -la /var/lib/pgsql/18/data/

If files exist, clear the directory:

Warning: This permanently deletes all contents of the data directory on this standby. Do not run this on the primary server.

sudo -u postgres rm -rf /var/lib/pgsql/18/data/*

Verify the directory is empty:

ls -la /var/lib/pgsql/18/data/

Expected output:

total 0
drwx------. 2 postgres postgres 6 ... .
drwx------. 4 postgres postgres 51 ... ..

Step 2: Run pg_basebackup

sudo -u postgres pg_basebackup \
  -h {PRIMARY_IP} \
  -U replicator \
  -D /var/lib/pgsql/18/data \
  -Fp -Xs -P -R

Enter the {REPL_PASSWORD} when prompted.

Expected output:

Password:
30318/30318 kB (100%), 1/1 tablespace

Step 3: Configure the replication slot and application name

sudo -u postgres vi /var/lib/pgsql/18/data/postgresql.auto.conf

Edit primary_conninfo to set sslmode=require and application_name=standby2:

primary_conninfo = 'user=replicator host={PRIMARY_IP} port=5432 sslmode=require application_name=standby2'

Add the slot name:

primary_slot_name = 'standby2_slot'

Step 4: Verify standby configuration files

ls -la /var/lib/pgsql/18/data/standby.signal

Expected output:

-rw------- 1 postgres postgres 0 ... /var/lib/pgsql/18/data/standby.signal

sudo -u postgres grep -E 'primary_conninfo|primary_slot_name' /var/lib/pgsql/18/data/postgresql.auto.conf

Expected output:

primary_conninfo = 'user=replicator host={PRIMARY_IP} port=5432 sslmode=require application_name=standby2'
primary_slot_name = 'standby2_slot'

Step 5: Start the standby service

sudo systemctl enable --now postgresql-18

Expected output:

Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-18.service → /usr/lib/systemd/system/postgresql-18.service.

Verify the service is running:

sudo systemctl status postgresql-18 --no-pager

Expected output:

● postgresql-18.service - PostgreSQL 18 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-18.service; enabled; preset: disabled)
     Active: active (running) since ...

Step 6: Verify replication on standby 2

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

Expected output:

 pg_is_in_recovery
-------------------
 t
(1 row)

sudo -u postgres psql -c "SELECT status, sender_host, slot_name FROM pg_stat_wal_receiver;"

Expected output:

  status   | sender_host  |   slot_name
-----------+--------------+---------------
 streaming | {PRIMARY_IP} | standby2_slot
(1 row)

Step 7: Verify both standbys on the primary

Run on the primary ({PRIMARY_HOST}):

sudo -u postgres psql -c "SELECT client_addr, application_name, state, sync_state FROM pg_stat_replication ORDER BY application_name;"

Expected output:

  client_addr   | application_name | state     | sync_state
----------------+------------------+-----------+------------
 {STANDBY1_IP}  | standby1         | streaming | async
 {STANDBY2_IP}  | standby2         | streaming | async
(2 rows)

Optional: Configure Synchronous Replication

By default, streaming replication is asynchronous -- the primary does not wait for standbys to confirm WAL receipt before committing transactions. Synchronous replication makes the primary wait for at least one standby to confirm, providing stronger data durability at the cost of increased commit latency.

Warning: Synchronous replication increases transaction commit latency because the primary must wait for the synchronous standby to acknowledge WAL receipt over the network. If the synchronous standby goes down and no other synchronous standby is available, all write transactions on the primary will hang until the standby recovers or synchronous_standby_names is cleared. Test thoroughly before enabling in production.

Step 1: Set synchronous_standby_names on the primary

Run on the primary ({PRIMARY_HOST}):

sudo -u postgres psql

ALTER SYSTEM SET synchronous_standby_names = 'FIRST 1 (standby1, standby2)';
SELECT pg_reload_conf();
\q

Expected output:

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

This setting makes one standby synchronous using priority-based selection. standby1 has higher priority. If standby1 disconnects, standby2 becomes the synchronous standby automatically. The names standby1 and standby2 must match the application_name values set in each standby's primary_conninfo.

Step 2: Verify synchronous replication

sudo -u postgres psql -c "SELECT application_name, sync_state FROM pg_stat_replication ORDER BY application_name;"

Expected output:

 application_name | sync_state
------------------+------------
 standby1         | sync
 standby2         | potential
(2 rows)

sync -- this standby is the current synchronous standby. The primary waits for its confirmation before committing.

potential -- this standby will become synchronous if the current synchronous standby disconnects.

Step 3: Revert to asynchronous replication (if needed)

To disable synchronous replication:

sudo -u postgres psql -c "ALTER SYSTEM RESET synchronous_standby_names;" -c "SELECT pg_reload_conf();"

Monitoring

Use these queries for ongoing monitoring of the replication topology. All monitoring queries run on the primary unless noted.

Replication status overview

SELECT client_addr, application_name, state, sent_lsn, write_lsn,
       flush_lsn, replay_lsn, sync_state
FROM pg_stat_replication
ORDER BY application_name;

Replication lag in bytes

SELECT client_addr, application_name,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes,
       pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn) AS flush_lag_bytes
FROM pg_stat_replication
ORDER BY application_name;

Under idle conditions, both lag values should be near zero. Under load, flush_lag_bytes (network transfer) should be smaller than replay_lag_bytes (standby apply).

Replication slot health

SELECT slot_name, active, restart_lsn,
       pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes
FROM pg_replication_slots
ORDER BY slot_name;

Both slots should show active = true when standbys are connected. The retained_bytes value indicates how much WAL the slot is holding. Large values on an inactive slot indicate a disconnected standby and growing WAL retention.

WAL receiver status (run on each standby)

SELECT status, sender_host, sender_port, slot_name,
       flushed_lsn, last_msg_send_time, last_msg_receipt_time
FROM pg_stat_wal_receiver;

Validation

Quick check

Run on the primary:

sudo -u postgres psql -c "SELECT client_addr, application_name, state, sync_state FROM pg_stat_replication ORDER BY application_name;"

Expected output:

  client_addr   | application_name | state     | sync_state
----------------+------------------+-----------+------------
 {STANDBY1_IP}  | standby1         | streaming | async
 {STANDBY2_IP}  | standby2         | streaming | async
(2 rows)

Both rows must show state = streaming. If synchronous replication was enabled, sync_state will show sync and potential instead of async.

Full validation

1. Verify standby mode on each standby

Run on each standby:

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

Expected output:

 pg_is_in_recovery
-------------------
 t
(1 row)

2. Verify replication lag is near zero

Run on the primary:

sudo -u postgres psql -c "SELECT client_addr, application_name, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes FROM pg_stat_replication ORDER BY application_name;"

Expected output:

  client_addr   | application_name | replay_lag_bytes
----------------+------------------+------------------
 {STANDBY1_IP}  | standby1         |                0
 {STANDBY2_IP}  | standby2         |                0
(2 rows)

Under idle conditions, replay_lag_bytes should be 0 or very close to 0.

3. Verify replication slots are active

Run on the primary:

sudo -u postgres psql -c "SELECT slot_name, active, restart_lsn FROM pg_replication_slots ORDER BY slot_name;"

Expected output:

  slot_name    | active | restart_lsn
---------------+--------+-------------
 standby1_slot | t      | 0/5000148
 standby2_slot | t      | 0/5000148
(2 rows)

Both slots must show active = t.

4. Verify data replication with a test table

Run on the primary:

sudo -u postgres psql -c "CREATE TABLE replication_test (id int, created_at timestamptz DEFAULT now()); INSERT INTO replication_test VALUES (1);"

Expected output:

CREATE TABLE
INSERT 0 1

Wait a few seconds, then run on each standby:

sudo -u postgres psql -c "SELECT * FROM replication_test;"

Expected output:

 id |          created_at
----+-------------------------------
  1 | 2026-03-01 ...
(1 row)

Clean up the test table on the primary:

sudo -u postgres psql -c "DROP TABLE replication_test;"

5. Verify SSL is used for replication connections

Run on the primary:

sudo -u postgres psql -c "SELECT s.pid, r.client_addr, r.application_name, s.ssl, s.version AS ssl_version FROM pg_stat_ssl s JOIN pg_stat_replication r ON s.pid = r.pid ORDER BY r.application_name;"

Expected output:

  pid  |  client_addr   | application_name | ssl | ssl_version
-------+----------------+------------------+-----+-------------
 12345 | {STANDBY1_IP}  | standby1         | t   | TLSv1.3
 12346 | {STANDBY2_IP}  | standby2         | t   | TLSv1.3
(2 rows)

Both rows must show ssl = t.

Troubleshooting

References

• PostgreSQL 18: High Availability, Load Balancing, and Replication
• PostgreSQL 18: Log-Shipping Standby Servers
• PostgreSQL 18: Replication Configuration
• PostgreSQL 18: pg_basebackup
• PostgreSQL 18: Monitoring Statistics
• PostgreSQL 18: The pg_hba.conf File