Database Replication and Consistency

Advanced System Design & Software Architecture · Lesson 29 · High-Level Design and Distributed Systems

Lesson Overview

A learner pays for a course, the checkout transaction commits, and the next request is the order-history page from Database Design and Indexing for Scale. The history query is correct, indexed, and fast. It runs on a streaming replica that is four seconds behind the primary. The page says the learner has no new order. They pay again. Support then finds two captures and one very clear timeline in pg_stat_replication.replay_lag.

Replication copies PostgreSQL so a dead primary is not the end of the service and so read traffic has somewhere else to land. It does not copy the meaning of "I just committed." That meaning is a contract you design: which commits wait for a standby, which reads are allowed to see an older snapshot, and which session must stick to the primary until a replica's replay LSN has caught the commit. Asynchronous replicas add read capacity and lose the right to answer "show me what I just wrote" unless the application carries a token. Synchronous commit buys that token by adding network delay to every acknowledged write, and it can stop acknowledging writes when the standby you named is unreachable.

This lesson builds that contract for TechLearningPro commerce. You will follow WAL from the primary through walsender and walreceiver to a hot standby, choose synchronous_commit and synchronous_standby_names with their actual PostgreSQL meanings, promote with fencing so two primaries cannot both take money, and route Spring reads with AbstractRoutingDataSource plus an LSN check against pg_last_wal_replay_lsn(). Sharding comes next, in Database Sharding and Partitioning. The vocabulary of linearizability and session guarantees is CAP Theorem and Consistency Models. Engine detail lives in Streaming Replication, Physical Replication, Failover, High Availability, and Logical Replication.

Learning Objectives

By the end of this lesson you will be able to:

1. Describe physical streaming replication as WAL shipping and replay, including what a hot standby can and cannot serve.
2. Distinguish synchronous_commit values (off, local, remote_write, on, remote_apply) once synchronous_standby_names is set.
3. Explain why an asynchronous replica can acknowledge a commit on the primary that a crash of the primary then loses, and when that loss is acceptable.
4. Implement read-your-writes with a commit LSN compared to pg_last_wal_replay_lsn() on the replica.
5. Route read-only Spring transactions to a replica and writes to the primary with AbstractRoutingDataSource.
6. Promote a standby with pg_promote() only after the old primary is fenced, and name the split-brain failure that fencing prevents.
7. Use replication slots without filling the primary disk, including max_slot_wal_keep_size.
8. Read pg_stat_replication lag columns and standby query-cancel conflicts (max_standby_streaming_delay, hot_standby_feedback).
9. Choose physical versus logical replication for failover versus selective feed to a warehouse.
10. Write the operational checks that prove a failover finished: one primary, replicas streaming, and the application writer pointed at the new primary.

Prerequisites

• Lesson 28's orders schema and the history query. Replication applies that plan on more than one node. It does not repair it.
• MVCC and Transactions. Replicas replay committed WAL. They do not merge uncommitted work.
• A Spring service that already uses @Transactional(readOnly = true) for history and a read-write transaction for checkout. This lesson routes those two transactions to different JDBC URLs.
• Lesson 24's read and write rates, so replica count is a capacity number rather than a slogan.
• You do not need Patroni installed. Where this lesson mentions it, Patroni is a real high-availability controller that stores leadership in etcd, Consul, ZooKeeper, or Kubernetes. The SQL and the Spring routing stand without it.

Detailed Core Concepts

What is being copied

PostgreSQL physical replication copies the cluster by shipping write-ahead log. The primary appends WAL for committed and flushed change, a walsender process streams those records, and the standby's walreceiver writes them. The startup process replays them into the same file layout the primary uses. A byte-level copy follows. You do not get a different schema on the standby. You get the same indexes, the same bloat, and the same bad plan, executed later.

hot_standby = on allows SELECT while replay continues. The standby is read-only. An insert fails with SQLSTATE 25006 (read_only_sql_transaction). That error is a routing bug when the application sent a write to a replica, and it is a useful tripwire: catch it and retry the write on the primary once, rather than showing the learner a database stack trace.

The primary does not wait for a standby unless you configure it to. Default synchronous_commit = on waits until the commit record is flushed to the local disk of the primary. Durability here means "survives a crash of this process," not "survives loss of this machine." A second machine enters the commit path only through synchronous_standby_names.

Synchronous and asynchronous, in PostgreSQL's words

synchronous_standby_names lists standbys that may participate, with a quorum method:

