Moving 30 SQLite databases to Postgres without losing a record

First estimate for the move: 125 hours.

I scrapped the approach and wrote my own. Convert with pgloader. Review the result against the SQLite files and against our Job Management spreadsheet. Fix anything with scripts, never by hand, so every fix re-runs cleanly on the day. Then run both databases side by side: every write goes to SQLite and to Postgres, compared nightly, until a full week of jobs has matched. Only then switch reads. Only then stop writing SQLite. Nothing gets deleted until it has run clean for another week.

New estimate: about 21 hours of my time over two weeks. The rest runs in the background.

What follows are the working notes behind that plan, with the business details left out. This is the plan as written, before any data has moved.

Why the data has to move

JAS grew one small database at a time. Each part of the system that keeps records got its own SQLite file, which is the right call on a single server: no database server to run, nothing to tune, and a backup is a file copy. Containers change that. Copies of a job can run at the same time on machines that come and go, and SQLite is built for one machine writing to a local disk. So before anything is containerised, the data moves to a database server, and Postgres is the obvious one.

The inventory came first, read from production before a line of the plan was written. Each database is owned by one part of the system. Alongside them sit thirteen small JSON and CSV files holding state: sync cursors, "already sent" lists and OAuth tokens. Eight table names repeat across databases (runs, events, meta and so on). And no query anywhere joins across two databases.

That last fact is what the whole design rests on. Each SQLite file becomes its own schema inside one Postgres database, named after the part of the system that owns it. The repeated table names never collide, nothing gets renamed, and each store sets its search_path to its own schema, so the SQL it already has keeps working unqualified.

Not everything should move

Of the 30, two stay as files, on purpose:

• A large read-only archive. Nothing has written to it since early October. The warning against SQLite on shared storage is about writers; a file nobody writes to is safe anywhere. It can be bulk-copied later if reporting ever needs it.
• A full-text search index. It is derived: one command rebuilds it from source. Porting SQLite's FTS5 to Postgres full-text search is a separate job with its own risks, and nothing is lost by leaving a rebuildable file where it is.

Photos and other binary files are out of scope as well. They belong in object storage, and that is a different move.

Convert, then distrust the conversion

pgloader does the first conversion: point it at a SQLite file and it creates the tables in Postgres and copies the rows. It is fast, and its output is a draft. SQLite's column types are suggestions, not rules. A column declared INTEGER will store the text 12a without a word of complaint, so any converter has to guess what each column really holds, and every guess gets checked against the data.

The review is a script that writes a report per database, comparing the SQLite file with its Postgres copy:

1. Row counts per table, equal on both sides.
2. For every column, what SQLite actually stored (the typeof() counts) against the declared type. A column that mixes integers and text becomes text in Postgres, never a silently coerced number.
3. Per column: nulls, minimum, maximum and distinct values, equal on both sides.
4. Money and quantity columns summed on both sides; the totals must be equal.
5. Every JSON value parses, and every date matches the format the application writes.
6. Fifty sampled rows per table, compared field by field.
7. For the job records, a cross-check against the Job Management spreadsheet the business already trusts.

That last check is a report, not a gate. The spreadsheet carries manual edits and columns the system doesn't own, so some differences are expected. They get read by a person, not "fixed" by a script.

Dates stay as text for now, exactly as the application writes them today. Turning them into real timestamps, money into numeric and 0/1 flags into booleans is worth doing, but not in the same move. One change at a time: the migration moves the data, and the tightening comes after the old files are retired.

Fix with scripts, never by hand

Every problem the review finds becomes a step in the copy script: a per-table transform that runs every time. Nothing is patched by hand in the target database. A hand fix made during rehearsal is gone the moment the copy re-runs on the night, and the night is when you least want to remember it.

The copy script follows the same rules as the rest of the system:

• It reads from a consistent snapshot, using SQLite's backup API, so it can run while the system is live.
• It streams rows in chunks of 5,000 and never loads a table into memory.
• It is resumable per table: a re-run reloads only what didn't finish.
• After loading, it sets each identity sequence to the table's highest id, so new rows can't collide with old ones.

The SQL that doesn't travel

Most of the SQL in JAS is portable as written: LIMIT, RETURNING, INSERT ... ON CONFLICT, string concatenation with || and single-key JSON access with ->> all behave the same on both engines. Some of it isn't, and it was counted before it was estimated:

