PostgreSQL Replication and Scaling - Complete Notes

An in-depth and interview-focused guide to physical and logical replication, read replicas, replication lag, durability choices, failover safety, high availability, and practical scaling patterns for production PostgreSQL.

00. The replication mental model

Replication copies an ordered history of changes from one PostgreSQL server to another. Scaling decides how applications use those copies without breaking correctness.

Start with one primary server. Transactions change data on that primary. Before changed data pages need to reach permanent storage, PostgreSQL describes the changes in write-ahead log, or WAL. Replication transports information derived from this change history to another server. A physical standby replays WAL against a binary copy of the whole cluster. A logical subscriber receives row-level changes selected by publications and applies them to named tables.

Core terms

• Primary: the current server accepting normal writes.
• Standby: a physical copy that remains in recovery and replays WAL.
• Hot standby: a standby that accepts strictly read-only SQL connections.
• Publisher: a database that exposes logical change sets called publications.
• Subscriber: a database that receives and applies logical changes.
• Upstream and downstream: the sender and receiver for one replication link.
• LSN: a position in the WAL stream, written like 0/16B6C50.
• RPO: the maximum data loss the business is prepared to accept.
• RTO: the maximum acceptable time to restore service after failure.

01. Physical streaming replication

Physical streaming replication starts from a base backup and continuously replays the primary's WAL. It copies the whole cluster at the storage level, including every database in that cluster.

WAL sender, receiver, storage and replay

These stages explain why one number called "lag" is not enough. WAL can wait before it is sent, spend time in the network, be received but not flushed, or be durable but not replayed. The primary exposes one row per directly connected WAL sender in pg_stat_replication. A standby exposes its receiver in pg_stat_wal_receiver. The standby functions pg_last_wal_receive_lsn() and pg_last_wal_replay_lsn() show received and replayed positions.

-- Run on the primary.
SELECT application_name,
       client_addr,
       state,
       sync_state,
       sent_lsn,
       write_lsn,
       flush_lsn,
       replay_lsn,
       write_lag,
       flush_lag,
       replay_lag
FROM pg_stat_replication;

-- Run on a standby.
SELECT status,
       sender_host,
       sender_port,
       slot_name,
       written_lsn,
       flushed_lsn,
       latest_end_lsn,
       latest_end_time
FROM pg_stat_wal_receiver;

SELECT pg_is_in_recovery(),
       pg_last_wal_receive_lsn(),
       pg_last_wal_replay_lsn(),
       pg_last_xact_replay_timestamp();

How a standby finds WAL

A server starts in standby mode when standby.signal exists. It can restore complete WAL segments through restore_command, read already-present files from pg_wal, and stream records through primary_conninfo. PostgreSQL cycles among available sources while recovery continues. A durable WAL archive is therefore a valuable fallback when a streaming connection is interrupted or the primary has already recycled an old segment.

Physical replication normally requires the same major PostgreSQL version and compatible storage architecture. It is not a table-by-table feature. DDL, catalog changes, indexes, extensions stored in the cluster, and user data are replayed together because the standby follows physical WAL. External files, operating-system configuration, secrets, load-balancer state and extension shared libraries still require separate management.

02. Configuration and standby bootstrap

A reliable setup has four parts: WAL capacity, authenticated transport, a consistent base backup, and a retention path that lets a disconnected standby catch up.

-- Primary SQL.
CREATE ROLE repl_user WITH LOGIN REPLICATION PASSWORD 'use-a-secret-manager';
SELECT * FROM pg_create_physical_replication_slot('standby_a');

# Primary pg_hba.conf, use a narrowly scoped address and strong authentication.
hostssl replication repl_user 10.20.30.41/32 scram-sha-256

# Primary postgresql.conf.
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10

# Bootstrap from a consistent physical base backup.
pg_basebackup \
  --host=primary.internal \
  --username=repl_user \
  --pgdata=/var/lib/postgresql/data \
  --write-recovery-conf \
  --slot=standby_a \
  --wal-method=stream

# Resulting standby configuration conceptually includes:
primary_conninfo = 'host=primary.internal user=repl_user sslmode=verify-full'
primary_slot_name = 'standby_a'

