02 Plan the cutover

This module provisions nothing and runs nothing. It is where you write the document that module 03 executes.

Outcome

A cutover runbook, in a file, in your own words, that another person could execute without you in the room. Six decisions, each with a stated reason and a stated failure mode. Module 04 hands you the mechanics; this module is where you decide what to do with them.

Why a runbook rather than a checklist you improvise

A cutover is a small number of steps executed under time pressure, in an order that matters, with one irreversible-feeling moment in the middle. Everything about that situation degrades improvisation. The specific failure this module exists to prevent is not a step done wrong; it is a step nobody thought of, discovered at the moment the application is already pointed at the new database and the old one has stopped being authoritative.

There is one such step in this workshop. You will not be told which one until you have written your runbook. If you find it here, on paper, with the seed still running, you will have learned the thing the workshop is for.

Two properties are worth designing in from the start:

• Every step is either verifiable or reversible. Preferably both. A step whose success you cannot observe is a step you will assume worked.
• The abort path is written before the happy path is executed. Deciding to stop is a decision made badly under pressure and well in advance.

Step 1 — Create the file

cd "$(git rev-parse --show-toplevel)/workshops/postgres_migration"
mkdir -p runbook
${EDITOR:-nano} runbook/cutover.md

Keep it in your own checkout. It is yours; nobody grades it, and module 04 asks you to execute it as written and then amend it with what you learned.

Step 2 — Fill in the skeleton

Copy this into the file and replace every TODO. The prompts under each section are the considerations, not the answers.

## Cutover runbook: RDS Postgres to ClickHouse Managed Postgres

Owner:
Date:
Expected write downtime:            TODO
Maximum acceptable write downtime:  TODO

### 0. Preconditions (verified before the window opens)
- TODO
- TODO

### 1. Replication scope
Tables replicated:                  TODO
Tables deliberately NOT replicated: TODO
Publication definition:             TODO

### 2. The signal I wait for before quiescing writes
Signal:                             TODO
Query that reads it:                TODO
Threshold to proceed:               TODO
Maximum time to wait before I abort: TODO

### 3. The window
| # | Step | Command or action | How I verify it worked | Reversible? |
|---|---|---|---|---|
| 1 | TODO | TODO | TODO | TODO |
| 2 | TODO | TODO | TODO | TODO |
| 3 | TODO | TODO | TODO | TODO |
| 4 | TODO | TODO | TODO | TODO |
| 5 | TODO | TODO | TODO | TODO |
| 6 | TODO | TODO | TODO | TODO |

### 4. Verification, after writes resume
Queries run on BOTH instances:      TODO
What equality proves, and what it does not: TODO
What I check that is not a row count: TODO

### 5. Abort criteria
I abort if:                         TODO
Abort procedure:                    TODO
State the system is left in after an abort: TODO

### 6. Rollback trigger
I roll back if, after resuming writes: TODO
Rollback procedure:                  TODO
Point after which rollback is no longer clean, and why: TODO

Step 3 — Work the six decisions

1. Replication scope

You are choosing which tables the migration carries. In module 03 that is the pipe's table selection, and you will pick the four shop tables explicitly rather than accepting the whole database. Decide whether you agree, and write down why.

Consider: what happens to a table that exists on the source and is not selected — does it error, or arrive empty, or arrive stale? What does accepting everything enrol on a source that gains a table next week, and is that a feature or an unreviewed change? Which of your four tables has a foreign key to another, and does that constrain the order rows can be applied in? Is there anything in this schema you would deliberately not migrate — an audit log, a cache table, a staging table someone left behind?

Note that the pipe creates a Postgres publication underneath to express your choice, so the answer is auditable on the source whichever surface you set it from. Catalog views that answer questions here: pg_publication, pg_publication_tables.

2. The signal you wait on

Before you quiesce writes you need to know replication has caught up. Before you can know that, you need to pick a number that means "caught up".

Consider: what is the difference between a row count matching and the replication stream having caught up, and why can the first be true while the second is false on a database taking writes? Postgres exposes several positions — a slot's restart_lsn and confirmed_flush_lsn, the source's pg_current_wal_lsn(), and per-worker write_lag, flush_lag and replay_lag. Which of those tells you the consumer has durably received everything the source has written, as opposed to something weaker? What unit is the difference between two LSNs, and what function turns it into a number you can compare against a threshold?

