pg-migration-guard

A free, static safety checker for PostgreSQL SQL migrations. It reads a proposed Flyway or raw SQL migration and flags the DDL patterns that quietly take a heavy lock, rewrite a whole table, or destroy data, before that migration reaches production. Every finding names the hazard, explains why it blocks, and prints a safe rewrite.

It parses SQL with libpg_query, the same C parser PostgreSQL itself uses, so it reads real statements rather than guessing with regexes: a column named like a keyword, a schema-qualified table name, or dollar-quoted code is read correctly, not misread by a pattern match. The parser is bundled as a self-contained BSD-3-Clause binary and bound through the Python standard library (ctypes), so there are no third-party Python dependencies, and the tool never connects to a database or the network.

What it catches

Ten migration hazards (12 rule IDs):

Rule	Hazard
IDX01 / IDX02	CREATE INDEX without CONCURRENTLY, and a CONCURRENTLY index op inside a transaction (which errors)
COL01	ADD COLUMN with a volatile DEFAULT (rewrites the table)
COL03	ALTER COLUMN ... TYPE (rewrites the table)
NN01	SET NOT NULL (scans the whole table under ACCESS EXCLUSIVE)
FK01	ADD FOREIGN KEY without NOT VALID (validates under a write-blocking lock)
CHK01	ADD CHECK without NOT VALID (scans the whole table)
PK01	ADD PRIMARY KEY / UNIQUE (builds the index under ACCESS EXCLUSIVE)
ORD01 / ORD02	a backfill and its constraint in one migration, and an unbatched UPDATE/DELETE with no WHERE
LCK01	lock-taking DDL with no lock_timeout guard
DES01	destructive ops: TRUNCATE, DROP TABLE, DROP COLUMN, DROP SCHEMA, DROP DATABASE

Install

pip install pg-migration-guard

Requires Python 3.8+. No other dependencies. A prebuilt parser binary is bundled for Linux (x86_64, aarch64), macOS (arm64, x86_64), and Windows (x86_64).

Use

pg-migration-guard db/migration/V42__add_index.sql
pg-migration-guard --format json db/migration/V42__add_index.sql
pg-migration-guard --fail-on warn --pg-version 15 db/migration/V42__add_index.sql

Options:

• --format {human,json} (default human)
• --fail-on {blocker,warn,advisory,none} (default blocker) sets the severity that makes the command exit nonzero.
• --pg-version N assumes a Postgres major version for version-gated rules.
• --self-test runs the internal checks. --version prints the version.

Exit codes: 0 clean, 1 a finding at or above --fail-on, 2 a usage or parse error.

GitHub Action

Add a workflow that fails the pull request and annotates each risky line:

name: migration-guard
on:
  pull_request:
    paths: ["db/migration/**.sql"]
jobs:
  guard:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with:
          python-version: "3.x"
      - uses: technical-turtle/pg-migration-guard@v1
        with:
          path: db/migration
          fail-on: blocker
          # pg-version: "15"   # optional

Findings are emitted as GitHub annotations (::error:: for blocker, ::warning:: for warn, ::notice:: for advisory), so they appear inline on the diff.

Before and after

A migration that looks harmless:

CREATE INDEX idx_orders_customer ON orders (customer_id);
ALTER TABLE orders DROP COLUMN legacy_note;

$ pg-migration-guard V42__orders.sql

  BLOCKER  DES01  Destructive operation (TRUNCATE / DROP TABLE / DROP COLUMN)   (high)
    stmt #1, line 2:  ALTER TABLE orders DROP COLUMN legacy_note
    Why:  DROP COLUMN is fast and metadata-only, but it is irreversible and breaks any
          app version still selecting the column during a rolling deploy.
    Safe: Confirm intent. For a live column, stop referencing it first (contract phase), then drop.

  WARN     IDX01  CREATE INDEX without CONCURRENTLY   (high)  [latent blocker on a large/hot table]
    stmt #0, line 1:  CREATE INDEX idx_orders_customer ON orders (customer_id)
    Why:  Plain CREATE INDEX takes a SHARE lock, blocking all writes to the table until the build finishes.
    Safe: CREATE INDEX CONCURRENTLY idx ON tbl (col);  -- outside a transaction

Summary: 1 blocker, 1 warn, 0 advisory

The safe rewrite: build the index without holding a write lock, and drop the column in two steps (stop reading it in the app first, then drop it in a later migration):

CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);  -- outside a transaction
-- ship a release that stops reading legacy_note, then in a later migration:
ALTER TABLE orders DROP COLUMN legacy_note;

Known limitations

• Liquibase changelogs (XML, YAML, JSON) are not parsed. Point this tool at SQL migrations (raw SQL or Flyway SQL); for Liquibase changelog formats, use the paid Postgres Migration Safety Auditor.
• The tool assumes a migration runs inside a transaction (the Flyway and Liquibase default). A Liquibase formatted-SQL file with runInTransaction:false can therefore get a spurious IDX02 finding on a CONCURRENTLY index operation, since outside a transaction that operation is actually fine.

Full coverage

pg-migration-guard is the free version of the Postgres Migration Safety Auditor. The paid tool checks 33 rules (not 12), each linked to an official Postgres source, and adds Liquibase XML / YAML / JSON changelog support, Postgres-version and table-size aware checks, a config file with per-rule mute controls, and a Claude Code skill: https://technical-turtle.com/postgres-migration-auditor

License

MIT. Bundles the libpg_query parser (BSD 3-Clause); see THIRD-PARTY-NOTICES.md.