Production Guide#

This guide covers considerations for running pgmi in production environments: performance, rollback strategies, monitoring, and operational patterns.

Connection requirements#

pgmi requires a direct PostgreSQL connection or a connection pooler in session mode.

pgmi uses session-scoped temporary tables (pg_temp) that exist only for the lifetime of a single database connection. Connection poolers in transaction or statement mode reassign connections between operations, destroying the temporary tables mid-deployment.

This applies to PgBouncer, Pgpool-II, AWS RDS Proxy, Azure PgBouncer, and any other connection pooler. Direct connections are always safe. If you use a pooler, either configure session mode for pgmi deployments or bypass the pooler with a direct connection string.

PostgreSQL compatibility#

The minimum PostgreSQL version depends on which layer you use:

The 11+ figure applies to the CLI and the basic scaffold (set in the Go core — see the core minimum ). The advanced template raises the floor to 15+: security_invoker views, which the membership/RLS model relies on, were introduced in PostgreSQL 15.

Both floors are tested, not asserted. CI runs the core suite and the basic template against PostgreSQL 11, 15 and 17, and the advanced template against 15 and 17, so a construct that raises either floor fails the build. A server below 11 is refused before any schema work with invalid configuration: this server reports PostgreSQL <version>, and pgmi requires 11 or newer and exit code 10 — hashtextextended, which the deploy advisory lock needs, is what sets that floor.

Managed cloud PostgreSQL#

The basic template works on PostgreSQL 11+; the advanced template requires 15+ (see the compatibility matrix above). Where they differ further is what the advanced template requires from the deployment connection — none of which is superuser:

1. CREATEROLE — the advanced template creates database_admin, database_api, database_customer roles. Verify that the provider’s deployment role can create them.
2. CREATE EXTENSION for uuid-ossp, pgcrypto, pg_trgm, hstore. Extension availability and the role allowed to install each one vary by provider and service tier.
3. CREATE SCHEMA — the template lays out its core / api / membership / extensions schemas.

Entity lifecycle standards (created_at / deleted_at on tables marked object_id core.entity_id) are enforced by a deploy-end sweep over pg_temp functions — no DDL event trigger, no superuser. The reconcile machinery lives in pg_temp and disappears at session end.

Tested means the repository exercises the combination in CI. Expected means the core requirements are standard PostgreSQL features, but the provider is not part of continuous integration. Capability-dependent means the advanced template works only when the actual deploy role can create roles and schemas and install all four required extensions. Verify those capabilities on the selected service and tier before production use.

Deployment strategies#

Single-transaction deployment#

Transactional changes succeed or fail together. This provides the strongest atomicity, but holds locks longer. Sequence advances and effects outside the transaction are not covered.

-- deploy.sql
BEGIN;

DO $$
DECLARE
    v_file RECORD;
BEGIN
    FOR v_file IN (
        SELECT p.path, p.content
        FROM pg_temp.pgmi_plan_view p
        JOIN pg_temp.pgmi_source_view s ON s.path = p.path
        WHERE s.is_sql_file
        ORDER BY p.execution_order
    )
    LOOP
        RAISE NOTICE 'Executing: %', v_file.path;
        EXECUTE v_file.content;
    END LOOP;
END $$;

COMMIT;

When to use:

• Small deployments (< 10 files)
• All changes are quick (no long-running DDL)
• You need all-or-nothing semantics

Tradeoffs:

• Locks held until all migrations complete
• Long-running migrations block other operations
• Failure in any file rolls back everything

Error context with exception blocks#

Wrap each file execution in an exception block to capture which file failed. The outer transaction still rolls back entirely on failure, but you get clear diagnostics.

-- deploy.sql
BEGIN;

DO $$
DECLARE
    v_file RECORD;