• FIRST 1 (standby_a, standby_b) waits for one connected standby, preferring standby_a.
• ANY 1 (standby_a, standby_b) waits for any one of them.

synchronous_commit then says how far that standby must get before the primary tells the client the commit succeeded:

If synchronous_standby_names is empty, local, remote_write, on, and remote_apply collapse to the same practical wait: local flush. The replica-related values do nothing until a standby is named. Set both, or you have configured a word and not a wait.

Async replication is the configuration TechLearningPro uses for extra history readers: standbys not listed in synchronous_standby_names, or synchronous_commit = local on the checkout session. The primary acknowledges after local flush. The replica applies the record milliseconds or seconds later. Read capacity scales. A history read can miss the row that checkout already returned as success. That is replica lag, and it is the normal state of an async copy, not an incident, until lag exceeds the SLO you published.

Sync replication puts the standby on the commit latency budget. A round trip and a disk flush on another host sit inside every payment commit. If the required standby disconnects, commits that need it block. PostgreSQL will wait, and synchronous_commit does not time out by itself. Pair it with statement_timeout or with a client timeout, and decide what the application does when the wait fires: fail the checkout, or retry with SET LOCAL synchronous_commit = local only if the product accepts a commit that might exist only on the primary. Doing that silently for payments is how you invent data loss in the name of availability. Doing it for a progress heartbeat can be the right product call. Write the choice down per transaction, not as a cluster-wide mood.

remote_apply is the synchronous form that makes read-your-writes true on that standby without an LSN dance. The commit returns only after replay. History served from that same standby sees the order. You still pay the replay time on the write, and any other async replica remains stale. One sync standby is not a promise about the whole fleet.

Read-your-writes is a session property

The learner's guarantee is smaller than "every replica is current." It is: after my commit, my next read sees my write. Other learners can see the order a little later. PostgreSQL does not attach this guarantee to async replicas by default. The application does, with one of three patterns:

1. Primary pin. After a write, route that user's reads to the primary until a deadline or until a replica catches up. Simple, and it concentrates reads back on the primary right after the write spike you hoped to shed.
2. LSN token. On the primary, after commit, read pg_current_wal_lsn(). Store it in the session. A replica read is allowed only when pg_last_wal_replay_lsn() >= token. Compare them in SQL as pg_lsn, not as strings. If the replica is behind, use the primary for this request.
3. Sync apply. Commit with remote_apply and read the standby you waited for. The token is implicit. You must actually route to that standby.

The LSN sampled with pg_current_wal_lsn() after commit can sit slightly ahead of your transaction if concurrent sessions wrote more WAL. Waiting for that position is conservative: the replica that has reached it has applied your commit too. Sampling inside the transaction, before commit, can observe a position the commit record has not reached yet. Sample after a successful commit on the same primary connection.

Monotonic reads are the partner guarantee: once this session has seen a version, a later read from a more lagged replica must not go backward. A pin to the primary, or a token that only moves forward, provides it. Random replica choice per request breaks it. A learner who refreshes and watches their new order disappear is a monotonic-read bug, even if each replica is "healthy."

Failover and split brain

Promotion makes a standby a primary. SELECT pg_promote(); (PostgreSQL 12 and later) or pg_ctl promote stops replay and starts accepting writes. The timeline id increases. WAL on the new timeline diverges from any node that kept writing on the old one.

Split brain is two nodes that both believe they are primary. It happens when the old primary is only partitioned, not stopped, and clients still reach it, while the standby is promoted because heartbeats failed. Both accept payments. Replication cannot merge those histories. The repair is a human with a backup and a reconciliation script.

Fencing means the old primary cannot accept writes before the new one does. Practical fences: the cloud API stops the VM, the storage layer revokes the volume, or a consensus lease (the mechanism Patroni stores in etcd, Consul, or ZooKeeper) is the only authority that may point the writer DNS at a node. A load-balancer health check that only pings TCP is not a fence. Both nodes can answer TCP during a partition.

After promotion, the old primary must be rebuilt onto the new timeline before it can follow again. pg_rewind can do that when wal_log_hints = on or data checksums were enabled before the divergence, and when the old primary's cluster is intact enough to rewind. Otherwise take a new pg_basebackup. Plan the disk time. A failover diagram that ends at pg_promote() has not finished the outage.

