Cutover runbook, model answer

The runbook below is presented section by section. Each block is the model text; the prose after it is why the line reads that way, and what happens to a cutover that omits it. Every command is one module 04 actually executes, against the real files in workshops/postgres_migration/sql/.

Your runbook is not wrong for differing. It is wrong for containing a step whose success you cannot observe, or for omitting one whose failure you only discover at the next step.

Header

## Cutover runbook: RDS Postgres to ClickHouse Managed Postgres

Owner:                              <you>
Date:                               <the day you run it>
Expected write downtime:            60 seconds
Maximum acceptable write downtime:  5 minutes

Why a number and not "however long it takes". The point of writing the window down in advance is that you can add the steps up: quiesce is one SIGTERM and a drain, the lag check converges in seconds once writes stop, sql/04_sequences.sql is four setval calls over four max() scans, and restarting the writer is a process start. Nothing in that list is minutes long, so a runbook that predicts "a few minutes" has not been added up. PROVISIONAL: 38 seconds of measured write downtime is what module 04 expects; predicting 60 gives you room for one thing going slowly without tripping your own abort.

Why a maximum as well as an expectation. The maximum is the number that turns "this is taking a while" into a decision. Without it, the decision is made by whoever is most tired.

0. Preconditions, verified before the window opens

### 0. Preconditions (verified before the window opens)
- SHOW wal_level on the source returns `logical`, BackupRetentionPeriod >= 1, parameter group `in-sync`
- Target has all four tables, same definitions and indexes, restored from a schema-only pg_dump
- shop_writer and shop_analytics exist on the target with the SAME passwords as the source
- Grants re-applied on the target after the restore (pg_dump --no-privileges dropped them)
- pg_stat_replication.state on the source reads `streaming`, not `catchup`
- Lag has stopped falling: pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) is stable
- ClickPipes egress addresses are in subscriber_cidrs and the slot reads active = t

The three RDS prerequisites are one precondition, not three. rds.logical_replication plus the reboot, backup_retention_period >= 1, and the rds_replication grant. Only the first fails loudly. The other two produce a publication that works, a pipe that reports no error, and rows that never arrive — so they belong in a precondition list, checked before the window, rather than in a step whose failure you would attribute to something else.

Same passwords on both instances is a precondition, not a detail. It is what makes the connection host the only thing that changes at cutover. A runbook that recreates the roles with fresh passwords still works, and it turns a one-variable edit into a configuration migration — which is the claim the whole exercise is built on.

streaming, not catchup, and lag flat. Every megabyte of lag you have not drained before the window is window you will spend draining it. This is the precondition that most directly buys the number in the header.

1. Replication scope

### 1. Replication scope
Tables replicated:                  customers, products, orders, order_items
Tables deliberately NOT replicated: none in this schema; anything added later is out of scope
                                    until the pipe's table selection is changed on purpose
Scope set at:                       the migration pipe's table selection, which creates the
                                      publication on the source on my behalf
Verified by:                        pg_publication.puballtables must read `f`
                                    pg_publication_tables lists exactly four rows

Why the enumerated list rather than everything. Both work on RDS and both do an initial load, so the difference is not capability — it is that an explicit list records a scope decision and holds it. A table added to the schema next week is not replicated until somebody changes the pipe on purpose, which is a reviewable change rather than a silent enrolment.

Why the verification is on the source and not in the console. The pipe manages the publication for you, which relocates the decision without removing it: puballtables and pg_publication_tables still tell you exactly what scope is in force, in the source's own catalog, and they do it independently of the UI you set it from. Verifying against the thing that will actually be replicated from — rather than against the form you filled in — is the habit worth keeping. If puballtables reads t, the selection was wider than you intended and the scope decision was bypassed without an error.

On the foreign keys. orders references customers, and order_items references both orders and products. Logical decoding applies changes per table and does not order them to satisfy constraints across tables, which is fine here because all four tables are in one pipe and the target's constraints are satisfied by the time the initial load completes. It would not be fine if you replicated order_items and left orders out — the correct conclusion is that a referential closure is a scope constraint, not just a preference.

2. The signal you wait on

### 2. The signal I wait for before quiescing writes
Signal:    the consumer has durably flushed everything the source has written, read as the
           byte distance between pg_current_wal_lsn() and the slot's confirmed_flush_lsn
Query that reads it (on the SOURCE):
           SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
           FROM pg_replication_slots;
