Custom Migration Steps

pgschema generates the plan from the diff between your schema files and the database, but the diff engine cannot infer everything. The most common case is a schema change that requires touching existing user data, such as backfilling a new column. Declare the end state in your schema file, then edit the generated plan to add the steps by hand.

Rows of config tables (lookups, settings, feature flags) do not need this: list them in pgschema.toml, keep their rows in CSV files loaded by \copy, and pgschema plans the row changes for you. See Config Data. This page covers one-off changes to data that pgschema does not own, and any other step the diff cannot produce.

This works because the plan’s source_fingerprint pins the database state the plan was generated against, not the plan’s SQL. You can freely edit the steps; pgschema apply still detects if the database has drifted since planning.

Example: add a NOT NULL column with backfill

Desired state in schema.sql:

CREATE TABLE users (
    id bigint PRIMARY KEY,
    email text NOT NULL,
    email_domain text NOT NULL
);

Generate Plan

pgschema plan --host localhost --db myapp --user postgres \
  --file schema.sql --output-json plan.json

The generated plan adds the column directly, which would fail on a non-empty table:

{
  "groups": [
    {
      "steps": [
        {
          "sql": "ALTER TABLE users ADD COLUMN email_domain text NOT NULL;",
          "type": "table.column",
          "operation": "create",
          "path": "public.users.email_domain"
        }
      ]
    }
  ]
}

Edit Plan

Rewrite the step to add the column as nullable first, insert a backfill step, then enforce the constraint. Only the sql field is required for hand-added steps:

{
  "groups": [
    {
      "steps": [
        {
          "sql": "ALTER TABLE users ADD COLUMN email_domain text;",
          "type": "table.column",
          "operation": "create",
          "path": "public.users.email_domain"
        },
        {
          "sql": "UPDATE users SET email_domain = split_part(email, '@', 2);"
        },
        {
          "sql": "ALTER TABLE users ALTER COLUMN email_domain SET NOT NULL;"
        }
      ]
    }
  ]
}

Steps within a group execute in a single transaction, so the backfill and constraint either all succeed or roll back together.

Apply Plan

pgschema apply --host localhost --db myapp --user postgres --plan plan.json

After apply, the database matches schema.sql, so subsequent plans are empty — the data migration naturally runs only once.

For large tables, run the backfill in batches outside the transaction to avoid long locks. Put ADD COLUMN in one plan, batch the UPDATE with your own tooling, then run a second plan for SET NOT NULL. See Online DDL for lock-aware patterns.

The same technique applies to any change the diff engine cannot infer, such as rewriting a rename from DROP + ADD into ALTER TABLE ... RENAME, or resolving a DELETE on a config table that is blocked by a foreign key from user data.

Getting started

CLI Reference

Workflow

Configuration

Example: add a NOT NULL column with backfill

​Example: add a NOT NULL column with backfill

Example: add a NOT NULL column with backfill