Baselining an Existing Production Database

The database has been in production for six years. Its schema was built by hand, by an ORM’s auto-sync, by scripts in a wiki, and by a DBA who has since left. Now the team is adopting Flyway (or Liquibase, or Sqitch) so that every future change is versioned and reviewed — and the very first flyway migrate fails with Found non-empty schema(s) "public" but no schema history table, because the tool has no idea how the existing tables got there. Baselining is the one-time step that tells the migration tool “everything up to here already exists; start counting from the next version”. Done carelessly, it records a baseline that does not match reality, and every environment built from migrations silently differs from production. This guide shows how to baseline so that the recorded starting point is provably the real one. It builds on the version-control fundamentals in Schema Version Control Basics.

Baseline Without Running Anything on Production Five steps. Dump production's schema; save it as the baseline migration V1; build a scratch database from V1 and diff it against production until they match; mark V1 as applied on production without executing it; add the first real change as V2. Baseline Without Running Anything on Production STEP 1 Dump schema pg_dump --schema-only STEP 2 Save as V1 V1__baseline.sql STEP 3 Prove parity scratch DB diff = empty STEP 4 Mark applied flyway baseline STEP 5 First change V2__… runs normally
The baseline migration is executed only on new environments; on production it is recorded as applied, which is why proving it matches production matters.

Symptom / Error Signatures

These are the situations that call for a baseline:

  • Flyway refuses to run: Found non-empty schema(s) "public" but no schema history table. Use baseline() or set baselineOnMigrate to true to initialize the schema history table.
  • Liquibase tries to create tables that already exist: ERROR: relation "customers" already exists, because its DATABASECHANGELOG table is empty.
  • A new developer’s database, built from the migration directory, is missing tables that production has, because the early schema was never captured as a migration.
  • An ORM’s schema-sync mode (TypeORM synchronize, Prisma db push, Django without migrations) has been used in production and must be replaced by migrations, as discussed in disabling TypeORM synchronize in production.

Root Cause Analysis

A migration tool knows a database’s state only through its history table: Flyway’s flyway_schema_history, Liquibase’s DATABASECHANGELOG, Sqitch’s registry schema. An existing database has objects but no history, so from the tool’s point of view it is in an unknown state. The tool offers two ways out, and they are not equivalent.

Approach What it does Risk
Baseline marker only (flyway baseline, baselineOnMigrate) writes a row saying version N is the baseline; earlier migrations are ignored if no file captures the existing schema, new environments cannot be built
Baseline migration + marker a V1__baseline.sql file captures the full existing schema; production records V1 as applied without running it the file may not match production exactly
Liquibase changelog-sync records every changeset in the changelog as executed without running any same as above: the changelog must already describe production
Running the baseline on production executes the dump against the live database fails on existing objects, or worse, partially succeeds

The second approach is the one to use: it gives you a file from which any environment can be built, and a history table on production that starts at the right place. Its single risk — a baseline file that does not match production — is eliminated by checking parity before marking it applied.

Two Paths From the Same Baseline File The baseline file V1__baseline.sql feeds two paths. On production, flyway baseline records V1 in the history table without executing it. On new environments such as CI and developer databases, flyway migrate executes V1 and then V2 onward. A parity check compares a scratch database built from V1 with production. Two Paths From the Same Baseline File V1__baseline.sql full existing schema Production baseline: V1 recorded, not run CI / dev / new env migrate: V1 executed, then V2… Parity check scratch from V1 vs prod recorded executed
Production and new environments reach the same schema by different routes — recorded versus executed — which only works if the parity check proves V1 equals production.

Immediate Mitigation

1. Dump production’s schema without data or ownership noise. Use a read replica if you have one; --schema-only reads the catalog and takes only brief ACCESS SHARE locks.

# Shell · PostgreSQL · read-only credentials · run against a replica where possible
# WARNING: pg_dump holds ACCESS SHARE on each table for the whole run; avoid running during a migration.
pg_dump --schema-only --no-owner --no-privileges --no-comments \
  --exclude-schema=flyway --file=V1__baseline.sql \
  "postgres://readonly:***@prod-replica:5432/app"