Threshold to proceed BEFORE quiescing:  state = `streaming` and the figure flat under a few MB
Threshold to proceed AFTER quiescing:   exactly 0
Maximum time to wait before I abort:    15 minutes with the figure not falling

Two thresholds, because zero is not reachable while writes are running. At 200 orders a second the source is always a little ahead of the consumer, so a runbook that waits for zero before quiescing waits forever. The order is therefore: quiesce first, then wait for zero. That sequencing is the answer to the question module 02 asks about the order of "quiesce" and "wait", and it is the most common structural error in a first draft.

Why confirmed_flush_lsn and not a row count. A row count matching proves the right number of rows arrived at the moment you counted. It says nothing about the replication stream, and on a database taking writes it can match while the stream is behind. confirmed_flush_lsn is the position the consumer has confirmed it flushed — the only one of the available signals that means durably received everything the source wrote.

Why not the pipe's own lag metric. It is the right cross-check and the wrong gate, for one reason: it is reported by the component whose health you are trying to establish. A pipe that has stopped consuming can report a stale figure; the slot on the source cannot, because the source computes the difference itself. Read the console to see what the pipe thinks, then gate on the slot. This also makes the gate portable — confirmed_flush_lsn is a property of the slot, so it reads identically whether the consumer is a managed pipe or a hand-made subscription.

Why not write_lag, flush_lag or replay_lag. They are useful and they are time intervals per worker, which makes them good for watching a trend and poor as a gate: you cannot compare them against a byte threshold and they are null when nothing is in flight.

Why an abort timeout at all. Without one, "lag is not converging" is a feeling. With one it is a condition, decided in advance, by somebody who was not under pressure.

3. The window

### 3. The window
| # | Step | Command or action | How I verify it worked | Reversible? |
|---|---|---|---|---|
| 1 | Quiesce writes | kill "$(cat /tmp/writer.pid)" | final JSON line prints, committed noted | yes, restart it |
| 2 | Confirm level | pg_wal_lsn_diff(...) on the source | returns exactly 0 | n/a, read-only |
| 3 | Fix sequences | psql "$TARGET_DSN" -f sql/04_sequences.sql | last_value = max(id) per table | yes, setval again |
| 4 | Repoint the writer | restart writer.py against $TARGET_HOST | first JSON line, committed > 0, failed = 0 | yes, until step 4 commits |
| 5 | Repoint the dashboard | SHOP_DB_HOST=$TARGET_HOST:5432, recreate Grafana | all eight panels render | yes, revert the variable |
| 6 | Reconcile | sql/05_reconcile.sql on both, diff | customers and products identical | n/a, read-only |

The window opens at step 1 and closes at step 4. Steps 5 and 6 happen with writes already flowing to the target, which is why the dashboard repoint is not inside the window: reads never stop, and Grafana serving stale-by-a-minute data from RDS for another two minutes costs nothing.

Step 1 — quiesce writes

kill "$(cat /tmp/writer.pid)" && sleep 3 && tail -1 /tmp/writer.log

SIGTERM makes the writer finish the transactions it is inside and print a final summary, so no order is torn mid-transaction. Note the committed total: it is the number you reconcile against, and it is only available here. kill -9 would give you a shorter window and an unknown number of in-flight transactions, which is a trade nobody should make to save two seconds.

Step 2 — confirm the source and target are level

psql -h "$RDS_HOST" -At -c "SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) FROM pg_replication_slots;"

With writes quiesced this converges to 0 within seconds. This is the moment exactly-zero is reachable, and it is the gate: everything the source ever wrote is now on the target, durably.

Step 3 — fix the sequences

psql "$TARGET_DSN" -f sql/04_sequences.sql

This is the step nobody improvises. Replication moves table data. Replicated rows are applied with their literal key values and never call nextval(), so every sequence on the target has never issued a number — pg_sequences.last_value reads NULL, not 1 — while orders holds 12 million rows with order_id up to 12,000,000. An untouched sequence does not resume near its table's data: the first nextval() returns 1. All four primary keys in sql/01_schema.sql are bigserial, so all four have the same problem.

The file calls setval(sequence, value) — the two-argument form, which marks the value as already used, so the next nextval() returns max + 1 — with COALESCE(max(id), 1) to cover an empty table. Verify rather than trust it:

