1.4Replication Operations

In Silver, replication was "basic knowledge" only. Gold demands the operator's perspective: actually building replication, choosing a synchronization mode, and monitoring for lag or gaps. You need enough depth to make concrete parameter choices that balance availability requirements (how much downtime is acceptable) against data-loss tolerance (how much data loss is acceptable).

1.4.1Streaming replication setup parameters

• wal_level determines how much information gets written to WAL. Replication requires at least replica (or logical if using logical replication). max_wal_senders caps the number of concurrently connected standbys/replication clients (i.e., the limit on walsender processes).
• synchronous_standby_names specifies which standbys count as synchronous replicas, by name, priority, and grouping (e.g., 'FIRST 1 (standby1, standby2)'). synchronous_commit controls how much confirmation a commit waits for (on = wait for the sync standby to confirm the WAL write, remote_write = wait only for the standby's OS-level write, off = local only).
• hot_standby_feedback sends information about long-running queries on the standby back to the primary, preventing the primary's VACUUM from removing row versions those queries still need (which would otherwise cause a replication conflict that cancels the query). The trade-off: it can increase bloat on the primary.

1.4.2Logical replication operations

• On the publisher side: CREATE PUBLICATION defines the set of tables to replicate (CREATE PUBLICATION pub1 FOR TABLE orders, customers; or FOR ALL TABLES). ALTER PUBLICATION adds/removes target tables, and DROP PUBLICATION removes the publication.
• On the subscriber side: SUBSCRIPTION (CREATE SUBSCRIPTION sub1 CONNECTION '...' PUBLICATION pub1; specifies the connection and publication and starts subscribing; an initial data copy runs automatically at creation). Unlike physical replication, this allows table-level selective sync, and can be arranged bidirectionally or aggregated from multiple sources.
• Logical replication does not auto-propagate DDL (schema changes must be applied manually on both sides), and it does not cover sequence values or large objects—key differences from physical replication.

1.4.3Monitoring views and processes

• pg_stat_replication is checked on the primary side. It shows one row per connected standby with sent_lsn (WAL position sent), write_lsn, flush_lsn, replay_lsn (position applied), and sync_state (sync/potential/async)—letting you see lag and which standby is currently synchronous.
• pg_stat_wal_receiver is checked on the standby side. It shows the connection info for the upstream primary and the WAL position received (received_lsn)—the standby-side counterpart to the primary-side pg_stat_replication.
• Processes: walsender (forked per standby on the primary to send WAL; counts against max_wal_senders) and walreceiver (receives and applies WAL on the standby). pg_receivewal is a dedicated client tool that streams WAL and saves it as files—useful for archiving, or when you just want to collect WAL without standing up a full standby.

Trace building streaming replication from scratch. On the primary's postgresql.conf, set wal_level = replica and max_wal_senders = 10 or so, and add a line to pg_hba.conf permitting replication connections from the standby. Create the standby with something like pg_basebackup -h primary_host -D /var/lib/pgsql/data -U replicator -P -R—the -R option auto-generates standby.signal and the connection info (primary_conninfo). If the availability requirement is strict and "not a single committed row can be lost," set synchronous_standby_names = 'FIRST 1 (standby1)' and leave synchronous_commit = on, making commits on the primary wait for standby1 to confirm the WAL write—synchronous replication (at the cost of higher commit latency). If performance is the priority instead, choose synchronous_commit = off or an asynchronous setup. If long analytical queries run on the standby, set hot_standby_feedback = on to avoid a replication conflict where the primary's VACUUM removes rows those queries still need, canceling them. After setup, monitor on the primary by periodically running SELECT application_name, sync_state, replay_lsn FROM pg_stat_replication;, checking that sync_state shows sync as expected and that the gap between sent_lsn and replay_lsn is not growing too large. On the standby, check SELECT status, received_lsn FROM pg_stat_wal_receiver;. To stream only specific tables to another system via logical replication, create CREATE PUBLICATION sales_pub FOR TABLE orders; on the publisher, then run CREATE SUBSCRIPTION sales_sub CONNECTION 'host=pub_host dbname=sales user=repl' PUBLICATION sales_pub; on the subscriber—the initial data copy runs at creation time, followed by ongoing changes. To add a table later, use ALTER PUBLICATION sales_pub ADD TABLE customers;, but since DDL changes like adding a new column are not auto-propagated by logical replication, enforce an operational rule to apply such changes manually on both the publisher and subscriber. To archive WAL exclusively, keep a streaming receiver running with something like pg_receivewal -D /archive/wal -h primary_host.

1.4.4Section summary

• Streaming setup: tune availability/performance/data-loss tolerance via wal_level=replica (logical needs logical), max_wal_senders, synchronous_standby_names, synchronous_commit, hot_standby_feedback
• Logical replication: publisher-side CREATE/ALTER/DROP PUBLICATION, subscriber-side SUBSCRIPTION. DDL is not auto-propagated
• Monitoring: primary-side pg_stat_replication / standby-side pg_stat_wal_receiver. Processes are walsender/walreceiver; pg_receivewal is for WAL-only collection