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):

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 36 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.