psql "$TARGET_DSN" -c "SELECT sequencename, last_value FROM pg_sequences WHERE schemaname = 'public' ORDER BY sequencename;"
psql "$TARGET_DSN" -c "SELECT (SELECT max(order_id) FROM orders) AS max_order_id,
  (SELECT last_value FROM orders_order_id_seq) AS seq_last_value;"

The two numbers must be equal. Skip this step and the target reconciles perfectly on row counts and checksums, reports no lag, passes every check in a data-focused runbook — and the very first order the application places fails with duplicate key value violates unique constraint "orders_pkey", then keeps failing for every id up to the maximum. That is millions of rejected writes on a database that looked healthy sixty seconds earlier.

Why it is inside the window and not before it. The sequence has to be set from the final max(id), which only exists once writes have stopped and replication has drained. Run it earlier and the rows that arrive afterwards push max(id) past last_value again.

Why it is reversible. setval can be run again with a higher value, and the failure mode is a gap in the id sequence rather than data loss. That is a rare property in a cutover window and it is worth knowing: it means step 3 is the cheapest step to get wrong.

Step 4 — repoint the writer

nohup python3 writer.py \
  --dsn "postgresql://shop_writer:${WRITER_PASSWORD}@${TARGET_HOST}:5432/shop?sslmode=require" \
  --rate 200 --concurrency 16 --report-seconds 10 \
  > /tmp/writer.log 2>&1 &
echo $! > /tmp/writer.pid

The role name and the password do not change; the host does. That is the whole edit, and it is why this is a repoint rather than a migration. Note the wall-clock time from the kill in step 1 to the first JSON line with a non-zero committed — run date at both points, because that interval is the number the exercise exists to produce and it cannot be recovered afterwards.

The window is closed once that line appears. Verify failed is 0 on the next line as well: a writer that connects and cannot insert is a grant problem that looks like a success.

Step 5 — repoint the dashboard

export SHOP_DB_HOST="${TARGET_HOST}:5432"
docker compose up -d --force-recreate

One environment variable and one recreate. SHOP_DB_HOST is host:port, not a bare host — the datasource passes it straight through as the connection URL. Not one panel's SQL changes, which you can check mechanically rather than by eye:

./scripts/preflight.sh && echo "panel SQL matches bench/queries"

Step 6 — reconcile

psql -h "$RDS_HOST" -At -F'|' -f sql/05_reconcile.sql > /tmp/source.txt
psql "$TARGET_DSN"  -At -F'|' -f sql/05_reconcile.sql > /tmp/target.txt
diff /tmp/source.txt /tmp/target.txt

Inside the window, with writes stopped, the diff is empty. Run it after resuming writes and the two order tables legitimately differ, so the pass condition is the narrower one module 04 checks: customers and products identical, the order tables greater-or-equal on the target.

See the next section for what that comparison does and does not establish.

4. Verification after writes resume

### 4. Verification, after writes resume
Queries run on BOTH instances:  sql/05_reconcile.sql -- count(*) plus sum(hashtext(...)) per table
What equality proves:           the four tables agree on the hashed expression and the row count,
                                at the instant each side was read
What it does not prove:         unhashed columns, sequence state, index health, or anything after
                                the query returned
What I check that is not a row count:
                                every sequence's last_value against its table's max(id);
                                pg_replication_slots on the source returns no rows after the drop;
                                all eight panels render against the new host with no SQL edited
Expected differences:           orders and order_items differ, because the writer is now committing
                                to the target only; customers and products must match exactly

Why a checksum and not just a count. A count proves the right number of rows arrived. The sum(hashtext(...)) column proves the right rows arrived: summation is order-independent, so it compares across two servers whose physical row order differs, and it costs one sequential scan per table, which is what makes it affordable inside a window.

What that specific file would miss, since module 02 asks. It hashes one expression per table, not the whole row: email for customers, sku for products, order_id || status for orders, order_item_id || line_total for order_items. A corruption confined to customers.region, orders.placed_at, order_items.quantity or order_items.product_id passes cleanly. That is a deliberate trade — a whole-row hash costs more and this runs against a live instance under time pressure — and the right way to hold it is to know which columns you did not check rather than to believe the word "reconciled".

Why the two order tables are expected to differ. After step 4 the writer commits to the target only, and RDS will never see those rows. A clean diff on customers and products plus target counts greater than or equal to the source's on the two order tables is the correct result. If you want an exact comparison on all four, capture both outputs inside the window, between step 2 and step 4 — a worthwhile amendment to make in module 04 Step 2.