Transactions acknowledged only on the old primary under async replication can be absent after a failover that promotes a replica which had not flushed them. That loss is the price of local commit. If checkout used a sync standby and synchronous_commit = on, commits the client was told succeeded are on that standby's disk. Promote that standby, or one at least as far along, and you keep acknowledged payments. Promoting a cascading replica that had not received those records reintroduces the loss. Know which node is allowed to become primary.

Slots, feedback, and conflicts

A replication slot remembers how far a consumer has received WAL so the primary does not recycle segments the consumer still needs. Physical slots protect a standby. Logical slots protect a subscription or a decoder. A standby that stays down retains WAL on the primary until the disk fills and the primary stops accepting writes. max_slot_wal_keep_size (PostgreSQL 13+) caps what a slot may retain. When the cap is hit the slot is invalidated and that standby needs a new base backup. The alternative, unlimited retention, takes the primary down to save a replica. Prefer the invalidated slot.

Hot-standby queries can conflict with replay. Replay of a VACUUM that removes tuples a standby query still needs will cancel the query once max_standby_streaming_delay elapses. Raising the delay makes replica reads more stable and makes lag worse, because replay waits. hot_standby_feedback = on tells the primary the standby's oldest snapshot so vacuum keeps those tuples. The primary bloats instead. Pick which side pays. A long report on a replica that also serves the home page will cancel checkout-related queries or bloat the primary. Run the report on a dedicated replica with its own delay budget.

Physical copy versus logical publication

Physical replication is the failover and read-scale tool: whole cluster, same major version, replica is not writable. Logical replication publishes row changes for listed tables through a publication and a subscription. The subscriber is a separate primary with its own indexes. You can replicate commerce.orders to a warehouse database and add analytical indexes there that lesson 28 refused to put on the checkout primary. Schema changes do not ride along as part of ordinary row replication. Apply DDL on both sides in a planned order. Unique conflicts on the subscriber stop the subscription until you resolve the row. Logical replication is the wrong tool for automatic failover of the whole cluster. It is the right tool for a selective feed. Logical Replication is the mechanics lesson. This lesson decides which problems it is allowed to solve.

Architecture and Design Diagrams

mermaidCopy
flowchart TD
    checkout[Checkout write]
    history[History read]
    route[Routing DataSource]
    primary[Primary PostgreSQL]
    syncsb[Sync standby remote_apply]
    async1[Async replica A]
    async2[Async replica B]
    checkout --> route
    history --> route
    route -->|read-write and pinned sessions| primary
    route -->|read-only when replay LSN caught up| async1
    route -->|read-only when replay LSN caught up| async2
    primary -->|WAL sync commit waits| syncsb
    primary -->|WAL async| async1
    primary -->|WAL async| async2

Checkout always uses the primary. Its commit waits for the sync standby only for payment transactions set to remote_apply or on. History uses an async replica when that replica's pg_last_wal_replay_lsn() has passed the session token, and uses the primary otherwise. The sync standby exists to hold acknowledged payment WAL, not to absorb the home-page QPS. Putting home-page traffic on the sync standby couples read load to commit latency.

mermaidCopy
sequenceDiagram
    participant App as OrderService
    participant P as Primary
    participant R as Async replica
    App->>P: BEGIN insert order COMMIT
    P->>P: flush local WAL
    App->>P: SELECT pg_current_wal_lsn
    P-->>App: token 0/16B3748
    App->>R: SELECT pg_last_wal_replay_lsn at least token
    alt replay caught up
        R-->>App: history rows include the order
    else replay behind
        App->>P: same history SELECT
        P-->>App: history rows include the order
    end

Failover is a separate sequence. Fence the old primary, promote the sync standby, repoint the writer URL, rebuild the old node as a replica, and confirm pg_stat_replication from the new primary. A health check that flips DNS because one ping failed, without fencing, is the split-brain sequence.

Technical Explanation

The processes and the views

On the primary, pg_stat_replication has one row per connected sender. state is startup, catchup, streaming, or backup. sync_state is async, potential, sync, or quorum. sent_lsn, write_lsn, flush_lsn, and replay_lsn are the pipeline. write_lag, flush_lag, and replay_lag are durations. Replay lag is what the learner feels. Flush lag is what you lose if the primary dies and you promote that standby. A standby can be flush-current and still replay-behind if a long query or a slow apply is in the way. Those are different incidents.

sqlCopy
SELECT application_name, state, sync_state,
       sent_lsn, flush_lsn, replay_lsn,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

