8.1 Physical Streaming Replication Architecture

8.1 Physical Streaming Replication Architecture

[!NOTE] Core Design Philosophy: PostgreSQL physical streaming replication provides continuous, low-latency, byte-for-byte data duplication from a read-write primary server to one or more standby replicas. By streaming Write-Ahead Log (WAL) records directly across a dedicated TCP connection as they are generated—rather than waiting for completed 16MB WAL segment files to be archived—PostgreSQL achieves near-real-time replica synchronization with sub-second replication delay under normal network conditions.

Physical streaming replication forms the backbone of disaster recovery, high availability, and read-workload scaling in enterprise PostgreSQL deployments. To manage and troubleshoot replication clusters effectively, a database administrator must understand the internal background processes, configuration requirements, hot standby query mechanics, and real-time monitoring views.

---

The Physical Replication Pipeline: Byte-Level WAL Streaming

In PostgreSQL, physical replication is strictly block-level and binary-identical. When an INSERT, UPDATE, DELETE, or DDL operation executes on the primary server, the engine generates Write-Ahead Log records containing binary diffs and page-level byte mutations. Rather than transmitting high-level SQL statements (as in statement-based logical replication), physical streaming replication transmits these exact binary WAL records.

+-------------------------------------------------------------------------------------------+
|                               Physical Replication Pipeline                               |
+-------------------------------------------------------------------------------------------+
|  PRIMARY SERVER                                  STANDBY SERVER                           |
|  +-------------------------+                     +-------------------------------------+  |
|  | Client DML Backend      |                     | Client Read-Only Session            |  |
|  +------------+------------+                     +------------------+------------------+  |
|               | writes WAL                                          | reads buffer        |
|               v                                                     v                     |
|  +-------------------------+                     +-------------------------------------+  |
|  | WAL Buffers / pg_wal    |                     | Standby Shared Buffers / Page Blocks|  |
|  +------------+------------+                     +------------------+------------------+  |
|               | reads                                               ^                     |
|               v                                                     | applies byte diffs  |
|  +-------------------------+     TCP Stream      +------------------+------------------+  |
|  | walsender Process       | ==================> | walreceiver      | startup (Redo)   |  |
|  | (Primary Worker)        |   (libpq protocol)  | Process          | Process          |  |
|  +-------------------------+                     +------------------+------------------+  |
|                                                             | writes                      |
|                                                             v                             |
|                                                  +-------------------------------------+  |
|                                                  | Standby pg_wal Directory            |  |
|                                                  +-------------------------------------+  |
+-------------------------------------------------------------------------------------------+

Physical vs. File-Based Log Shipping

Prior to the introduction of streaming replication, PostgreSQL relied solely on file-based log shipping: the primary waited until an entire 16MB WAL segment file filled up, compressed it, and transferred it to the standby via an archive_command (e.g., rsync or cloud storage sync). The standby read completed segments using restore_command.

While log shipping remains a vital component of disaster recovery archiving, streaming replication operates directly on active WAL buffers. As soon as WAL records are written into primary memory, they are pushed across the TCP connection in small chunks, reducing replication lag from minutes down to milliseconds.

---

Core Background Processes: Roles and Responsibilities

The physical streaming replication architecture relies on three specialized background processes spanning the primary and standby nodes:

1. walsender (Primary Node)

• Origin: Spawned directly by the primary server's postmaster supervisor whenever a standby replica connects requesting a replication stream.
• Functionality: Acts as a dedicated server-side replication stream handler. It reads WAL records directly from the primary's WAL buffers in shared memory (or from the pg_wal disk directory if the standby is lagging behind current memory buffers) and transmits them across the network using PostgreSQL's streaming replication protocol.
• Scaling: A separate walsender process is spawned for every connected standby or basebackup client. The maximum number of concurrent sender processes is controlled by the max_wal_senders parameter (default: 10).

2. walreceiver (Standby Node)

• Origin: Launched by the standby's postmaster during startup recovery.
• Functionality: Connects to the primary server over a standard TCP connection using the libpq client protocol (authenticating with credentials defined in primary_conninfo). It receives incoming WAL chunks from the primary's walsender, writes them sequentially into the standby's local pg_wal directory, and issues fsync calls to ensure the received WAL records are durable on standby storage.
• Acknowledgment: Periodically sends heartbeat and progress feedback messages (StandbyReplyMessage) back to the primary, reporting the standby's current written, flushed, and replayed Log Sequence Numbers (LSNs).

3. startup Process (Standby Node)

• Origin: The initial core recovery worker started on the standby server.
• Functionality: Commonly referred to as the redo process, the startup process reads WAL records received by walreceiver (or pulled from archive via restore_command) and sequentially replays the physical changes directly into the standby's shared buffers and persistent relation forks (base/).
• Continuous Recovery: Because the standby operates in continuous recovery mode, the startup process continuously applies modifications without opening the cluster for local write transactions.

---

Standby Node Configuration in Modern PostgreSQL

Starting with PostgreSQL 12, the historical configuration file recovery.conf was deprecated and completely removed. Recovery and replication parameters were consolidated directly into the main configuration files (postgresql.conf and postgresql.auto.conf), and standby activation was decoupled into a dedicated signal file.

1. The standby.signal Trigger File

To instruct a PostgreSQL instance to start up as a standby replica rather than a standalone read-write primary, an empty file named standby.signal must exist in the root of the standby's data directory ($PGDATA):

# Place the standby trigger file in the standby data directory
touch /var/lib/postgresql/data/standby.signal

If standby.signal is present, PostgreSQL enters standby mode: it connects to the upstream source, continuously replays WAL, and forbids local write transactions. If standby.signal is absent upon server start, PostgreSQL completes any pending crash recovery and immediately promotes itself to an independent read-write primary.

