In this lesson, you will learn how to configure PostgreSQL streaming replication to propagate Write-Ahead Logging (WAL) data to standby servers in real-time.
The Streaming Concept
At the core of PostgreSQL high availability is the WAL. When a transaction commits, the changes are recorded as WAL records. Streaming replication works by establishing a continuous connection between a "Primary" server and a "Standby" server. Instead of waiting for a file to be archived (as in PITR), the Primary sends WAL data through a network socket as it is generated.
Setting Up the Primary
To enable this, we must configure the Primary to allow a replication connection and identify a user with the REPLICATION privilege. In your postgresql.conf on the Primary, ensure you have:
wal_level = replica
max_wal_senders = 10
The wal_level = replica setting is crucial; it ensures the WAL includes enough information for a standby to reconstruct the data. max_wal_senders defines how many concurrent standby connections the Primary can handle.
Streaming to the Standby
Once the Primary is ready, you create the Standby by performing a physical backup of the Primary (using pg_basebackup). This tool copies the data files and creates a standby.signal file in the data directory, which tells PostgreSQL to start in standby mode rather than acting as a normal database.
The Standby then initiates the replication protocol:
- It connects to the Primary.
- It requests a "replication slot" or a specific WAL sequence number (LSN).
- The Primary begins "streaming" new WAL records as they arrive in the shared memory buffer.
- The Standby receives these records, writes them to its own WAL files, and replays them into its local buffer cache.
Because the Standby is constantly applying these logs, it stays in a nearly identical state to the Primary, allowing it to take over immediately if the Primary fails.
Key Takeaway: Streaming replication bypasses disk-based archival by pushing WAL records directly through the network to the Standby, ensuring the secondary server remains in constant synchronization with the Primary's transaction stream.