Everything else stays as it is. A small shared helper owns the connections and translates SQLite's ? placeholders into the Postgres driver's %s, escaping any literal % first. It checks the placeholder count against the parameter count, and it refuses a statement it can't account for rather than guessing. Each store changes by a handful of lines: how it connects, where its schema lives, and the rows in the table above.

The helper sits on SQLAlchemy 2.0 for connection pooling, with psycopg 3 as the driver and Alembic owning the Postgres schema. All three ship pre-built wheels, so the container image still needs no compiler (the reasoning is in Docker Best Practices).

Run both, side by side

This is the part that makes the move safe. One setting, with a per-store override, decides which database is in charge:

In shadow mode every write runs on SQLite first. Then the same statement, with the same parameters, runs on Postgres in its own transaction, committed when SQLite commits. Reads never touch the shadow, and reads always come from the same engine as the authoritative write, so a problem on the shadow side can never become a missing record for a user.

The details that decide whether two databases really stay identical:

• A shadow failure never fails the job. It is caught, logged with its statement and counted in a table on the primary side. The nightly compare repairs it, and any count above zero raises an alert.
• Generated ids are copied, not regenerated. After an insert on the primary, the helper passes the id it got into the shadow insert. Two databases handing out their own ids under concurrent writers would drift apart on the first busy morning.
• Timestamps come from the application, never from SQL defaults, so both sides hold the same text.
• Deletes are writes too. The weekly retention job's deletes are shadowed like everything else.

Compare every night

A nightly job compares every table: the row count, plus an order-independent digest computed the same way on both sides. Rows are read in primary-key order, in chunks, and each value is normalised before hashing: integers as decimal text, floats rounded to 12 significant figures, binary data as a SHA-256, and a fixed sentinel for NULL. Large tables are compared in key ranges, so a mismatch is located, not just detected.

A repair script re-copies from whichever side is primary. The same script is the rollback tool after any mode change, which means the rollback path gets exercised every time drift is repaired, not for the first time on a bad day.

What "a full week" means

A week is not seven days on the calendar. It is every job the business runs in a week, run on both databases and matched. Some jobs run every few minutes. Others run on a Monday, on a Friday, on a Sunday night, or once a month. A soak that ends on a Thursday has never seen Friday's reports.

So the exit criteria are written down before the switch, and all of them have to hold:

• Seven consecutive clean nightly compares.
• No shadow failures in the last 72 hours.
• Every weekly job has run on both sides; the monthly one either falls inside the window or gets a dry run.
• The spreadsheet cross-check has been read and signed off.
• Job durations within 25% of today's. The shadow adds a network round trip to every statement, so the write-heavy jobs are timed in rehearsal first.

Switch in small, reversible steps

1. Rehearse. Copy from a production snapshot, run the review, fold every fix into the script, and repeat until the report is clean. Nothing in production changes.
2. Go live with shadow writes. Deploy the code in the old mode first, so the deploy itself changes nothing. Then, in one evening window: stop every writer, snapshot the files, run the copy and the count checks, switch to shadow and start everything again. The pause is optional, since the compare would catch anything written in between, but it gives the first compare a clean baseline. Incoming webhooks are retried by their sender for more than a day, so the window loses nothing. To roll back, switch the setting back; SQLite is still the truth.
3. Soak until every exit criterion holds.
4. Switch reads. Postgres becomes primary and SQLite becomes the shadow, so the nightly compare keeps running in the other direction. Hold for at least three working days, including a Monday or a Friday. To roll back, switch back and re-sync anything written only to Postgres.
5. Stop writing SQLite. The files stay where they are, untouched, as the fallback.
6. Retire after one more clean week: archive the files, lengthen the database's backup retention, and delete the code paths that only existed for the move.

While it runs

The move doesn't freeze other work. For the length of the window a few rules hold for any change: a schema change ships with both the SQLite change and the Postgres migration in the same pull request; new stores use the shared helper from day one; no new JSON or CSV state files. A CI job runs every store's tests against a real Postgres container, so a store that drifts fails a build instead of a compare.

The plan is the job

The hundred hours didn't disappear because of better tooling. They disappeared because of a better plan. Knowing how to move a live business system without losing a single record is the job. This is why I'm an engineer.