On a standby, SELECT pg_is_in_recovery(); returns true. pg_last_wal_receive_lsn() is what has arrived. pg_last_wal_replay_lsn() is what queries can see. A node that returns false for pg_is_in_recovery() is a primary. Two nodes returning false is split brain, not "multi-master." PostgreSQL physical replication has one primary.

primary_conninfo on the standby points at the primary. primary_slot_name ties it to a slot created with pg_create_physical_replication_slot. max_wal_senders on the primary must cover every direct standby plus a margin for base backups. Cascading replicas connect to a standby, not to the primary, so they do not consume the primary's sender slots. They also are not automatically the sync standby you waited for. Do not promote a cascade node and assume it holds every acknowledged commit.

Commit waiting, concretely

A payment method sets the wait for its own transaction:

sqlCopy
BEGIN;
SET LOCAL synchronous_commit = remote_apply;
INSERT INTO commerce.orders (...)
VALUES (...);
COMMIT;

SET LOCAL ends with the transaction. Progress heartbeats stay on the session default of local and do not pay for remote_apply. If no sync standby is connected, the payment COMMIT blocks. The application timeout must be shorter than the learner's patience and must not retry blindly. A retry can create a second order if the first commit actually landed and only the response was lost. Idempotency keys on checkout belong to Idempotency and Exactly-Once Processing. This lesson's job is to make the timeout visible and the duplicate possible to detect.

remote_write is a deliberate middle: the standby OS has the bytes, local primary disk has them, and you did not wait for the standby disk. A power loss of both machines at once can drop a commit the client observed. Use it when that joint failure is inside the product's loss budget. Do not describe it as "synchronous and therefore safe" in a review.

Routing in Spring

AbstractRoutingDataSource chooses a target from a lookup key on each connection request. The key for this service is primary or replica. Read-write transactions and any session that holds an LSN the replicas have not reached use primary. @Transactional(readOnly = true) may use replica.

javaCopy
public final class ReplicaRoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        boolean readOnly = TransactionSynchronizationManager.isCurrentTransactionReadOnly();
        if (!readOnly || LsnPin.needsPrimary()) {
            return "primary";
        }
        return "replica";
    }
}

LsnPin is application state, a request-scoped or session-scoped holder, not a Spring type. After checkout commits, the service runs SELECT pg_current_wal_lsn() on the primary connection and stores the result. The history method asks the replica SELECT pg_last_wal_replay_lsn() >= ?::pg_lsn. If the answer is false or the replica is down, LsnPin.needsPrimary() stays true for this request. The history SQL is unchanged from lesson 28. Only the connection changes.

Configure two pools. The replica pool's statement timeout can be tighter because a cancelled standby query is retryable on the primary. Do not point both pools at the same URL and call it routing. Do not use a replica URL as the DataSource of a @Transactional method that inserts. readOnly = true is a hint Spring and the JDBC driver pass through. It is not a security boundary. A missing annotation on a new write method will send inserts to the replica, and 25006 is how you will notice. Alert on that SQLSTATE.

A load balancer in front of replicas is optional. If you use one, it must not also front the primary, or a write will land on a random standby. Health checks should query SELECT pg_is_in_recovery() and only keep nodes that return true in the replica pool. A promoted node that still sits in the replica pool will accept writes that other replicas never see.

Promotion runbook you can rehearse

1. Confirm the candidate: sync_state and flush_lag from pg_stat_replication recorded before the primary died. Prefer the standby that was sync for payment commits.
2. Fence the old primary. Stop the compute or remove its network and volume. Verify you cannot open a new connection to it.
3. On the candidate, SELECT pg_promote();. Then SELECT pg_is_in_recovery(); must be false.
4. Repoint the Spring writer configuration at the new host. Restart or refresh the primary pool. Leave replicas on the old writer URL and they will follow a server that is gone.
5. Create a slot if the new primary has none, and point surviving standbys' primary_conninfo at the new primary. They may need a rebuild if their timeline diverged.
6. Rebuild the fenced node with pg_rewind or pg_basebackup. Bring it up as a standby. Watch it enter streaming.
7. Run a write and a replica read with an LSN token in a staging drill before you call the failover done.

Patroni automates the lease, the promote, and the callback that can run a script to move a virtual IP. It does not remove the need to understand steps 2 and 6. A controller that promotes without fencing is an automated split brain. Read its fencing configuration in the drill, not during the outage. Failover walks the SQL. This lesson owns the decision of who is allowed to promote.

