Database Migrations with pgroll

This repository provides an example of a PostgreSQL schema migration using pgroll, a tool designed for zero-downtime migrations.

🛠 Configuration

Before running the commands, you must configure the environment variables to allow pgroll to connect to the database.

Environment Variables

Export the following variables to your terminal or add them to a .env file.

export PGROLL_PG_URL="postgresql://<your_user>:<your_password>@<your_host>:<your_port>/<your_db>?sslmode=<disable|require>"
export PGROLL_SCHEMA="<your_schema>"

Quick load from .env

If you prefer managing configurations via a .env file, you can load them with the following command.

export $(grep -v '^#' .env | xargs)

For details on connection string syntax, refer to the Official CLI Documentation.

🚀 Migration Workflow

Initialization

Required only the first time to prepare the database for pgroll.

pgroll init

Direct Execution

For the simple migrations that do not require an intermediate testing phase, use the --complete flag. This performs the "expand" and "contract" phases in a single step.

Create demo schema

pgroll start postgres-migrations/00_create_demo_schema.yaml --complete

Create users table

pgroll start postgres-migrations/01_create_users_table.yaml --complete

Add columns

pgroll start postgres-migrations/02_add_past_due_column.yaml --complete
pgroll start postgres-migrations/03_add_canceled_column.yaml --complete

All-in-one

It is also possible to migrate all configurations in the migrations directory with a single command.

pgroll migrate --complete

Populate Table

To carry out a realistic test, it is necessary to fill the database with a few million records.

You can run the SQL script populate.sql under postgres-scripts directory.

Zero-Downtime migration

For the critical changes, follow the two-step flow to ensure application compatibility:

Start the migration

This creates the new version of the schema while keeping the old one active.

pgroll start postgres-migrations/04_add_status_column.yaml

Verify

Check the pgroll status.

pgroll status

Finalize or Rollback:

pgroll complete  # If everything is working correctly
# OR
pgroll rollback  # If you encounter issues

Clean Old Columns

Once migration is complete you can drop safely unused columns.

pgroll start postgres-migrations/05_drop_columns.yaml --complete