## TL;DR
Sqlite and postgres are different databases with different DDL, types, and transactional behavior: a migration green on sqlite proves nothing about postgres. Reproduce the failure against real postgres (a container is fine), fix the dialect-specific statements, and add a CI job that runs the migration chain against postgres. Local-dev convenience must never be the migration test.

## The query

```text
agent's migration config pointed at sqlite while production runs postgres - the migration passed locally and failed in prod
```

## Use this when

- Migration passed on sqlite but failed on postgres
- Local/prod database mismatch hid a migration failure
- Need postgres parity for migration testing
- An agent tested migrations only on sqlite

## Not for

- Failures that reproduce on postgres locally (fix the migration directly)
- Application queries behaving differently (a query problem, not the migration)
- Intentional sqlite-only tooling (then prod must not be postgres)

## Steps

### 1. Reproduce against real postgres

```bash
docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=[pw] postgres:16
```

Point the migration config at it and run the chain from zero. Expected output: the production failure reproduces locally. Common hits: ALTER COLUMN TYPE syntax, concurrent index builds, transactional DDL assumptions, type names sqlite accepted loosely.

### 2. Fix the dialect differences

Rewrite the failing statements in postgres dialect: explicit type casts in ALTER COLUMN ... TYPE ... USING, CREATE INDEX CONCURRENTLY outside transactions, postgres-native types. Keep the migration postgres-first; add sqlite compatibility only if local dev truly needs it.

Expected output: the chain runs clean on postgres from zero.

### 3. Find every other sqlite-ism in the chain

Grep the migrations for sqlite-isms: bare ALTER TABLE ... RENAME that postgres handles differently, type names like TEXT-for-everything, missing USING clauses, batch-mode assumptions from alembic's sqlite workaround.

Expected output: a list, fixed one by one, each verified on postgres.

### 4. Add postgres migration testing to CI

A CI job spins up postgres (service container) and runs the full migration chain from zero plus up/down on every change. Sqlite may stay for fast unit tests, but it is no longer the migration gate.

Expected output: the next dialect bug breaks the build, not production.

### 5. Make the agent target-aware

The agent's migration procedure starts with "what database does production run", and every migration is tested against that database family. "It passed on sqlite" stops being evidence.

Expected output: migrations are written for the production dialect from the first draft.

## Tool compatibility

The sqlite-vs-postgres gap applies to alembic, django, rails, and any ORM-backed migration tool with a sqlite dev default.

## Variant phrasings

### migration passed sqlite failed postgres

Steps 1 and 2: reproduce on real postgres, fix the dialect.

### test migrations against postgres in CI

Step 4: service-container postgres running the full chain from zero.

### alembic sqlite vs postgres migration differences

Step 3: audit the chain for sqlite-isms; alembic's batch mode exists precisely because of them.

## Why it happens

Sqlite prioritizes permissiveness (dynamic typing, transactional DDL, lenient ALTER) while postgres prioritizes strictness (rigid types, lock-heavy DDL, exact syntax). A migration is a program written in a dialect: running it against a different dialect tests a different program. The mismatch is invisible until production because sqlite never complains about the things postgres rejects.

## Edge cases

- Alembic's batch mode for sqlite rewrites migrations at runtime: a migration that relies on batch mode may behave differently on postgres in subtle ways. Read the rendered SQL.
- Postgres version matters too: test against the production major version, not just "postgres".
- The reverse mismatch (dev on postgres, prod on mysql) has the same shape with different specifics: the rule is "test on the production family", not "postgres specifically".

## Provenance

Resolved from the public thread: https://vectle.com/posts/pst_8PfyPEZ6BkkE1i5IGaZG2w