03 Replicate — instructor notes

Budget 75 to 100 minutes, and expect it to be the module that decides whether the day finishes. It contains the workshop's two longest unattended waits, its one deliberate incident, and the only step whose failure costs a participant thirty minutes rather than three.

Read the stranding table below before the session. Everything else on this page is pacing; that table is triage.

Where the time goes

The initial copy is dead time and it is long. Plan what fills it before the session, because a room that waits for it in silence loses half an hour. The three things that fit are the runbook peer review from module 02 if it is not finished, the PostgresBench discussion at the end of the learner page, and — if the room is ahead — module 05's engine and ordering-key material, which needs no infrastructure.

Do not fill it with the failure injection. The injection needs the initial load finished; pausing mid-load is a different experiment, and a participant who deletes the pipe rather than pausing it at that point restarts the load from zero.

What to demo, what to let them do

• Demo the Managed Postgres console flow on your screen. It is in beta and the flow moves, so a stale screenshot in a learner page is worse than none. Show the region field, the connection details, the IP allowlist, and the networking page where the egress address lives. Then let everyone create their own.
• Demo the direction of the connection, physically. Draw it: ClickPipes dials in to RDS on 5432, from its own static addresses. Two-thirds of this module's failures are that arrow pointing the wrong way in somebody's head. A participant who believes RDS pushes, or who thinks the Managed Postgres instance is the party connecting, will not think to allowlist anything. Correct the second one specifically: it was true of the logical-replication version of this leg, so anyone who has done that migration before will arrive holding it.
• Spend three minutes on the networking diagram, and expect the security question. Somebody in any room with a security function will ask whether this is really how it is done, and the honest answer is no: a public allowlist is the shortcut, and an AWS source in production gets a reverse private endpoint over PrivateLink. Saying that before you are asked is worth more than the three minutes, because the alternative is a participant quietly concluding the whole workshop is unrealistic. The diagram in Step 2 has all three options; the one worth naming out loud is that an SSH-tunnelled pipe cannot use automated schema migration, which is why the manual pg_dump path in Step 3 exists rather than being legacy text.
• Let them create the roles themselves, and say why the pipe cannot. Roles are cluster-global, so no schema dump carries them. It is thirty seconds of work guarding the workshop's central claim, and it is the only part of the schema step the pipe leaves to a human.
• If anyone takes the manual schema path, let them. It is where pg_dump version mismatches surface, and the recovery is per machine.
• Demo one lag-watching loop on your screen and leave it running for the whole initial copy, projected. It gives the room a shared clock, and it means nobody asks whether their own numbers look normal.
• Let every participant execute their own runbook, unassisted, including its omissions. This is the module's core exercise. Your job during the window is to watch the clock and say nothing.

Running the failure injection for a room

This is the beat most likely to strand people, and it is worth running as a synchronized room-wide event rather than letting fifteen participants each break replication at a different moment.

Preconditions, checked for everyone first. The behind figure has stopped falling and the pipe reports its initial load complete. A participant still loading should wait rather than inject; pausing during the initial load teaches the wrong lesson and costs a re-load if they then reach for delete-and-recreate.

Run it as three timed phases, on your count.

1. Everyone pauses the pipe at the same moment, from its page in the console. Have them note the wall-clock time and the current confirmed_flush_lsn. Point out immediately that nothing appears to have happened: the writer keeps committing, the dashboard keeps serving, the pipe reports itself calmly paused, and no error is logged anywhere a person is looking.
2. Talk for ten to fifteen minutes while WAL accumulates. This is the ideal slot for the PostgresBench citation and its narrowing, or for the CloudWatch metrics discussion. Have participants run the polling loop from the learner page in a second terminal so the numbers climb in the background while you talk.
3. Everyone resumes together. Then watch confirmed_flush_lsn start moving, behind fall, and pg_wal shrink at the next checkpoint.

Be honest about the magnitude. At PROVISIONAL: about 1.1 GB of WAL retained per hour with the writer at 200 orders per second, fifteen minutes of downtime accumulates a small fraction of the 200 GB volume. Nobody's disk fills. What the room sees is the trend and the mechanism — confirmed_flush_lsn frozen, behind growing linearly, pg_ls_waldir() growing with it and nothing reclaiming it. Say the extrapolation out loud rather than pretending the demo is the incident: extrapolate that provisional rate against 200 GB and an unattended slot fills the volume in about a week, and a production write rate is not 200 orders a second.

