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.

Pooler Mode	Compatible	Why
Session	Yes	Connection stays with one backend for the entire session
Transaction	No	Backend may change between transactions — `pg_temp` state lost
Statement	No	Backend may change between statements — `pg_temp` state lost

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:

Layer	Min PostgreSQL	What drives the floor
pgmi core / CLI	11+	Session-scoped temp tables and a session-mode connection (no transaction pooler). Set deliberately in the Go core.
basic template	11+	Plain SQL migrations — no extensions, roles, or version-specific syntax.
advanced template	15+	`WITH (security_invoker = true)` views (PostgreSQL 15) underpin the RLS model. Also needs a role that can create roles, schemas, and extensions (`CREATEROLE` + `CREATE EXTENSION`) and the `uuid-ossp` / `pgcrypto` / `pg_trgm` / `hstore` extensions. No superuser required.
API / MCP features	15+	Ship inside the advanced template, so they share its floor.

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.

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:

CREATEROLE — the advanced template creates database_admin, database_api, database_customer roles. The deploy role needs CREATEROLE (the admin role every managed provider grants — rds_superuser, azure_pg_admin, supabase_admin, Cloud SQL’s cloudsqlsuperuser — has it).
CREATE EXTENSION for uuid-ossp, pgcrypto, pg_trgm, hstore. All four are on every major managed provider’s default whitelist.
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.

Provider	Basic template	Advanced template	Notes
Self-hosted / Docker / Kubernetes	✅	✅	Full control; nothing special required.
AWS RDS for PostgreSQL	✅	✅	`rds_superuser` has `CREATEROLE` and installs the four whitelisted extensions. No event trigger needed.
AWS Aurora PostgreSQL	✅	✅	Same as RDS.
Azure Database for PostgreSQL — Flexible Server	✅	✅	`azure_pg_admin` covers `CREATEROLE` + extension install.
Azure Cosmos DB for PostgreSQL (formerly Citus)	✅	⚠️	Citus-specific semantics; advanced template not validated against sharded tables.
Google Cloud SQL for PostgreSQL	✅	✅	`cloudsqlsuperuser` grants `CREATEROLE` and the whitelisted extensions.
Google AlloyDB	✅	✅	Inherits Cloud SQL’s admin model.
Supabase	✅	✅	`supabase_admin` covers role + extension creation.
Neon	✅	✅	The project’s owner role has `CREATEROLE` and can install the extensions.
Railway / Render / Fly.io (managed instances)	✅	✅	The provisioned owner role can create roles, schemas, and extensions.

Verify the deploy role’s grants, not superuser status: it needs CREATEROLE, CREATE SCHEMA, and CREATE EXTENSION for the four listed extensions. On a managed provider, deploy with that provider’s admin role (rds_superuser, azure_pg_admin, supabase_admin, etc.) — it carries all three. If a provider locks one of the four extensions out of its whitelist, remove that extension’s CREATE EXTENSION line and the features depending on it.

Deployment strategies#

Single-transaction deployment#

All changes succeed or fail together. Maximum safety, but holds locks longer.

-- deploy.sql
BEGIN;

DO $$
DECLARE
    v_file RECORD;
