A minute-by-minute Laravel MySQL to Postgres cutover runbook
TuskShiftNew, free, in your browser: paste your SHOW CREATE TABLE and see what breaks on Postgres (int unsigned overflow, TIMESTAMP shift, _ci collations, sequences, tinyint(1), ENUM/SET, zero dates, FULLTEXT). Nothing is uploaded.
Update 26 Oct: checker v2 is out (adds TIMESTAMP time-zone shift, increments() headroom and bigint unsigned checks): free checker v2 + full migration checklist.
By cutover night the hard work should be done. The code runs on both engines, CI is green on both, and the data copy has been rehearsed. What is left is a sequence of small steps that have to happen in the right order, by people who are tired, with a clear point where somebody says "stop, we roll back".
This is the runbook we use for an offline Laravel MySQL→Postgres window, plus the rollback plan that goes with it. Times are relative to T, the moment the window opens. Fill the gaps from your own rehearsal, not from anyone's blog post.
The week before
The window goes smoothly only if these are already true:
- Portable code (no IFNULL, no GROUP_CONCAT, ilike where search must stay case-insensitive) has been running in production on MySQL for about a week with no regressions.
- Your test suite runs against real Postgres in CI, next to the MySQL job.
- At least two full rehearsals from a restored production backup: copy, verify, smoke test — with timings written down.
- Postgres is provisioned in the same region, backups on, max_connections sized for every PHP-FPM and queue worker, needed extensions created.
- Postgres .env values exist in the deploy tool but are not active.
- Status-page note and customer email are written.
The day before: test-restore a fresh MySQL backup, re-read rollback, and build an empty target from your own migrations:
php artisan migrate:fresh --database=pgsql --forceBuilding the schema from migrations, then loading data only, means the Postgres schema is exactly what your app expects.
The window (relative to T)
- T+0 — Maintenance mode
- T+1 — Stop workers and scheduler
- T+2 — Prove nothing writes to MySQL
- T+3 — Final backup, MySQL read-only
- T+5 — Snapshot foreign keys, start the copy
- T+copy — Restore FKs, reset sequences, ANALYZE
- T+copy+verify — Verify. Must be clean, or roll back
- next — Record cutover marks
- next — Switch env, smoke test behind the secret
- next — Start workers, go live, watch
T+0: maintenance mode
php artisan down --retry=60 --secret="cutover-check-token"--retry sets Retry-After. --secret lets you bypass maintenance for smoke tests by visiting /cutover-check-token once. Use a long random value, not this one.
T+1: stop everything that writes
Stop queue workers (supervisorctl stop all, or php artisan horizon:terminate), disable the cron for schedule:run, and let running jobs finish. Anything else that writes to the database (reporting tools, a second app) gets stopped too.
T+2: prove nothing writes
-- on MySQL
SHOW PROCESSLIST;Nothing from the app user should be there except your own session. A forgotten worker will write rows to MySQL that never reach Postgres.
T+3: final backup, then MySQL read-only
Take the final backup or snapshot. Then:
SET GLOBAL read_only = ON;Privileged accounts can still write. On MySQL 8, super_read_only = ON closes that gap. Leave MySQL in this state after cutover: untouched, readable, ready to take over again.
T+5: copy
psql "$PG_URL" -f sql/pg-fk-snapshot.sql # save FK definitions first
# run your copy: pgloader with a data-only config, or your own chunked copySnapshot foreign keys before the load — some bulk loaders drop them and do not put them back. After the copy:
- Re-create any missing foreign key (adding it validates existing rows, so orphans fail loudly).
- Reset every sequence to MAX(id) — explicit ids do not advance sequences.
- Run ANALYZE; so the planner has statistics.
If you skip the sequence reset, the first insert after go-live fails with duplicate key value violates unique constraint "..._pkey".
Verify: the hard gate
Compare row counts and a checksum of every row, per id range. If verify does not come back clean, you roll back. Nobody decides at 2 a.m. that a mismatch is probably fine. Every mismatch should already have been explained during rehearsal.
Record cutover marks
Right after verify, before any user can write to Postgres, record the max id of every table and the time. This is what makes a later rollback possible:
CREATE TABLE IF NOT EXISTS cutover_marks (
table_name text PRIMARY KEY,
max_id bigint,
marked_at timestamptz NOT NULL DEFAULT now()
);
DO $$
DECLARE r record; m bigint;
BEGIN
FOR r IN
SELECT c.table_schema, c.table_name
FROM information_schema.columns c
JOIN information_schema.tables t
ON t.table_schema = c.table_schema AND t.table_name = c.table_name
WHERE c.table_schema = 'public'
AND c.column_name = 'id'
AND c.data_type IN ('integer', 'bigint')
AND t.table_type = 'BASE TABLE'
AND c.table_name <> 'cutover_marks'
LOOP
EXECUTE format('SELECT max(id) FROM %I.%I', r.table_schema, r.table_name) INTO m;
INSERT INTO cutover_marks (table_name, max_id) VALUES (r.table_name, m)
ON CONFLICT (table_name) DO UPDATE SET max_id = EXCLUDED.max_id, marked_at = now();
END LOOP;
END
$$;Switch, smoke test, go live
Set DB_CONNECTION=pgsql and the host/port/database/user/password. Then php artisan config:cache. If you run Octane, reload it.
Still in maintenance mode, open the app through the secret URL and run the smoke-test list from rehearsal: log in with a mixed-case email, run a search, open the three most important reports, run an export, dispatch a queue job, receive a webhook.
Then start workers and the scheduler, watch failed_jobs for a few minutes, and run php artisan up. For the next hour watch the error tracker, queue depth and slow queries. On Postgres, log_min_duration_statement gives you a slow-query log.
Decide the two numbers before you start
- Rollback deadline — if you are not up by T+something, you roll back, even if the problem looks small.
- Fix-forward point — how long after go-live rollback is still an option (often 24 hours). After that you fix on Postgres.
Name one person who makes the go or no-go call.
Rollback, case 1: before php artisan up
No user has written to Postgres yet, so rollback is cheap:
- Restore the previous env (DB_CONNECTION=mysql), php artisan config:cache.
- SET GLOBAL read_only = OFF; on MySQL.
- Start workers and the scheduler, php artisan up.
- Write down what failed and reproduce it on the rehearsal environment, not live.
Rollback, case 2: after up
Postgres has writes that MySQL does not. Either you carry them back or you accept losing them. The cutover marks tell you which rows to carry. For each table, export new and changed rows from Postgres. Booleans need casting to integers for MySQL.
-- psql, one line per table
\copy (SELECT id, number, amount, is_paid::int, paid_at, created_at, updated_at
FROM invoices
WHERE id > (SELECT max_id FROM cutover_marks WHERE table_name = 'invoices')
OR updated_at > (SELECT marked_at FROM cutover_marks WHERE table_name = 'invoices')
AT TIME ZONE 'UTC') TO 'invoices-after-cutover.tsv'Load into a staging table on MySQL, then upsert. Do not use REPLACE INTO — it deletes and re-inserts, which fires ON DELETE CASCADE on child tables.
-- MySQL, client started with --local-infile=1
CREATE TABLE invoices_rollback LIKE invoices;
LOAD DATA LOCAL INFILE 'invoices-after-cutover.tsv'
INTO TABLE invoices_rollback
(id, number, amount, is_paid, paid_at, created_at, updated_at);
INSERT INTO invoices (id, number, amount, is_paid, paid_at, created_at, updated_at)
SELECT id, number, amount, is_paid, paid_at, created_at, updated_at FROM invoices_rollback
ON DUPLICATE KEY UPDATE
number = VALUES(number), amount = VALUES(amount), is_paid = VALUES(is_paid),
paid_at = VALUES(paid_at), created_at = VALUES(created_at), updated_at = VALUES(updated_at);
DROP TABLE invoices_rollback;Limits: hard deletes on Postgres are not carried back, and tables without updated_at only bring back new rows. Rehearse this path once with a test table before cutover night.
Keep MySQL
Leave MySQL running and read-only for at least a week. Take a final backup before you decommission it, and test that it restores.
Everything above works as written. TuskShift packages this runbook as a fill-in-the-blanks checklist with FK snapshot/restore SQL, sequence reset, a resumable copy and a per-range verify tool — optional.
TuskShift on Gumroad · Not affiliated with Laravel, PostgreSQL, MySQL, MariaDB or Timescale.
Before the runbook: two free checks
- Free 2-minute readiness check (a single PHP file that scans the code and runs read-only data checks)
- The two bugs you get in the first hour (sequence reset SQL plus case-sensitive email logins)
Doing this migration for real? The TuskShift Migration Kit has the full runbook, data-validation SQL and cutover checklist. Launch price: 30% off with code LAUNCH30 until Nov 5 → https://tuskshift.gumroad.com/l/tuskshift/LAUNCH30 . Also want metrics on Timescale? Kit + Timescale Metrics Kit bundle: https://tuskshift.gumroad.com/l/fmfnj