pg_basebackup takes a base backup from a running cluster through the replication protocol. The target data directory must be handled carefully, permissions must be correct, and tablespaces need an intentional mapping. In production, automate the process through a tested backup or HA system rather than copying a live data directory with a general file tool.

Bootstrap checklist

1. Verify the primary version, system identifier, tablespaces and storage capacity.
2. Create a dedicated replication role and narrow pg_hba.conf rule.
3. Size sender and slot limits with operational headroom.
4. Create or reserve the correct slot if a slot-based design is used.
5. Take a consistent base backup and transfer it over authenticated encryption.
6. Configure the upstream, slot, archive restore path and standby.signal.
7. Start the standby and confirm it reaches streaming state.
8. Generate a known write, observe receive and replay advance, and read it on the standby.
9. Test reconnection, restart, archive fallback, promotion and rebuilding the old primary.

03. Replication slots and WAL retention

A replication slot records how far a consumer still needs the WAL or logical decoding history. It protects the consumer, but transfers storage risk to the server that owns the slot.

A physical slot prevents required WAL from being removed before the standby receives it. A logical slot also protects information needed to decode changes and can hold back removal of row versions through its catalog horizon. Slots remain when consumers disconnect. That persistence is their value and their danger.

SELECT slot_name,
       slot_type,
       database,
       active,
       active_pid,
       restart_lsn,
       confirmed_flush_lsn,
       wal_status,
       safe_wal_size,
       inactive_since,
       invalidation_reason
FROM pg_replication_slots
ORDER BY slot_name;

SELECT slot_name,
       pg_size_pretty(
         pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
       ) AS retained_wal
FROM pg_replication_slots
WHERE restart_lsn IS NOT NULL;

Set alerts on retained bytes, free space, inactivity, slot invalidation and consumer progress. max_slot_wal_keep_size can cap WAL retained by slots, but crossing the cap may make a slot unusable and force a rebuild. It converts an unlimited primary-disk threat into a bounded retention policy with an explicit consumer recovery consequence.

04. Read replicas and read-only routing

A hot standby can remove some read work from the primary, but it is an eventually consistent, restricted execution environment, not a transparent second primary.

During hot standby, transaction_read_only is always true. DML, DDL, sequence changes, row-locking queries such as SELECT FOR UPDATE, temporary table creation, maintenance commands and many catalog changes are unavailable. A report that writes staging rows to a temporary table cannot simply be moved to a physical replica.

-- Check where this connection landed.
SELECT pg_is_in_recovery() AS is_standby,
       current_setting('transaction_read_only') AS read_only;

-- Capture a causal marker on the primary after a write transaction.
SELECT pg_current_wal_lsn();

-- A router or application can wait until the chosen standby reports
-- pg_last_wal_replay_lsn() at or beyond the required marker,
-- with a bounded timeout and primary fallback.

A load balancer that randomly sends every SELECT to a replica can break signup, checkout, authorization and background workflows. A user may write a row on the primary and immediately fail to find it on a lagging replica. Transactions must stay on one connection, and read-only routing must understand semantic freshness, not just SQL keywords.

Hot standby query conflicts

WAL replay represents work that already committed upstream. If a standby query blocks a replayed DDL lock or still needs row versions that primary vacuum removed, PostgreSQL must eventually delay replay or cancel the query. max_standby_streaming_delay and max_standby_archive_delay bound how long replay waits for relevant conflicts. pg_stat_database_conflicts counts cancellation reasons.

hot_standby_feedback reports standby snapshot needs upstream and can reduce cleanup conflicts. The tradeoff is delayed cleanup and possible bloat on the primary. It does not prevent every conflict, such as an Access Exclusive lock conflict. An HA replica normally favors prompt replay and short queries. An analytics replica may tolerate more replay delay. One replica rarely serves both goals perfectly.

05. Replication lag: types, causes and remediation

Diagnose the first pipeline boundary that falls behind. Treat lag as a symptom with multiple possible causes, not as a single server property.

