Every store now writes to SQLite and Postgres, and SQLite is still the one that gets read. The next step moves the reads. I've written that step as a script and run it both ways on the sandbox, including the way back with something deliberately broken.

The counters nobody moves

Many tables number their rows themselves: the database hands out the next id when a row is added. While SQLite is in charge, SQLite hands out the ids and Postgres is told which id to store. A Postgres identity column keeps its own counter, and an insert that brings its own id never moves it.

So the counters sit where they were on the day the data was copied. I checked production: 19 of 25 would hand out an id that already exists. The first new row after the switch would fail.

The fix is one statement per table: set the counter past the highest id in use. When it runs is the harder part. Until the switch, SQLite keeps generating ids, so a counter set an hour early is already stale. It has to happen inside the switch, with nothing writing.

A window of seconds

  1. Compare the stores being moved. This needs no downtime, and anything less than a clean result stops the script.
  2. Stop the scheduled jobs and let any that are running finish.
  3. Stop the services, the one that receives incoming work last.
  4. Check that no write to the copy has failed since the compare.
  5. Set every counter past its highest id, holding each store's write lock.
  6. Switch the stores' setting from "write to both, read SQLite" to "write to both, read Postgres", keeping a copy of the old setting.
  7. Start the receiving service first, then everything else, then the same scheduled jobs.

If anything fails inside the window, the services come straight back up with the old setting. On the sandbox the whole run took seven seconds, the compare included.

Living on Postgres

With Postgres in charge, I sent a signed test message through the front door and ran it through the worker's queue code, with a stand-in for the real handler so nothing left the sandbox. A record was registered with its id handed out by Postgres, and SQLite, now the copy, received the same id. Search found the new record. Every read-only check behind the team's assistant ran against Postgres without an error, and a compare matched, now with Postgres as the reference.

A rollback that copies nothing proves nothing

The way back is the same window, with one difference: before the setting goes back, SQLite is brought up to date from Postgres, because Postgres was the truth while it was in charge. On the sandbox it took five seconds and copied nothing, because the copy had stayed in step.

That shows the rollback runs. It doesn't show it repairs anything. So I switched one store to Postgres again, deleted a row from SQLite behind the system's back, as if a copy-write had been lost, and rolled back. The rollback put the row back. Then I added a record with SQLite in charge again, and its new id did not collide with the one Postgres had handed out.

What I took from it

  • If ids come from one database and are written into another, the second one's counters don't follow. Check them before you depend on them.
  • Some fixes only work at one moment. Put them inside the switch, not in a checklist the day before.
  • Rehearse the rollback with a fault in it. A clean way back that copies nothing tells you very little.

The production switch waits for the soak to finish: a full week of clean nightly compares. The progress post has where that stands.