Trace Mode

Run your migrations against a disposable Docker Postgres container and compare pgfence's lock predictions with actual PostgreSQL behavior. No credentials needed, no risk to real data.

Quick Start

bash 
pgfence trace migrations/*.sql

This spins up a temporary postgres:17-alpine container, executes each statement, queries pg_locks and the system catalog after every step, then tears down the container. The entire process is automatic.

What Trace Does

For each statement in your migration, trace mode:

  1. Executes the statement against a real PostgreSQL instance
  2. Samples pg_locks during execution to observe the lock mode acquired
  3. Diffs system catalog snapshots (pg_class, pg_constraint, pg_index) to detect table rewrites, implicit index creation, and constraint validation state
  4. Compares actual behavior against pgfence's static analysis predictions
  5. Reports a verification status for each check

Verification Statuses

StatusMeaning
ConfirmedStatic prediction matches actual PostgreSQL behavior
MismatchStatic prediction was wrong; trace result takes precedence
Trace-onlyTrace found something static analysis missed (e.g., table rewrite, implicit index)
Static-onlyPolicy or best-practice check that trace cannot verify (e.g., missing lock_timeout)
ErrorStatement failed to execute (syntax error, missing dependency, etc.)

Options

FlagDefaultDescription
--pg-version <version>17PostgreSQL version for the Docker container
--docker-image <image>postgres:<version>-alpineCustom Docker image (overrides --pg-version)
--format <format>autoMigration format: sql, typeorm, prisma, knex, drizzle, sequelize, auto
--output <format>cliOutput format: cli, json, github, sarif, gitlab
--cifalseCI mode: exit 1 on mismatches, errors, or risk threshold violations
--max-risk <level>highMaximum allowed risk level
--snapshot <path>Schema snapshot for definitive type analysis
--plugin <paths...>Plugin file paths for custom rules

pgfence trace uses the same reporter set as pgfence analyze, including GitHub, SARIF, and GitLab output.

Targeting a Specific PostgreSQL Version

bash 
# Test against PG 14 to match production
pgfence trace --pg-version 14 migrations/*.sql

# Test against PG 15
pgfence trace --pg-version 15 migrations/*.sql

Custom Docker Images

Use --docker-image for extensions like PostGIS, pgvector, or TimescaleDB:

bash 
# PostGIS
pgfence trace --docker-image postgis/postgis:17 migrations/*.sql

# pgvector
pgfence trace --docker-image pgvector/pgvector:pg17 migrations/*.sql

CI Mode

In CI mode, pgfence trace exits with code 1 when:

  • Any finding exceeds the --max-risk threshold
  • Any mismatch is detected between static and trace results
  • Any statement fails to execute
yaml 
- name: Verify migration safety
  run: pgfence trace --ci --max-risk medium migrations/*.sql

How CONCURRENTLY Statements Are Handled

CREATE INDEX CONCURRENTLY and similar statements cannot run inside a transaction. pgfence handles this by using an observer connection that polls pg_locks during execution to observe the locks it sees. Because the observer is polling, very short-lived lock spikes can still be missed. This is a capability other tools skip entirely.

Session Isolation

Between file traces, pgfence issues RESET ALL to prevent session state leakage. Timeouts, search_path, or other settings set by one migration file do not affect subsequent files.

Security

  • Container binds to 127.0.0.1 only (not exposed to the network)
  • Container password is ephemeral (random 20-char base64url, never written to disk)
  • Container is automatically cleaned up on exit, even on errors
  • Database credentials are never included in error messages or output

Requirements

Trace mode requires Docker to be installed and running. Use pgfence analyze for static-only analysis without Docker.

Trace with ORM Migrations

Trace mode works with all supported ORM formats:

bash 
# TypeORM
pgfence trace --format typeorm src/migrations/*.ts

# Knex
pgfence trace --format knex migrations/*.ts

# Auto-detect
pgfence trace migrations/*