SELECT application_name,
       pg_size_pretty(
         pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)
       ) AS not_sent,
       pg_size_pretty(pg_wal_lsn_diff(sent_lsn, write_lsn)) AS network_or_write,
       pg_size_pretty(pg_wal_lsn_diff(write_lsn, flush_lsn)) AS not_flushed,
       pg_size_pretty(pg_wal_lsn_diff(flush_lsn, replay_lsn)) AS not_replayed
FROM pg_stat_replication;

-- Time since the last replayed transaction on a standby.
SELECT CASE
         WHEN pg_last_xact_replay_timestamp() IS NULL THEN NULL
         ELSE clock_timestamp() - pg_last_xact_replay_timestamp()
       END AS replay_time_gap;

Common lag causes

• A bulk load, index build, rewrite, vacuum full or large update creates WAL faster than normal.
• The network cannot sustain peak WAL generation or has intermittent loss.
• Standby storage has lower throughput or higher latency than primary storage.
• Long standby queries delay replay because of recovery conflicts.
• Recovery is intentionally paused with pg_wal_replay_pause().
• A very large transaction cannot become visible until its commit is replayed or applied.
• CPU-heavy decompression, checksums, encryption or competing queries starve receiver or replay.
• A logical apply worker stops on a constraint, permission, schema or data conflict.
• A restart repeatedly redoes catch-up work, or required WAL has already been removed.

Lag incident playbook

1. Confirm the affected link, role, timeline, receiver state and whether recovery is paused.
2. Record current, sent, written, flushed and replayed positions without restarting anything.
3. Compare backlog change over several samples. Catching up and falling behind need different actions.
4. Compare WAL bytes generated per second with network and standby write capacity.
5. Inspect standby conflicts, long queries, storage latency, CPU pressure and logs.
6. Protect correctness by removing a stale replica from freshness-sensitive routing.
7. Protect disk by watching slots and archive growth while the consumer is delayed.
8. After repair, verify backlog reaches steady state and run a known-write visibility check.

Do not immediately restart a lagging standby. A restart can destroy useful evidence and does not solve inadequate throughput. Do not delete retained WAL or a slot to reclaim space without accepting that the consumer may require a fresh base backup. Throttle the WAL-producing operation only when the business and incident scope permit it.

06. Asynchronous and synchronous replication

Asynchronous replication favors write availability and latency. Synchronous replication can strengthen commit durability, but a commit waits for selected downstream acknowledgment.

Physical streaming replication is asynchronous by default. A primary can acknowledge a commit before the standby has received it, so failover can lose recently acknowledged transactions. Synchronous replication configures the primary to wait for one or more named standbys at a chosen acknowledgment level.

# Wait for the first two available standbys by listed priority.
synchronous_standby_names = 'FIRST 2 (dc_a_1, dc_a_2, dc_b_1)'

# Wait for any two members of the candidate set.
synchronous_standby_names = 'ANY 2 (dc_a_1, dc_a_2, dc_b_1)'

-- Override durability for one transaction when policy allows it.
BEGIN;
SET LOCAL synchronous_commit = 'remote_apply';
UPDATE payments SET status = 'captured' WHERE payment_id = 9001;
COMMIT;

FIRST is priority-based. ANY is quorum-based for acknowledgments. This commit quorum does not itself elect a primary or prevent two primaries. Election quorum and synchronous commit acknowledgment solve different problems.

Place synchronous standbys according to the failure the business needs to survive. Same-rack replicas give lower latency but share rack risk. Cross-region replicas survive wider failures but add wide-area round-trip time to every synchronous commit. Locks remain held while a transaction waits at commit, so downstream slowness can increase contention upstream. Measure tail latency, not only averages.

07. Cascading replication and topology

A cascading standby receives WAL from an upstream server and relays it to downstream standbys, reducing direct sender connections and repeated wide-area traffic from the primary.

Primary
  |-- HA standby in region A
  |-- Relay standby in region B
        |-- Read replica B1
        |-- Read replica B2

The relay must allow replication connections, have enough max_wal_senders, and expose appropriate authentication. Each downstream configures primary_conninfo for its chosen upstream. Cascading physical replication is asynchronous. Primary synchronous settings do not wait through a cascade because the primary does not directly know the downstream nodes.

After upstream promotion, downstreams can follow the new timeline when recovery_target_timeline = 'latest', which is the default. That does not remove the need to ensure they connect to the correct upstream and have the required WAL history. Topology changes need the same care as initial bootstrap.

