PostgreSQL Streaming Replication on AlmaLinux 10 (Primary/Standby, Failover)

PostgreSQL on AlmaLinux series, part 4. Previous: part 3, PostgreSQL Backup and Restore on AlmaLinux 10: pg_dump, pg_dumpall and pg_basebackup. Next up: performance tuning with postgresql.conf, EXPLAIN ANALYZE, indexes and autovacuum.

Streaming replication keeps a second PostgreSQL server, the standby, continuously in sync with the primary by shipping write-ahead log (WAL) records over a normal database connection. The standby can answer read-only queries, and if the primary fails you can promote the standby to take over within seconds. This guide builds a two-node primary/standby pair on AlmaLinux 10 with a replication slot, verifies that it works, and then walks through a planned switchover, an unplanned failover, and rejoining the old primary with pg_rewind.

Applies to: PostgreSQL 18 from the PGDG repository on AlmaLinux 10, as installed in part 1 (service postgresql-18, data directory /var/lib/pgsql/18/data, binaries in /usr/pgsql-18/bin). With the AppStream PostgreSQL 16 package the service is postgresql and the data directory is /var/lib/pgsql/data; everything else is the same. Also applies to RHEL, Rocky Linux, Oracle Linux and CentOS Stream 10.

Prerequisites

The existing primary db1 (192.168.1.40) running PostgreSQL 18, set up as in part 1, with listen_addresses already opened for remote connections.
A second AlmaLinux 10 host db2 (192.168.1.41) for the standby, with the same PostgreSQL major version installed from the same repository. Install the packages as in part 1, but do not run initdb on it: the standby gets its data directory from the primary.
Both hosts on the same CPU architecture, with clocks synced by chronyd so lag timestamps make sense.
Root or sudo access on both hosts. Commands shown with a postgres=# prompt run in psql as the postgres superuser (sudo -iu postgres psql).
Familiarity with pg_basebackup from part 3 (PostgreSQL Backup and Restore on AlmaLinux 10: pg_dump, pg_dumpall and pg_basebackup) helps, because it is how the standby is seeded.

How streaming replication works

Every change on the primary is written to WAL first. A WAL sender process on the primary streams those records to a WAL receiver on the standby, which writes them to its own pg_wal and replays them. The standby is a byte-for-byte physical copy, so it replicates the whole cluster (every database, role and table), not individual tables. A replication slot on the primary remembers how far the standby has got, so the primary never recycles WAL that the standby still needs.

Item	Value in this guide
Primary	`db1`, 192.168.1.40
Standby	`db2`, 192.168.1.41
Replication role	`replicator` (LOGIN REPLICATION)
Replication slot	`db2_slot` on db1
Rewind role	`rewinder` (used by `pg_rewind` later)
Port	5432/tcp

Step 1: Check the replication settings on the primary

PostgreSQL 18 defaults are already suitable for one or more standbys: wal_level = replica, max_wal_senders = 10, max_replication_slots = 10 and hot_standby = on. Confirm them, and confirm that data checksums are on, because pg_rewind needs either data checksums or wal_log_hints.

postgres=# SELECT name, setting FROM pg_settings
           WHERE name IN ('wal_level','max_wal_senders','max_replication_slots',
                          'hot_standby','listen_addresses','data_checksums','wal_log_hints');

PostgreSQL 18 enables data checksums by default at initdb. If data_checksums shows off (for example, a cluster upgraded from an older release), turn on wal_log_hints; this one needs a restart:

postgres=# ALTER SYSTEM SET wal_log_hints = on;
$ sudo systemctl restart postgresql-18

A replication slot keeps WAL until the standby consumes it, so a standby that is down for days can fill the primary’s disk. Put a ceiling on that (this one only needs a reload):

postgres=# ALTER SYSTEM SET max_slot_wal_keep_size = '20GB';
postgres=# SELECT pg_reload_conf();

Step 2: Create the replication and rewind roles

Use a dedicated role for replication rather than the postgres superuser. The REPLICATION attribute lets it open replication connections and nothing more.

