Database schema changes are a fact of life for any growing application. But if you’re running your own Postgres—whether in Docker, on a VM, or using a managed cloud database—schema migrations can be risky. Traditional migration tools often lock tables, cause downtime, and make rollbacks a nightmare. For teams that need high availability, this simply isn’t good enough.

pgroll is an open-source CLI tool that brings zero-downtime, reversible schema migrations to any Postgres instance, no matter where it runs. This guide will show you how to use pgroll for safe, continuous database changes—without service interruptions or lock-induced headaches.

What is pgroll?

pgroll is a purpose-built migration tool for Postgres that enables you to:

• Apply schema changes without downtime or table locks

• Instantly roll back in-progress migrations if something goes wrong

• Let old and new schema versions coexist, so you can upgrade apps at your own pace

pgroll achieves this by using the expand/contract migration pattern and managing multiple schema versions under the hood. It’s a single Go binary, works with any Postgres 14+ instance (including RDS, Aurora, DigitalOcean, GCP, or your own containers), and requires no special extensions or plugins.

Why Not Use Traditional Migration Tools?

Classic migration tools (like Flyway, Liquibase, or plain SQL scripts) often:

• Acquire ACCESS EXCLUSIVE locks, blocking reads/writes during DDL

• Require downtime windows and careful coordination

• Make rollbacks hard—often requiring a full DB restore

• Don’t support phased rollouts or canary deployments

pgroll solves these problems by:

• Never locking tables for long periods

• Allowing both old and new schema versions to be used at the same time

• Making rollbacks instant and safe, even after a migration has started

How pgroll Works

pgroll uses the expand/contract pattern, but automates all the complexity for you:

• Expand: Only backward-compatible changes are applied first (e.g., adding nullable columns, helper columns for backfills, etc.).

• Multi-version: Both the old and new schema versions are available as Postgres schemas (with views mapping to the same underlying tables).

• Contract: Once your new app version is live, pgroll finalizes the migration, cleans up old columns/constraints, and removes the old schema version.

• Rollback: You can revert to the previous schema version at any time before completing the migration.

This means you can deploy new app versions that use the new schema, while old app versions continue to work—no downtime, no dual-write hacks in your codebase

Getting Started with pgroll

Prerequisites

• A running Postgres database (anywhere: Docker, VM, or managed service)

• pgroll CLI installed (see below)

Step 1: Install pgroll

For most Linux/macOS systems, download the prebuilt binary:

curl -L https://github.com/xataio/pgroll/releases/latest/download/pgroll-$(uname -s)-$(uname -m) -o /usr/local/bin/pgroll
chmod +x /usr/local/bin/pgroll

Or use Homebrew on macOS:

brew tap xataio/pgroll
brew install pgroll

Or build from source:

go install github.com/xataio/pgroll

Step 2: Initialize pgroll on Your Database

pgroll needs a dedicated schema (default: pgroll) to store its state. Run:

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

This works for any Postgres instance—local, cloud, or containerized

Running Your First Migration

Step 3: Define a Migration

Write your desired schema state in a YAML or JSON file. Example: creating a users table.

migrations/01_create_users.yaml

operations:
  - create_table:
      name: users
      columns:
        - name: id
          type: serial
          pk: true
        - name: name
          type: varchar(255)
          unique: true
        - name: description
          type: text
          nullable: true

Step 4: Start the Migration

pgroll start migrations/01_create_users.yaml --postgres-url "postgres://user:password@host:port/dbname" --complete

• For the first migration, you can use --complete to apply everything in one go.

• For breaking changes, you’ll use a two-phase workflow (see below).

Example: Zero-Downtime Breaking Change

Suppose you want to make description NOT NULL—a classic breaking change.

migrations/02_make_description_not_null.yaml

operations:
  - alter_column:
      table: users
      column: description
      nullable: false
      up: SELECT COALESCE(description, 'No description provided')
      down: description

Start the migration (expand phase):

pgroll start migrations/02_make_description_not_null.yaml --postgres-url "postgres://user:password@host:port/dbname"

pgroll will:

• Create a helper column and backfill it safely, without blocking writes

• Set up triggers to keep both columns in sync

• Expose a new schema version with the updated column

Deploy your new app version to use the new schema (see next section).

Complete the migration (contract phase):

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

If you need to roll back before completion:

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

How to Use Multiple Schema Versions in Your App

pgroll creates versioned schemas (e.g., public_01_initial, public_02_make_description_not_null) with views that map to the real tables. To target a specific schema version, set the search_path in your database connection.

Example: Node.js (pg)

const { Pool } = require('pg');
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

const schema = process.env.PGROLL_SCHEMA_VERSION || 'public';

async function getUsers() {
  const client = await pool.connect();
  try {
    await client.query(`SET search_path TO ${schema}`);
    const res = await client.query('SELECT * FROM users');
    console.log(res.rows);
  } finally {
    client.release();
  }
}

Get the latest schema version:

export PGROLL_SCHEMA_VERSION=$(pgroll latest --with-schema --postgres-url "postgres://user:password@host:port/dbname")

Update your app’s config or deployment to use this schema version for phased rollouts.

Integrating with CI/CD and ORMs

• Use pgroll in your deployment pipelines to automate migrations.

• Generate migration files from your ORM’s SQL output, then convert them to pgroll format if needed.

• Always review generated migrations for breaking changes and backfill logic.