08. Replication, backups and PITR

Replication provides a moving copy for availability. A base backup plus archived WAL provides a historical recovery path. Strong systems use both.

PITR restores a physical base backup, retrieves archived WAL through restore_command, and replays until a configured recovery target. Every required WAL segment between the base backup and target must be available. Archive monitoring must verify both timely storage and actual restore. A directory full of files is not evidence that the chain is complete.

Timelines separate histories after recovery or promotion. When a standby is promoted, PostgreSQL creates a new timeline so new WAL does not overwrite the old branch. Timeline history lets recovery follow the correct ancestry. An old primary cannot safely rejoin as primary merely by restarting. It must be fenced and rebuilt or rewound through a verified procedure.

09. Logical replication mental model

Logical replication decodes selected committed changes into table-level operations. It trades the binary completeness of physical replication for selectivity and looser coupling.

The publisher defines a publication inside one database. The publication selects tables or all tables in schemas, may select operation types, and can use row filters or column lists. A subscriber defines a connection and one or more publication names. The publisher's logical slot preserves and decodes the change stream. Subscriber workers copy existing rows and then apply ongoing transactions.

Good use cases

• Copy a subset of operational tables into a reporting PostgreSQL database.
• Move between major PostgreSQL versions with a planned low-downtime migration.
• Consolidate selected tables from databases when key spaces and ownership are controlled.
• Feed change events to a consumer through logical decoding.
• Replicate selected rows or columns when physical replication would expose too much.
• Maintain a writable subscriber for controlled integration, accepting conflict management.

Replica identity

Inserts carry the new row, so they do not require a lookup identity. Updates and deletes must find the existing subscriber row. By default, PostgreSQL uses the primary key. A suitable unique index can be selected with REPLICA IDENTITY USING INDEX. As a fallback, REPLICA IDENTITY FULL sends old-row values, which increases WAL and lookup cost and can be inefficient without a usable subscriber index.

-- Best default: a stable primary key.
CREATE TABLE accounts (
  account_id bigint PRIMARY KEY,
  email text NOT NULL,
  status text NOT NULL
);

-- Or choose a qualifying unique index.
CREATE UNIQUE INDEX accounts_external_id_uq
    ON accounts (external_id);
ALTER TABLE accounts
    REPLICA IDENTITY USING INDEX accounts_external_id_uq;

-- Last-resort fallback for a keyless table.
ALTER TABLE legacy_events REPLICA IDENTITY FULL;

A publication that includes updates or deletes will reject those operations on a table without an acceptable replica identity. Treat a stable key as part of the logical replication contract, not as an optional performance optimization.

10. Publications, subscriptions and initial sync

Logical replication setup is simple in syntax but operationally depends on pre-created schema, compatible data, adequate workers, secure connectivity, slot capacity and a deliberate cutover.

-- Publisher configuration requires:
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

-- Publisher database.
CREATE PUBLICATION commerce_pub
FOR TABLE orders, order_items
WITH (publish = 'insert, update, delete, truncate');

-- The subscriber tables must already exist.
-- A schema-only dump is a common starting point:
pg_dump --schema-only --table=orders --table=order_items source_db |
  psql target_db

-- Subscriber database. Keep secrets outside command history in production.
CREATE SUBSCRIPTION commerce_sub
CONNECTION 'host=publisher.internal dbname=app user=logical_repl sslmode=verify-full'
PUBLICATION commerce_pub
WITH (copy_data = true);

By default, creating the subscription creates a logical slot on the publisher and copies existing table data. Each table synchronization obtains a consistent starting point while concurrent changes continue entering the slot. When the copy catches the chosen point, normal apply keeps the table current. Initial copy can load the publisher, network and subscriber, while the slot retains WAL that cannot yet be released.

Row filters, column lists and operation sets

CREATE PUBLICATION active_customer_pub
FOR TABLE customers (
  customer_id,
  email,
  plan,
  updated_at
)
WHERE (status = 'active')
WITH (publish = 'insert, update, delete');

-- After adding tables or changing subscribed publication membership:
ALTER SUBSCRIPTION active_customer_sub REFRESH PUBLICATION;

