Make your first schema change with pglifecycle.

Step 02

Pull the database into a project

pull reads the database and writes one YAML file for each object. The project directory contains project.yaml and directories for tables, views, functions, roles, and other object types. Objects that belong to a schema are grouped by schema.

Commit the project before changing it. This gives you a baseline for the changes that follow.

• Skip objects you do not want to own with -N/--exclude-schema, -T/--exclude-table, and --exclude-extension. Each is repeatable.
• Cluster roles and users come from pg_dumpall and are included by default. Leave them out with --no-roles, or leave privileges out entirely with -x.
• Password hashes are omitted unless you pass --include-password-hashes. They are written to the project as plain text, so only enable it for a repository you trust.
• To pull from an existing pg_dump -Fc file instead of a live database, pass it with -D. Roles are skipped because there is no cluster to read them from.

$ pglifecycle pull -h localhost -U postgres -d app_production schema/
pglifecycle v2.0.0-alpha.1 Creating app_production@localhost → schema/

Created schema/ with 412 objects:

    4  schemas         38  sequences        11  views
    2  extensions      37  tables            1  materialized view
    3  types           26  functions        14  users
                                             9  roles

$ git init -q # if this is a new repository
$ git add schema/
$ git commit -qm "Pull app_production into schema/"

Step 04

Preview the DDL

deploy compares the project with a database and generates the DDL needed to make them match. Without --apply, it does not change the database. The DDL is written to standard output or to the file specified by -o.

Add -v to show a summary of the plan. Log messages go to standard error, leaving standard output available for the generated SQL.

• Changes are reconciled in place where PostgreSQL allows it: columns, constraints, indexes, triggers, CREATE OR REPLACE for functions and views, ALTER TYPE … ADD VALUE for enums.
• Drops and drop-and-recreate fallbacks are left out unless you pass --allow-drop. Each one excluded is reported as a warning.
• Preview against a dump instead of a live database with -D, or leave grants out of the comparison with -x.

If you want to review the SQL in the pull request, write it to a file and commit it with the YAML change.

$ pglifecycle deploy -v -d app_production schema/
  INFO  pglifecycle v2.0.0-alpha.1 running deploy
  INFO  Comparing app_production against localhost:5432/app_production
  INFO  Plan: 2 statement(s) included, 0 excluded

-- pglifecycle deploy
-- project: app_production
-- source: localhost:5432/app_production
-- destructive statements: none

-- TABLE public.users
ALTER TABLE public.users
    ADD COLUMN last_login_at timestamp with time zone;

-- INDEX public.users_last_login_at_idx
CREATE INDEX users_last_login_at_idx ON public.users USING btree (last_login_at);

$ pglifecycle deploy -d app_production -o deploy.sql schema/

# or compare against a dump rather than a live database
$ pglifecycle deploy -D app_production.dump -o deploy.sql schema/

Git and CI

Where Git fits

A pglifecycle project is a directory of text files, so it works with the same Git workflow you use for code:

1. Create a branch, edit the object's YAML, and commit the change.
2. For the pull request, run deploy without --apply against a copy of the target database. Review the generated DDL with the YAML change.
3. On merge, run the same command with --apply.
4. Run pull --update against the live database periodically. A non-empty git diff shows a change made outside the project.

deploy compares the project with the database's current state. It does not replay a sequence of migrations.

A GitHub Action for installing pglifecycle is in progress. Until it is available, install a release binary or use the container image in your job.

name: schema
on: [pull_request]

jobs:
  preview:
    runs-on: ubuntu-latest
    container: ghcr.io/gmr/pglifecycle:latest
    steps:
      - uses: actions/checkout@v4
      - name: Preview the DDL
        env:
          PGHOST: ${{ secrets.PGHOST }}
          PGUSER: ${{ secrets.PGUSER }}
          PGDATABASE: app_staging
          PGPASSWORD: ${{ secrets.PGPASSWORD }}
        run: pglifecycle deploy -v -o deploy.sql schema/
      - uses: actions/upload-artifact@v4
        with:
          name: deploy-sql
          path: deploy.sql

$ git checkout -b users-last-login
$ $EDITOR schema/tables/public/users.yaml
$ pglifecycle deploy -d app_staging schema/ # read before committing
$ git commit -am "Track last_login_at on users"
$ git push -u origin users-last-login