PGmigrate

PGmigrate is a database migration tool designed for PostgreSQL that supports:

• Transactional and nontransactional migrations: Leverages the full power of PostgreSQL DDL.
• Callbacks: Allows executing DDL at specific steps of the migration process (e.g., dropping code before migrations and recreating it after).
• Online migrations: Enables executing a series of transactional migrations and callbacks within a single transaction, allowing for a simple ROLLBACK to maintain a consistent state if an error occurs.

To run migrations against a non-default schema (useful for SaaS applications using schemas for client separation), use the -m <schema> option.

By default, schema restriction is enabled, which prevents pgmigrate from accessing or modifying relations outside of system schemas and the selected schema.

Note: Some operations, such as relation drops and non-transactional migrations, have limitations with schema restriction. You can disable this check using the --disable_schema_check option.

pgmigrate -m <schema> ...

You can set PostgreSQL session options (like isolation levels or timeouts) before running migrations using the -s CLI flag or the session key in your configuration.

Note: This feature will not work with connection poolers (such as odyssey or pgbouncer) when they are in non-session mode.

Example of setting serializable isolation level and a 30-second lock timeout via CLI:

pgmigrate -s "SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE" \
    -s "SET lock_timeout = '30s'" ...

Install the yandex-pgmigrate package using pip to start using the PostgreSQL migration tool.

pip install yandex-pgmigrate

To run the project tests, you need a running PostgreSQL instance with superuser privileges (required to create and drop databases).

Option 1: Using tox If you have tox installed, run:

tox

Option 2: Using Docker If you have make and docker configured, run:

make test

If your development workflow results in non-sequential migration versions (e.g., version N+2 is merged before N+1), running migrations might skip the missing versions. To prevent this and ensure all migrations are applied in order, use the --check_serial_versions option.

pgmigrate --check_serial_versions ...

By default, PGmigrate complains with pgmigrate.MalformedStatement: Non ascii symbols in file if it detects non-ASCII characters. To allow UTF-8 characters (e.g., for inserting initial data), insert the following modeline at the top of your migration file:

/* pgmigrate-encoding: utf-8 */

In heavy production environments, migrations can be blocked by other queries, creating a lock queue that makes the database unavailable. To mitigate this, use the -l <interval> option.

This starts a separate thread that runs pg_terminate_backend(pid) for every PID blocking any of the pgmigrate connection PIDs every <interval> seconds.

Requirements and Constraints:

• Relies on the pg_blocking_pids() function (available since PostgreSQL 9.6).
• The migration user must have permission to terminate other PIDs (e.g., be the application user or have the pg_signal_backend grant). To terminate superuser PIDs, run pgmigrate as a superuser.
• Warning: Session setup should not manipulate the application_name setting, as the termination logic expects application names in pg_stat_activity to match internal DSN values.

pgmigrate -l <interval> ...

Callbacks are SQL scripts executed at specific points in the migration lifecycle. You can configure them via command-line arguments or a migrations.yml configuration file.

Lifecycle hooks:

• beforeAll: Executed after BEGIN and before the first migration.
• beforeEach: Executed before each migration.
• afterEach: Executed after each migration.
• afterAll: Executed before COMMIT and after the last migration.

CLI Configuration: Use the -a flag to map hook names to file paths.

YAML Configuration: Define callbacks in your migrations.yml file using the following structure:

callbacks:
    beforeAll:
        - callbacks/beforeAll
    beforeEach:
        - callbacks/beforeEach
    afterEach:
        - callbacks/afterEach
    afterAll:
        - callbacks/afterAll
        - grants

You can specify the PostgreSQL connection string using three methods:

1. Command-line argument: Use the -c flag.
2. Configuration file: Use the conn key in migrations.yml.
3. Environment variables: Use standard PostgreSQL environment variables (e.g., PGDATABASE).

Note: If using environment variables, you must explicitly set the connection string to an empty value via the CLI or config file to force psycopg2 to pick up the environment variables.

# CLI
pgmigrate -c 'dbname=foodb user=foo ...'

# Config file
conn: dbname=foodb

# Environment variables (requires empty -c)
PGDATABASE=foodb pgmigrate -c ''

All migration SQL files must follow a specific naming convention to be recognized by PGmigrate. Files that do not match this pattern will be skipped.

Pattern: V<version>__<description>.sql

Example: V0001__Initial_schema_foo.sql

If you have an existing database that was not managed by PGmigrate, you can use the baseline command to tell PGmigrate that the database is already at a specific version. This prevents PGmigrate from attempting to re-run old migrations that would cause errors.

Usage:

1. Use pgmigrate clean to remove existing schema version info if necessary.
2. Use pgmigrate -b <version> baseline to set the current state.

# Set the database as being at version 3
pgmigrate -b 3 baseline