Row filters are evaluated according to publication rules as rows enter, leave or change within the filter. Column lists limit replicated columns. These are data-distribution controls, not a substitute for transport encryption, target authorization or data-governance review. TRUNCATE is not filtered row by row. Initial copy behavior and combinations of overlapping publications deserve explicit tests before production.

Monitor subscriber state

-- Subscriber.
SELECT subname,
       pid,
       relid::regclass,
       received_lsn,
       last_msg_send_time,
       last_msg_receipt_time,
       latest_end_lsn,
       latest_end_time
FROM pg_stat_subscription;

SELECT subname,
       apply_error_count,
       sync_error_count,
       stats_reset
FROM pg_stat_subscription_stats;

SELECT srsubstate, count(*)
FROM pg_subscription_rel
GROUP BY srsubstate
ORDER BY srsubstate;

-- Publisher.
SELECT slot_name,
       active,
       restart_lsn,
       confirmed_flush_lsn
FROM pg_replication_slots
WHERE slot_type = 'logical';

Catalog state codes in pg_subscription_rel are useful during initial synchronization, but operators should use the documentation for their deployed major version when interpreting them. Alert on stopped workers, error counters, stale receipt times, retained WAL, tables that do not reach ready state and data-validation failures.

11. Logical conflicts, limitations and change management

A logical subscriber is a normal writable database. That flexibility means constraints, local writes, permissions and schema drift can stop or alter replication.

PostgreSQL records logical conflict statistics in pg_stat_subscription_stats and logs details. A conflict that raises an error requires a decision: change subscriber data or permissions so the transaction can apply, or skip the remote transaction using the documented subscription controls. Skipping preserves progress but intentionally creates divergence, so record the decision and reconcile data.

Important built-in limitations

• Schema definitions and DDL are not replicated. Create and evolve target schema separately.
• Sequence state is not replicated, although inserted identity values are ordinary column data.
• Large objects are not replicated. Store required content in supported table data or copy separately.
• Targets are regular tables, not views, materialized views or foreign tables.
• Tables match by fully qualified name and columns match by name.
• Subscriber-only columns need suitable defaults or nullable behavior.
• Partitioned-table mapping depends on publication settings and target table structure.
• TRUNCATE with foreign keys can fail if related target tables are outside the subscription.
• Replica identity and supported operator behavior constrain update and delete application.

Safe schema evolution pattern

Additive changes usually go to the subscriber first, then publisher, so incoming rows never refer to a missing target column. For removal or incompatible type changes, stop producing the old shape, allow replication to drain, update the publication or application contract, then alter nodes in a tested order. Exact order depends on whether old and new application versions overlap.

-- 1. Subscriber: accept the future value first.
ALTER TABLE orders ADD COLUMN fulfillment_note text;

-- 2. Publisher: add the same compatible column.
ALTER TABLE orders ADD COLUMN fulfillment_note text;

-- 3. Deploy writers that populate the new column.
-- 4. Validate apply and data.
-- 5. Add stricter defaults or constraints through a separately tested rollout.

12. High availability, promotion and split-brain safety

Failover is a distributed-systems decision: determine that the old primary must no longer accept writes, choose a sufficiently current standby, promote exactly one node, and redirect clients.

PostgreSQL supplies replication, state inspection and promotion primitives such as pg_ctl promote and pg_promote(). It does not by itself decide a complete automatic HA policy, reconfigure every client or fence an unreachable machine. HA managers, service managers, distributed configuration stores, virtual IPs, proxies and managed database platforms combine these pieces in different ways.

Split brain and fencing

Split brain means more than one node accepts writes for the same logical database history. It can happen when a network partition makes the primary and the failover controller unable to see each other, but clients can still reach both. Later, WAL histories diverge and cannot be merged by ordinary physical replication.

Fencing removes the old primary's ability to serve writes before or as the new one is promoted. Examples at a high level include powering off or isolating the old node through infrastructure, revoking its network path, detaching its storage, or using a provider operation with strong guarantees. Closing one proxy route is insufficient if any client or administrator can still reach the old server directly.

Planned switchover or emergency failover