Security

The replication role has the REPLICATION attribute and CONNECT on the database it needs. It is not a superuser by habit. pg_hba.conf allows that role from the standby CIDR only, over TLS, with clientcert=verify-full or an equivalent host SSL requirement. Application logins used by Spring have no REPLICATION attribute and no privilege to pg_promote(). Promotion credentials live with the operator path or the HA controller, not in the checkout pod. Replicas contain the same personal data as the primary. A "read replica for analysts" that skips the row-level policies of the primary is a data leak with good intentions. Row-Level Security has to be present on a logical subscriber because logical replication lands in a different database where policies are not part of the WAL stream. Physical replicas already have the same policies, because they have the same files.

Practical Implementation or Design Walkthrough

Problem

TechLearningPro checkout writes 200 orders per second at peak. History reads 4,000 times per second. One primary can take the writes and cannot take the reads once the working set and the connection count grow. A lost primary must not lose a payment the API already called successful. A learner must see their order on the next page load.

Requirements

• Acknowledged payment commits survive loss of the primary host.
• History p95 stays within the lesson 28 budget, and a read immediately after checkout sees that order.
• Progress heartbeats may acknowledge before a standby flushes.
• Failover to a promoted standby completes without two writers.
• Replica lag beyond five seconds pages a human. Lag beyond thirty seconds removes that replica from the read pool.

These numbers are design targets for the exercise, chosen so the SLO is testable.

PostgreSQL shape

sqlCopy
-- postgresql.conf on the primary
-- wal_level = replica
-- max_wal_senders = 10
-- max_replication_slots = 10
-- synchronous_standby_names = 'FIRST 1 (pay_standby)'
-- synchronous_commit = local
-- max_slot_wal_keep_size = '32GB'
-- hot_standby = on

SELECT pg_create_physical_replication_slot('pay_standby');
SELECT pg_create_physical_replication_slot('history_a');

Cluster default synchronous_commit = local keeps heartbeats off the standby wait. Payment code sets remote_apply locally so the commit returns only after pay_standby has replayed it. History replicas history_a and history_b are async. pay_standby is not in the Spring replica pool.

Standby connection, in the standby's configuration:

textCopy
primary_conninfo = 'host=primary.internal user=replicator password=... sslmode=verify-full'
primary_slot_name = 'history_a'

LSN-aware history read

javaCopy
public List<OrderHistoryRow> recentAfterWrite(
        UUID learnerId, OffsetDateTime since, int limit) {
    if (!replicaHasCaughtUp()) {
        return primaryReader.recent(learnerId, since, limit);
    }
    try {
        return replicaReader.recent(learnerId, since, limit);
    } catch (DataAccessException ex) {
        return primaryReader.recent(learnerId, since, limit);
    }
}

boolean replicaHasCaughtUp() {
    String token = LsnPin.token();
    if (token == null) {
        return true;
    }
    Boolean caughtUp = replicaJdbc.sql(
            "SELECT pg_last_wal_replay_lsn() >= CAST(? AS pg_lsn)")
        .param(token)
        .query(Boolean.class)
        .optional()
        .orElse(false);
    return Boolean.TRUE.equals(caughtUp);
}

primaryReader and replicaReader are the lesson 28 query against two JdbcClient instances, or one routing data source whose key LsnPin influences. The cast to pg_lsn is the comparison. A null replay LSN means the standby has applied nothing yet. Treat that as not caught up.

Store the token after commit:

javaCopy
String token = primaryJdbc.sql("SELECT pg_current_wal_lsn()::text")
    .query(String.class)
    .single();
LsnPin.remember(token);

Call that on the same primary DataSource that committed, after the transaction completes. Inside an open transaction the LSN can still move before commit.

Validation

• Commit a payment with remote_apply, then SELECT pg_is_in_recovery() on pay_standby and read the order there before the method returns. The test fails if the row is missing.
• Commit with the default local, stop the primary before the async replica replays, promote the replica, and show the heartbeat row can be missing while the payment row is present. That is the loss budget, demonstrated on purpose in a lab.
• Point a write at the replica and assert SQLSTATE 25006.
• Hold a transaction open on a replica, vacuum the same tuples on the primary, and observe either a cancelled query or added lag according to max_standby_streaming_delay.
• Fill WAL against a paused replica in a lab with a small max_slot_wal_keep_size and show the slot invalidates instead of filling the disk without bound. Do this on a scratch cluster.
• Run the promotion runbook on that scratch cluster. Assert exactly one node returns pg_is_in_recovery() = false.

