staffPostgreSQL Major UpgradePro answerTested in a controlled lab, summarised

You must cut a write workload from PostgreSQL 14 to 18 with logical replication. Where is the rollback boundary, and how do you prove the subscriber caught the source stop LSN?

Every part of this migration is reversible until one moment, and the question is whether you can name it precisely. Before the new cluster accepts a write, the old one is still the authoritative copy and aborting costs nothing but time. After the first commit lands on the new cluster, the two histories have diverged, and pointing the application back is data loss rather than a rollback. Interviewers also probe the proof, because lag looking small is the reassurance people accept instead of evidence. There is a third area that catches teams out entirely, which is the state logical replication does not carry across. This page covers the boundary, the proof, and that gap.

What the interviewer is scoring

Weak answer: flips the endpoint when lag looks small, or says you can always fail back to the old primary after the new one took writes.

In short: The source remains authoritative until the target accepts writes. After the target writes, a simple redirect back is unsafe; you need reverse replication or freeze/reconcile plus a position you actually captured.

ProFrom a real interview loop

The spoken answer and the reasoning behind it

The question, the intro, what the interviewer is scoring, the reasoning outline, what you would verify and the follow-up probes are all open above. Pro unlocks the spoken answer to “You must cut a write workload from PostgreSQL 14 to 18 with logical replication. Where is the rollback boundary, and how do you prove the subscriber caught the source stop LSN?”, the reasoning behind it, and the verification that backs it.
  • The full 90-second answer, written first person, the way you would actually say it
  • How I reason through it: the mechanism, the decision points, and where the claim stops
  • Verification steps with a written summary of what a controlled lab run showed, labelled as a summary rather than a transcript.
  • Worked responses to the 3 follow-up probes listed above, plus the traps that lose the point

Card required. Cancel before day 7 and you are not charged.

Compare plans

How to reason through it

  • Keep the source read-write until cutover; the target stays a subscriber until it is deliberately opened to writes.
  • Capture the source stop position at freeze; do not reconstruct it afterwards.
  • Prove subscriber and slot confirmation reached that exact position before allowing target writes.
  • Reconcile the state logical replication does not carry: sequence values, schema changes made during the window, and large objects.
  • After target writes, a simple redirect to source is unsafe without reverse replication or a freeze-and-reconcile plan.
  • Name the abort window: before target writes it is free, after it is a data-loss decision.
  • Verify application sentinels on the new writer, not just replication lag.

What I would verify

  • Freeze writes on the source and record the exact stop position at that moment.
  • Confirm the subscriber's applied position reached that recorded position, rather than reading a lag figure.
  • Advance and verify sequences on the target before any application write.
  • Run an application-level check on the new writer, not only a replication check.
  • State explicitly, before the first write, that the abort window is now closing.

Follow-ups they push on

  • Lag reads as zero. Is that catch-up?
  • The new cluster has taken writes and something is wrong. Can you go back?
  • What does logical replication not bring across?

The probes are open. Pro carries the spoken answer, the reasoning behind it, and the verification steps, including a worked response to each of these.

Concepts tested

logical replication cutoverstop LSNsubscriber catch-uprollback boundaryreverse replicationnon-replicated statemajor-version compatibility

Learn it, run it, then say it

Three steps, in order. Nothing here is a detour.

Questions that go with this one