September 21, 20265 min read

Same Migrations, Different Schemas: Adopting a Production Hotfix Back into Git

Detect schema drift with Ptah and adopt a production hotfix into Git while preserving the existing PostgreSQL index.

Simon HartSolutions Engineer

Matching migration histories do not guarantee matching schemas. And when production differs from Git, production may contain the change you want to keep.

Suppose you add an index while investigating a slow query. The team keeps it, but the SQL never reaches the repository. Staging and production report the same applied migrations, yet only production has the index.

We’ll use Ptah to detect the difference and adopt the hotfix: inspect the live index, record it in Git, and migrate both databases without rebuilding it on production.

Migration history and the current schema

Ptah reads different evidence for each check:

Command	What it checks
`ptah migrations status`	Which migrations were applied
`ptah schema compare`	How the current schema differs from the desired schema

Both databases start with an orders table containing id, customer_id, and total_cents. The initial migration and schema file define no index on customer_id.

The manual change adds an index to production:

CREATE INDEX CONCURRENTLY orders_customer_id_idx
    ON public.orders (customer_id);

State	Staging	Production
Latest applied migration	`1789992000`	`1789992000`
`orders` table	Present	Present
`orders_customer_id_idx`	Absent	Present

The commands were run with Ptah 0.7.0 and PostgreSQL 18.6. PRODUCTION_URL and STAGING_URL name their connection URLs. To reproduce the example, use disposable databases; the example README gives the starting state. These checks establish schema behavior, not query performance.

The sequence is: detect drift → inspect the index → update the desired schema → create a migration → apply → compare again.

Confirm the history, then inspect the schema

First, check production against the original migration directory:

ptah migrations status \
  --db-url "$PRODUCTION_URL" \
  --migrations-dir migrations \
  --verify-sum --exit-code

The relevant output is:

Current Version: 1789992000
Total Migrations: 1
Applied Migrations: 1
Pending Migrations: 0
Out-of-order Migrations: 0
Status: ✅ Database is up to date

The command exits 0 on both databases, including checksum verification. The manual index changed neither the migration files nor their applied history.

Now compare production with the schema recorded before the incident:

ptah schema compare \
  --db-url "$PRODUCTION_URL" \
  --schema-file before.sql

The difference is:

Differences detected (1 category):
  indexes_removed (1): orders_customer_id_idx orders

Reconciling SQL:
DROP INDEX IF EXISTS "orders_customer_id_idx";

The file defines the desired state without this index, so the proposed SQL would remove it. schema compare only prints the SQL. Here the drift is intentional: we’ll adopt the index into Git. Executing that DROP would undo the hotfix.

Verify the index you intend to keep

Before adoption, check the index’s definition and whether it is usable:

SELECT pg_get_indexdef(indexrelid) AS definition,
       indisvalid AS valid,
       indisready AS ready
FROM pg_index
WHERE indexrelid = to_regclass('public.orders_customer_id_idx');

Run the query against production in your PostgreSQL client. The result below uses psql’s expanded output:

definition | CREATE INDEX orders_customer_id_idx ON public.orders USING btree (customer_id)
valid      | t
ready      | t

This is the expected B-tree index on customer_id, with both catalog flags true. PostgreSQL supplies the normalized definition, including the access method omitted from the original SQL.

Add CREATE INDEX orders_customer_id_idx ON public.orders (customer_id); to the desired schema in schema.sql. Staging still needs a migration to create it.

Record the verified hotfix in a new migration

An unconditional CREATE INDEX would fail on production because the index already exists. The adoption migration needs to handle that existing object.

Once the existing index has been checked, the adoption migration can be small:

-- +ptah no_transaction

CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_customer_id_idx
    ON public.orders (customer_id);

Staging creates the index; production skips the create. Ptah records the migration in both databases.

CONCURRENTLY lets PostgreSQL build the index while ordinary writes continue. PostgreSQL requires it to run outside a transaction block, which is why the file starts with no_transaction. Keep other schema changes paused between inspection and adoption so the object you checked is still the object you adopt.

Ptah’s native format requires the matching down file included in the example. Rolling it back removes the index, even on production where it predates adoption. Review that consequence before rolling back a database that still needs the hotfix.

Keep the original migration unchanged. Save the new files in migrations/ and update the directory checksum:

ptah migrations hash --dir migrations

Apply the migration to staging:

ptah migrations up \
  --db-url "$STAGING_URL" \
  --migrations-dir migrations \
  --verify-sum

After that succeeds, apply the same migration to production:

ptah migrations up \
  --db-url "$PRODUCTION_URL" \
  --migrations-dir migrations \
  --verify-sum

Commit schema.sql, the migration pair, and ptah.sum together. The schema describes the target; the migration gets existing databases there.

Verify both databases

After applying the migration, compare each database with the updated schema:

ptah schema compare \
  --db-url "$STAGING_URL" \
  --schema-file schema.sql \
  --exit-code

ptah schema compare \
  --db-url "$PRODUCTION_URL" \
  --schema-file schema.sql \
  --exit-code

Each comparison exits 0 and ends with:

=== SCHEMA COMPARISON ===

No schema differences detected.

Both databases also reported both migrations as applied. Production’s index kept its object identifier: adoption did not rebuild it. Replaying the complete history into a fresh database produced the same application schema.

Choose the schema for each deployment check

Use this comparison as a deployment check. In the tested release, schema compare --exit-code returns 1 when it finds a difference. Without that flag, a completed comparison can print a difference and still exit 0.

When	Compare the deployment target against
Before migration	The schema from the currently deployed revision
After migration	The schema from the incoming revision

Using the incoming schema before migration would also report intentional changes that have yet to run. Checking only a fresh database misses manual changes on the deployment target.

If the pre-migration check finds drift, inspect it before proceeding. For this hotfix, the team chose adoption after checking the index; another difference may need a different decision.

History and schema are separate signals. Drift can be intentional. After adoption, both databases should agree on the migration history and the actual schema, including the hotfix.

Example files

← All articles