00 Example · Migration plan
Migration plan
The implementation plan of DD-0024, the fictional Lanefold upgrade of Bookings DB to PostgreSQL 17 over logical replication, with dependents, questions, a write-freeze rollout and a three-case rollback.
No credit card required
01 Markdown
Copy the markdown
# DD-0024 Upgrade Bookings DB to PostgreSQL 17: implementation plan
Fictional example from Lanefold, a made-up freight company. This is the Implementation plan variation of DD-0024, the same migration the platform team use case follows. Paste it into the plan, or have an agent import it with import_plan_file over MCP. Names in double brackets become catalog mentions when written over MCP.
## Approach
Upgrade [[Bookings DB]] from PostgreSQL 16 to 17 over logical replication: a new 17 instance subscribes to a publication on the 16 primary, catches up, and takes over after a short write freeze. Streaming replication cannot cross major versions; logical replication can, with limits this plan accounts for. A reverse subscription from 17 back to 16 keeps the old primary current for 24 hours after cutover so the team can go back without losing writes.
Every service that reads or writes the database is listed under planned changes so its owner confirms compatibility before the cutover.
## Planned changes
- [[Bookings DB]]: major version upgrade over logical replication. Owner: Ana Duarte.
- [[Booking Service]]: new connection string and pooler restart. Owner: Lena Park.
- [[Dispatch Service]]: pause tendering during the write freeze. Owner: Tomas Reyes.
- [[Refunds Service]]: new connection string. Owner: Ana Duarte.
## Open questions
- Q1: Does Booking Service's connection pooler reconnect after the primary changes? Answered by Lena Park: yes, PgBouncer reconnects on the next query, confirmed on staging with a forced failover.
- Q2: Dispatch Service holds a transaction while a tender is out. Does tendering pause during the write freeze, or do open tenders fail? Owner: Tomas Reyes. Options: pause tendering for the freeze (preferred), or let open tenders fail and re-offer. Open.
## Logical replication limits this plan handles
- Sequences are not replicated. After the data catches up and before the cutover, set every sequence on 17 past the highest value on 16.
- DDL is not replicated. No schema migrations run on Bookings DB from the day the subscription starts until both subscriptions are dropped.
- Every replicated table needs a primary key or a replica identity. The inventory step confirms this for every table in the publication.
- Large objects are not replicated. The inventory step confirms Bookings DB has none.
## Tasks
- [ ] Inventory tables without a primary key, large objects and pending schema migrations; freeze the migration queue.
- [ ] Create the PostgreSQL 17 instance with the same parameter group, adjusted for 17 defaults, and subscribe it to a publication on the 16 primary.
- [ ] Let the subscription catch up. Compare row counts per table and set the sequences.
- [ ] Run the Booking Service, Dispatch Service and Refunds Service integration suites against 17 on staging.
- [ ] Rehearse the cutover and the rollback on staging, and record how long each step took.
- [ ] Resolve Q2 with Tomas Reyes and implement the chosen Dispatch Service behavior.
- [ ] Announce the production cutover window to dispatch and billing.
- [ ] Production cutover, as below.
- [ ] Attach a standby to 17. After 24 hours of production traffic on 17, drop both subscriptions and decommission 16.
- [ ] Record each change on this design doc as it lands.
## Rollout
Only one database accepts application writes at any moment, and replication runs in one direction at a time. The cutover enforces both, in order:
1. Prepare 17 and establish logical replication from 16. Validate schema compatibility, the replicated tables and the rollback procedure in staging before touching production.
2. Freeze application writes on 16 by setting the database to read-only for application roles, and drain outstanding transactions. Dispatch Service pauses tendering for the freeze (pending Q2). If the agreed freeze window cannot be met, abort the cutover (first rollback case below).
3. Confirm every committed change has reached 17: replication lag at zero and row counts matching. Synchronize the sequences on 17, then stop forward replication.
4. While writes remain paused, establish reverse replication from 17 to 16 without copying existing rows again, and confirm it is ready. Forward replication must already be stopped, so no change can loop between the two.
5. Repoint Booking Service, Dispatch Service and Refunds Service to 17 by changing each service's connection secret and restarting it. Confirm that 16 shows no application connections, then resume writes on 17. 16 stays read-only for application roles and receives 17's writes only through reverse replication.
6. Monitor reverse replication through the 24-hour rollback window. Retire it only after validation and acceptance.
## Rollback
There are three different situations, and the plan treats them differently.
- Before 17 accepts application writes (steps 1 to 4 above): keep 16 as the source of truth. Lift its read-only setting, keep the services pointed at it and drop the forward subscription. Nothing is lost.
- During the rollback window, after writes have landed on 17: freeze writes on 17 for application roles, drain outstanding transactions and confirm every remaining change has reached 16. Synchronize the sequences on 16, stop reverse replication, then repoint the services back to 16 and lift its read-only setting. Do not switch back if that synchronization cannot be confirmed.
- After reverse replication is retired: use the tested recovery procedure on 17. Returning to 16 at that point is a separately validated migration, not a rollback, which is why reverse replication stays up for the whole window.
At no point are both databases writable for application roles, and at no point do both replication directions run. The read-only setting moves from one database to the other only after lag has reached zero and the old primary's application connections are confirmed at zero.
## Verification
- Replication lag stayed at zero for the full freeze before repointing.
- All three integration suites pass against 17 before the production cutover.
- Post-cutover smoke checks: create a booking, tender a load, process one refund.
- Each planned change above has a recorded change on the design doc by the owner who made it. A planned change with no record is a question to ask, not proof the step was skipped.
This plan records what the team intends to do and what it recorded doing. It does not run the migration, and a recorded change is the owner's report, not proof that the cutover was correct.
This example is fictional. Lanefold is a made-up freight company, and the services and people in it don't exist. Use the shape, not the content.
Put it in ArchDocs
- Create a design doc for the migration, or open the one you already have, and write the Spec first: what must be true after the upgrade. The platform team use case follows this same DD-0024.
- Open Implementation plan and paste this plan. Mention each dependent service with the @ picker so its object page lists the migration.
- Add each compatibility question in Open questions and assign it to the service owner.
- Choose the database and each dependent under Planned changes. As the owners finish their part, each one selects Record a change, so plan versus actual shows which planned changes have a record and which are still a question to ask.
Over MCP, an agent can import this file as the plan with import_plan_file, raise the questions with add_design_doc_question and record its own changes with record_architecture_change. The plan records intent and reported work; it does not run the migration or monitor the database. The evidence guide explains how recorded changes compare with the plan.
02 Start
Write the real one
No credit card required