BEGIN
    FOR v_file IN (
        SELECT p.path, p.content
        FROM pg_temp.pgmi_plan_view p
        JOIN pg_temp.pgmi_source_view s ON s.path = p.path
        WHERE s.is_sql_file
        ORDER BY p.execution_order
    )
    LOOP
        RAISE NOTICE 'Executing: %', v_file.path;
        BEGIN
            EXECUTE v_file.content;
        EXCEPTION WHEN OTHERS THEN
            -- Capture context before re-raising
            RAISE EXCEPTION 'Failed on %: %', v_file.path, SQLERRM;
        END;
    END LOOP;
END $$;

COMMIT;

When to use:

• Any deployment where you need clear error context
• Debugging which file caused a failure

Note: This is still all-or-nothing. The BEGIN...EXCEPTION...END block creates an implicit savepoint for error recovery, not separate transactions. If any file fails, the entire deployment rolls back.

Committing in phases#

pgmi’s execution contract makes this first-class, and no external orchestration is involved. Everything through your first top-level transaction terminator is one atomic unit; after it, each top-level statement autocommits, exactly as in psql:

-- deploy.sql
BEGIN;
-- schema changes that must land together
COMMIT;

-- from here on, each top-level statement commits on its own
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

BEGIN;
-- anything that must be atomic again says so
COMMIT;

Two constraints still apply. Both are PostgreSQL’s, not pgmi’s:

1. Transaction control needs the tail, not a procedure. COMMIT raises invalid transaction termination whenever a transaction block surrounds it — in a DO block and in a CALLed procedure alike. That is always the case in the atomic head, so a plan loop there commits once when the head ends, never per file. Run the same DO block at top level in the tail and COMMIT works: outside a transaction block, PostgreSQL 11+ lets both DO and CALL end and start transactions. Procedures buy you nothing the tail doesn’t.
2. EXECUTE runs statements in a function context. CREATE INDEX CONCURRENTLY, VACUUM, and CREATE DATABASE are refused there whatever the transaction state — CREATE INDEX CONCURRENTLY cannot be executed from a function. Write those at top level in the tail, never through the plan loop.

Recommended default: idempotent migrations in the atomic head. If a deploy fails, fix it and redeploy. Reach for the tail when a statement genuinely cannot run inside a transaction, or when a data migration is too large for one — and write everything after the first COMMIT idempotently, because a later failure leaves the already-committed statements applied.

Phased deployment#

Different handling for different phases.

-- deploy.sql
DO $$
DECLARE
    v_file RECORD;
BEGIN
    -- Phase 1: Extensions
    RAISE NOTICE '=== Phase 1: Extensions ===';
    FOR v_file IN (
        SELECT p.path, p.content
        FROM pg_temp.pgmi_plan_view p
        JOIN pg_temp.pgmi_source_view s ON s.path = p.path
        WHERE s.is_sql_file AND p.path LIKE './extensions/%'
        ORDER BY p.execution_order
    )
    LOOP
        RAISE NOTICE 'Executing: %', v_file.path;
        EXECUTE v_file.content;
    END LOOP;

    -- Phase 2: Migrations
    RAISE NOTICE '=== Phase 2: Migrations ===';
    FOR v_file IN (
        SELECT p.path, p.content
        FROM pg_temp.pgmi_plan_view p
        JOIN pg_temp.pgmi_source_view s ON s.path = p.path
        WHERE s.is_sql_file AND p.path LIKE './migrations/%'
        ORDER BY p.execution_order
    )
    LOOP
        RAISE NOTICE 'Executing: %', v_file.path;
        EXECUTE v_file.content;
    END LOOP;

    -- Phase 3: Idempotent setup
    RAISE NOTICE '=== Phase 3: Setup ===';
    FOR v_file IN (
        SELECT p.path, p.content
        FROM pg_temp.pgmi_plan_view p
        JOIN pg_temp.pgmi_source_view s ON s.path = p.path
        WHERE s.is_sql_file AND p.path LIKE './setup/%'
        ORDER BY p.execution_order
    )
    LOOP
        RAISE NOTICE 'Executing: %', v_file.path;
        EXECUTE v_file.content;
    END LOOP;
END $$;

When to use:

• Production deployments with mixed requirements
• Extensions and DDL that need different handling
• Clear separation between migration types

Lock management#

Understanding PostgreSQL locks#

DDL operations acquire locks that can block reads and writes:

Lock timeout strategy#

Set aggressive lock timeouts to fail fast rather than queue indefinitely:

-- In your migration files
SET lock_timeout = '5s';  -- Fail if lock not acquired in 5 seconds

ALTER TABLE users ADD COLUMN phone TEXT;

RESET lock_timeout;

Or at the start of deploy.sql:

SET lock_timeout = '10s';
-- ... migrations ...
RESET lock_timeout;

Concurrent index creation#

For large tables, use CONCURRENTLY to avoid blocking.

It does not belong in a migration file. Migration files reach PostgreSQL through EXECUTE v_file.content inside a DO block, and CREATE INDEX CONCURRENTLY cannot be executed from a function — the transaction state is irrelevant. Write it at top level in deploy.sql, after your first COMMIT, where pgmi’s execution contract puts you in psql mode (per-statement autocommit):

-- deploy.sql: Handle concurrent index separately
-- First, run regular migrations in a transaction (the atomic head)
BEGIN;
-- ... regular migrations ...
COMMIT;

-- Then run the concurrent index (psql mode: autocommit, outside any transaction).
-- Reap a previous failed build first: it leaves an INVALID index, and
-- IF NOT EXISTS matches on name alone, so it would skip the rebuild forever.
DO $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM pg_index
        WHERE indexrelid = to_regclass('idx_users_email') AND NOT indisvalid
    ) THEN
        DROP INDEX idx_users_email;
    END IF;
END $$;

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email ON users(email);

-- Finally, continue with remaining work — atomic because it says so
BEGIN;
-- ... remaining migrations ...
COMMIT;

After the first COMMIT, statements are not implicitly grouped: any later phase that must be atomic writes its own BEGIN ... COMMIT, and a mid-script failure keeps the already-autocommitted statements applied — write them idempotently. The DO block above is what makes this one idempotent; see making a concurrent index re-runnable for why neither guard works alone, and the deploy.sql guide for the full contract.

Rollback strategies#

Automatic rollback (transaction-based)#

If you use single-transaction deployment, PostgreSQL rolls back automatically on any error:

-- deploy.sql
BEGIN;
-- ... all migrations via EXECUTE v_file.content ...
COMMIT;
-- If any migration fails, nothing is committed

Compensating transactions#

For deployments where you need to undo specific migrations, create matching rollback scripts:

-- migrations/002_add_email_column.sql
ALTER TABLE users ADD COLUMN email TEXT;

-- rollback/002_add_email_column.sql
ALTER TABLE users DROP COLUMN IF EXISTS email;

Then in deploy.sql, implement rollback capability:

-- deploy.sql with rollback support
DO $$
DECLARE
    v_rollback BOOLEAN := COALESCE(current_setting('pgmi.rollback', true), 'false')::boolean;
    v_file RECORD;
BEGIN
    IF v_rollback THEN
        -- Execute rollback scripts in reverse order
        FOR v_file IN (
            SELECT path, content FROM pg_temp.pgmi_source_view
            WHERE path LIKE './rollback/%' AND is_sql_file
            ORDER BY path DESC
        )
        LOOP
            RAISE NOTICE 'Rolling back: %', v_file.path;
            EXECUTE v_file.content;
        END LOOP;
    ELSE
        -- Normal deployment
        FOR v_file IN (
            SELECT p.path, p.content
            FROM pg_temp.pgmi_plan_view p
            JOIN pg_temp.pgmi_source_view s ON s.path = p.path
            WHERE s.is_sql_file AND p.path LIKE './migrations/%'
            ORDER BY p.execution_order
        )
        LOOP
            RAISE NOTICE 'Executing: %', v_file.path;
            EXECUTE v_file.content;
        END LOOP;
    END IF;
END $$;

Usage:

pgmi deploy . -d mydb --param rollback=true

Exception blocks for error context#

