How do you set up streaming replication in PostgreSQL, and what are the key components involved?

Question

Accepted Answer

Streaming replication in PostgreSQL creates a real-time copy of the primary database on one or more standby servers. It works by continuously streaming Write-Ahead Log records from the primary to the standbys, which apply them to maintain an up-to-date copy. Setting up streaming replication is a core skill for a mid-level DBA.

On the primary server, several configuration changes are needed. Set wal_level to replica or logical, which determines how much information is written to the WAL. The replica level includes enough information for physical replication. Set max_wal_senders to at least the number of standbys plus some headroom for backup tools, as each standby requires a WAL sender process. Set wal_keep_size to retain enough WAL segments to allow standbys to catch up after brief disconnections. Alternatively, configure WAL archiving as a fallback for standbys that fall far behind.

Create a replication user with the REPLICATION privilege. In pg_hba.conf, add a rule allowing the standby to connect as this user with the replication database type. Use scram-sha-256 authentication and require SSL for security.

On the standby server, start by taking a base backup of the primary using pg_basebackup. This command connects to the primary and copies the entire data directory plus WAL files needed for consistency. The typical command is pg_basebackup -h primary_host -U replication_user -D /var/lib/postgresql/data -Fp -Xs -P, which creates a file-format backup with streaming WAL and shows progress. This process creates the initial standby data directory.

After the base backup completes, create a standby.signal file in the data directory to tell PostgreSQL to start in standby mode. Configure primary_conninfo in postgresql.conf with the connection string to the primary, including the host, port, user, password, and SSL parameters. When you start the standby, it connects to the primary and begins receiving WAL records in real time.

The standby operates in one of two modes. A hot standby, configured with hot_standby = on, accepts read-only connections while replaying WAL records. This allows you to distribute read queries to the standby, reducing load on the primary. A warm standby does not accept connections and exists solely as a failover target. Hot standby is the more common configuration because it provides both redundancy and read scaling.

Monitor replication health by checking pg_stat_replication on the primary, which shows each connected standby's state, sent and written LSN positions, and replay lag. On the standby, pg_stat_wal_receiver shows the connection status and received LSN. The difference between the primary's sent LSN and the standby's replayed LSN indicates the replication lag.

Key maintenance tasks include monitoring replication lag and alerting when it exceeds your tolerance, ensuring WAL archiving is configured as a fallback for reconnection after extended outages, and periodically testing the failover procedure to ensure the standby can be promoted successfully when needed.

As a mid-level DBA, also understand the limitations. Physical streaming replication copies the entire database cluster, not individual databases or tables. You cannot replicate a subset of data with streaming replication; for that, you need logical replication.