Data Migration

pgschema manages schema state, not data. When a schema change requires touching existing data (e.g. backfilling a new column), declare the end state in your schema file, then edit the generated plan to insert the data migration steps. 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 seeding lookup-table rows alongside a new table.

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