Failure handling

Replica down: history uses the primary, and an alert fires because the primary is now carrying read QPS it was not sized for. Shed the home page or expand the primary before the connection pool saturates. Sync standby down: payment commits block until timeout. Checkout returns a clear "try again" and does not switch to local for payments. Primary down: failover runbook. Clients in flight get connection errors. Idempotent checkout retries. History tokens from the old primary are meaningless on the new timeline. Clear LsnPin on failover and read the new primary until a fresh token exists.

Enterprise Real-World Scenarios

Scenario 1: Order history that must not forget a payment

Context. The home page reads order history through a replica pool. Checkout writes the primary. Enrollment week produces the 4,000 reads and 200 writes from the capacity model.

Failure. Routing sent every read to a random async replica. Lag of a few seconds was normal and unmeasured. Learners double-paid. The incident channel discussed cache invalidation. The cache was empty. The replica was simply behind, and replay_lag had been two to six seconds all morning.

Design. Payment transactions set synchronous_commit = remote_apply and wait for pay_standby. The API stores pg_current_wal_lsn() in the user session. History reads use a replica only when pg_last_wal_replay_lsn() has reached that token. Otherwise they use the primary. Async replicas absorb the bulk of history traffic for learners who are not in the seconds after a purchase. The sync standby stays out of that pool so a home-page storm cannot slow payment replay.

Operations. Page when any history replica's replay_lag exceeds five seconds, and remove it from the pool at thirty seconds. Page immediately when pay_standby is not sync in pg_stat_replication, because payment commits will block. Drill promotion quarterly, including the fence.

Scenario 2: A bank ledger replica and a marketing feed

Context. A separate staff-training design, not a claim about a particular bank. Account balances live in PostgreSQL. Marketing wants a feed of new accounts for a campaign tool. An engineer proposes one replica for both.

Conflict. The ledger's acknowledged debit must be on a standby disk before the client hears success, and balance reads for the account holder must not go backward. The campaign tool can tolerate minutes of delay and must not run VACUUM-conflicting reports on the ledger's failover node.

Design. Ledger commits use synchronous_commit = on with FIRST 1 of two named standbys in another zone, so one zone loss does not block forever if the survivor is connected. Account-holder reads use the LSN token or the primary. A logical publication of non-secret account-opened facts feeds a subscriber database the campaign tool owns. That subscriber has its own indexes and its own vacuum. It is not a failover candidate. Physical replicas of the ledger stay in the fenced HA set and do not serve ad hoc exports. Logical Replication is the feed. Physical replication is the HA path. Mixing them on one node couples a marketing scan to debit latency.

Best Practices

• Decide commit durability per transaction. Payments can wait for remote_apply or on. Heartbeats can stay local.
• Set synchronous_standby_names and synchronous_commit together. Either one alone does not wait for a replica.
• Keep the sync standby out of the general read pool if read spikes would delay apply.
• Carry an LSN token for read-your-writes. Compare pg_lsn values in SQL.
• Pin a session to the primary when the token is ahead of every replica, and release the pin when a replica catches up.
• Fence before promote. Treat two nodes with pg_is_in_recovery() = false as split brain.
• Cap slot WAL with max_slot_wal_keep_size. An invalidated slot is cheaper than a full disk on the primary.
• Give long reports a replica of their own, with an explicit max_standby_streaming_delay.
• Use logical replication for selective copies and different indexes. Use physical replication for failover.
• Alert on replay_lag, on sync_state, and on SQLSTATE 25006.
• Rebuild the old primary after failover before you trust the new topology.
• Keep replication roles off application deployments, and restrict pg_hba.conf to replica networks over TLS.
• Clear session LSN pins when the primary identity changes. Old tokens are a different timeline.

Common Mistakes and Anti-Patterns

Calling every replica synchronous because the feature is "on." synchronous_commit = on without synchronous_standby_names waits for local flush only. The cluster is async and the review is wrong. Show a sync row in pg_stat_replication during a commit, or stop using the word.

Serving read-your-writes from a random async replica. The history query is fast and stale. Learners miss their own writes. Route with an LSN token or a primary pin. A fixed "read replica for all GETs" rule ignores session guarantees.

Promoting on a failed ping. The old primary still accepts writes. Both timelines take orders. Fence first, then pg_promote(), then move the writer endpoint.

