pgroll Documentation

pgroll uses a declarative approach to migrations instead of requiring direct SQL scripts. This provides several safety benefits:

• Automated safety steps: pgroll implements the necessary sequence of steps to perform a schema change safely.
• Minimized lock contention: The tool ensures that any locks required on affected database objects are held for the shortest possible duration, reducing the risk of blocking application traffic.

Unlike traditional migration tools, pgroll enables zero-downtime schema changes by maintaining two versions of the schema simultaneously: the version before the change and the version after the change. This allows for:

• Side-by-side rollouts: Run new application versions that require the updated schema alongside old application versions that are incompatible with it.
• Version selection: Applications can explicitly select which schema version they want to interact with during a rollout.

pgroll enables side-by-side schema versions by creating a separate Postgres schema for each migration. It uses Postgres views to expose specific tables or columns to client applications based on the schema version they are configured to use.

Key Mechanisms:

• Renaming Columns: pgroll creates a new schema with a view that uses the new column name. The old schema remains untouched until the complete phase, where the old schema is dropped and the actual column is renamed.
• Complex Constraints (e.g., NOT NULL): For changes like adding a NOT NULL constraint, pgroll duplicates the affected column and backfills it. It uses triggers or backfills to keep the old and new columns in sync, allowing both versions to coexist until the migration is completed.

To drop a column from an existing table, define a drop_column operation. You must provide the table name, the column name to be removed, and a down SQL expression. The down field is mandatory because it is used to backfill the column with data if you need to revert to the previous schema version while a migration is active.

For example, if you drop a price column, you can provide a down expression that sets the value to 0 for any new rows inserted against the new schema, ensuring compatibility with the old schema during the migration period.

drop_column:
  table: name of table
  column: name of column to drop
  down: SQL expression

Once a migration has started, client applications can access the new schema version by setting their PostgreSQL search_path to the new schema version name. The specific name is provided in the output of the pgroll start command.

Example:

SET search_path TO 'public_initial_migration';

On macOS or Linux, you can install pgroll using Homebrew by tapping the official repository and then installing the package.

# macOS or Linux
brew tap xataio/pgroll
brew install pgroll

To rename a table, define a rename_table operation in your migration file. The table remains accessible via its old name in the old schema version and becomes accessible via its new name in the new schema version. The actual renaming of the table in the database occurs only upon successful migration completion.

Structure of the operation:

• from: The current name of the table.
• to: The desired new name for the table.

Before using pgroll, you must initialize the database. This creates internal tables to track the current schema version and version history.

Use the init command with the --postgres-url flag.

pgroll --postgres-url postgres://user:password@host:port/dbname init

To rename a constraint, use the rename_constraint operation. The constraint will retain its old name during the active migration period and will only be renamed to the new name upon migration completion.

Structure:

• table: The name of the table containing the constraint.
• from: The current name of the constraint.
• to: The desired new name for the constraint.

{
  "rename_constraint": {
    "table": "table name",
    "from": "old constraint name",
    "to": "new constraint name"
  }
}