PostgreSQL Streaming Replication on CentOS Setup

To configure PostgreSQL streaming replication on CentOS, you set up a primary server that continuously sends write-ahead log (WAL) data to one or more standby servers, which replay it to stay synchronized. This guide walks through the exact steps for CentOS 7 and 8 with PostgreSQL 12 or later, covering initial setup, configuration, and verification.

Prerequisites and Initial Setup

Before starting, ensure you have two CentOS servers (primary and standby) with PostgreSQL installed. The versions must match exactly. For CentOS, the official PostgreSQL repository provides the latest versions. Install PostgreSQL on both servers using the same package manager commands, then initialize the data directory if the installer did not do it automatically.

• Both servers should have static IP addresses or resolvable hostnames.
• Firewalld must allow PostgreSQL traffic (default port 5432) between the servers.
• SELinux may need adjustment; set it to permissive or configure the correct policy.

Configuring the Primary Server

On the primary server, edit postgresql.conf (usually in /var/lib/pgsql/12/data/). Set the following parameters:

1. listen_addresses = 'localhost, primary_ip' – listen on the network interface.
2. wal_level = replica (or logical if you need logical replication).
3. max_wal_senders = 10 – number of concurrent standby connections.
4. wal_keep_size = 128 (or wal_keep_segments for older versions) – amount of WAL to retain for lagging standbys.
5. hot_standby = on – allows read-only queries on the standby.

Next, edit pg_hba.conf to allow replication connections from the standby server. Add a line like:

host replication replica_user standby_ip/32 md5

Create a dedicated replication user with the REPLICATION privilege:

CREATE USER replica_user REPLICATION LOGIN PASSWORD 'strong_password';

Restart the primary server to apply the changes.

Taking a Base Backup

Before starting the standby, you must copy the primary’s data directory to the standby. The recommended tool is pg_basebackup. On the standby server, stop PostgreSQL, then run:

pg_basebackup -h primary_ip -U replica_user -D /var/lib/pgsql/12/data -P --wal-method=stream

This creates a consistent copy of the data directory and streams WAL files during the backup. Ensure the standby data directory is empty or removed first.

Configuring the Standby Server

After the base backup, create a standby.signal file in the data directory to tell PostgreSQL this server is a standby:

touch /var/lib/pgsql/12/data/standby.signal

In postgresql.conf on the standby, set:

• hot_standby = on – allow read-only queries.
• primary_conninfo = 'host=primary_ip port=5432 user=replica_user password=strong_password' – connection to the primary.

For PostgreSQL 12 and later, you can also set primary_slot_name to a replication slot to prevent WAL loss if the standby disconnects. Create the slot on the primary with SELECT * FROM pg_create_physical_replication_slot('standby_slot'); and reference it in primary_conninfo.

Start the standby server. Check the logs for any errors, and verify replication status.

Verifying Replication

On the primary, run:

SELECT * FROM pg_stat_replication;

You should see a row with the standby’s host and state ‘streaming’. On the standby, run:

SELECT pg_is_in_recovery(); – returns true.

To test, create a table and insert rows on the primary, then query the same table on the standby – the data should appear almost instantly.

Common Issues and Troubleshooting

Several issues can arise during setup:

• Authentication failures: Ensure pg_hba.conf has the correct IP and method. Use md5 or scram-sha-256.
• WAL not sent: Check max_wal_senders is high enough and wal_level is correct. Also, verify network connectivity and firewall rules.
• Standby not starting: Look at the standby log. Common causes: missing standby.signal, wrong permissions on the data directory, or incompatible primary_conninfo.
• Lag grows: Increase wal_keep_size or use a replication slot. Monitor with pg_stat_replication.

Promoting the Standby

If the primary fails, you can promote the standby to take over. Run pg_ctl promote (or select pg_promote(); from a session) on the standby. It will stop recovery and become a new primary. After promotion, you need to reconfigure the old primary as a standby if you want to rejoin it.

Frequently Asked Questions

Can I use streaming replication with different PostgreSQL versions? No, the major versions must match exactly. Minor versions can differ but it’s recommended to keep them in sync.

What is the difference between streaming replication and logical replication? Streaming replication copies the entire cluster at the physical level, while logical replication copies individual tables or databases with more flexibility. Streaming is simpler and has lower overhead.

How do I monitor replication lag? Use pg_stat_replication on the primary – columns sent_lsn, write_lsn, flush_lsn, and replay_lsn show the WAL positions. The difference indicates lag.

Can I have more than one standby? Yes, you can configure multiple standbys. Each needs its own connection slot or use max_wal_senders accordingly.

What happens if the standby loses connection? It will keep trying to reconnect. If the primary has enough WAL retained (via wal_keep_size or a slot), the standby will catch up once reconnected. Without a slot, the standby might need a new base backup.

Is streaming replication a full backup solution? No, it’s for high availability and read scaling. You still need regular backups for disaster recovery, as replication does not protect against accidental data deletion or corruption.

Related

• Phone Not Reading SD Card? Fixes & Causes
• Where Winds Meet Return o Soul Walkthrough & Rewards
• What Is a REST API? A Beginner’s Guide