Step by Step Guide on Setting Up Physical Streaming Replication in PostgreSQL
Physical streaming replication in PostgreSQL allows you to maintain a live copy of your database on a standby server, which continuously receives updates from the primary server’s WAL (Write-Ahead Log). This standby (or hot standby) can handle read-only queries and be quickly promoted to primary in case of failover, providing high availability and disaster recovery.
In this guide, I will walk through provisioning a primary PostgreSQL 16 server and a standby server on Linux, configuring them for streaming replication, and verifying that everything works. I assume you are an experienced engineer familiar with Linux, but new to PostgreSQL replication, so I will keep it friendly and straightforward.
Figure: Real-time data streaming from a primary PostgreSQL server (left) to a standby server (right). The standby constantly applies WAL records received from the primary over a network connection, keeping an up-to-date copy of the database ready for failover.
Step 1: Prepare Two Linux Servers and Install PostgreSQL 16
Before diving into PostgreSQL settings, set up two Linux servers (virtual or physical). One will act as the primary database server, and the other as the standby (read replica). For a smooth replication setup, both servers should be as similar as possible in OS, hardware, and PostgreSQL version. In particular, ensure the following prerequisites:
PostgreSQL 16 is installed on both servers via the official PostgreSQL repositories. Both servers must run the same major PostgreSQL version and architecture (mixing different versions won’t work for physical replication). If you haven’t installed PostgreSQL yet, do so now (e.g., on Ubuntu: sudo apt install postgresql-16, or on RHEL/CentOS: use the PostgreSQL Yum repository). Make sure the PostgreSQL service is running on the primary server.
Network connectivity: The standby must be able to reach the primary on the PostgreSQL port (default 5432). If the servers are in a cloud environment like AWS EC2, configure the security group or firewall to allow the standby’s IP to connect to the primary on port 5432. For example, in AWS you’d add an inbound rule permitting the standby’s private IP address (or subnet) access to port 5432 on the primary. It is best to use private network interfaces for replication to reduce latency and avoid exposing the database publicly.
System settings: Ensure your servers have the necessary OS user and permissions for PostgreSQL. The installation usually creates a postgres UNIX user that owns the data directories. You will run many commands as this postgres user. Also, verify that important prerequisites like consistent time sync (NTP) are in place, as it is generally good practice for database servers (though not specific to replication).
With the infrastructure ready, let’s proceed to configure the primary PostgreSQL server to accept replication connections.