postgres=# CREATE ROLE replicator WITH LOGIN REPLICATION PASSWORD 'Use-A-Long-Random-Secret';

pg_rewind (used later to rejoin a failed primary) needs to read files from the source server. Instead of using a superuser, the PostgreSQL documentation lists the four functions it needs, so create a minimal role for it:

postgres=# CREATE ROLE rewinder WITH LOGIN PASSWORD 'Another-Long-Random-Secret';
postgres=# GRANT EXECUTE ON FUNCTION pg_catalog.pg_ls_dir(text, boolean, boolean) TO rewinder;
postgres=# GRANT EXECUTE ON FUNCTION pg_catalog.pg_stat_file(text, boolean) TO rewinder;
postgres=# GRANT EXECUTE ON FUNCTION pg_catalog.pg_read_binary_file(text) TO rewinder;
postgres=# GRANT EXECUTE ON FUNCTION pg_catalog.pg_read_binary_file(text, bigint, bigint, boolean) TO rewinder;

Both roles replicate to the standby automatically, so after a failover they already exist on the new primary.

Step 3: Allow the connections in pg_hba.conf and firewalld

Replication connections match the special database name replication in pg_hba.conf; an all entry does not cover them. Add rules for both nodes now. pg_basebackup copies pg_hba.conf to the standby, so the same rules will be in place whichever node is primary later. Edit /var/lib/pgsql/18/data/pg_hba.conf on db1 and add, above any broader reject rules:

# TYPE  DATABASE     USER        ADDRESS            METHOD
host    replication  replicator  192.168.1.40/32    scram-sha-256
host    replication  replicator  192.168.1.41/32    scram-sha-256
host    postgres     rewinder    192.168.1.40/32    scram-sha-256
host    postgres     rewinder    192.168.1.41/32    scram-sha-256

postgres=# SELECT pg_reload_conf();
postgres=# SELECT line_number, type, database, user_name, address, auth_method, error
           FROM pg_hba_file_rules WHERE 'replication' = ANY(database) OR 'rewinder' = ANY(user_name);

Open port 5432 to the other node only. Run this on db1 now; run it on db2 too (with 192.168.1.40 as the source) so the reverse direction works after a switchover:

$ sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.1.41/32" service name="postgresql" accept'
$ sudo firewall-cmd --reload

Step 4: Store the passwords on both nodes with .pgpass

Keep the passwords out of shell history and scripts by putting them in the postgres user’s ~/.pgpass (home directory /var/lib/pgsql) on both hosts. For replication connections the database field must be the literal word replication.

$ sudo -iu postgres
$ cat > ~/.pgpass <<'EOF'
192.168.1.40:5432:replication:replicator:Use-A-Long-Random-Secret
192.168.1.41:5432:replication:replicator:Use-A-Long-Random-Secret
192.168.1.40:5432:postgres:rewinder:Another-Long-Random-Secret
192.168.1.41:5432:postgres:rewinder:Another-Long-Random-Secret
EOF
$ chmod 0600 ~/.pgpass

From db2, test the replication connection before going further. IDENTIFY_SYSTEM is a replication-protocol command; a row with the system identifier and timeline means authentication and the network path both work:

[postgres@db2 ~]$ psql "host=192.168.1.40 user=replicator replication=true" -c "IDENTIFY_SYSTEM;"

Step 5: Seed the standby with pg_basebackup

On db2, make sure PostgreSQL is stopped and the data directory is empty (it should not exist yet or be empty, because you skipped initdb):

$ sudo systemctl stop postgresql-18
$ sudo ls -A /var/lib/pgsql/18/data

Then, as postgres on db2, take the base backup straight into the data directory:

[postgres@db2 ~]$ /usr/pgsql-18/bin/pg_basebackup -h 192.168.1.40 -U replicator \
    -D /var/lib/pgsql/18/data -X stream -c fast -P -R -C -S db2_slot