For MySQL, use mysqldump --no-data --skip-add-drop-table --routines --triggers --set-gtid-purged=OFF, then remove AUTO_INCREMENT= counters from the output so the file describes structure, not state.

2. Prove the file rebuilds production exactly. Load it into a scratch database of the same major version, dump that database the same way, and diff the two dumps. Iterate until the diff is empty.

# Shell · CI or workstation · scratch database only, never production
createdb baseline_check
psql -v ON_ERROR_STOP=1 -d baseline_check -f V1__baseline.sql
pg_dump --schema-only --no-owner --no-privileges --no-comments baseline_check > rebuilt.sql
diff <(grep -v '^--' V1__baseline.sql) <(grep -v '^--' rebuilt.sql) && echo "parity: OK"

3. Record the baseline on production without executing it. Flyway’s baseline command creates the history table and inserts a baseline row for the given version.

# Shell · Flyway CLI · migration role with CREATE on the history schema
# WARNING: run exactly once per database; a second baseline on a populated history table is refused.
flyway -url="jdbc:postgresql://prod-primary:5432/app" -user=migrator \
  -baselineVersion=1 -baselineDescription="Existing production schema" baseline

With Liquibase, put the baseline changesets in the changelog and run liquibase changelog-sync against production, after previewing with liquibase changelog-sync-sql.

4. Ship the first real change as version 2. Run flyway info first: it must show V1 as Baseline and V2 as Pending. Then migrate normally.

Permanent Fix / Long-Term Pattern

Once baselined, the database must only ever change through migrations. Disable any ORM auto-sync, revoke DDL privileges from application roles so console changes need a deliberate escalation, and add a scheduled drift check comparing production with a scratch database built from the full migration directory — the audit described in detecting production schema drift against a desired state. Keep every environment on the same baseline: staging and other long-lived databases get the same baseline command after the same parity check, while ephemeral environments simply run V1.

Over time the baseline file becomes the natural squash point. When the history grows long, the same parity technique lets you replace V1 through V200 with a new baseline, as covered in squashing migration history safely. Store the parity-check script in the repository so it can be rerun whenever a baseline is regenerated.

-- PostgreSQL · after baselining · revoke DDL from the application role
-- WARNING: confirm no application code path runs DDL (ORM auto-sync, runtime CREATE TABLE) before revoking.
REVOKE CREATE ON SCHEMA public FROM app;
-- Objects are owned by the migration role, so app cannot ALTER or DROP them.
ALTER TABLE customers OWNER TO migrator;
-- ROLLBACK PATH: GRANT CREATE ON SCHEMA public TO app;
Baseline Commands by Tool Matrix of migration tools against the command that records a baseline without executing it, where the baseline is stored, and how to preview the effect. Baseline Commands by Tool Tool Record baseline Stored in Preview first Flyway flyway baseline -baselineVersion=1 flyway_schema_history flyway info Liquibase liquibase changelog-sync DATABASECHANGELOG changelog-sync-sql Sqitch sqitch log-only deploy (--log-only) sqitch registry sqitch status Prisma prisma migrate resolve --applied _prisma_migrations prisma migrate status Alembic alembic stamp head alembic_version alembic current
Every mainstream tool has a "record without running" operation; the parity check before it is the part you have to add yourself.

Verification Checklist

Frequently Asked Questions

Should I use baselineOnMigrate=true? Only as a convenience for databases you have already verified. It baselines automatically the first time migrate finds a non-empty schema with no history table, which is exactly the moment you want a human to have checked parity. Prefer an explicit baseline command after the check.

What version number should the baseline use? Version 1 is conventional when the baseline file is the first migration. If you already have a few migrations that ran on some databases, choose a baseline version above them and make sure the baseline file includes their effects.

Does the baseline need to include data? Only reference data the application cannot run without, such as lookup tables of status codes. Put that in the baseline or a separate repeatable migration. Business data never belongs in a migration file.

Can I baseline MySQL the same way? Yes. Use mysqldump --no-data with routines and triggers, strip AUTO_INCREMENT counters, run the parity check against a scratch MySQL of the same version, and then use the tool’s baseline command. Pay attention to character set and collation defaults, which often differ between old tables and new ones.

Related Articles