1. Declare the incident or maintenance window and identify the current authoritative primary.
2. Stop or drain writes when possible. Record primary and candidate replay positions.
3. Confirm the candidate is healthy, on the expected timeline and within the permitted RPO.
4. Fence the old primary using the environment's tested mechanism.
5. Allow remaining WAL to replay when possible, then promote exactly one chosen standby.
6. Verify it is no longer in recovery and can commit a controlled write.
7. Redirect writer traffic and verify pools discard old connections.
8. Repoint or rebuild other standbys against the new primary and timeline.
9. Validate application invariants, lag, slots, archiving and backups.
10. Rebuild or rewind the old primary only after proving the winning history.

-- A standby returns true before promotion.
SELECT pg_is_in_recovery();

-- Promotion request from SQL with a bounded wait.
SELECT pg_promote(wait_seconds => 60);

-- Confirm new writable role and identify its timeline through operational tools.
SELECT pg_is_in_recovery(),
       current_setting('transaction_read_only');

-- Old primary data must never simply be assumed compatible with the new timeline.

Automatic failover optimizes RTO but raises the cost of a false positive. Health checks must distinguish a failed database from a failed monitoring path, overloaded server, isolated network and unavailable coordination service. Use multiple signals and time bounds matched to the business. Practice failover under realistic connection pools, DNS caches, long transactions and partial failures.

13. Scaling patterns and their limits

Replicas scale some reads and improve availability. They do not make one primary's write, coordination or data-model limits disappear.

A sensible scaling sequence

1. Measure the workload, slow queries, waits, WAL rate, connection count and storage latency.
2. Fix query shape, indexes, transaction scope and accidental excess work.
3. Pool connections and set bounded concurrency so overload does not cascade.
4. Scale the primary vertically while the operational tradeoff remains favorable.
5. Add caches or replicas for clearly stale-tolerant read classes.
6. Separate heavy analytics when replica conflicts or resource contention remain unacceptable.
7. Partition for pruning and lifecycle needs, not merely because a table is large.
8. Shard only with an explicit routing key, ownership model and cross-shard plan.

Every physical replica must receive and replay the primary's WAL, so adding replicas consumes network, primary sender resources, standby storage writes, backup work and operational attention. A read moved away from the primary may still cause WAL replay conflicts and cache churn on the standby. Benchmark total system behavior.

Sharding at a high level

Sharding places disjoint data on independent writable PostgreSQL clusters. Choose a stable shard key such as tenant ID, route every request, make globally unique identifiers, and minimize cross-shard operations. Resharding moves live data while writes continue and is often harder than the original design.

PostgreSQL physical replication then provides HA inside each shard. Logical replication can help migrations or data movement, but it does not automatically solve concurrent writes to both old and new owners. Define a single writer for each key range during every migration phase.

14. Operational playbooks and observability

Reliable replication is maintained through invariants, alerts, rehearsed procedures and capacity forecasts rather than one-time configuration.

Minimum dashboard

• Node role, recovery state, timeline and last successful role transition.
• WAL generation bytes per second and peaks by deployment or maintenance event.
• Per-link sender state, byte backlogs and write, flush and replay timing.
• Receiver connection status, last message times and reconnect counts.
• Slot active state, retained WAL, catalog horizon, inactivity and invalidation.
• pg_wal, archive and filesystem free space with forecasted exhaustion.
• Archive success, failure count, age of last archived WAL and restore-test age.
• Standby conflict cancellations by reason and long-running standby queries.
• Logical subscription workers, table sync state, apply errors and conflict counts.
• Synchronous standby membership and commits waiting on replication.
• Backup age, verified restore age, RPO estimate and failover-drill age.

Standby can no longer find required WAL

1. Confirm the missing segment and whether a durable archive contains it.
2. Repair archive access or restore configuration if the segment exists.
3. If history is unavailable, stop repeated retries and plan a new base backup.
4. Protect the primary from obsolete slot retention and document any slot replacement.
5. Rebuild from the current authoritative primary, then validate catch-up and read behavior.

Primary disk pressure from retained WAL