Unbounded replication slots. A standby in another region goes down for a weekend. The slot retains WAL. The primary disk hits 100% and checkout stops. Set max_slot_wal_keep_size and alert on slot size before the cap.

One replica for failover, reports, and the home page. A reporting query delays replay via max_standby_streaming_delay, payment lag alerts fire, and home-page latency tracks the report. Split the roles across nodes.

Retrying a timed-out payment commit without an idempotency key. The commit may have succeeded and the response died. A second insert creates a second order. Timeouts plus sync commit make this more likely, not less, because the wait is longer.

Logical replication as a silent HA plan. Publications move rows. They do not promote a cluster, they do not ship every DDL statement you assumed they did, and a unique violation on the subscriber halts the apply. Keep a physical standby for failover.

Troubleshooting and Design Review

Design review questions: Which transactions wait for which standby, and what is the RTT? Which reads require read-your-writes, and where is the token stored? What fences the old primary? What is the slot cap? Which replica is allowed to run a ten-minute query? Where does a logical subscriber fit, and why is it not in the promote set?

Interview Questions and Answers

Q1. What does PostgreSQL stream to a physical replica, and what does the replica refuse to do?

The primary streams WAL. The standby writes and replays it, so the replica is a time-delayed copy of the same cluster, including indexes and unvacuumed bloat. With hot_standby = on it serves SELECT during replay. It refuses writes. An insert fails with SQLSTATE 25006. Physical replication is how you add a failover candidate and read capacity. It is not how you give the replica a different index set. That is logical replication into another database.

Q2. When does synchronous_commit = on actually wait for a standby?

When synchronous_standby_names names a connected standby that meets the FIRST or ANY quorum. Then on waits until that standby has flushed the commit WAL to disk. With an empty standby list, on waits only for the primary's local flush. local never waits for a standby. remote_write waits until the standby's operating system has the WAL. remote_apply waits until the standby has replayed the commit, so a query there can see it. off can acknowledge before the primary itself has flushed, and a crash can lose those commits.

Q3. A user pays and immediately does not see the order. What happened, and how do you fix the read path?

Checkout committed on the primary, often after only a local flush. History was routed to an async replica whose pg_last_wal_replay_lsn() had not reached that commit. The SQL and the index can be perfect. The snapshot is old. Fix it with a session token from pg_current_wal_lsn() after commit and a replica predicate pg_last_wal_replay_lsn() >= token::pg_lsn, or by reading the primary until a replica catches up. remote_apply on the payment plus reading that same standby also works. Reading a random other replica does not.

Q4. Why can an acknowledged async commit disappear after failover?

The client was told success after the primary flushed WAL locally. The replica you promote may not have received or flushed that record. Promotion starts a new timeline from the replica's last replayed state. Those missing commits are gone. If the product cannot lose an acknowledged payment, that transaction must wait for a standby flush or apply, and the node you promote must be one that met the wait. Heartbeats can stay async if losing them is acceptable.

Q5. What is split brain in this architecture, and what prevents it?

Two PostgreSQL nodes both accept writes, which you can detect when both return false from pg_is_in_recovery(). It follows a promotion while the old primary is only unreachable from the controller and still reachable from clients. WAL timelines diverge. Replication will not merge them. Prevention is fencing: stop the old node's compute, network, or disk before advertising the new primary. A TCP health check is not a fence. Consensus leases, as used by controllers such as Patroni with etcd or Consul, exist so only the lease holder is the writer.

Q6. How do you route reads in Spring without sending inserts to a standby?

Use AbstractRoutingDataSource. In determineCurrentLookupKey(), send the connection to the primary when the transaction is not read-only or when the session LSN is ahead of the replicas. @Transactional(readOnly = true) is what makes TransactionSynchronizationManager.isCurrentTransactionReadOnly() true. Keep separate pools. Health-check replica pool members with pg_is_in_recovery() so a promoted node does not remain a read target that now accepts writes. Treat SQLSTATE 25006 as a page-level signal that a write missed the router.

Q7. What fails if a replication slot's consumer disappears?

The primary keeps WAL for that slot. A long outage fills the disk and the primary stops committing. max_slot_wal_keep_size invalidates the slot instead of consuming the volume. The standby then needs a new base backup. Alert on retained WAL per slot while the consumer is still healthy, so you rebuild before the cap. Dropping a slot for a live standby makes the primary recycle WAL the standby still needs, and the standby fails out of streaming. The cap is for dead consumers, not a routine deletion.

