# pgroll — Zero-Downtime PostgreSQL Schema Migrations

> A Go-based migration tool for PostgreSQL that uses expand-and-contract patterns to apply schema changes without locking tables or breaking running applications.

## Install

Save in your project root:

# pgroll — Zero-Downtime PostgreSQL Schema Migrations

## Quick Use
```bash
# Install
go install github.com/xataio/pgroll@latest

# Start a migration
pgroll start 001_add_email.json --postgres-url "postgres://user:pass@localhost/mydb"

# Complete when ready
pgroll complete --postgres-url "postgres://user:pass@localhost/mydb"
```

## Introduction
pgroll is a schema migration tool for PostgreSQL that eliminates downtime during DDL changes. It applies the expand-and-contract pattern automatically: creating new columns/tables alongside old ones, syncing data via triggers, and cleaning up once all clients have switched over.

## What pgroll Does
- Applies schema migrations without locking tables or blocking reads/writes
- Manages dual-version schemas so old and new application code can run simultaneously
- Auto-generates triggers that keep old and new columns in sync during transitions
- Tracks migration state in a dedicated schema within the target database
- Supports rollback by simply cancelling the incomplete migration

## Architecture Overview
pgroll operates in two phases. During "start," it creates new schema objects (columns, tables, constraints) and installs row-level triggers that replicate writes between old and new shapes. During "complete," it drops the old objects and removes triggers. A version schema provides each application version its own search_path view.

## Self-Hosting & Configuration
- Install via Go: `go install github.com/xataio/pgroll@latest`
- Define migrations in JSON files with a declarative operations DSL
- Point to your database via `--postgres-url` or `PGROLL_PG_URL` env var
- State is stored in a `pgroll` schema inside the target database
- Run in CI by chaining `pgroll start` followed by `pgroll complete` after deploy

## Key Features
- True zero-downtime: no ACCESS EXCLUSIVE locks during migration
- Automatic trigger generation for column transforms and renames
- Versioned views let old and new code read the schema they expect
- JSON-based migration format is easy to review in pull requests
- Supports adding NOT NULL columns, changing types, and renaming without downtime

## Comparison with Similar Tools
- **Flyway** — sequential SQL scripts but requires maintenance windows for breaking changes
- **Atlas** — declarative schema-as-code, supports expand/contract via versioned schemas
- **golang-migrate** — simple up/down SQL files, no zero-downtime strategy built in
- **Sqitch** — change-management focused, no automatic dual-schema support
- **pg_squeeze** — optimizes table bloat, not a general migration tool

## FAQ
**Q: Does pgroll work with managed PostgreSQL services like RDS or Cloud SQL?**
A: Yes. It only requires standard DDL and trigger permissions, no superuser access.

**Q: Can I write migrations in SQL instead of JSON?**
A: pgroll uses a JSON DSL for its operations. Raw SQL can be embedded in a "sql" operation type for custom statements.

**Q: What happens if I need to roll back?**
A: Run `pgroll rollback` before completing. It drops the new objects and removes triggers, restoring the original schema.

**Q: How does it handle data backfills?**
A: Triggers sync new writes automatically. For existing rows, pgroll can run a backfill during the start phase in batches.

## Sources
- https://github.com/xataio/pgroll
- https://pgroll.com/docs

---
Source: https://tokrepo.com/en/workflows/asset-d4e550bd
Author: AI Open Source