1. Measure free space, WAL generation rate and time to exhaustion.
2. Identify the exact slot, archive failure or standby holding the oldest WAL.
3. Restore the consumer quickly if it can catch up before exhaustion.
4. Add safe capacity or reduce controllable WAL-producing work if available.
5. Drop or invalidate a consumer only through an explicit decision to rebuild it.
6. After recovery, add a retention limit and alerts appropriate to the agreed RPO.

Logical apply stopped

1. Read subscriber logs and pg_stat_subscription_stats; identify the transaction and table.
2. Check schema, permissions, RLS, unique constraints, replica identity and local target writes.
3. Preserve conflicting rows and determine authoritative data ownership.
4. Repair the target or intentionally skip only through the documented mechanism.
5. Resume, observe acknowledgment progress and validate the affected data range.
6. Prevent recurrence through key-space, DDL or access-control changes.

15. Replication security

Replication streams can expose the database's sensitive contents. Protect them as privileged data paths and minimize who can create, consume or redirect them.

• Create dedicated roles with LOGIN REPLICATION instead of using superusers.
• Narrow pg_hba.conf by user, replication database field and source network.
• Use TLS and verify the server identity, not encryption without authentication.
• Keep passwords out of configuration committed to source control and out of command history.
• Rotate credentials through a procedure that avoids breaking every standby simultaneously.
• Restrict network routes so only expected upstream and downstream nodes communicate.
• Protect base backups, WAL archives, temporary copies and snapshots with encryption and access logs.
• Review publication tables, columns and row filters as a data-governance boundary.
• Grant subscription-owner rights only on required target tables and schemas.
• Audit slot, publication, subscription, promotion and replication-role changes.

A replication role can read the WAL stream, from which privileged data may be extracted even if that role cannot issue ordinary table queries. Logical publications narrow delivered row changes, but connection strings and target ownership remain sensitive. A writable subscriber also has its own application and insider-threat surface.

16. Edge cases, mistakes and performance implications

Most production failures come from ignoring a boundary: retained history, stale reads, role authority, schema ownership, replay capacity or an untested recovery path.

Where replication costs appear

• Primary WAL generation, sender CPU and network bandwidth.
• Extra WAL retention from slots, archives and lagging standbys.
• Standby WAL writes, data-page replay, restartpoints and query I/O.
• Synchronous commit network round trips, remote flush and possibly replay latency.
• Locks held longer while synchronous commits wait.
• Logical decoding memory, spill or streaming work for large transactions.
• Subscriber index and constraint maintenance for every applied row.
• Initial synchronization scans, network transfer and bulk target writes.
• Primary bloat when hot standby feedback or logical horizons delay cleanup.

Large transactions

A large transaction can generate a burst of WAL, retain locks, delay logical visibility until commit, consume decoding resources and create a long apply unit. Break bulk work into bounded, restartable transactions when business atomicity allows. Do not split a transaction whose invariant genuinely requires all-or-nothing behavior merely to improve replication metrics.

Version and compatibility boundaries

Physical replication normally cannot cross major PostgreSQL versions, which is why major upgrades use binary upgrade, dump and restore, or logical replication strategies. Logical replication is more flexible, but schema, type, feature and protocol behavior still must be validated for the exact source and target versions. Read the documentation for both deployed versions and rehearse rollback.

17. Practice scenarios and questions

Use these exercises to practice decisions, commands and failure reasoning. A strong answer names the required guarantee, observes evidence, explains tradeoffs and includes validation.

Hands-on lab

1. Build a primary and physical standby in disposable environments. Use a dedicated replication role, TLS if the lab supports it, a named slot and pg_basebackup.
2. Insert a marker row on the primary. Capture its commit-side WAL position and watch the standby's receive and replay positions pass it.
3. Pause WAL replay briefly, generate writes, and compare receive backlog with replay backlog. Resume replay before retention becomes unsafe.
4. Run a long read on the standby while updating and vacuuming relevant primary rows. Observe conflict counters under lab-appropriate delay settings.
5. Create a publication for a keyed table and a subscription in another cluster. Watch initial synchronization and ongoing apply.
6. Cause a safe unique-key conflict on the disposable subscriber. Capture logs and counters, repair it, then verify apply resumes.
7. Perform a controlled physical promotion. Prove the new server is writable, then rebuild the old primary as a standby rather than returning it as a competing writer.
8. Restore a base backup with archived WAL to a chosen target in an isolated environment. Validate application rows, not only server startup.