Use PL/pgSQL exception blocks to capture which file failed and provide diagnostic context:

-- deploy.sql with error context
BEGIN;

DO $$
DECLARE
    v_file RECORD;
    v_current_path TEXT;
BEGIN
    FOR v_file IN (
        SELECT p.path, p.content
        FROM pg_temp.pgmi_plan_view p
        JOIN pg_temp.pgmi_source_view s ON s.path = p.path
        WHERE s.is_sql_file
        ORDER BY p.execution_order
    )
    LOOP
        v_current_path := v_file.path;
        RAISE NOTICE 'Running: %', v_file.path;
        BEGIN
            EXECUTE v_file.content;
        EXCEPTION WHEN OTHERS THEN
            RAISE EXCEPTION 'Migration failed on %: %', v_current_path, SQLERRM;
        END;
    END LOOP;
END $$;

COMMIT;

Note: This is all-or-nothing — if any migration fails, the entire transaction rolls back. The exception block provides clear context about which file caused the failure. For true partial progress, see Committing in phases .

Important: PL/pgSQL does not support direct SAVEPOINT commands. If you need savepoint-based isolation (like the test framework provides), use top-level SQL outside of DO blocks, or use BEGIN...EXCEPTION...END blocks which create implicit savepoints for error recovery.

Monitoring and observability#

Deployment progress#

pgmi outputs PostgreSQL RAISE NOTICE messages. Capture them in your CI/CD:

pgmi deploy . -d mydb 2>&1 | tee deployment.log

Custom progress tracking#

Add notices in deploy.sql:

DO $$
DECLARE
    v_file RECORD;
    v_total INT;
    v_count INT := 0;
BEGIN
    SELECT count(*) INTO v_total FROM pg_temp.pgmi_plan_view;

    FOR v_file IN (
        SELECT p.path, p.content
        FROM pg_temp.pgmi_plan_view p
        JOIN pg_temp.pgmi_source_view s ON s.path = p.path
        WHERE s.is_sql_file
        ORDER BY p.execution_order
    )
    LOOP
        v_count := v_count + 1;
        RAISE NOTICE '[%/%] Executing: %', v_count, v_total, v_file.path;
        EXECUTE v_file.content;
    END LOOP;
END $$;

Audit logging#

Log deployments to a table for historical tracking:

-- deploy.sql: Audit logging
DO $$
DECLARE
    v_deployment_id UUID := gen_random_uuid();
    v_file RECORD;
    v_env TEXT := COALESCE(current_setting('pgmi.env', true), 'unknown');
    v_files_count INT;
BEGIN
    SELECT count(*) INTO v_files_count FROM pg_temp.pgmi_plan_view;

    -- Record deployment start
    INSERT INTO audit.deployments (id, started_at, env, files_count)
    VALUES (v_deployment_id, now(), v_env, v_files_count);

    FOR v_file IN (
        SELECT p.path, p.content
        FROM pg_temp.pgmi_plan_view p
        JOIN pg_temp.pgmi_source_view s ON s.path = p.path
        WHERE s.is_sql_file
        ORDER BY p.execution_order
    )
    LOOP
        RAISE NOTICE 'Executing: %', v_file.path;
        EXECUTE v_file.content;

        -- Log file execution
        INSERT INTO audit.deployment_files (deployment_id, file_path, executed_at)
        VALUES (v_deployment_id, v_file.path, now());
    END LOOP;

    -- Record deployment completion
    UPDATE audit.deployments SET completed_at = now() WHERE id = v_deployment_id;
END $$;

Audit and compliance#

Advanced template: runtime security stack#

Scope: advanced template only. This is SQL that pgmi init --template advanced copied into your project, not behaviour of the pgmi binary.

Deployments scaffolded from the advanced template carry a layered runtime defense for the APIs they serve — trusted-gateway identity, session GUCs that fail closed, JIT-provisioned membership, and row-level security:

Advanced template: built-in tracking#

Scope: advanced template only. This is SQL that pgmi init --template advanced copied into your project, not behaviour of the pgmi binary.