Option	What it does
`-X stream`	Streams the WAL generated during the backup over a second connection, so the copy is self-consistent
`-c fast`	Requests an immediate checkpoint instead of waiting for the next scheduled one
`-R`	Writes `standby.signal` and adds `primary_conninfo` (and the slot name) to `postgresql.auto.conf`
`-C -S db2_slot`	Creates the physical replication slot `db2_slot` on the primary and uses it
`-P`	Shows progress

Look at what -R wrote. If a password was used for the connection it may be included in primary_conninfo; the file is mode 0600 and owned by postgres, and with .pgpass in place you can remove the password from it.

[postgres@db2 ~]$ ls -l /var/lib/pgsql/18/data/standby.signal
[postgres@db2 ~]$ cat /var/lib/pgsql/18/data/postgresql.auto.conf

Give the standby a name so it shows up clearly on the primary (the WAL receiver uses cluster_name as its application_name when primary_conninfo does not set one), fix SELinux labels, and start it:

[postgres@db2 ~]$ echo "cluster_name = 'db2'" >> /var/lib/pgsql/18/data/postgresql.conf
[postgres@db2 ~]$ exit
$ sudo restorecon -Rv /var/lib/pgsql/18
$ sudo systemctl enable --now postgresql-18
$ sudo journalctl -u postgresql-18 -n 20 --no-pager

The log should show entering standby mode, consistent recovery state reached, database system is ready to accept read-only connections and started streaming WAL from primary.

Step 6: Verify replication

On the primary, pg_stat_replication has one row per connected standby:

postgres=# SELECT application_name, client_addr, state, sync_state,
                  sent_lsn, replay_lsn, write_lag, flush_lag, replay_lag,
                  pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes
           FROM pg_stat_replication;
postgres=# SELECT slot_name, active, wal_status, restart_lsn FROM pg_replication_slots;

You want state = streaming, the slot active = t and wal_status = reserved. On the standby:

postgres=# SELECT pg_is_in_recovery();
postgres=# SELECT status, sender_host, slot_name, written_lsn, flushed_lsn FROM pg_stat_wal_receiver;
postgres=# SELECT pg_last_wal_replay_lsn(), now() - pg_last_xact_replay_timestamp() AS replay_delay;

pg_is_in_recovery() returns t on a standby. Note that replay_delay grows while the primary is idle, because it measures time since the last replayed transaction; compare LSNs for an exact picture. Finally, prove data flows and that the standby is read-only:

-- on db1
postgres=# CREATE TABLE repl_test (id int, note text);
postgres=# INSERT INTO repl_test VALUES (1, 'written on db1');

-- on db2
postgres=# SELECT * FROM repl_test;
postgres=# INSERT INTO repl_test VALUES (2, 'should fail');
ERROR:  cannot execute INSERT in a read-only transaction

Optional: Synchronous replication

By default replication is asynchronous: a commit returns on the primary before the standby has the WAL, so a primary crash can lose the last few transactions. With synchronous replication the primary waits for the standby to confirm each commit. On db1:

postgres=# ALTER SYSTEM SET synchronous_standby_names = 'FIRST 1 (db2)';
postgres=# SELECT pg_reload_conf();
postgres=# SELECT application_name, sync_state FROM pg_stat_replication;

sync_state should change to sync. The trade-off is serious: with only one standby, every write on the primary hangs if the standby is down. Either run two standbys with ANY 1 (db2, db3), or be ready to clear the setting (ALTER SYSTEM RESET synchronous_standby_names; plus a reload) during standby maintenance.

Step 7: Planned switchover (swap the roles)

A switchover is a controlled role swap for maintenance, with no data loss. First check the standby is caught up (replay_lag_bytes near 0 on db1), stop application writes, then stop the primary cleanly. On a clean (fast or smart) shutdown, the primary waits for connected standbys to receive all WAL before it exits.

# db1
$ sudo systemctl stop postgresql-18

Promote db2. It switches to a new timeline and starts accepting writes:

# db2
postgres=# SELECT pg_promote();
postgres=# SELECT pg_is_in_recovery();   -- now f
postgres=# SELECT timeline_id FROM pg_control_checkpoint();