Design questions

1. Checkout requires read-your-writes, but product search tolerates 30 seconds of staleness. Draw the connection-routing rules and fallback behavior.
2. The business allows no acknowledged payment loss but can tolerate higher payment latency. Choose a synchronous configuration and explain its behavior during standby loss.
3. An analytics query needs two hours and frequently gets canceled on the HA standby. Compare delay changes, hot standby feedback, a dedicated replica and a logical reporting copy.
4. A slot retains 600 GB of WAL while its consumer is offline and disk will fill in 45 minutes. Write the evidence-gathering and decision tree.
5. Plan a major-version migration with logical replication. Include schema setup, sequences, initial sync, validation, write freeze, final catch-up, cutover and rollback boundary.
6. A network partition isolates the old primary from the HA controller but not from some clients. Explain why promotion without fencing is unsafe.
7. Two regions require local low-latency reads, while writes remain in one region. Describe routing, cascading, staleness expectations and disaster recovery.
8. An application wants active-active writes through two logical subscriptions. List the key, conflict, sequence, ordering and DDL questions that must be solved first.

Short-answer checks

• Why can replay_lsn trail flush_lsn?
• When can timestamp lag look large even while fully caught up?
• What protects a disconnected standby from WAL recycling?
• Why can hot_standby_feedback cause primary bloat?
• What is the difference between remote_write and remote_apply?
• Why is a synchronous quorum not a primary-election quorum?
• Why are DDL and sequence state migration tasks in logical replication?
• Which identity normally locates rows for logical update and delete?
• Why must the old primary be rebuilt or safely rewound after failover?
• How do replication and PITR solve different recovery problems?

18. Interview questions and model answers

19. One-screen cheat sheet

• Physical: base backup plus whole-cluster WAL replay, normal HA foundation.
• Logical: publications, slots and subscriptions for selected table changes.
• Pipeline: generate, send, receive, write, flush, replay, become visible.
• LSN: WAL position; use differences for byte backlog.
• Primary view: pg_stat_replication.
• Standby view: pg_stat_wal_receiver.
• Role check: pg_is_in_recovery().
• Slot view: pg_replication_slots.
• Slot rule: protects consumers but can retain WAL until primary disk fills.
• Read replica: eventually consistent and more restricted than primary read-only mode.
• Causal read: primary routing, replay-position wait or suitable synchronous policy.
• Replay conflict: delay WAL or cancel standby query; feedback may cause bloat.
• Async: lower latency, possible recent acknowledged loss on failover.
• Sync on: wait for durable remote flush from selected standbys.
• Remote apply: wait for replay and visibility to new standby snapshots.
• FIRST: priority synchronous selection.
• ANY: quorum synchronous acknowledgments, not primary election.
• Cascade: standby relays WAL; fewer links, extra dependency and lag.
• Replica identity: stable key for logical update and delete.
• Logical gaps: DDL, sequences and large objects require separate handling.
• Conflict: repair business truth, resume, then validate data.
• Promotion: fence old primary, promote one candidate, redirect and verify.
• Split brain: multiple writable authorities; prevent with coordination and fencing.
• Backup: independent base backup plus WAL archive for PITR.
• Scaling: optimize first, pool, classify reads, then replicas or deliberate sharding.
• Operations: monitor rates, backlog stages, slots, disk, archive and restore drills.

20. Official reference map

• High availability, load balancing and replication
• Log-shipping standby servers, streaming, slots, cascading and synchronous replication
• Hot standby behavior and recovery conflicts
• Replication configuration parameters
• Replication, receiver, slot and subscription statistics
• WAL, recovery, backup and replication administration functions
• pg_basebackup
• Continuous archiving and point-in-time recovery
• Logical replication overview and architecture
• Publications and replica identity
• Subscriptions and initial synchronization
• Logical replication row filters
• Logical replication column lists
• Logical replication conflicts
• Logical replication restrictions
• Monitoring logical replication
• CREATE PUBLICATION
• CREATE SUBSCRIPTION