Verify the thing you changed, not only the data. Step 3 changed sequences, so the sequence check belongs here. A verification section that only reads rows will confirm every table and miss the one object the window actually mutated.

5. Abort criteria

### 5. Abort criteria
I abort if:
- lag has not fallen for 15 minutes, or is growing
- the pipe is erroring, or the slot reads active = f and does not recover
- reconciliation mismatches on customers or products before the window opens
- any error class I have not seen before, during the window, that I cannot explain in two minutes

Abort procedure:
1. Do NOT repoint anything. If the writer is stopped, restart it against RDS.
2. Delete the migration pipe -- with the source still reachable, so the remote slot goes with it.
3. On the source: SELECT slot_name, active FROM pg_replication_slots -- must return no rows.
   If it returns a slot, SELECT pg_drop_replication_slot('<name>').
4. If I set default_transaction_read_only on the source, turn it back off before restarting
   the writer, or every write is refused and it looks like a permissions failure.

State the system is left in after an abort:
- RDS is authoritative and taking writes, exactly as before
- the target holds a partial or complete stale copy, and no slot remains on the source

An abort happens before the application is repointed, which is what makes it cheap. Nothing has been written to the target that RDS does not have, so the abort is a cleanup rather than a recovery.

Step 3 of the abort procedure is the whole reason the section exists. An abort that stops at "pause the pipe" leaves an inactive slot on the source, and an inactive slot is a disk-fill timer: Postgres may not recycle WAL a slot has not confirmed, so pg_wal grows at the application's write rate and no amount of VACUUM or checkpoint tuning frees a byte. That converts a clean rollback into an incident starting a few hours later, on a machine nobody is looking at, presenting as a disk problem on the source. Module 03's failure injection stages exactly this so the signal is familiar before it matters.

Why delete with the source reachable. Deleting the pipe while the source is reachable removes the remote slot as part of the same operation. Against an unreachable one it does not, and the slot survives everything that knew about it, with nobody left to resume. The order of your teardown follows from this, and so does module 07's.

6. Rollback trigger

### 6. Rollback trigger
I roll back if, after resuming writes:
- the writer cannot commit against the target and the cause is not the sequences
- panel queries fail against the target for a reason I cannot fix in 10 minutes
- data written to the target is provably wrong, not merely unfamiliar

Rollback procedure (only while it is still clean -- see below):
1. Stop the writer.
2. Repoint the writer and Grafana back to $RDS_HOST.
3. Reconcile RDS against itself: nothing was written there, so it is intact.
4. Manually replay or discard the rows committed to the target during the attempt.

Point after which rollback is no longer clean, and why:
The first transaction the writer commits to the target. Replication runs one way, RDS never sees it,
so from that moment "roll back" means "reconcile two divergent databases", not "change a host".

Rollback happens after the repoint, which is what makes it expensive. The distinction from an abort is not severity, it is whether the application has been moved. Conflating the two produces a runbook whose abort procedure quietly assumes writes have gone somewhere they have not.

The point of no return is seconds after step 4, and that is the honest answer. It is tempting to write "one hour" or "until the next backup". Neither is true here: the replication leg is one-way, so the moment an order lands on the target, RDS is missing data and a reverse cutover is a merge.

What replaces rollback after that point. Forward fix. That is why steps 3 and 6 of the window verify things rather than trusting them, and why the sequence check is inside the window: after the point of no return, the cheap options are gone and all you have left is diagnosing the target.

What would have to be true for a reverse cutover to be as simple as the forward one. A publication and subscription in the opposite direction, created before cutover and kept caught up — that is, bidirectional or reversed logical replication set up in advance, plus a plan for the sequences on the way back. It is not true here, and pretending it might be is how a rollback plan becomes a paragraph nobody could execute.

Where your runbook may legitimately differ

• A lag threshold of a few kilobytes instead of exactly zero after quiescing. Defensible if you say why and how long you wait for it.
• FOR ALL TABLES, if you wrote down that you accept future tables being enrolled without review. It is a decision either way; only an unexamined default is wrong.
• Reconciling inside the window rather than after it, which is strictly better for exactness and costs you window. Say which you chose and what it bought.
• A longer expected downtime because you added a step — a VACUUM ANALYZE on the target, say, or a manual smoke query. Adding a step and adding its seconds to the header is the exercise working.

What is not a legitimate difference: a window with no verification column, an abort procedure that leaves a slot on the source, or a rollback section with no point of no return. Those are the three things this page exists to check.