pgt

⚠️ Currently a work-in-progress.

pgt is a collection of tools for PostgreSQL:

• bench is a tool for running benchmarks written as SQL Go templates
• migrate is a simple and production-safe plain SQL migration system
• mux is a PostgreSQL protocol proxy that can multiplex connections/sessions (useful for debugging in-progress transactions)

Migrations

Features:

• Version table tracks full history of migrations; updated atomically with transaction but also includes mechanism for detecting partially failed up/down for non-txn migrations
• Auto transaction wrapping, but can be disabled e.g. for create index concurrently
• Configurable schema version table
• Watchdog that looks for other backend processes that are blocked waiting for locks taken by migrations, and cancels if too many are blocked for too long. (sorta inspired by autovacuum's back-off strategy)
• Convenience for dumping schema SQL (--dump flag that calls pg_dump or custom command, e.g. exec'ing pg_dump in a docker container)
• Test mode that automatically creates an isolated test database, runs each migration (up+down+up or just up if irreversible)
• Interactive mode which can prompt for each migration and show SQL
• Version table bootstrapping is implemented as reviewable, explicitly built-in "zero migration".
• Migrations are very nearly plain SQL (special directives are just comments)

Possible future features:

• Export reports on locks acquired by migrations (e.g. detect non-concurrent index creation)
• Helper for renumbering migrations post-merge
• Helper prompts for resolving issues like unexpected statement timeouts and previously failed partial migrations.

Caveats:

• Once created (version 0 applied), specified version table must be exactly the same as the existing version table, or pgt will think the version has be reset to -1. The table can be renamed by manually running alter table ... rename to ....
• Currently pgt does not know how to force-set the version or otherwise recover from failed partial migrations (no-transaction migrations), but this can be accomplished by manually patching the version table.

Usage:

USAGE:
    pgt migrate [OPTIONS]

OPTIONS:
    -h, --help                                              show usage help
    -d, --database <VALUE>         PGT_DATABASE             database connection string  (required)
    -s, --source <VALUE>           PGT_MIGRATION_SOURCE     path to migrations directory  (required)
    -t, --target <VALUE>           PGT_MIGRATION_TARGET     version to target  (default: latest)
    --version-table <VALUE>        PGT_VERSION_TABLE        use custom version table name  (default: pgt.schema_version)
    -y, --yes                                               bypass all confirmation prompts
    --test                         PGT_TEST                 enable test mode
    --test-database-name <VALUE>                            (default: pgt_migrate_test_1694394525)
    --retain-test-database
    --dump <VALUE>                 PGT_DUMP                 file path to dump schema to
    --dump-command <VALUE>         PGT_DUMP_COMMAND         command used to dump schema  (default: pg_dump --schema-only '{{.Url}}')
    --max-block-duration <VALUE>   PGT_MAX_BLOCK_DURATION   (default: 10s)
    --max-block-processes <VALUE>  PGT_MAX_BLOCK_PROCESSES  (default: 0)

Examples

Basic migration:

001_create_foo.sql

create table foo (
    id int primary key not null generated by default as identity,
    value int
);

Migration with down/rollback:

002_foo_description.sql

alter table foo add column description text;
--pgt:down
alter table foo drop column description;

Disable transaction auto-wrapping:

003_foo_value_index.sql

--pgt:no_transaction
create index concurrently foo_value_idx on foo using btree (value);

--pgt:down
drop index foo_data_idx;