The advanced template maintains a persistent execution log in internal.deployment_script_execution_log:

Non-idempotent scripts are skipped on subsequent deployments. The companion view internal.vw_deployment_script provides last execution and execution count per script.

Basic template: stateless#

Scope: basic template only. This describes the deploy.sql that pgmi init --template basic copied into your project, not behaviour of the pgmi binary.

The basic template does not persist execution history. Every deployment re-executes all files (using CREATE OR REPLACE / IF NOT EXISTS for safety). Implement your own tracking in deploy.sql if needed — see the Audit logging section below.

Session transparency#

During deployment, all state is queryable — files (pgmi_source_view), parameters (pgmi_parameter_view), execution plan (pgmi_plan_view), test plan (pgmi_test_plan()). This enables runtime inspection and debugging, though session state does not persist after the connection ends.

Performance considerations#

Timeout configuration#

Set appropriate timeouts for your deployment size:

# Small deployments (default 3 minutes)
pgmi deploy . -d mydb

# Large deployments
pgmi deploy . -d mydb --timeout 30m

# Via pgmi.yaml
# timeout: 30m

Statement timeout#

For individual long-running statements, use PostgreSQL’s statement_timeout:

-- In migration file
SET statement_timeout = '10min';
-- Long-running operation
CREATE INDEX idx_large_table ON large_table(column);
RESET statement_timeout;

Connection pooling#

See Connection Requirements above. Use a direct connection or session-mode pooler for deployments:

# Direct connection for deployment (bypasses pooler)
pgmi deploy . --connection "postgresql://user:pass@db-server:5432/mydb"

# Application traffic goes through pooler
# postgresql://user:pass@pgbouncer:6432/mydb

CI/CD#

The complete pipeline pattern lives in CI/CD : install a pinned, checksum-verified binary; use a direct connection from the CI secret store; pin the session contract with --compat 1; and use --force only to bypass an interactive confirmation. Provider authentication examples are in Connections .

Deployment gates#

Use pgmi’s exit codes for pipeline control:

#!/bin/bash
pgmi deploy . -d mydb
exit_code=$?

case $exit_code in
    0)  echo "Deployment successful" ;;
    10) echo "Configuration error"; exit 1 ;;
    11) echo "Connection failed"; exit 1 ;;
    13) echo "SQL execution failed"; exit 1 ;;
    *)  echo "Unexpected error: $exit_code"; exit 1 ;;
esac

See CLI Reference for all exit codes.

Multi-database deployments#

Sequential deployment#

Deploy to multiple databases in sequence:

for db in db1 db2 db3; do
    echo "Deploying to $db..."
    pgmi deploy . -d $db --param env=production || exit 1
done

Parallel deployment (with caution)#

pgmi deploy . -d db1 --param env=production &
pgmi deploy . -d db2 --param env=production &
pgmi deploy . -d db3 --param env=production &
wait

Warning: Parallel deployment requires that migrations don’t depend on cross-database state.

Disaster recovery#

Pre-deployment backup#

Always backup before production deployments:

pg_dump -Fc mydb > backup_$(date +%Y%m%d_%H%M%S).dump
pgmi deploy . -d mydb

Point-in-time recovery#

If using PostgreSQL’s WAL archiving, note the LSN before deployment:

SELECT pg_current_wal_lsn();
-- Deploy
-- If rollback needed, restore to this LSN

Blue-green deployments#

Deploy to a standby database, then switch:

# 1. Deploy to blue (standby) with tests gating the commit
pgmi deploy . -d mydb_blue --param env=production

# 2. If deployment succeeds (tests passed), switch traffic (application config or DNS)

# 3. Blue becomes production, green becomes standby

Tests run as part of deployment via CALL pgmi_test() in your deploy.sql. If any test fails, the deployment rolls back and traffic stays on the current production database.

Next steps#

• Security Guide — Secrets handling in CI/CD
• Testing Guide — Pre-deployment testing patterns
• Configuration Reference — Timeout configuration and precedence