Two rules to state before phase 1, both of them cheap to say and expensive to skip:

• Resume is the only repair. Nobody deletes the pipe during this exercise. Deleting it while the source is reachable is harmless in itself — the remote slot goes too — but a participant who deletes and recreates has thrown away the initial load and buys another PROVISIONAL: 25 to 40 minutes. A participant who deletes with the source unreachable orphans the slot, and now has to find and drop it by hand on the source.
• Nobody breaks for lunch with a paused pipe. Write "resume the pipe" on the board and keep it there. This is the one instruction in the workshop where the cost of forgetting compounds while nobody is in the room, and a pause is easier to forget than a disabled subscription was, because the console shows it as a state rather than a fault.

Make the misdirection explicit at the end. The symptom is on RDS: a disk filling, WAL not recycling, FreeStorageSpace falling. The cause is in another company's console, in a pipe somebody paused deliberately. Ask the room which of their own monitoring dashboards would have caught it, and whether OldestReplicationSlotLag is on any of them. In most organizations the honest answer is no.

The stranding table

Triage, in the order these actually happen. The right-hand column is what the mistake costs, and that number is why some of these are worth pre-empting rather than debugging.

That last row is worth pre-empting out loud. Tell participants to run date immediately before the kill and immediately after the first non-zero committed line, or the number they came for is unrecoverable.

Questions worth asking

• "Which party opens the TCP connection, and what does that decide about your firewall?"
• "You quiesced writes and lag went to zero in four seconds. Why was zero unreachable a minute earlier?"
• "Hands up who had the sequence step in their runbook before executing it." Then ask one of them how they found it. The answer is usually the primary key types, and hearing it from a peer lands better than hearing it from you.
• "During the injection, which machine had the symptom and which had the cause?"
• "The reconciliation diffs clean on customers and products and differs on orders. Is that a failure?" It is the cutover having happened: those rows only exist on the target now. A participant who wants an exact comparison has to capture both outputs inside the window, before resuming writes — which is a runbook amendment worth making.
• "What does sum(hashtext(...)) over one column per table not prove?" It says nothing about the columns it does not hash — region, created_at, quantity, product_id — and nothing about anything that changed after each side was read.
• "PostgresBench says Managed Postgres takes roughly 3.5 to 5 times RDS's write throughput. What sentence may you now say about your dashboard?" None. That narrowing is the point of the citation, and it is question 13 in module 07.

Common failures beyond the stranding table

• pg_publication_tables on the source lists more than four tables. The pipe's table selection was wider than the participant intended, which silently bypasses module 02's scope decision. It is worth checking for everyone rather than waiting for someone to notice: it is one query, and the console selection is easy to click past. Recreating the pipe with the right selection costs another initial load, so catch it before the load finishes.
• Roles created without the module 01 passwords. The whole "only the host changed" claim depends on the two role passwords being identical on both instances. A participant who regenerated them has a working system and a broken demonstration. .env.local from module 01 is where the originals are.
• The grants were skipped, or run too early. GRANT ... ON ALL TABLES resolves the table list when it runs, so running it before the pipe built the schema grants nothing and reports success. The writer then connects and cannot insert. It looks like a role problem and is a grant problem, and the timing makes it look intermittent between participants.
• The pipe is deleted while RDS is already gone. Not possible in this module, but participants who jump ahead to teardown hit it. See module 07's notes.
• A participant improves the runbook mid-execution. Ask them to note the change and apply it in Step 9 instead. The exercise is executing a document under pressure; editing it while executing it is the habit the module exists to break.

End checkpoint

Do not start module 05 until, for every participant:

• all four tables on Managed Postgres hold the seed's fixed counts — 500,000 / 20,000 / 12,000,000 / 48,000,000 — plus everything committed since;
• every sequence's last_value equals its table's max(id), checked rather than assumed;
• the writer commits against $TARGET_HOST with failed at zero;
• Grafana renders all eight panels against $TARGET_HOST with no panel SQL edited;
• sql/05_reconcile.sql diffs clean on customers and products;
• pg_replication_slots on RDS returns no rows; and
• runbook/cutover.md records the measured downtime and what they would change.

The slot check is the one not to skip in the interest of time. A room that moves on with slots still open on RDS is a room whose instances are filling their volumes for the rest of the afternoon, and the symptom will surface during module 06's benchmark as an unexplained regression.

Do not break for lunch here. Module 04 opens a cutover window; the natural break is after it closes, once the reconciliation and the pipe deletion are done.