Q8. Why do replica queries get cancelled, and what does hot_standby_feedback change?

Replay must apply vacuum and other WAL that can remove tuples a standby snapshot still needs. After max_standby_streaming_delay, PostgreSQL cancels the query and continues replay. Raising the delay preserves the query and grows lag. hot_standby_feedback sends the standby's xmin so the primary's vacuum keeps those tuples. The primary bloats. Long reports belong on a replica that is allowed to lag, not on the sync standby payments wait for.

Q9. When would you choose logical replication instead of another physical replica?

When the destination needs a subset of tables, its own indexes, or a major-version bridge, and it is not the failover target. A warehouse of orders with analytical indexes is a subscriber of a publication. You apply schema changes yourself. Unique conflicts pause apply. Failover of checkout stays on a physical standby that has the whole cluster and can pg_promote(). Using the logical subscriber as the new primary loses every object you did not publish and every privilege you did not recreate.

Q10. How do you know a failover is finished?

The old primary cannot accept connections. Exactly one node returns pg_is_in_recovery() = false. The writer URL in Spring points at that node. Surviving standbys show streaming in pg_stat_replication on the new primary, with the slots you still need. The fenced node has been rebuilt with pg_rewind or pg_basebackup and is a standby, not a second writer. A scratch drill has committed a payment and read it through the LSN rule. Until those are true, the failover is a promotion plus a hope.

Q11. What is the difference between write lag, flush lag, and replay lag?

Write lag is delay before the standby's operating system has the WAL. Flush lag is delay before the standby disk has it, which bounds loss if you promote that node. Replay lag is delay before queries on the standby can see the records, which is what read-your-writes feels. A node can be flushed and still replay-behind because of a conflicting query. Payment safety looks at flush or apply, depending on synchronous_commit. Home-page correctness looks at replay.

Hands-On Exercises

Exercise 1: Read scaling with read-your-writes

Problem. History must leave the primary, and a learner who just checked out must see the new order on the next request.

Tasks.

1. Specify which transactions use local, on, or remote_apply, and name the standby in synchronous_standby_names.
2. Show the SQL that captures a token and the SQL that decides the replica is safe to read.
3. Describe Spring routing with AbstractRoutingDataSource and the case that still uses the primary.
4. Define lag thresholds that page and that remove a replica from the pool.
5. State what happens to the token across a failover.

Deliverables. A short ADR, the routing rule, the SQL, and a test list that includes a stale replica and SQLSTATE 25006.

Exercise 2: Promotion without split brain

Problem. The primary host is gone. pay_standby was the synchronous standby. An async history replica is further behind.

Tasks.

1. Choose which node to promote and why the other is unsafe for acknowledged payments.
2. Write the fence step before pg_promote().
3. List how the old primary returns as a replica.
4. Explain one divergent write you could see if you skipped the fence, and why replication will not merge it.

Rubric.

Lesson Summary

Physical streaming replication copies WAL from one PostgreSQL primary to hot standbys. Asynchronous standbys add read capacity and can lag. A commit the client has been told about is on a standby only if that transaction waited, which means synchronous_standby_names plus a synchronous_commit value of remote_write, on, or remote_apply. Read-your-writes for everyone else is an application token: pg_current_wal_lsn() after commit, compared as pg_lsn to pg_last_wal_replay_lsn() before a replica read. Promotion is safe only after the old primary is fenced. Slots must be capped. Long queries need a replica that is allowed to conflict. Logical replication feeds other systems. It does not replace the physical standby you intend to promote.

Database Sharding and Partitioning splits this primary when one writer is no longer enough. Replication still applies inside each shard. CAP Theorem and Consistency Models names the guarantee this lesson implemented as read-your-writes, and the stronger guarantee a payment commit was buying from remote_apply.

Further Reading

• PostgreSQL documentation: Write-Ahead Log, Streaming replication protocol, Replication configuration, Hot standby, and High availability.
• TechLearningPro: Streaming Replication, Failover, Logical Replication, Replication Issues, High Availability.
• Spring Framework: org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource and transaction synchronization in the data access chapter.
• Patroni documentation for lease-based fencing and automated promotion. Treat it as a controller of the steps in this lesson, not as a different consistency model.
• Next lessons: Database Sharding and Partitioning, CAP Theorem and Consistency Models.