db2 still carries primary_conninfo and primary_slot_name pointing at db1 in postgresql.auto.conf. They are ignored on a primary, but clear them so they do not get copied back later, and create a slot for db1:

# db2
postgres=# ALTER SYSTEM RESET primary_conninfo;
postgres=# ALTER SYSTEM RESET primary_slot_name;
postgres=# SELECT pg_reload_conf();
postgres=# SELECT pg_create_physical_replication_slot('db1_slot');

Point your applications at db2 now (see “Making clients follow the primary” below), then turn db1 into the new standby with Step 8.

Step 8: Rejoin the old primary with pg_rewind

The old primary cannot simply follow the new one if it wrote any WAL that never reached the standby, which is almost always the case after a crash. pg_rewind fixes that by copying back only the blocks that changed since the timelines diverged, which is far faster than a new base backup. It works after a clean switchover too (it reports that no rewind was needed). With db1 stopped, as postgres on db1:

[postgres@db1 ~]$ /usr/pgsql-18/bin/pg_rewind --target-pgdata=/var/lib/pgsql/18/data \
    --source-server="host=192.168.1.41 port=5432 user=rewinder dbname=postgres" --progress

If db1 crashed rather than shutting down cleanly, pg_rewind first runs crash recovery on it automatically in single-user mode. It also copies configuration files such as postgresql.auto.conf from db2, which is why you reset primary_conninfo there first. Now make db1 a standby of db2 and start it:

[postgres@db1 ~]$ touch /var/lib/pgsql/18/data/standby.signal
[postgres@db1 ~]$ sed -i "s/^cluster_name = .*/cluster_name = 'db1'/" /var/lib/pgsql/18/data/postgresql.conf
[postgres@db1 ~]$ grep -q '^cluster_name' /var/lib/pgsql/18/data/postgresql.conf || echo "cluster_name = 'db1'" >> /var/lib/pgsql/18/data/postgresql.conf
[postgres@db1 ~]$ exit
$ sudo systemctl start postgresql-18

primary_conninfo and primary_slot_name can be changed with a reload, so set them from psql on db1 and drop the old slot db1 kept from its time as primary (an unused slot would hold WAL forever):

# db1 (now in recovery)
postgres=# ALTER SYSTEM SET primary_conninfo = 'host=192.168.1.41 port=5432 user=replicator';
postgres=# ALTER SYSTEM SET primary_slot_name = 'db1_slot';
postgres=# SELECT pg_reload_conf();
postgres=# SELECT pg_drop_replication_slot('db2_slot');

Verify with the Step 6 queries, swapping the roles: pg_stat_replication on db2 should show db1 streaming. To swap back later, repeat Steps 7 and 8 in the other direction (recreate db2_slot on db1 first).

Unplanned failover when the primary dies

PostgreSQL itself does not fail over automatically; promotion is a deliberate action. When the primary is really gone:

Fence the old primary first. Make sure db1 cannot come back as a writable primary: power it off, or if you can reach it, sudo systemctl disable --now postgresql-18. Two writable primaries (split brain) means diverging data that must be reconciled by hand.
Check how far the standby got with SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(); on db2. With asynchronous replication, transactions committed on db1 after the last received LSN are lost.
Promote db2 with SELECT pg_promote(); (or sudo -iu postgres /usr/pgsql-18/bin/pg_ctl promote -D /var/lib/pgsql/18/data), then reset primary_conninfo and create db1_slot as in Step 7.
Repoint clients to db2.
Rebuild redundancy once db1 is repaired, with pg_rewind (Step 8). If its disk was replaced or pg_rewind fails, empty its data directory and seed it again with pg_basebackup from db2, exactly as in Step 5.

For automatic failover with leader election and fencing, look at a cluster manager such as Patroni or pg_auto_failover. Learn the manual procedure above first, since those tools run the same steps for you.

Making clients follow the primary

Any client built on libpq (psql, Python psycopg, PHP pgsql and others) can list several hosts and connect only to the one that accepts writes, so a failover needs no config change on the client:

$ psql "host=192.168.1.40,192.168.1.41 port=5432 dbname=invdb user=appsvc target_session_attrs=read-write"

JDBC has the equivalent jdbc:postgresql://192.168.1.40:5432,192.168.1.41:5432/invdb?targetServerType=primary. Read-only reporting can use target_session_attrs=standby (libpq) to prefer the standby. Alternatives are a floating virtual IP managed by keepalived or a DNS name you update during failover.

Troubleshooting

Symptom	Cause and fix
`no pg_hba.conf entry for replication connection from host "192.168.1.41", user "replicator"`	The rule must use the database keyword `replication`; `all` does not match. Add the line from Step 3 and reload.
`Connection refused` or a timeout from db2	`listen_addresses` is still `localhost` (needs a restart after changing), or firewalld on the primary blocks 5432. Check with `ss -ltnp \| grep 5432` and `firewall-cmd --list-all`.
`pg_basebackup: error: directory "/var/lib/pgsql/18/data" exists but is not empty`	An `initdb` ran on the standby. Stop the service and empty the directory (only on the standby!).
`requested WAL segment ... has already been removed`	The standby fell behind further than the primary kept WAL, usually because it ran without a slot or the slot was invalidated by `max_slot_wal_keep_size` (`wal_status = lost`). Rebuild the standby with `pg_basebackup`.
Primary disk filling with `pg_wal`	An inactive slot is holding WAL. Find it with `SELECT slot_name, active, wal_status FROM pg_replication_slots;` and drop it with `pg_drop_replication_slot()` if its standby is gone. Keep `max_slot_wal_keep_size` set.
`canceling statement due to conflict with recovery` on the standby	Long read queries clash with replayed cleanup. Raise `max_standby_streaming_delay` or set `hot_standby_feedback = on` on the standby (at the cost of some bloat on the primary).
`pg_rewind`: target server needs to use either data checksums or `wal_log_hints = on`	Enable `wal_log_hints` (Step 1) before you need it. For a node without either, rebuild it with `pg_basebackup`.
`requested timeline 2 is not a child of this server's history`	The node wrote WAL after the split. Run `pg_rewind` against the new primary, or rebuild it.
Write commits hang on the primary	Synchronous replication is on and no synchronous standby is connected. Bring the standby back or reset `synchronous_standby_names` and reload.
Standby will not start, AVC denials in `/var/log/audit/audit.log`	Files have the wrong SELinux label. Run `sudo restorecon -Rv /var/lib/pgsql/18`.

Quick reference

Task	Command
Seed a standby	`pg_basebackup -h primary -U replicator -D datadir -X stream -c fast -R -C -S slot -P`
Replication status (primary)	`SELECT * FROM pg_stat_replication;`
Receiver status (standby)	`SELECT * FROM pg_stat_wal_receiver;`
Am I a standby?	`SELECT pg_is_in_recovery();`
List slots	`SELECT * FROM pg_replication_slots;`
Promote standby	`SELECT pg_promote();`
Rejoin old primary	`pg_rewind --target-pgdata=datadir --source-server="host=newprimary user=rewinder dbname=postgres"`
Make a node a standby	`touch datadir/standby.signal` plus `primary_conninfo`

What’s next in the PostgreSQL series

Part 5 covers performance tuning: postgresql.conf memory settings, EXPLAIN ANALYZE, indexes and autovacuum. Earlier parts: part 1, Install PostgreSQL on AlmaLinux 10: PGDG vs AppStream, pg_hba.conf and Remote Access; part 2, PostgreSQL Users, Roles and Privileges on AlmaLinux 10; and part 3, PostgreSQL Backup and Restore on AlmaLinux 10: pg_dump, pg_dumpall and pg_basebackup, whose pg_basebackup section is the foundation of this guide.

Official references: PostgreSQL documentation: Log-Shipping Standby Servers, Replication settings, pg_stat_replication, pg_rewind and libpq connection strings.

PostgreSQL Streaming Replication on AlmaLinux 10: Primary/Standby Setup, Switchover and Failover