2. The primary_conninfo Connection String

The standby identifies how to reach the upstream primary server via the primary_conninfo configuration parameter. This string contains standard libpq connection parameters:

# Configured in postgresql.conf or postgresql.auto.conf on the standby
primary_conninfo = 'host=192.168.1.50 port=5432 user=repuser password=SuperSecretReplicationKey application_name=standby_node1'

• application_name: Identifies the replica instance on the primary server. This identifier is crucial when configuring synchronous replication priority and quorum lists.
• Security Requirement: The replication user (repuser) must have the REPLICATION attribute in the primary catalog (CREATE ROLE repuser WITH REPLICATION LOGIN PASSWORD '...';) and must be granted access in the primary's pg_hba.conf (host replication repuser 192.168.1.0/24 scram-sha-256).

3. Automated Provisioning with pg_basebackup -R

The recommended administrative tool for initializing a new standby replica from a live primary is pg_basebackup. Specifying the -R (or --write-recovery-conf) flag automates the creation of all required standby files:

# Execute on the standby server to clone the primary and configure replication
pg_basebackup \
  -h 192.168.1.50 \
  -p 5432 \
  -U repuser \
  -D /var/lib/postgresql/data \
  -Fp \
  -Xs \
  -R \
  -v -P

When invoked with -R, pg_basebackup automatically:

1. Creates the empty standby.signal file inside the target -D data directory.
2. Writes the appropriate primary_conninfo setting into postgresql.auto.conf matching the connection parameters used during the backup.

---

Hot Standby Capability: Read Scalability and Constraints

By default, a standby in continuous recovery cannot service client connections. Enabling Hot Standby permits the replica to accept read-only SQL connections while the startup process concurrently replays the WAL stream.

# Enabled by default in modern PostgreSQL (postgresql.conf)
hot_standby = on

Permitted and Prohibited Operations on Hot Standby

• Permitted Operations: Read-only queries (SELECT), non-modifying catalog inspections, transaction commands with read-only characteristics (BEGIN READ ONLY), cursor navigations, and EXPLAIN (without ANALYZE if modifying data).
• Prohibited Operations: Any DML write (INSERT, UPDATE, DELETE), DDL alterations (CREATE TABLE, ALTER TABLE, DROP INDEX), advancing or creating sequence generators (nextval()), manual locks that conflict with exclusive recovery locks, and creating standard or temporary tables that write to the system catalogs.

[!TIP] Read-Pool Offloading: Hot standby replicas are widely deployed behind connection poolers (such as PgBouncer) and load balancers to offload heavy read queries, analytical reports, and business intelligence dashboards from the primary write master.

---

Monitoring Replication Telemetry: pg_stat_replication

To observe connected replicas, measure replication lag, and verify synchronization health, database administrators query the pg_stat_replication dynamic system view on the primary server.

-- Query real-time streaming replication status on the primary
SELECT 
    pid,
    application_name,
    client_addr,
    state,
    sync_state,
    sync_priority,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    pg_wal_lsn_diff(sent_lsn, replay_lsn) AS total_byte_lag,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication;

Detailed Breakdown of Key Columns

1. Connection Identity:

   • pid: Process ID of the walsender process running on the primary.
   • application_name: The identity string defined in the standby's primary_conninfo.
   • client_addr: IP address of the connected standby replica.

2. Replication States (state):

   • startup: walsender process is initializing and negotiating authentication.
   • catchup: Standby is replaying historical WAL to catch up with current primary activity.
   • streaming: Normal operating state; primary and standby are in active real-time WAL stream synchronization.
   • backup: walsender is streaming a base backup (e.g. pg_basebackup).
   • stopping: walsender is cleanly shutting down.

3. Log Sequence Numbers (LSN Progress Tracking):

   • sent_lsn: The latest WAL LSN sent by the primary's walsender over the TCP socket.
   • write_lsn: The latest WAL LSN written to the standby's local disk file cache by walreceiver (not yet flushed).
   • flush_lsn: The latest WAL LSN flushed to durable disk storage via fsync on the standby.
   • replay_lsn: The latest WAL LSN replayed into the database heap and buffer pages by the standby's startup process. Only data up to replay_lsn is visible to read queries on the standby!

4. Lag Calculation with pg_wal_lsn_diff(): PostgreSQL provides the function pg_wal_lsn_diff(lsn1, lsn2) to calculate the exact byte difference between two Log Sequence Numbers:

   -- Measure byte delay between primary generation and standby query visibility
   SELECT application_name, 
          pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_delay_bytes
   FROM pg_stat_replication;
   

5. Time-Based Lag Metrics: Columns write_lag, flush_lag, and replay_lag provide interval timestamps representing the round-trip elapsed time between when WAL was written locally and when the standby acknowledged write, flush, and replay.

---

Exam Tips and Common Pitfalls

• Exam Trap: PostgreSQL 12+ Configuration Changes: If an exam question asks about configuring standby replication using recovery.conf, recognize immediately that recovery.conf is obsolete. In PostgreSQL 12 and newer, standby operation requires standby.signal in $PGDATA and primary_conninfo in postgresql.conf / postgresql.auto.conf.
• Exam Trap: Process Mapping: Do not confuse walsender and walreceiver. The walsender process executes on the primary server; the walreceiver process executes on the standby server. The startup process also runs on the standby to perform the physical redo replay.
• Exam Trap: Read Visibility Boundary: Data modified on the primary is not visible to read queries on a hot standby replica as soon as it arrives at write_lsn or flush_lsn. The standby's read sessions can only view mutations once the startup process has executed the redo logic up to replay_lsn.