BEGIN
    FOR v_file IN (
        SELECT path, content FROM pg_temp.pgmi_plan_view
        ORDER BY 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 path, content FROM pg_temp.pgmi_plan_view
        ORDER BY 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.

Per-file commits (when you really need them)#

True per-file commits are complex in pgmi’s model because:

You can’t use COMMIT inside PL/pgSQL DO blocks (only inside procedures via CREATE PROCEDURE + CALL)
Each top-level SQL statement auto-commits when not in a transaction
Top-level SQL has no procedural constructs (no loops, no variables) — so you can’t iterate pgmi_source_view outside a DO block

Recommended approach: Use idempotent migrations with single-transaction deployment. If deployment fails, fix the issue and redeploy — pgmi will re-run idempotent scripts safely.

If you genuinely need per-file commits (e.g., very large data migrations that can’t fit in one transaction), consider:

External orchestration: Run pgmi deploy multiple times with different --param values to control which phase runs
Idempotent scripts with tracking: Each script checks a tracking table before running (see Metadata Guide for UUID-based tracking)
Split into separate projects: One pgmi project per logical phase, deployed sequentially

Why this matters: pgmi’s session-based model intentionally keeps everything in one transaction for atomicity. Breaking this requires moving orchestration outside of deploy.sql.

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 path, content FROM pg_temp.pgmi_plan_view
        WHERE path LIKE './extensions/%'
        ORDER BY 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 path, content FROM pg_temp.pgmi_plan_view
        WHERE path LIKE './migrations/%'
        ORDER BY 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 path, content FROM pg_temp.pgmi_plan_view
        WHERE path LIKE './setup/%'
        ORDER BY 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:

Operation	Lock type	Blocks
`CREATE TABLE`	AccessExclusiveLock	Everything on new table
`ALTER TABLE ADD COLUMN`	AccessExclusiveLock	All operations on table
`CREATE INDEX`	ShareLock	Writes (not reads)
`CREATE INDEX CONCURRENTLY`	ShareUpdateExclusiveLock	Other DDL only

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:

-- 003_add_user_email_index.sql
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email ON users(email);

Note: CREATE INDEX CONCURRENTLY cannot run inside a transaction. Structure your deploy.sql accordingly:

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

-- Then run the concurrent index (outside transaction)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email ON users(email);

-- Finally, continue with remaining work
BEGIN;
-- ... remaining migrations ...
COMMIT;

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 path, content FROM pg_temp.pgmi_plan_view
            WHERE path LIKE './migrations/%'
            ORDER BY 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 path, content FROM pg_temp.pgmi_plan_view
        ORDER BY 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 Per-file commits .

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 path, content FROM pg_temp.pgmi_plan_view
        ORDER BY 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 path, content FROM pg_temp.pgmi_plan_view
        ORDER BY 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: built-in tracking#

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

Column	Description
`deployment_script_object_id`	UUID from `<pgmi-meta>` (or auto-generated from path)
`file_path`	File path at execution time
`idempotent`	Whether script was re-runnable
`deployment_script_content_checksum`	Content hash at execution time
`sort_key`	Execution ordering key
`xact_id`	PostgreSQL transaction ID (correlates with WAL)
`executed_at`	Timestamp
`executed_by`	Database role that ran the script

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#

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 patterns#

GitHub Actions#

deploy:
  runs-on: ubuntu-latest
  env:
    PGMI_VERSION: v0.11.0         # pin to a specific release tag
  steps:
    - uses: actions/checkout@v4

    - name: Install pgmi (pinned, checksum-verified)
      run: |
        file="pgmi_${PGMI_VERSION#v}_linux_amd64.tar.gz"
        base="https://github.com/vvka-141/pgmi/releases/download/${PGMI_VERSION}"
        curl -fsSLO "${base}/${file}"
        curl -fsSLO "${base}/checksums.txt"
        sha256sum --ignore-missing -c checksums.txt
        tar -xzf "${file}" pgmi
        sudo install pgmi /usr/local/bin/pgmi

    - name: Deploy
      env:
        PGMI_CONNECTION_STRING: ${{ secrets.DATABASE_URL }}
      run: |
        pgmi deploy . -d ${{ vars.DATABASE_NAME }} --compat 1 --force \
          --param env=production \
          --timeout 15m

GitLab CI#

deploy:
  stage: deploy
  image: ubuntu:latest
  variables:
    PGMI_VERSION: v0.11.0
    PGMI_CONNECTION_STRING: $DATABASE_URL
  before_script:
    - |
      file="pgmi_${PGMI_VERSION#v}_linux_amd64.tar.gz"
      base="https://github.com/vvka-141/pgmi/releases/download/${PGMI_VERSION}"
      curl -fsSLO "${base}/${file}"
      curl -fsSLO "${base}/checksums.txt"
      sha256sum --ignore-missing -c checksums.txt
      tar -xzf "${file}" pgmi
      install pgmi /usr/local/bin/pgmi
  script:
    - pgmi deploy . -d $DATABASE_NAME --compat 1 --force --param env=production

Azure DevOps#

steps:
  - task: AzureCLI@2
    inputs:
      azureSubscription: 'my-service-connection'
      scriptType: 'bash'
      scriptLocation: 'inlineScript'
      inlineScript: |
        pgmi deploy . -d $DATABASE_NAME \
          --host $PGHOST \
          --azure \
          --sslmode require \
          --compat 1 --force \
          --param env=production \
          --timeout 15m

GitHub Actions (Azure)#

deploy:
  runs-on: ubuntu-latest
  permissions:
    id-token: write
    contents: read
  steps:
    - uses: actions/checkout@v4

    - uses: azure/login@v2
      with:
        client-id: ${{ secrets.AZURE_CLIENT_ID }}
        tenant-id: ${{ secrets.AZURE_TENANT_ID }}
        subscription-id: ${{ secrets.AZURE_SUBSCRIPTION_ID }}

    - name: Deploy
      run: |
        pgmi deploy . -d ${{ vars.DATABASE_NAME }} \
          --host ${{ vars.AZURE_PG_HOST }} \
          --azure \
          --sslmode require \
          --compat 1 --force \
          --param env=production \
          --timeout 15m

GitHub Actions (AWS)#

deploy:
  runs-on: ubuntu-latest
  permissions:
    id-token: write
    contents: read
  steps:
    - uses: actions/checkout@v4

    - uses: aws-actions/configure-aws-credentials@v4
      with:
        role-to-assume: ${{ secrets.AWS_ROLE_ARN }}
        aws-region: ${{ vars.AWS_REGION }}

    - name: Install pgmi
      env:
        PGMI_VERSION: v0.11.0
      run: |
        file="pgmi_${PGMI_VERSION#v}_linux_amd64.tar.gz"
        base="https://github.com/vvka-141/pgmi/releases/download/${PGMI_VERSION}"
        curl -fsSLO "${base}/${file}"
        curl -fsSLO "${base}/checksums.txt"
        sha256sum --ignore-missing -c checksums.txt
        tar -xzf "${file}" pgmi
        sudo install pgmi /usr/local/bin/pgmi

    - name: Deploy
      run: |
        pgmi deploy . -d ${{ vars.DATABASE_NAME }} \
          --host ${{ vars.RDS_HOST }} \
          -U ${{ vars.RDS_USER }} \
          --aws --aws-region ${{ vars.AWS_REGION }} \
          --sslmode require \
          --force \
          --param env=production \
          --compat 1 \
          --timeout 15m

GitHub Actions (GCP)#

deploy:
  runs-on: ubuntu-latest
  permissions:
    id-token: write
    contents: read
  steps:
    - uses: actions/checkout@v4

    - uses: google-github-actions/auth@v2
      with:
        workload_identity_provider: ${{ secrets.GCP_WORKLOAD_IDENTITY_PROVIDER }}
        service_account: ${{ secrets.GCP_SERVICE_ACCOUNT }}

    - name: Install pgmi
      env:
        PGMI_VERSION: v0.11.0
      run: |
        file="pgmi_${PGMI_VERSION#v}_linux_amd64.tar.gz"
        base="https://github.com/vvka-141/pgmi/releases/download/${PGMI_VERSION}"
        curl -fsSLO "${base}/${file}"
        curl -fsSLO "${base}/checksums.txt"
        sha256sum --ignore-missing -c checksums.txt
        tar -xzf "${file}" pgmi
        sudo install pgmi /usr/local/bin/pgmi

    - name: Deploy
      run: |
        pgmi deploy . -d ${{ vars.DATABASE_NAME }} \
          -U ${{ vars.CLOUDSQL_USER }} \
          --google --google-instance ${{ vars.CLOUDSQL_INSTANCE }} \
          --force \
          --param env=production \
          --compat 1 \
          --timeout 15m

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