Note which side of the leg each of those lives on, because it decides what you can rely on. The slot positions are on the source, so they read the same whether the consumer is a hand-made subscription or a managed pipe — which is what makes a slot-based gate portable between the two. A pipe's own lag metric is a fine cross-check and a poor gate: it is reported by the thing whose health you are trying to establish.

Then: is your threshold zero, or a small number of bytes? On a database taking 200 writes a second, is exactly zero a state that ever occurs while writes are running — and what does that imply about the order of "quiesce" and "wait for lag" in your window?

Catalog views: pg_replication_slots and pg_stat_replication, both on the source. Functions: pg_current_wal_lsn(), pg_wal_lsn_diff().

3. The window itself

Six numbered steps, roughly. You need to quiesce writes, confirm catch-up, do something to the target that replication did not do for you, repoint the application, resume writes, and verify.

Consider, for the third of those: logical replication replicates table data. Enumerate, from the schema in sql/01_schema.sql, every database object that holds state and is not table data. For each one, ask what its value is on the target after replication has copied every row, and what the first write after cutover does if that value is wrong. Look hard at the column type of every primary key in that file. There are four of them, and they all have the same problem.

Then: how long does each step take? Which one dominates? If your answer to "expected write downtime" is "however long the window takes", the runbook is not finished — the point of writing it is that you can add the steps up in advance.

4. Verification

Consider: a row count matching on both sides proves the right number of rows arrived. What proves the right rows arrived? You need something computable on two servers whose physical row order differs, cheap enough to run inside a cutover window, and comparable by eye or by diff. Look at sql/05_reconcile.sql for one such shape, and decide whether it convinces you — it hashes one or two columns per table, not all of them, which is a deliberate trade. What class of corruption would it miss?

Then: what do you verify that is not about rows at all? You just changed something on the target in step 3. Verify it.

5. Abort criteria

An abort happens before the application is repointed, so it is cheap. Write down the conditions that trigger one and be specific enough to act on: lag not converging within N minutes, a reconciliation mismatch, an error class you have not seen before.

Consider: after an abort, what state is the system in? Is the subscription still running? Is the replication slot still holding WAL on the source? That last question is not rhetorical — module 03 will show you what an abandoned slot does to a disk, and an abort procedure that leaves one behind has converted a clean rollback into an incident starting in a few hours.

6. Rollback trigger

Rollback happens after the application is repointed, so it is expensive. Distinguish the two.

Consider: for how long after cutover is rolling back to RDS clean, and what makes it stop being clean? Writes have been landing on the target; the replication leg runs one way; RDS has not seen those rows. What would have to be true for a reverse cutover to be as simple as the forward one, and is it true here? Name the point of no return explicitly, along with what you would do instead after it — because "roll back" stops being an option and something else has to take its place.

Step 4 — Rehearse it on paper

Read your runbook top to bottom as if you were the person executing it at 11pm and had not written it. Three checks:

1. Does every step name a command or an action concrete enough to execute without deciding anything?
2. Does every step name how you know it worked? A step with an empty verification column is a step you will assume worked.
3. Is there any step whose failure you would only discover at the next step? Those are the ones worth reordering.

Then check your runbook against your seed. If /tmp/seed.log now ends with ANALYZE, go back to module 01's Step 5 and take the baseline before starting module 03.

Done when

• runbook/cutover.md exists with no TODO left in it;
• your window has a numbered step list, each row with a verification and a reversibility answer;
• you have written an expected write downtime as a number, and a maximum you would tolerate;
• your abort criteria say what state an abort leaves the source's replication slot in; and
• your rollback section names an explicit point of no return.

The model runbook, with the reasoning behind each line, is in the reference track. Read it after module 04, not before — comparing your executed runbook against it is the exercise; comparing your blank one against it is not.

Next: module 03 builds the replication leg, breaks it on purpose so you see what an orphaned slot costs, and then has you execute the runbook you just wrote.