pgevolve

Postgres-specific declarative schema management.

pgevolve treats a directory of CREATE-style SQL files as the source of truth for one or more Postgres schemas, introspects a live database to derive its current state, and computes ordered, dependency-aware migration plans that bring the database to the desired state. It refuses to lose data unless explicitly authorized in a per-plan intent file.

Current release: v0.3.9 (Postgres 14–18). See the Changelog for per-release detail.

What to read

• New? Start with Install and Quick start.
• Day-to-day user? Commands, Configuration, Cookbook, and Troubleshooting.
• Asking "does pgevolve support X?" — the Reference section is the living capability catalogue. Every claimed feature has a fixture; status legend at the top of each spec file.
• Curious how it works? Architecture explains the parser → IR → diff → planner → executor pipeline.
• Considering contributing? Read the Constitution, the v1.0 charter, and any recent Specs + Plans to see how features get designed and shipped.

Project links

• Source: github.com/saosebastiao/pgevolve
• Library API: docs.rs/pgevolve-core
• Issues: GitHub Issues

License

Dual-licensed under MIT or Apache-2.0.

Installation

v0.3.x is pre-release; the only supported install is from source.

Prerequisites

• Rust 1.95 or newer. The repo pins 1.95 in rust-toolchain.toml, so rustup will pick it up automatically.
• Docker (optional). Required only for pgevolve validate --shadow, for the tier-3/4/5 test suites, and for any local property-test runs.
• Postgres 14–18. pgevolve introspects through pg_catalog; major versions outside this range are not tested.

Build the binary

git clone https://github.com/saosebastiao/pgevolve.git
cd pgevolve
cargo build --release -p pgevolve

The release binary lands at target/release/pgevolve. Add it to your PATH (or copy it to ~/.local/bin/, /usr/local/bin/, etc.):

install -m 0755 target/release/pgevolve ~/.local/bin/pgevolve
pgevolve --version

Verify the install

A no-Postgres smoke check:

pgevolve --help
pgevolve init --dir /tmp/pgevolve-smoke
ls /tmp/pgevolve-smoke
# → pgevolve.toml  schema/  plans/  .gitignore

If you have Docker available and want to verify the shadow path works end-to-end:

cd /tmp/pgevolve-smoke
echo '[shadow]
backend          = "testcontainers"
postgres_version = "16"' >> pgevolve.toml
mkdir -p schema/app
cat > schema/app/0001-init.sql <<'SQL'
-- @pgevolve schema=app
CREATE SCHEMA app;
CREATE TABLE app.users (
    id    bigint NOT NULL,
    email text   NOT NULL,
    CONSTRAINT users_pkey PRIMARY KEY (id)
);
SQL
pgevolve validate --shadow
# → pgevolve validate --shadow: round-trip matched (1 object(s))

Upgrading

Until a stable release ships, upgrade by pulling and rebuilding:

cd pgevolve
git pull
cargo build --release -p pgevolve
install -m 0755 target/release/pgevolve ~/.local/bin/pgevolve

The pgevolve metadata schema is upgraded idempotently by every command that touches the DB, so there's no separate migration step on upgrade.

Pre-built binaries / package managers

cargo install pgevolve (the canonical install path described above) is published to crates.io as of v0.3.3. The following additional distribution channels are not yet shipped:

• GitHub-release prebuilt binaries for Linux x86_64 / macOS arm64 / Windows x86_64 — planned for v1.0.
• Homebrew formula — under consideration; no commitment.
• Docker image — under consideration; no commitment.

If you depend on one of these channels, open an issue saying so — demand drives prioritization.

Getting started

A walkthrough of pgevolve's full loop on a new project: scaffold → author SQL → plan → review → apply → status. Expect 5-10 minutes.

1. Initialize a project

mkdir myapp && cd myapp
pgevolve init

This creates:

myapp/
├── .gitignore
├── pgevolve.toml      ← project configuration
├── plans/             ← future plan directories live here
└── schema/            ← your SQL goes here

Open pgevolve.toml and edit at least [environments.dev].url to a DSN you can connect to. For the rest of the walkthrough we'll assume:

[project]
name           = "myapp"
schema_dir     = "schema"
plan_dir       = "plans"
layout_profile = "schema-mirror"

[managed]
schemas        = ["app"]

[planner]
strategy = "online"

[environments.dev]
url = "postgres://postgres@localhost:5432/myapp_dev"

Heads-up. pgevolve apply writes to the database. Use a throwaway database for this walkthrough — or run a local one via Docker: docker run --rm -d -p 5432:5432 -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=myapp_dev postgres:16.

2. Author the first version of the schema

The schema-mirror layout profile wants schema/<schema>/<kind>/<name>.sql. For a users table in schema app:

mkdir -p schema/app/tables
mkdir -p schema/app/_schema  # the `_schema.sql` lives at schema/app/

Create schema/app/_schema.sql:

-- @pgevolve schema=app
CREATE SCHEMA app;

Create schema/app/tables/users.sql:

-- @pgevolve schema=app
CREATE TABLE app.users (
    id         bigint      NOT NULL,
    email      text        NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT users_pkey PRIMARY KEY (id)
);

The -- @pgevolve schema=app directive lets pgevolve associate any unqualified objects in the file with the app schema (the CREATE TABLE here is already qualified, so the directive is mainly future- proofing).

3. Lint and (optionally) shadow-validate

Quick check that your source parses and obeys the layout profile:

pgevolve lint
# pgevolve lint: 0 findings

If you have Docker available, you can round-trip the IR through an ephemeral Postgres to catch normalization surprises before they hit your real database:

# Add a [shadow] block to pgevolve.toml first:
echo '
[shadow]
backend          = "testcontainers"
postgres_version = "16"' >> pgevolve.toml

pgevolve validate --shadow
# pgevolve validate --shadow: round-trip matched (1 object(s))

4. Plan the first migration

pgevolve plan --db dev
# Wrote plan abc1234567890123 to plans/2026-05-11-abc1234567890123 (1 group(s), 3 step(s), 0 intent(s))

Inspect what got written:

ls plans/2026-05-11-abc1234567890123/
# intent.toml  manifest.toml  plan.sql
cat plans/2026-05-11-abc1234567890123/plan.sql

You'll see the same DDL you authored, wrapped in -- @pgevolve directive comments that pgevolve's executor reads. For details on the directive format and the three-file layout, see plan-format.md.

5. Apply

pgevolve apply plans/2026-05-11-abc1234567890123 --db dev
# applied (apply_id=<uuid>)

The app.users table now exists in myapp_dev:

psql myapp_dev -c '\d app.users'

6. Make a change

Add a display_name column to schema/app/tables/users.sql:

CREATE TABLE app.users (
    id           bigint      NOT NULL,
    email        text        NOT NULL,
    display_name text,
    created_at   timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT users_pkey PRIMARY KEY (id)
);

Plan and apply the change:

pgevolve diff --db dev
# 1 change(s):
#   - AlterTable
#       alter table app.users (1 op(s))

pgevolve plan --db dev
# Wrote plan xyz9876543210xyz to plans/2026-05-11-xyz9876543210xyz (1 group(s), 1 step(s), 0 intent(s))

cat plans/2026-05-11-xyz9876543210xyz/plan.sql
# … contains ALTER TABLE app.users ADD COLUMN display_name text;

pgevolve apply plans/2026-05-11-xyz9876543210xyz --db dev
# applied (apply_id=<uuid>)

7. See history

pgevolve status --db dev
# 2 recent apply/applies:
#   <uuid>  plan=abc1234567890123  status=succeeded  started=…  finished=…
#   <uuid>  plan=xyz9876543210xyz  status=succeeded  started=…  finished=…

pgevolve status --db dev --apply-id <uuid>
# apply <uuid>  plan=xyz9876543210xyz  status=succeeded
#   started_at=…  finished_at=…
#   pgevolve=0.1.0  source_rev=-  target=<hash>
#   steps (1):
#     [  1] g1 add_column   status=succeeded

What's next

• A destructive change. Drop the display_name column you just added — pgevolve will write an intent.toml with approved = false and refuse to apply until you flip it. See troubleshooting.md.
• The cookbook (cookbook.md) covers patterns for adding FKs / setting NOT NULL / dropping columns safely, declaring GRANTs and row-level-security policies, tuning storage parameters, and how the online-rewrite policies change the plan shape.
• Configuration (configuration.md) has the full pgevolve.toml reference, including per-environment strategy overrides for production-grade deployments.
• Cluster surface. Roles live above the per-database layer. If your deployment owns its own Postgres cluster, scaffold a parallel cluster project with pgevolve cluster init (separate from the per-DB project you just created) to manage CREATE ROLE declaratively. See the cluster spec.

Cookbook

Concrete migration patterns, with the plan shape pgevolve produces and why. Every recipe assumes you've gone through Getting started and have a working project.

Add a nullable column

The simplest case. The planner emits one ALTER TABLE ADD COLUMN step in a single transactional group.

-- Before
CREATE TABLE app.users (
    id    bigint NOT NULL,
    email text   NOT NULL,
    CONSTRAINT users_pkey PRIMARY KEY (id)
);

-- After (add `display_name`)
CREATE TABLE app.users (
    id           bigint NOT NULL,
    email        text   NOT NULL,
    display_name text,
    CONSTRAINT users_pkey PRIMARY KEY (id)
);

pgevolve plan --db dev →

-- @pgevolve group id=1 transactional=true
BEGIN;
-- @pgevolve step=1 kind=add_column destructive=false targets=app.users
ALTER TABLE app.users ADD COLUMN display_name text;
COMMIT;

No intent required.

Add a NOT NULL column to a populated table

This is where pgevolve earns its keep. Adding a NOT NULL column with a default to a large table is normally an ACCESS EXCLUSIVE scan; pgevolve does it in two steps:

-- After
CREATE TABLE app.users (
    id           bigint NOT NULL,
    email        text   NOT NULL,
    display_name text   NOT NULL,
    CONSTRAINT users_pkey PRIMARY KEY (id)
);

If display_name already existed as nullable, the planner emits the four-step CHECK pattern:

BEGIN;
-- @pgevolve step=1 kind=add_check_for_not_null
ALTER TABLE app.users
  ADD CONSTRAINT __pgevolve_chk_display_name CHECK (display_name IS NOT NULL) NOT VALID;
COMMIT;

BEGIN;
-- @pgevolve step=2 kind=validate_constraint
ALTER TABLE app.users VALIDATE CONSTRAINT __pgevolve_chk_display_name;
COMMIT;

BEGIN;
-- @pgevolve step=3 kind=set_column_nullable
ALTER TABLE app.users ALTER COLUMN display_name SET NOT NULL;
-- @pgevolve step=4 kind=drop_constraint
ALTER TABLE app.users DROP CONSTRAINT __pgevolve_chk_display_name;
COMMIT;

SET NOT NULL is cheap once the validated CHECK proves no NULL rows exist; Postgres skips the table scan.

If you want the old-style single-step SET NOT NULL (e.g., on a guaranteed-empty table), set [planner.online_rewrites].not_null_via_check_pattern = false for that environment.

Add a foreign key without locking

Adding an FK to an existing table normally locks during validation. pgevolve emits the NOT VALID + VALIDATE pattern across two transaction groups:

-- After
CREATE TABLE app.invoices (
    id          bigint NOT NULL,
    customer_id bigint NOT NULL,
    CONSTRAINT invoices_pkey PRIMARY KEY (id),
    CONSTRAINT invoices_customer_fk FOREIGN KEY (customer_id) REFERENCES app.customers (id)
);

If app.invoices is new in this plan, the FK rides inline with the CREATE TABLE. If app.invoices already exists:

-- @pgevolve group id=1 transactional=true
BEGIN;
-- @pgevolve step=1 kind=add_constraint_not_valid destructive=false targets=app.invoices
ALTER TABLE app.invoices ADD CONSTRAINT invoices_customer_fk
  FOREIGN KEY (customer_id) REFERENCES app.customers (id) NOT VALID;
COMMIT;

-- @pgevolve group id=2 transactional=true
BEGIN;
-- @pgevolve step=2 kind=validate_constraint destructive=false targets=app.invoices
ALTER TABLE app.invoices VALIDATE CONSTRAINT invoices_customer_fk;
COMMIT;

The two groups are committed independently. If step 2 fails, step 1 stays committed and you can pgevolve plan again to retry only the validation.

Add a non-unique index concurrently

CREATE INDEX users_email_idx ON app.users (email);

If app.users already exists in the live database, the planner rewrites this to CREATE INDEX CONCURRENTLY in its own non-transactional group:

-- @pgevolve group id=1 transactional=false
-- @pgevolve step=1 kind=create_index_concurrent destructive=false targets=app.users_email_idx,app.users
CREATE INDEX CONCURRENTLY users_email_idx ON app.users USING btree (email);

If the index is UNIQUE, pgevolve uses the locking variant (CREATE UNIQUE INDEX) — see the indexes.md rationale.

Drop a column

 CREATE TABLE app.users (
     id    bigint NOT NULL,
     email text   NOT NULL,
-    legacy_email text,
     CONSTRAINT users_pkey PRIMARY KEY (id)
 );

pgevolve plan produces a destructive step and writes an intent.toml with approved = false:

plan_id = "..."

[[intent]]
id       = 1
step     = 1
kind     = "drop_column"
target   = "app.users.legacy_email"
reason   = "drops column legacy_email"
approved = false

pgevolve apply refuses to run while approved = false. Edit the file, commit the change, and the apply succeeds.

Drop a table

Same approval flow as drop-column, with extra data_loss_warning flag set in the destructiveness record.

Forward-reference FK cycle (chicken-and-egg)

Sometimes two tables FK each other:

CREATE TABLE app.posts (
    id     bigint NOT NULL,
    author bigint NOT NULL,
    CONSTRAINT posts_pkey PRIMARY KEY (id),
    CONSTRAINT posts_author_fk FOREIGN KEY (author) REFERENCES app.users (id)
);

CREATE TABLE app.users (
    id            bigint NOT NULL,
    latest_post   bigint,
    CONSTRAINT users_pkey PRIMARY KEY (id),
    CONSTRAINT users_latest_post_fk FOREIGN KEY (latest_post) REFERENCES app.posts (id)
);

The planner detects the cycle and extracts one of the FKs into a post-pass ALTER TABLE ADD CONSTRAINT step. Both tables are created without that FK first; the FK is added (with NOT VALID rewrite if the target is large) afterward.

Rename a column or table

pgevolve does not detect renames today — they diff as drop+add, which is destructive for columns (data loss). If you need to rename:

1. Add the new column / table, and a backfill in a data migration (a step pgevolve does not handle).
2. Cut over reads / writes to the new name.
3. Drop the old in a separate, intent-approved plan.

A future version may detect renames via a developer-supplied hint (e.g., a -- @pgevolve rename directive). For now the safety-first posture stands.

Re-apply after a partial failure

If step 4 of a 5-step plan fails:

• For a transactional group: the group rolls back. Earlier steps in the group are also rolled back. Step 4's error_message is in pgevolve.plan_steps. Re-plan from the current live state and the re-apply skips the steps that already committed in earlier groups.
• For an autocommit group (e.g., CONCURRENTLY step): earlier steps stay committed. Re-planning produces a smaller plan that picks up from where you stopped.

You don't manually fix anything in the plan directory. You re-run pgevolve plan --db <env> and apply the new plan.

Managing views

Create a simple view

-- schema/app/views/active_users.sql
CREATE VIEW app.active_users AS
  SELECT id, email FROM app.users WHERE deleted_at IS NULL;

pgevolve plan emits one create_view step in a transactional group.

Add a column to an existing view (compatible change)

If the new column is appended at the end of the SELECT list, the body change is compatible: Postgres can apply it without dropping the view. pgevolve emits CREATE OR REPLACE VIEW:

-- After: add `created_at`
CREATE VIEW app.active_users AS
  SELECT id, email, created_at FROM app.users WHERE deleted_at IS NULL;

-- @pgevolve step=1 kind=create_view destructive=false targets=app.active_users
CREATE OR REPLACE VIEW app.active_users AS
  SELECT id, email, created_at FROM app.users WHERE deleted_at IS NULL;

Reorder columns (incompatible change → DROP + CREATE)

Reordering columns or changing a column type is incompatible with CREATE OR REPLACE VIEW. pgevolve emits an explicit drop_view followed by create_view:

-- After: move `email` before `id`
CREATE VIEW app.active_users AS
  SELECT email, id FROM app.users WHERE deleted_at IS NULL;

-- @pgevolve step=1 kind=drop_view destructive=true intent_id=1 targets=app.active_users
DROP VIEW app.active_users;
-- @pgevolve step=2 kind=create_view destructive=false targets=app.active_users
CREATE VIEW app.active_users AS SELECT email, id FROM app.users WHERE deleted_at IS NULL;

The drop_view step is destructive — you must flip approved = true in intent.toml before applying.

Dependent-view cascade

If view B selects from view A, modifying A's body incompatibly automatically cascades to B. pgevolve walks the body_dependencies graph and emits explicit DROP + CREATE steps for every affected view. The plan is fully auditable: no hidden CASCADE drops.

To opt out of automatic cascade and instead get an error listing the affected views, set:

[planner.online_rewrites]
view_drop_create_dependents = false

Managing user-defined types

Define an enum

-- schema/app/types/order_status.sql
CREATE TYPE app.order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered');

pgevolve plan emits one create_type step.

Add a value to an existing enum

Append a new label at the end (or position it with BEFORE/AFTER in source):

-- After: add 'cancelled'
CREATE TYPE app.order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');

-- @pgevolve step=1 kind=alter_type_add_value destructive=false targets=app.order_status
ALTER TYPE app.order_status ADD VALUE 'cancelled' AFTER 'delivered';

No intent required. The step is transactional (Postgres 12+).

Rename an enum value

-- After: rename 'processing' to 'in_progress'
CREATE TYPE app.order_status AS ENUM ('pending', 'in_progress', 'shipped', 'delivered', 'cancelled');

-- @pgevolve step=1 kind=alter_type_rename_value destructive=false targets=app.order_status
ALTER TYPE app.order_status RENAME VALUE 'processing' TO 'in_progress';

Drop an enum value (ReplaceWithCascade)

Postgres does not support ALTER TYPE … DROP VALUE. Removing a value triggers a ReplaceWithCascade: DROP TYPE CASCADE + CREATE TYPE. All columns and views referencing the type are recreated in the same transactional group.

-- @pgevolve step=1 kind=drop_type destructive=true intent_id=1 targets=app.order_status
DROP TYPE app.order_status CASCADE;
-- @pgevolve step=2 kind=create_type destructive=false targets=app.order_status
CREATE TYPE app.order_status AS ENUM ('pending', 'shipped', 'delivered');

Flip approved = true in intent.toml before applying.

Create a domain with a CHECK constraint

-- schema/app/types/positive_int.sql
CREATE DOMAIN app.positive_int AS integer
  NOT NULL
  CHECK (VALUE > 0);

pgevolve plan emits one create_type step (domain uses the same step kind as enum/composite).

Add a CHECK constraint to an existing domain

-- After: also reject values above one million
CREATE DOMAIN app.positive_int AS integer
  NOT NULL
  CONSTRAINT positive_int_lower CHECK (VALUE > 0)
  CONSTRAINT positive_int_upper CHECK (VALUE <= 1000000);

-- @pgevolve step=1 kind=alter_domain_add_constraint destructive=false targets=app.positive_int
ALTER DOMAIN app.positive_int ADD CONSTRAINT positive_int_upper CHECK (VALUE <= 1000000);

Drop an attribute from a composite type (ReplaceWithCascade)

Postgres supports ALTER TYPE … DROP ATTRIBUTE only when no column or function depends on the composite. pgevolve always uses ReplaceWithCascade for composite attribute drops to handle the general case safely:

-- Before
CREATE TYPE app.address AS (street text, city text, zip text);

-- After: drop 'zip'
CREATE TYPE app.address AS (street text, city text);

-- @pgevolve step=1 kind=drop_type destructive=true intent_id=1 targets=app.address
DROP TYPE app.address CASCADE;
-- @pgevolve step=2 kind=create_type destructive=false targets=app.address
CREATE TYPE app.address AS (street text, city text);

Managing functions and procedures

Define a simple SQL function

-- schema/app/functions/add_one.sql
CREATE FUNCTION app.add_one(x integer)
  RETURNS integer
  LANGUAGE sql
  IMMUTABLE STRICT
AS $$
  SELECT x + 1
$$;

pgevolve plan emits one create_or_replace_function step.

Define a PL/pgSQL function with a static body

-- schema/app/functions/get_active_user.sql
CREATE FUNCTION app.get_active_user(p_id bigint)
  RETURNS app.users
  LANGUAGE plpgsql
  STABLE
AS $$
DECLARE
  r app.users;
BEGIN
  SELECT * INTO r FROM app.users WHERE id = p_id AND deleted_at IS NULL;
  RETURN r;
END
$$;

pgevolve extracts the app.users dep edge from the static SELECT statement at parse time.

Replace a function body (in-place)

Edit the function's SQL or PL/pgSQL body. If the language and return type are unchanged, pgevolve plan emits a single create_or_replace_function step — no DROP needed.

-- After: tighten to active users only
CREATE FUNCTION app.get_active_user(p_id bigint)
  RETURNS app.users
  LANGUAGE plpgsql
  STABLE
AS $$
DECLARE
  r app.users;
BEGIN
  SELECT * INTO r
  FROM app.users
  WHERE id = p_id AND deleted_at IS NULL AND suspended = false;
  RETURN r;
END
$$;

-- @pgevolve step=1 kind=create_or_replace_function destructive=false targets=app.get_active_user
CREATE OR REPLACE FUNCTION app.get_active_user(p_id bigint) ...;

No intent required. If the return type or language changes, pgevolve falls back to DROP FUNCTION CASCADE + CREATE OR REPLACE FUNCTION (destructive — requires intent approval).

Add an overload (same name, different arg types)

PL/pgSQL functions support overloading on arg types. Just add the second definition:

-- schema/app/functions/format_name.sql  (integer overload)
CREATE FUNCTION app.format_name(user_id integer)
  RETURNS text
  LANGUAGE sql STABLE
AS $$
  SELECT first_name || ' ' || last_name FROM app.users WHERE id = user_id
$$;

-- schema/app/functions/format_name_text.sql  (text overload)
CREATE FUNCTION app.format_name(raw_name text)
  RETURNS text
  LANGUAGE sql IMMUTABLE STRICT
AS $$
  SELECT initcap(raw_name)
$$;

pgevolve tracks each overload independently; the identity is qname + arg_types_normalized.

Define a procedure with COMMIT in the body

-- schema/app/procedures/process_batch.sql
CREATE PROCEDURE app.process_batch(batch_size integer)
  LANGUAGE plpgsql
AS $$
DECLARE
  r record;
BEGIN
  FOR r IN SELECT id FROM app.jobs WHERE status = 'pending' LIMIT batch_size LOOP
    UPDATE app.jobs SET status = 'done' WHERE id = r.id;
    COMMIT;
  END LOOP;
END
$$;

pgevolve detects COMMIT in the body and emits the step with transactional=false (outside a transaction block). The procedure-contains-commit lint warning fires as a reminder that the procedure cannot participate in a larger transaction.

Use -- @pgevolve dep: for dynamic SQL

If a function uses EXECUTE (dynamic SQL), pgevolve cannot extract deps statically. Declare them with a directive:

-- schema/app/functions/refresh_summary.sql
CREATE FUNCTION app.refresh_summary()
  RETURNS void
  LANGUAGE plpgsql
AS $$
BEGIN
  -- @pgevolve dep: app.summary
  EXECUTE 'REFRESH MATERIALIZED VIEW app.summary';
END
$$;

Without the directive, the plpgsql-dynamic-sql lint rule fires as an Error. The directive tells pgevolve that app.summary is a dependency, so the planner can order the refresh after any changes to that MV.

Tune storage parameters (reloptions)

Storage parameters (fillfactor, autovacuum_*, parallel_workers, GIN fastupdate, BRIN pages_per_range, …) are declared inline on the object. Each typed key has a None default that means "unmanaged" — pgevolve will neither set nor reset it.

Declare on a new or existing table

-- schema/app/tables/orders.sql
CREATE TABLE app.orders (
    id          bigint PRIMARY KEY,
    customer_id bigint NOT NULL,
    placed_at   timestamptz NOT NULL
) WITH (
    fillfactor          = 80,
    autovacuum_enabled  = true,
    autovacuum_vacuum_scale_factor = 0.05,
    parallel_workers    = 4
);

If app.orders already exists in the catalog without these settings, pgevolve plan emits a single batched ALTER TABLE:

ALTER TABLE app.orders SET (fillfactor = 80, autovacuum_enabled = true,
    autovacuum_vacuum_scale_factor = 0.05, parallel_workers = 4);

Both the inline WITH (…) form on CREATE TABLE and a separate ALTER TABLE app.orders SET (…); are accepted in source.

Per-AM index reloptions

Indexes accept access-method-specific options, validated at parse time so PG-invalid combinations fail fast:

CREATE INDEX orders_customer_id_idx ON app.orders (customer_id)
    WITH (fillfactor = 80);                       -- B-tree: 50..=100

CREATE INDEX orders_tags_idx ON app.orders USING gin (tags)
    WITH (fastupdate = false, gin_pending_list_limit = 4096);

CREATE INDEX orders_placed_at_idx ON app.orders USING brin (placed_at)
    WITH (pages_per_range = 32, autosummarize = true);

fillfactor ranges differ per AM: B-tree 50..=100, GiST 10..=100, SP-GiST 90..=100. BRIN and GIN reject fillfactor outright.

Removing a managed reloption

Removing a value from source does not issue a RESET. This is the same lenient pattern used by owner, grants, and RLS policies — pgevolve never destructively undoes state on the catalog side just because source went quiet.

To clear a reloption you previously managed:

1. Apply ALTER TABLE app.orders RESET (fillfactor); out-of-band (psql, your DBA tooling, a one-off migration).
2. Remove the fillfactor = 80 declaration from source.

On the next pgevolve plan run, both source and catalog read None and the diff is empty.

The unmanaged-reloption lint

If the catalog has a reloption that source doesn't declare, the unmanaged-reloption lint fires as a warning. This includes both typed keys (e.g., the DBA set fillfactor = 70 directly) and extension keys (e.g., pg_partman.retention_keep_table = 'true'). Waive via [[lint_waiver]] in intent.toml if the drift is intentional.

First-apply caveat. CREATE TABLE … WITH (…) against a brand-new (not-yet-in-catalog) object currently emits the CREATE step without the inline WITH (…) clause. The reloptions land on the second plan + apply cycle as an ALTER … SET. This is a known v0.3.x limitation (see docs/spec/reloptions.md); convergent in two iterations.

Grant a role read-only access to a table

pgevolve models per-object owner and grants as of v0.3.1. Both follow the lenient drift policy: declaring owner = None (the default in source) means "unmanaged" — the differ will neither set nor reset the owner. The same applies to grants: declared grants are added/kept; catalog grants you haven't declared surface as the unmanaged-grant lint warning but are never silently revoked.

-- schema/app/tables/orders.sql
CREATE TABLE app.orders (
    id          bigint PRIMARY KEY,
    customer_id bigint NOT NULL,
    placed_at   timestamptz NOT NULL
);

-- @pgevolve owner: app_owner
GRANT SELECT ON app.orders TO reporting;
GRANT SELECT (id, placed_at) ON app.orders TO analytics_readonly;

pgevolve plan emits:

ALTER TABLE app.orders OWNER TO app_owner;
GRANT SELECT ON TABLE app.orders TO reporting;
GRANT SELECT (id, placed_at) ON TABLE app.orders TO analytics_readonly;

To revoke an existing grant, simply remove the GRANT line from source. The differ emits an explicit REVOKE because the grant was previously managed (it appeared in your Vec<Grant> and is now gone). This is distinct from grants the catalog already had but source never claimed: those are unmanaged, never auto-revoked, and surface via the unmanaged-grant lint.

For cross-cutting defaults (e.g., "every new table in app is owned by app_owner and grants SELECT to reporting"), use ALTER DEFAULT PRIVILEGES:

ALTER DEFAULT PRIVILEGES IN SCHEMA app
    GRANT SELECT ON TABLES TO reporting;

pgevolve models these as first-class IR; see docs/spec/grants.md for the full surface.

Enable row-level security on a table

pgevolve models per-table rls_enabled, rls_forced, and an embedded policies: Vec<Policy> as of v0.3.2.

-- schema/app/tables/documents.sql
CREATE TABLE app.documents (
    id      bigint PRIMARY KEY,
    owner   text   NOT NULL,
    body    text   NOT NULL
);

ALTER TABLE app.documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY owner_can_read ON app.documents
    FOR SELECT
    USING (owner = current_user);

CREATE POLICY owner_can_write ON app.documents
    FOR INSERT
    WITH CHECK (owner = current_user);

pgevolve plan emits one step per change (CREATE/ALTER/DROP POLICY, ENABLE/DISABLE/FORCE/NOFORCE ROW LEVEL SECURITY). Any change to a policy's command (e.g., FOR SELECT → FOR UPDATE) goes through DROP + CREATE because Postgres has no ALTER POLICY … CHANGE COMMAND.

Two policy attributes use NormalizedExpr for diff (same canon as CHECK constraints): USING (…) and WITH CHECK (…). Whitespace and keyword-case differences between source and pg_policies therefore don't trigger spurious recreates.

The lenient-drift rule applies: a policy in the catalog that source doesn't declare surfaces as unmanaged-policy (warning) instead of an auto-DROP. Remove a managed policy from source to drop it explicitly.

See docs/spec/policies.md for the full attribute matrix.

Manage cluster roles

The role surface is cluster-level, not per-database. pgevolve manages it via a separate project type (pgevolve-cluster.toml + a roles/ tree) and a parallel command family: pgevolve cluster init / diff / plan / apply / status.

mkdir myapp-cluster && cd myapp-cluster
pgevolve cluster init

Author roles as CREATE ROLE SQL:

-- roles/app_owner.sql
CREATE ROLE app_owner WITH NOLOGIN;

-- roles/reporting.sql
CREATE ROLE reporting WITH LOGIN NOINHERIT;
GRANT app_owner TO reporting;

The full role-attribute matrix is supported (LOGIN/NOLOGIN, SUPERUSER/NOSUPERUSER, CREATEDB, CREATEROLE, REPLICATION, BYPASSRLS, CONNECTION LIMIT, VALID UNTIL). Passwords are intentionally not modeled — set them out-of-band so they never appear in source-controlled SQL.

pgevolve cluster plan
pgevolve cluster apply plans/2026-05-23-<id>

The per-database commands (pgevolve plan, pgevolve apply, etc.) treat the role names mentioned in GRANT / OWNER TO clauses as references — they don't create the roles. Use pgevolve cluster … to manage role lifecycle once at the cluster level, then reference those role names across all the per-DB projects that share the cluster.

See docs/spec/cluster.md for the full project layout and the role-attribute surface.

Set up logical replication

Postgres logical replication is declared in two places: a publication on the source database and a subscription on the target database. pgevolve manages both as first-class objects since v0.3.4 (publications) and v0.3.5 (subscriptions).

Step 1 — Declare the publication (source database project)

-- schema/publications/pub_orders.sql
CREATE PUBLICATION pub_orders
    FOR TABLE app.orders, app.order_items
    WITH (publish = 'insert, update, delete');

pgevolve plan against the source DB emits:

-- @pgevolve step=1 kind=create_publication destructive=false targets=pub_orders
CREATE PUBLICATION pub_orders
    FOR TABLE app.orders, app.order_items
    WITH (publish = 'insert, update, delete');

Step 2 — Declare the subscription (target database project)

Keep credentials out of source SQL by using the ${VAR} interpolation syntax:

-- schema/subscriptions/sub_orders.sql
CREATE SUBSCRIPTION sub_orders
    CONNECTION 'host=primary.example.com dbname=app user=repl_user password=${REPL_PWD}'
    PUBLICATION pub_orders
    WITH (binary = true, streaming = on);

The literal ${REPL_PWD} is stored verbatim in plan.sql. It is never resolved at plan time — only at apply time. This means:

• The plan file is safe to commit and code-review.
• The credential is never written to disk.

pgevolve plan against the target DB emits:

-- @pgevolve step=1 kind=create_subscription destructive=false targets=sub_orders
CREATE SUBSCRIPTION sub_orders
    CONNECTION 'host=primary.example.com dbname=app user=repl_user password=${REPL_PWD}'
    PUBLICATION pub_orders
    WITH (binary = true, streaming = on);

Step 3 — Apply with the credential in the environment

# In CI or your deploy script — never in source
export REPL_PWD="$(vault kv get -field=password secret/repl_user)"
pgevolve apply plans/2026-05-26-<plan-id>

pgevolve scans the plan for ${...} references before opening any connection. If REPL_PWD is not set, it prints a clear error and exits before touching the database:

error: unresolved env-var reference ${REPL_PWD} in step 1 (create_subscription)

Changing the connection string

Edit the CONNECTION value in source and re-plan:

-- Updated host after a primary failover
CREATE SUBSCRIPTION sub_orders
    CONNECTION 'host=newprimary.example.com dbname=app user=repl_user password=${REPL_PWD}'
    PUBLICATION pub_orders
    WITH (binary = true, streaming = on);

pgevolve plan detects the connection-string change and emits:

-- @pgevolve step=1 kind=alter_subscription_connection destructive=false targets=sub_orders
ALTER SUBSCRIPTION sub_orders
    CONNECTION 'host=newprimary.example.com dbname=app user=repl_user password=${REPL_PWD}';

Adding a publication to an existing subscription

-- Extend sub_orders to also consume pub_users
CREATE SUBSCRIPTION sub_orders
    CONNECTION 'host=primary.example.com dbname=app user=repl_user password=${REPL_PWD}'
    PUBLICATION pub_orders, pub_users
    WITH (binary = true, streaming = on);

-- @pgevolve step=1 kind=alter_subscription_add_publication destructive=false targets=sub_orders
ALTER SUBSCRIPTION sub_orders ADD PUBLICATION pub_users;

Lint: plaintext password caught at plan time

pgevolve's subscription-password-in-source lint fires at parse time if the source SQL contains a literal password:

-- This triggers a hard lint error — never commit plaintext credentials
CREATE SUBSCRIPTION bad_sub
    CONNECTION 'host=primary.example.com dbname=app user=repl password=hunter2'
    PUBLICATION pub_orders;

error[subscription-password-in-source]: CONNECTION string contains a literal
  password= value. Use ${VAR} env-var interpolation instead.
  --> schema/subscriptions/bad_sub.sql:2

The lint is severity Error and not waivable.

See docs/spec/subscriptions.md for the full option matrix, lint rules, and operational verb rejection.

Run the same plan against multiple environments

A plan is bound to a specific target_identity. If you generate against dev but want to apply against staging:

pgevolve apply plans/2026-05-11-abc1234567890123 --db staging \
    --allow-different-target

This is intentionally explicit. The much safer pattern is to plan twice (once per environment) and review both plans — drift between environments will show up as a plan difference.

Multi-column statistics for correlated columns

When two or more columns are strongly correlated (e.g., status and region always appear together), Postgres tends to drastically underestimate the selectivity of combined predicates. CREATE STATISTICS teaches the planner about these correlations.

Declare the statistic

-- schema/app/tables.sql
CREATE TABLE app.orders (
    id       bigint NOT NULL,
    status   text   NOT NULL,
    region   text   NOT NULL,
    amount   numeric NOT NULL,
    CONSTRAINT orders_pkey PRIMARY KEY (id)
);

-- schema/app/statistics.sql
CREATE STATISTICS app.orders_status_region
    ON (status, region)
    FROM app.orders;

pgevolve plan --db dev →

-- @pgevolve group id=1 transactional=true
BEGIN;
-- @pgevolve step=1 kind=create_statistic destructive=false targets=app.orders_status_region
CREATE STATISTICS app.orders_status_region ON (status, region) FROM app.orders;
-- @pgevolve step=2 kind=create_statistic destructive=false targets=app.orders_status_region
ANALYZE app.orders;
COMMIT;

No intent required.

Limit to specific kinds

If you only want functional dependency tracking (the cheapest kind):

CREATE STATISTICS app.orders_dep
    (dependencies)
    ON (status, region)
    FROM app.orders;

The kinds clause accepts any non-empty subset of ndistinct, dependencies, mcv. Omitting the clause enables all three (Postgres default).

Raise the analyze target for fine-grained estimates

The statistics_target controls how many rows the analyzer samples when building the statistic. The Postgres default is -1 (inherit the column setting, usually 100). Raising it to 500 gives much more accurate estimates for skewed distributions:

-- Before (no target override)
CREATE STATISTICS app.orders_status_region
    ON (status, region)
    FROM app.orders;

-- After (raise target)
CREATE STATISTICS app.orders_status_region
    ON (status, region)
    FROM app.orders;
ALTER STATISTICS app.orders_status_region SET STATISTICS 500;

pgevolve plan --db dev →

-- @pgevolve group id=1 transactional=true
BEGIN;
-- @pgevolve step=1 kind=alter_statistic_set_target destructive=false targets=app.orders_status_region
ALTER STATISTICS app.orders_status_region SET STATISTICS 500;
COMMIT;

This uses the cheap AlterStatisticSetTarget path — no DROP + CREATE needed.

What triggers a destructive ReplaceStatistic

Changing the column list or the kinds requires a DROP STATISTICS + CREATE STATISTICS because Postgres has no in-place ALTER for those fields:

-- Before
CREATE STATISTICS app.orders_status_region
    ON (status, region) FROM app.orders;

-- After — add amount column
CREATE STATISTICS app.orders_status_region
    ON (status, region, amount) FROM app.orders;

pgevolve plan --db dev →

-- @pgevolve group id=1 transactional=false
-- @pgevolve step=1 kind=replace_statistic destructive=true targets=app.orders_status_region intent=required
DROP STATISTICS app.orders_status_region;
CREATE STATISTICS app.orders_status_region ON (status, region, amount) FROM app.orders;

intent=required means you must acknowledge the destructive step in pgevolve.toml or pass --intent on the CLI before the plan can be applied.

Lint: unmanaged statistics

If a statistic exists in the live database but is not declared in source, pgevolve emits an unmanaged-statistic warning (severity Warning, waivable):

WARN unmanaged-statistic: statistics app.orders_status_region exists in the
     catalog but is not declared in source. Add it to source or waive this
     lint in pgevolve.toml.

To waive it:

# pgevolve.toml
[[lint.waive]]
rule = "unmanaged-statistic"
target = "app.orders_status_region"
reason = "Legacy statistic, managed out-of-band."

See docs/spec/statistics.md for the full surface, step-kind matrix, and catalog reader notes.

Troubleshooting

Common errors and what to do about them. Each entry shows the actual error string pgevolve emits, the cause, and the fix.

Pre-flight failures (exit code 2)

Target identity mismatch

target identity mismatch: plan=abc12345abcd1234 live=ff00112233445566

Cause. The plan was built against a different database than the one you're applying to. target_identity is a hash of (current_database, host, port, cluster_name, system_identifier); applying a dev plan to prod (or vice versa) hits this.

Fix.

• If the difference is intentional: re-run pgevolve apply with --allow-different-target.
• Otherwise: re-plan against the correct environment with pgevolve plan --db <env>.

Unapproved intent

unapproved destructive intents: 1

Cause. The plan declares one or more destructive steps; the corresponding [[intent]] rows in intent.toml still have approved = false.

Fix. Open intent.toml, review each [[intent]] row, and change approved = false to approved = true for the ones you authorize. Commit the change in your code-review tool of choice before applying.

Drift detected

drift detected since planning: 3 change(s)

Cause. The live database changed between when you ran pgevolve plan and when you ran pgevolve apply — typically because someone (or another tool) ran DDL out of band.

Fix.

• Inspect what changed: pgevolve diff --db <env>. Compare to manifest.toml's target_snapshot_json.
• If the drift is intentional: re-plan with pgevolve plan --db <env> and apply the new plan.
• If the drift is unintentional: investigate the source. Don't paper over it with --allow-drift.

--allow-drift exists as a documented escape hatch for "I know the drift is harmless"; it should be a thinking step, not a reflex.

Apply failures (exit code 3)

Advisory lock held

pgevolve advisory lock is held by another session

Cause. Another pgevolve apply is running, or one crashed without releasing the lock cleanly.

Fix.

• Wait for the other apply to finish.

• If you're sure no one else is applying:

  SELECT held_by, held_since, pgevolve_version FROM pgevolve.lock;
  

  shows who claims to hold the lock. The session-scoped advisory lock releases automatically when its session disconnects, so a stale pgevolve.lock row often clears itself the moment the next acquirer takes the lock. Stuck rows from a crash are clearable by:

  SELECT pg_advisory_unlock_all();
  

  in the session that holds it, or by terminating that session via pg_terminate_backend.

Step failed

step 4 (group 2) failed: [42P07] relation "app.users" already exists

Cause. Postgres rejected the SQL. The bracketed code (42P07 here) is the SQLSTATE; the rest is the server message.

Fix. Inspect the audit log to see the exact step and SQL:

pgevolve status --db <env>
pgevolve status --db <env> --apply-id <uuid>

Or directly:

SELECT step_no, kind, status, error_message, sql_text
FROM pgevolve.plan_steps
WHERE apply_id = '<uuid>'
ORDER BY step_no;

Common subtypes:

• 42P07 relation already exists → you're trying to create something that's already there. Usually means the live state drifted; re-plan.
• 23505 duplicate key value violates unique constraint → an FK validation or unique-index build failed because the existing data doesn't satisfy the constraint. Fix the data first (out of band), then re-plan.
• 42501 permission denied → the connection's role lacks the privilege. Connect as a sufficiently-privileged role (typically the schema owner) or grant the missing privileges out of band.
• 25006 cannot run inside a transaction block (CREATE INDEX CONCURRENTLY) → almost certainly indicates a plan-format bug; file an issue.

Lint / validation failures (exit code 1)

Parse error

error: parse error: SyntaxError(...): ERROR:  syntax error at or near "..." at /path/to/file.sql:42:1

Cause. pg_query couldn't parse one of your SQL statements. The file path and line are in the error message.

Fix. Run the offending SQL against a real Postgres to see the same error. Most often it's a typo or an unsupported feature.

Unsupported object kind

error: unsupported object kind: <kind> at /path/to/file.sql:1:1

Cause. You wrote a statement type that's not in pgevolve's whitelist for the current release (e.g., a Postgres feature pgevolve doesn't yet model).

Fix. See docs/spec/objects.md for the current coverage and roadmap. Views, materialized views, user-defined types (enum/domain/composite), functions, procedures, triggers, and extensions are supported as of v0.2.

Layout-profile violation

error: [schema_mirror_path] table should be at `app/tables/users.sql`; found at `schema/oops/users.sql` (schema/oops/users.sql:1:1)

Cause. Your file is in a path that the configured layout profile doesn't permit.

Fix. Either move the file, or switch [project].layout_profile to one whose rules match your existing layout. free-form enforces no path rules.

managed_schemas_match

error: [managed_schemas_match] schema `audit` is declared in source but not listed in `[managed].schemas`

Cause. Your source declares a schema that's not in your [managed].schemas list — meaning pgevolve would ignore everything in it.

Fix. Add the schema name to [managed].schemas, or remove it from the source.

Config errors (exit code 4)

Missing config

config error: i/o reading pgevolve.toml: No such file or directory (os error 2)

Cause. pgevolve.toml doesn't exist at the path pgevolve is looking at (default ./pgevolve.toml).

Fix. Run pgevolve init if this is a new project, or pass --config <path> if your config lives elsewhere.

Unknown environment

unknown environment: `prod`

Cause. --db prod referenced an environment that's not in pgevolve.toml.

Fix. Add an [environments.prod] block, or use the correct env name.

Invalid strategy

parse error: ... unknown variant `bogus`, expected `atomic` or `online`

Cause. [planner].strategy (or [environments.<env>].strategy) is not "atomic" or "online".

Fix. Use one of the two valid values.

Shadow validation

Docker not available

--shadow requires Docker. Install Docker or run without --shadow.

Cause. pgevolve validate --shadow couldn't run docker info.

Fix. Either install Docker (and ensure your user can run it without sudo), or drop the --shadow flag — non-shadow validate doesn't require Docker.

Shadow round-trip mismatch

pgevolve validate --shadow: 1 mismatch(es):
  - tables.app.users.columns.email.collation: `None` vs `Some(...)`

Cause. Your source IR doesn't match what pgevolve gets back after applying it to a fresh Postgres of the configured version. Usually indicates a Postgres normalization that the IR doesn't account for, or a bug in pgevolve's introspection.

Fix. Report this as an issue with the source file + the error output. Until it's fixed, you can pin a different [shadow] postgres_version to see if the mismatch is version-specific.

When all else fails

Open an issue at https://github.com/saosebastiao/pgevolve/issues with:

1. The exact command you ran.
2. The full output (stderr + stdout).
3. The relevant slice of pgevolve.toml.
4. The Postgres version (SELECT version();).
5. Your pgevolve --version.

Sensitive output? Redact the DSN — pgevolve never prints passwords, but your environment variables might.

Command reference

Per-command details with realistic invocations. The capability spec lists every flag with its implementation status; this file shows them in context.

Global flags

These apply to every subcommand.

pgevolve init

Scaffolds a new project.

USAGE: pgevolve init [--dir <path>] [--force]

Creates: pgevolve.toml, schema/, plans/, and adds a .gitignore section if one doesn't exist yet. Refuses to overwrite an existing pgevolve.toml unless --force.

pgevolve lint

USAGE: pgevolve lint [--format human|json]

Parses the source tree, runs the universal rules and the configured layout-profile rules, and prints any findings. Exit 0 on no errors; exit 1 on any error-severity finding.

The --format flag is the top-level pgevolve --format ... flag and must precede the subcommand. --format sql is rejected for lint (only meaningful for diff).

Human format (default)

Layout violations look like:

error: [schema_mirror_path] table should be at `app/tables/users.sql`; found at `schema/oops/users.sql` (schema/oops/users.sql:1:1)
pgevolve lint: 1 finding(s), 1 error(s)

JSON format

pgevolve --format json lint emits a stable structured document:

{
  "findings": [
    {
      "severity": "error",
      "rule": "schema_mirror_path",
      "message": "table should be at `app/tables/users.sql`; found at `schema/oops/users.sql`",
      "location": { "file": "schema/oops/users.sql", "line": 1, "column": 1 }
    }
  ],
  "total": 1,
  "errors": 1
}

Severity values are stringified ("error", "warning", "lint-at-plan"). Findings without a known source location omit the location field.

Universal rules (e.g., closed_world_references, managed_schemas_match) are listed in the spec.

pgevolve validate

USAGE: pgevolve validate [--shadow] [--shadow-validate] [--shadow-strict]

Parses the source tree (subsumes lint parse).

Without --shadow the command reports parse success and 0 lint findings. With --shadow it additionally:

1. Starts a postgres:<major>-alpine container.
2. Builds a plan from (empty, source) and applies it.
3. Introspects the shadow DB into a Catalog.
4. Diffs the source IR against the introspected IR.
5. Reports any divergences as Findings on stderr.

Exit 0 on match; 1 on any divergence.

pgevolve diff

USAGE: pgevolve diff --db <env> [--url <dsn>] [--shadow-validate] [--shadow-strict]

Prints the change set between the source IR and a live database. Always exits 0; this is informational.

Output formats (selected with the global --format flag):

• human (default) — one-line summary per change, indented details.
• json — the same ChangeSet serialized.
• sql — naive ALTER SQL with no online rewrites. For review only; run plan for the applyable form.

pgevolve diff --db dev
# 1 change(s):
#   - AlterTable
#       alter table app.users (1 op(s))

pgevolve plan

USAGE: pgevolve plan --db <env> [--url <dsn>] [-o <dir>] [--shadow-validate] [--shadow-strict]

The full pipeline: parse → diff → order → rewrite → group → Plan::from_grouped → write_plan_dir.

plan also enforces LintAtPlan findings: if any unwaived LintAtPlan-severity finding is present (e.g., column-position drift), the command exits 2 and writes no plan directory. Acknowledge findings with [[lint_waiver]] rows in intent.toml — see configuration.

pgevolve plan --db dev
# Wrote plan abc1234567890123 to plans/2026-05-11-abc1234567890123 (1 group(s), 1 step(s), 0 intent(s))

If diff is empty, plan still writes a directory with zero groups — useful for asserting "no changes" in CI.

pgevolve apply

USAGE: pgevolve apply <plan-dir> --db <env> [--url <dsn>]
                                  [--allow-different-target] [--allow-drift]

Reads a plan directory and applies it.

Exit codes (spec §13):

Approval flow for destructive plans

When plan produces destructive intents, they're written to intent.toml with approved = false. apply reads them and refuses to run until they're flipped. See plan-format.md.

pgevolve status

USAGE: pgevolve status --db <env> [--url <dsn>] [--apply-id <uuid>] [--limit <n>]

pgevolve status --db dev
# 3 recent apply/applies:
#   <uuid-1>  plan=abc1234567890123  status=succeeded  started=2026-05-11T18:00:00Z  finished=2026-05-11T18:00:03Z
#   …

With --format json, emits a serializable shape for automation.

pgevolve bootstrap

USAGE: pgevolve bootstrap --db <env> [--url <dsn>]

Installs or upgrades the pgevolve metadata schema (the pgevolve.bootstrap_version, apply_log, plan_steps, and lock tables). Other commands auto-bootstrap, so this is mostly useful for pre-bootstrapping a fresh DB before the first apply.

pgevolve dump

USAGE: pgevolve dump --db <env> -o <dir>

Introspect a live database and write source-format SQL to <dir>/schema.sql.

The command:

1. Connects to the database using the resolved DSN.
2. Reads the catalog for all managed schemas (from [managed].schemas).
3. Renders every object as a CREATE statement in dependency order: schemas → tables (inline PK/UK/CHECK) → FK ALTER TABLE ADD CONSTRAINT → standalone indexes → sequences.
4. Writes the result to <dir>/schema.sql.

pgevolve dump --db dev -o /tmp/schema-snapshot
# wrote 4096 bytes to /tmp/schema-snapshot/schema.sql
# note: output does not include pgevolve directives; add them before running `pgevolve lint`

Scope notes (v0.3.x):

• The entire catalog is written to a single schema.sql file. Multi-file layout following layout_profile is not yet implemented.
• The output does not include pgevolve source directives (-- pgevolve: intent = ... etc.), so it cannot be fed directly to pgevolve lint or used with parse_directory without first adding those directives. After dump, add directives manually or use a future pgevolve annotate helper.
• Coverage: schemas, tables (with inline PK / UK / CHECK constraints and FK ALTERs), standalone indexes, sequences, views, and materialized views are emitted. Functions, procedures, triggers, user-defined types, extensions, and role-level state (owners, grants, RLS policies, reloptions) are not yet emitted by dump.

The primary use case is adoption: pointing dump at an existing production database to produce a starting schema/ tree for a new pgevolve project.

pgevolve graph

USAGE: pgevolve graph [--graph-format dot|mermaid] [-o <path>] [--plan <dir>]

Render the source dependency graph. Read-only; no database connection required.

# DOT output to stdout (pipe to `dot -Tpng -o deps.png` for a diagram)
pgevolve graph

# Mermaid output to a file
pgevolve graph --graph-format mermaid -o schema/deps.md

Used by the conformance suite's L8 dep-graph golden layer: fixtures assert byte-stable DOT output for a given source tree.

pgevolve doctor

USAGE: pgevolve doctor --db <env> [--url <dsn>]

Project health check. Read-only; does not modify the database or write any files.

Reports:

• Bootstrap status (whether the pgevolve schema is installed). If not installed, the report tells you to run pgevolve bootstrap.
• NOT VALID constraints in managed schemas (candidates for a follow-up VALIDATE CONSTRAINT).
• INVALID indexes in managed schemas (candidates for a follow-up REINDEX CONCURRENTLY).
• Source object count vs. catalog object count (quick sanity check for unexpected drift) — schemas, tables, indexes, sequences.
• Recent failed applies from pgevolve.apply_log (only when bootstrapped).

pgevolve doctor --db dev
# pgevolve doctor — env dev
#   bootstrap: ok
#   drift: none
#   source:  1 schemas, 4 tables, 3 indexes, 1 sequences
#   catalog: 1 schemas, 4 tables, 3 indexes, 1 sequences
#   recent applies: no failures

Exit codes:

• 0 — every check passes (bootstrap installed, no drift, no recent apply failures).
• 1 — any of: bootstrap missing, NOT VALID constraint, INVALID index, or a failed apply in the recent log. The specific issue is printed in the report; exit code lets the command be scripted into deploy pre-flights.

A pgevolve.apply_log query error is not counted as an issue (the table may not exist on very old bootstrap versions); the message is printed but the command still returns 0 for that signal alone.

pgevolve rewrite-table (CLI skeleton)

USAGE: pgevolve rewrite-table <qname> --db <env> --confirm-rewrite

Destructive table rewrite. Not yet implemented — the CLI surface is stable but the command currently errors with a not-yet-implemented message. The implementation lands with the column-reorder sub-spec.

The intended use case is column-position reorder: when pgevolve plan detects column-position drift and you have an approved [[lint_waiver]] for the relevant column-position-drift finding, this command performs the shadow-copy table rewrite to materialise the new column order.

pgevolve cluster (v0.3.0+)

Cluster-level commands manage state shared across an entire Postgres cluster (today: roles; future: tablespaces, GUCs, foreign servers). They use a parallel project type — a directory containing pgevolve-cluster.toml and a roles/ tree — that is separate from a per-DB pgevolve project. See docs/spec/cluster.md for the surface and the project shape.

USAGE: pgevolve cluster [--config <path>] <subcommand>

Subcommands:
  init [<path>]   Scaffold a new cluster project.
  diff            Show the diff between source roles and the live cluster.
  plan            Write a cluster plan directory under `cluster-plans/<id>/`.
  apply [<id>]    Apply a cluster plan. Defaults to the most recent.
  status          List cluster plans under `cluster-plans/`.

Notes:

• Cluster commands read pgevolve-cluster.toml, not pgevolve.toml.
• The connection DSN comes from [connection].dsn in pgevolve-cluster.toml. The role used must be able to read pg_authid (typically superuser).
• The [bootstrap].roles list names roles pgevolve treats as PG-owned and never diffs in or out (default ["postgres"]; cloud Postgres typically needs additional entries, e.g. ["postgres", "cloudsqlsuperuser"]).
• Passwords are not stored in source; set them out-of-band.
• v0.3.0 limitations: cluster apply does not yet read intent.toml to gate destructive role drops, does not take an advisory lock, and does not write to a per-DB-style apply log. Review cluster-plans/<id>/plan.sql before applying. See docs/spec/cluster.md.

Shadow validation

--shadow-validate is an optional opt-in cross-check available on validate, diff, and plan. When set, pgevolve boots a shadow Postgres (using the [shadow] block in pgevolve.toml) and verifies the source dependency graph against the pg_depend catalog view. Discrepancies are reported as warnings; --shadow-strict promotes them to errors (and requires --shadow-validate).

# Check dep graph consistency; warn on discrepancies
pgevolve validate --shadow-validate

# Same but fail on any discrepancy
pgevolve validate --shadow-validate --shadow-strict

# Shadow-validate during plan; fail if dep graph diverges
pgevolve plan --db dev --shadow-validate --shadow-strict

Shadow backend selection follows [shadow].backend in pgevolve.toml (auto | testcontainers | dsn). See configuration.

Configuration reference

Everything in pgevolve.toml. The capability spec lists what's implemented vs. planned; this file walks through how to use what's implemented today.

pgevolve init creates a minimal config; this guide explains each section.

File location

By default pgevolve loads ./pgevolve.toml. Override with --config <path> on any command.

[project]

[project]
name           = "myapp"            # informational
schema_dir     = "schema"           # relative to the config file
plan_dir       = "plans"            # relative to the config file
layout_profile = "schema-mirror"    # one of the built-ins, or a path

[managed]

[managed]
schemas        = ["app", "billing"]
ignore_objects = ["app.legacy_etl_*", "billing.audit_*"]

The [managed] filter is the safety net. Even if your source tree declares only one table, an unfiltered apply would emit drops for every catalog object outside your control. The filter prevents that.

[planner]

[planner]
strategy = "online"   # "atomic" | "online"

[planner.online_rewrites]

[planner.online_rewrites]
create_index_concurrent       = true
fk_not_valid_then_validate    = true
check_not_valid_then_validate = true
not_null_via_check_pattern    = true
refresh_mv_concurrently       = true
view_drop_create_dependents   = true

Each switch defaults to true. Setting any to false disables that specific rewrite without dropping to atomic mode. Useful for environments where you want online behavior in general but need to opt out of one pattern (e.g., a managed-service Postgres that disallows CONCURRENTLY).

[environments.<name>]

[environments.dev]
url      = "postgres://localhost/myapp_dev"

[environments.prod]
url_env  = "DATABASE_URL_PROD"        # read DSN from env var (recommended)
strategy = "online"                   # overrides [planner].strategy for --db=prod

[environments.test]
url      = "postgres://localhost/myapp_test"
strategy = "atomic"                   # opt-out for fast / hermetic test DB

Omit both url and url_env to fall through to PGEVOLVE_DATABASE_URL and libpq env vars (PGHOST, PGUSER, etc.).

Connection precedence

For pgevolve <cmd> --db <env>:

1. --url <dsn> (CLI argument)
2. [environments.<env>].url
3. [environments.<env>].url_env
4. PGEVOLVE_DATABASE_URL
5. libpq env vars
6. ~/.pgpass

[shadow]

[shadow]
backend          = "auto"         # auto | testcontainers | dsn
url              = "postgres://localhost/myapp_shadow"   # for backend = "dsn"
url_env          = "PGEVOLVE_SHADOW_URL"                 # alternative to url
reset            = "drop_schema_cascade"                 # drop_schema_cascade | none
extensions       = ["pgcrypto", "uuid-ossp"]
postgres_version = "17"           # major version; used to select container or validate DSN

backend values

• "auto" (default): uses url / url_env if set; otherwise tries testcontainers if Docker is available; otherwise errors with a helpful message.
• "testcontainers": always uses Docker. Hermetic; requires Docker to be running. Pulls postgres:<major>-alpine.
• "dsn": connects to a user-supplied Postgres. No Docker required. Useful for developers without Docker or for projects with pre-installed extensions (TimescaleDB, PostGIS, etc.).

pgevolve validate --shadow and the --shadow-validate flag on plan / diff / validate all read this block. Without it those flags error out with a helpful message.

[[lint_waiver]]

[[lint_waiver]]
rule   = "column-position-drift"
target = "app.users"
reason = "applied via separate rewrite-table operation; see PR #234"

[[lint_waiver]] rows acknowledge LintAtPlan-severity findings so that pgevolve plan doesn't refuse with exit 2. Live in the plan's intent.toml, not in pgevolve.toml.

Match semantics: a waiver applies when rule equals the finding's rule name and target is a substring of the finding's message. A waiver that matches zero findings is reported as a warning ("unused waiver").

Preflight at apply time validates structural well-formedness: both rule and target must be non-empty strings. A malformed waiver row causes preflight to exit 2.

Multiple [[lint_waiver]] rows are supported — use one per finding.

[cluster]

[cluster]
project = "../my-cluster"

Optional. Links this per-DB project to a sibling cluster project (managed via pgevolve cluster … against a pgevolve-cluster.toml). When set, cluster-aware lints (e.g. grant-references-unknown-role) cross-check grantee role names against the linked cluster project's declared roles.

See docs/spec/cluster.md for the cluster surface and docs/spec/grants.md for the cross-check rules.

Worked example: production-grade config

[project]
name           = "ledger"
schema_dir     = "schema"
plan_dir       = "plans"
layout_profile = "schema-mirror"

[managed]
schemas        = ["app", "billing", "audit"]
ignore_objects = ["audit.legacy_*"]

[planner]
strategy = "online"

[planner.online_rewrites]
# Production DB doesn't allow CONCURRENTLY (RDS-style).
create_index_concurrent       = false
fk_not_valid_then_validate    = true
check_not_valid_then_validate = true
not_null_via_check_pattern    = true

[environments.dev]
url      = "postgres://localhost/ledger_dev"
strategy = "atomic"               # fast local iteration

[environments.staging]
url_env  = "DATABASE_URL_STAGING"

[environments.prod]
url_env  = "DATABASE_URL_PROD"

[shadow]
backend          = "testcontainers"
postgres_version = "16"

CI runs pgevolve validate --shadow on every PR. The dev developer runs pgevolve plan --db dev, then pgevolve plan --db staging once the diff stabilizes; the same plan directory is applied to staging and production after intent-file approval.

Plan format

A plan directory is the unit of code review. This guide explains what each file means and how the executor consumes it.

plans/2026-05-11-abc1234567890123/
├── plan.sql        ← canonical artifact (commit this)
├── intent.toml     ← destructive approvals (commit this; flip approvals when ready)
└── manifest.toml   ← plan id, version metadata, embedded pre-image (commit this)

All three files are plain text. Commit them to the same repo as your schema/ tree — the plan directory is part of the migration history.

plan.sql

The applyable artifact. Reads cleanly with psql -f plan.sql for people who want to bypass the executor — pgevolve only relies on the structured -- @pgevolve directive comments to drive transactions and audit logging.

Header directives

-- @pgevolve plan id=abc1234567890123 version=0.3.3 ruleset=1 created=2026-05-11T18:42:11Z
-- @pgevolve source_rev=git:c0ffeeabc
-- @pgevolve target=tid-xyz
-- @pgevolve intents_required=2

Group and step directives

Each transactional group is wrapped in BEGIN; ... COMMIT;. Each step has its own directive line:

-- @pgevolve group id=1 transactional=true
BEGIN;
-- @pgevolve step=1 kind=create_table destructive=false targets=app.invoices
CREATE TABLE app.invoices ( ... );
-- @pgevolve step=2 kind=add_constraint_not_valid destructive=false targets=app.invoices.invoices_customer_fk
ALTER TABLE app.invoices ADD CONSTRAINT invoices_customer_fk
  FOREIGN KEY (customer_id) REFERENCES app.customers(id) NOT VALID;
COMMIT;

-- @pgevolve group id=2 transactional=false
-- @pgevolve step=3 kind=create_index_concurrent destructive=false targets=app.invoices.invoices_customer_idx
CREATE INDEX CONCURRENTLY invoices_customer_idx ON app.invoices (customer_id);

-- @pgevolve group id=3 transactional=true
BEGIN;
-- @pgevolve step=4 kind=validate_constraint destructive=false targets=app.invoices.invoices_customer_fk
ALTER TABLE app.invoices VALIDATE CONSTRAINT invoices_customer_fk;
-- @pgevolve step=5 kind=drop_column destructive=true intent_id=1 targets=app.users.legacy_email
ALTER TABLE app.users DROP COLUMN legacy_email;
COMMIT;

Step kinds — v0.2 additions (views and materialized views)

The following step kinds were added in v0.2 alongside view and materialized view support:

Step kinds — v0.2 additions (user-defined types)

The following step kinds were added in v0.2 alongside enum, domain, and composite type support:

ReplaceWithCascade. When a type change cannot be applied in place (e.g., dropping or reordering enum values, reordering composite attributes, or changing a domain's base type), the planner emits DROP TYPE <qname> CASCADE followed by CREATE TYPE. Both steps appear in the plan as drop_type (destructive — requires intent approval) and create_type.

Step kinds — v0.2 additions (functions and procedures)

The following step kinds were added in v0.2 alongside function and procedure support:

ReplaceWithCascade for functions. When a function's language or return type changes, CREATE OR REPLACE FUNCTION is not safe. The planner emits DROP FUNCTION <qname> CASCADE (destructive — requires intent approval) followed by create_or_replace_function. The CASCADE propagates to all dependent views and functions automatically.

Step kinds — v0.2 additions (extensions, triggers, partitions)

Step kinds — v0.3 additions (cluster roles)

These step kinds appear only in cluster plans (pgevolve cluster plan) — not in per-DB plans.

Step kinds — v0.3.1 additions (ownership and grants)

Step kinds — v0.3.2 additions (row-level security)

Step kinds — v0.3.3 additions (storage reloptions)

intent.toml

plan_id = "abc1234567890123"

[[intent]]
id       = 1
step     = 5
kind     = "drop_column"
target   = "app.users.legacy_email"
reason   = "drops column legacy_email"
approved = false

Every destructive step gets one [[intent]] row. intent.toml also supports [[step_override]] rows (see below). The executor:

• Reads this file at apply time.
• Refuses to run while any row has approved = false.
• Records the approval state in the audit log.

Approving destructive intents

Open intent.toml, change approved = false to approved = true for each row you want to allow, and commit the change. The plan id field must stay intact; pgevolve cross-checks it against plan.sql and manifest.toml and rejects mismatches.

Approval is intentional friction. A reviewer should be the one flipping approved = true, not the same person who authored the change. Treat the diff in intent.toml as the "are you really sure?" gate.

[[step_override]] — suppress or skip steps

intent.toml also accepts [[step_override]] rows. Step overrides let you suppress specific planner steps (e.g., skip a refresh_materialized_view during a maintenance window without regenerating the plan):

[[step_override]]
kind = "refresh_materialized_view"
target = "app.daily_summary"
suppress = true

When to use. Step overrides are appropriate for one-off operational situations (e.g., deferring an expensive REFRESH to off-peak hours). They are not a substitute for intent approval — destructive steps still require approved = true even when a [[step_override]] is present.

manifest.toml

plan_id                 = "abc1234567890123"
plan_hash               = "abc1234567890123…"  # full 64-char hex
pgevolve_version        = "0.3.3"
planner_ruleset_version = 1
source_rev              = "git:c0ffeeabc"
target_identity         = "tid-xyz"
created_at              = "2026-05-11T18:42:11Z"
target_snapshot_json    = "..."                # embedded pre-image catalog as pretty-printed JSON

Round-trip property

write_plan_dir(&p, dir); read_plan_dir(dir) == p — modulo the grafted destructive_reason (which lives in intent.toml, not in plan.sql). The round-trip is property-tested over random catalogs.

What pgevolve does and does NOT touch

pgevolve never modifies a plan directory after it's written. Edits to intent.toml's approval flags are made by users (typically in a code review). Anything else is suspicious.

Object kinds

Every top-level Postgres object kind pgevolve does, will, or won't manage. See ../README.md for the status legend.

Tables and schemas — core surface

Partitioning

Views

Functions, procedures, triggers

Custom types

Extensions

Triggers

Tests (whole Triggers section): tier-1: crates/pgevolve-core/src/ir/trigger.rs::tests, parse/builder/create_trigger_stmt.rs::tests, diff/triggers.rs::tests, plan/rewrite/triggers.rs::tests, plan/rewrite/emit/trigger.rs::tests, lint/rules/trigger_references_unmanaged_function.rs, lint/rules/trigger_references_unmanaged_table.rs; tier-C: every fixture under crates/pgevolve-conformance/tests/cases/objects/triggers/ (12 fixtures) plus objects/triggers/lint-unmanaged-function, lint-unmanaged-table.

IR shape

Trigger is a flat struct in pgevolve-core::ir::trigger:

Catalog::triggers: Vec<Trigger> — flat collection, sorted by (table_name, qname) after canonicalize().

Parser support

• CREATE [CONSTRAINT] TRIGGER name timing event [OR event …] ON table [REFERENCING …] [FOR [EACH] {ROW|STATEMENT}] [WHEN (expr)] EXECUTE {FUNCTION|PROCEDURE} fn() — all documented Postgres syntax variants accepted.
• COMMENT ON TRIGGER name ON table IS '…' — accepted alongside COMMENT ON FUNCTION and COMMENT ON EXTENSION.
• ALTER TRIGGER in source files — rejected at statement classification with a structural error. The only ALTER TRIGGER Postgres exposes is a rename; pgevolve does not support trigger renames.

Catalog reader

Queries pg_trigger joined with pg_class (for the relation name), pg_namespace, and pg_description. Two filters apply:

• NOT tgisinternal — excludes system-generated internal triggers (e.g., deferrable constraint enforcement triggers).
• NOT EXISTS (SELECT 1 FROM pg_depend WHERE objid = tg.oid AND deptype = 'e') — excludes triggers installed by extensions; consistent with the deptype='e' filter applied to every other catalog query.

Differ

There is no ALTER TRIGGER for body-level changes in Postgres; the only path is drop + recreate. CommentOn is always emitted separately when only the comment differs.

Planner steps

DropTrigger is placed in the same destructive ordering bucket as DropTable and DropFunction. CreateTrigger is placed after the target relation and trigger function are both created/updated.

Dependency edges

Both edges are DepSource::Structural. The function edge also ensures that a function change that triggers ReplaceWithCascade (drop + recreate the function) will cascade a drop + recreate of any trigger that references it, in the correct order.

Lint rules

Out of scope / notable gaps

• ALTER TRIGGER … RENAME TO — not supported. Rename is treated as Drop + Create (old name disappears, new name appears).
• Event triggers (CREATE EVENT TRIGGER) — a separate object kind, now ✅ Supported. See the EVENT TRIGGER row in the table above.
• WHEN clause dependency extraction — the WHEN predicate is stored as a NormalizedExpr for canonical diffing but its column references are not added as explicit dep edges. Renames of referenced columns will surface as a structural diff, prompting a Drop + Create.

Partitioning (detail)

Tests (whole Partitioning section): tier-1: crates/pgevolve-core/src/ir/partition.rs::tests, parse/builder/alter_table_attach_partition.rs::tests, plan/rewrite/partitions.rs::tests, lint/rules/partition_references_unmanaged_parent.rs; tier-2: crates/pgevolve-core/src/catalog/queries/partitions.rs, partitioned_tables.rs; tier-C: every fixture under objects/partitions/ (12 fixtures) plus failure/partitions/reject-partition-to-nonpartitioned, reject-rekey.

IR shape

Partitioning is modeled as two optional fields on Table in pgevolve-core::ir::table, backed by types in pgevolve-core::ir::partition:

partition_by: Option<PartitionBy> — present on partitioned-parent tables.

Each PartitionColumn carries kind: PartitionColumnKind (Column(Identifier) or Expr(NormalizedExpr)), an optional collation: Option<QualifiedName>, and an optional opclass: Option<QualifiedName>.

partition_of: Option<PartitionOf> — present on partition-child tables.

PartitionBounds variants:

BoundDatum — Literal(NormalizedExpr) | MinValue | MaxValue.

A table may have both partition_by and partition_of set simultaneously (sub-partitioning).

Source surface — three syntactic forms

All three forms parse into the same Table IR:

Catalog reader

Two catalog queries:

• SELECT_PARTITIONED_TABLES — pg_class.relkind = 'p' + pg_get_partkeydef(c.oid). Reads the partition key definition for each partitioned-parent table and re-parses it into PartitionBy. Filters: NOT extension-owned.
• SELECT_PARTITIONS — pg_class.relispartition = true + pg_get_expr(c.relpartbound, c.oid). Reads the partition bounds text and re-parses it into PartitionOf. Joins pg_inherits to get the parent name. Filters: NOT extension-owned; scoped to managed schemas.

Both queries apply the NOT EXISTS (pg_depend deptype='e') filter consistent with every other catalog query.

Differ

Planner steps

AttachPartition is ordered in the same post-create bucket as CreateIndex (after the parent and child tables both exist). DetachPartition is ordered in the same destructive bucket as DropTable.

For a CreateTable on a partition child (Form 1 / Form 2 source), the planner emits the CREATE TABLE … PARTITION OF parent FOR VALUES … SQL directly; no separate AttachPartition step is needed. AttachPartition is emitted only when an existing standalone table is being attached to a parent, or when bounds are rebounding.

Dependency edges

The edge is DepSource::Structural. It ensures that when both a parent and a child partition are new, the parent's CreateTable is ordered before the child's.

Lint rules

Out of scope / notable gaps

• DETACH PARTITION CONCURRENTLY — not emitted. The non-concurrent DETACH PARTITION is used, which takes an AccessExclusiveLock. Concurrent detach is listed as ⛔ not planned for now.
• FOREIGN TABLE PARTITION OF — foreign-table partitions are not modeled. Foreign tables are 🔮 Future.
• Per-partition TABLESPACE and storage parameters — partition bounds + reloptions are modeled (partitions are Table in IR, so they inherit table reloptions automatically). Per-partition TABLESPACE overrides are ✅ Supported (see the TABLESPACE row under Storage and physical layout).
• Partition pruning at plan time — pgevolve does not skip unaffected partitions when only the parent changes. All managed partitions are included in every diff. Pruning is 🔮 Future.
• Pre-flight partition-overlap detection — pgevolve does not validate that declared bounds are non-overlapping before applying. Postgres enforces this at DDL time; a failed ATTACH PARTITION will surface as an apply error.

Security and roles

Replication and federation

Storage and physical layout

Operators, casts, collations, text search

Statistics, rules, and other helpers

What pgevolve deliberately does not manage

PG 18-only features

These features ship only on Postgres 18+. They are not part of the v0.3.6 PG 18 catalog-support work; each gets its own roadmap entry.

Column types

Every Postgres type family with pgevolve's support status. The IR's ColumnType enum is the source of truth; entries below mirror its variants and document the gaps.

See ../README.md for the status legend.

Numeric

Tests (whole section): tier-1: crates/pgevolve-core/src/ir/column_type.rs::tests (variant parse + display); tier-2: crates/pgevolve-core/tests/fixtures/parser/equivalent_pairs/0001-int-aliases, 0004-timestamp-tz; tier-C: objects/columns/alter-column-type-widening, alter-column-type-narrowing.

Character

Tests (whole section): tier-1: crates/pgevolve-core/src/ir/column_type.rs::tests; tier-2: crates/pgevolve-core/tests/fixtures/parser/equivalent_pairs/0003-varchar-aliases.

Binary

Tests: tier-1: crates/pgevolve-core/src/ir/column_type.rs::tests; tier-3: crates/pgevolve-core/tests/catalog_round_trip.rs.

Date / time

Tests (whole section): tier-1: crates/pgevolve-core/src/ir/column_type.rs::tests; tier-2: crates/pgevolve-core/tests/fixtures/parser/equivalent_pairs/0004-timestamp-tz.

Networking

Tests: tier-1: crates/pgevolve-core/src/ir/column_type.rs::tests; tier-3: crates/pgevolve-core/tests/catalog_round_trip.rs.

UUID, JSON, XML

Tests: tier-1: crates/pgevolve-core/src/ir/column_type.rs::tests; tier-3: crates/pgevolve-core/tests/catalog_round_trip.rs.

Bit strings

Tests: tier-1: crates/pgevolve-core/src/ir/column_type.rs::tests.

Arrays

Tests: tier-1: crates/pgevolve-core/src/ir/column_type.rs::tests.

Range and multirange types

Geometric

Text search

Object identifier types

User-defined types

Tests: tier-1: crates/pgevolve-core/src/ir/user_type.rs::tests; tier-C: objects/enums/, objects/domains/, objects/composites/ (see objects.md for fixture list).

Catch-all fallback

Type-level attributes

Constraints

Constraint kinds pgevolve models, plus their attributes.

See ../README.md for the status legend.

Kinds

FOREIGN KEY attributes

Tests (whole section): tier-1: crates/pgevolve-core/src/ir/constraint.rs::tests, parse/builder/create_stmt.rs::tests; tier-C: objects/tables/add-constraint-foreign-key.

CHECK attributes

Tests (whole section): tier-1: crates/pgevolve-core/src/ir/constraint.rs::tests, plan/rewrite/check_not_valid_validate.rs; tier-C: objects/tables/add-constraint-check, drop-constraint-check.

PRIMARY KEY / UNIQUE attributes

Tests (whole section): tier-1: crates/pgevolve-core/src/ir/constraint.rs::tests; tier-2: parser/equivalent_pairs/0006-pk-inline-vs-table-constraint.

Constraint-level features

Indexes

Index access methods and per-index options.

See ../README.md for the status legend.

Access methods

Tests (whole section): tier-1: crates/pgevolve-core/src/ir/index.rs::tests, parse/builder/index_stmt.rs::tests; tier-3: crates/pgevolve-core/tests/catalog_round_trip.rs (per-method introspection).

Per-index options

Tests (whole section): tier-1: crates/pgevolve-core/src/ir/index.rs::tests, parse/builder/index_stmt.rs::tests, render/index.rs::tests; tier-3: crates/pgevolve-core/tests/catalog_round_trip.rs.

Online-rewrite rules for indexes

Index naming

Tests: tier-1: crates/pgevolve-core/src/parse/builder/index_stmt.rs::tests; tier-3: crates/pgevolve-core/tests/catalog_round_trip.rs (constraint-backing index identification).

Object grants + ownership + default privileges

pgevolve manages Postgres object permissions declaratively. Every grantable IR object (Schema, Sequence, Table, View, MaterializedView, Function, Procedure, UserType) carries:

• owner: Option<Identifier> — opt-in object ownership.
• grants: Vec<Grant> — per-object ACL entries.

Plus the top-level Catalog.default_privileges: Vec<DefaultPrivilegeRule> mirroring pg_default_acl.

Source surface

ALTER TABLE app.t OWNER TO app_owner;
GRANT SELECT ON app.t TO readers;
GRANT INSERT (name) ON app.t TO readers;
GRANT EXECUTE ON FUNCTION app.foo(int) TO readers;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app GRANT SELECT ON TABLES TO readers;

REVOKE statements are rejected in source — revokes come from the diff against the catalog.

Tests: tier-1: crates/pgevolve-core/src/ir/grant.rs::tests, parse/builder/grants.rs, parse/builder/owner_stmt.rs::tests, parse/builder/default_privileges.rs; tier-2: crates/pgevolve-core/tests/catalog_grants.rs; tier-C: objects/grants/owner, objects/grants/table, objects/grants/schema, objects/grants/sequence, objects/grants/function, objects/grants/default-privs.

Drift policy

Catalog grants to roles not declared in source are tolerated and surface as a grants-to-unmanaged-role warning, never silently revoked. This protects out-of-band workflows (e.g., temp consultant grants) while still surfacing the drift so operators can decide.

Tests: tier-1: crates/pgevolve-core/src/lint/rules/grants_to_unmanaged_role.rs::tests, crates/pgevolve-core/src/diff/grants.rs::tests; tier-C: objects/grants/lint.

Cluster-link option

Optional [cluster] block in pgevolve.toml:

[cluster]
project = "../my-cluster"

When set, the grant-references-unknown-role lint cross-checks every grantee role name (in grants, owners, default-priv targets) against the linked cluster project's roles/*.sql. Missing role → Error severity, catching typos pre-apply.

When absent, per-DB independence preserved.

Tests: tier-1: crates/pgevolve-core/src/lint/rules/grant_references_unknown_role.rs::tests.

Ownership semantics

• owner: None — unmanaged. The differ ignores ownership for this object; shadow-validate also ignores any catalog-side owner.
• owner: Some(role) — managed. Diff emits ALTER <KIND> ... OWNER TO role when catalog disagrees.

Source authors opt in per-object by writing an ALTER ... OWNER TO statement. Omitting it leaves the object unmanaged.

Tests: tier-1: crates/pgevolve-core/src/parse/builder/owner_stmt.rs::tests, crates/pgevolve-core/src/diff/owner_op.rs::tests; tier-C: objects/grants/owner.

Passwords

Passwords are not modeled. v0.3.0's cluster surface already says this for roles; this sub-spec inherits the same stance for object-level permissions.

Lint rules

• grants-to-unmanaged-role (warning, waivable) — catalog has grants to roles not in source. Tests: tier-1: crates/pgevolve-core/src/lint/rules/grants_to_unmanaged_role.rs::tests; tier-C: objects/grants/lint.
• revoke-from-owner (error, non-waivable) — diff would REVOKE from the object's owner. PG silently rejects such REVOKEs; we pre-empt. Tests: tier-1: crates/pgevolve-core/src/lint/rules/revoke_from_owner.rs::tests.
• grant-references-unknown-role (error, when [cluster].project is set) — grantee not declared in the linked cluster source. Tests: tier-1: crates/pgevolve-core/src/lint/rules/grant_references_unknown_role.rs::tests.

Out of scope

• DATABASE, TABLESPACE, LANGUAGE, FOREIGN TABLE grants — cluster-level or unmanaged.
• LARGE OBJECT grants — not declarative.
• Row-level security policies — v0.3.2.

Row-level security policies

pgevolve manages Postgres RLS declaratively. Tables carry:

• rls_enabled: bool — ALTER TABLE t ENABLE/DISABLE ROW LEVEL SECURITY.
• rls_forced: bool — ALTER TABLE t FORCE/NO FORCE ROW LEVEL SECURITY (applies even to the table owner).
• policies: Vec<Policy> — embedded; policies can't exist orphan.

Source surface

CREATE POLICY author_only ON app.docs
    AS PERMISSIVE              -- (default)
    FOR ALL                    -- (default)
    TO public                  -- (default)
    USING (author = current_user);

ALTER TABLE app.docs ENABLE ROW LEVEL SECURITY;
ALTER TABLE app.docs FORCE ROW LEVEL SECURITY;

ALTER POLICY and DROP POLICY are rejected in source — both come from the diff against the catalog.

Tests: tier-1: crates/pgevolve-core/src/ir/policy.rs::tests, parse/builder/policy_stmt.rs::tests, diff/policies.rs::tests; tier-2: crates/pgevolve-core/tests/catalog_policies.rs; tier-C: objects/policies/simple-permissive-policy, policy-with-check, policy-with-roles, restrictive-policy, enable-rls, disable-rls, force-rls-toggle, drop-policy-on-source-removal.

Command-kind changes recreate

PG's ALTER POLICY can change roles, USING, and WITH CHECK but NOT the command kind. If source changes a policy from FOR SELECT to FOR INSERT, pgevolve emits DROP POLICY + CREATE POLICY as two separate plan steps.

Tests: tier-1: crates/pgevolve-core/src/plan/rewrite/policies.rs::tests; tier-C: objects/policies/alter-policy-command-recreates, alter-policy-roles.

Cross-cluster role validation

Policy TO clauses reference roles. The v0.3.1 cross-cluster lint grant-references-unknown-role extends to policy roles when [cluster].project is set in pgevolve.toml.

FORCE without policies = denial

PG's behavior: FORCE ROW LEVEL SECURITY with no policies defined denies every row, including for the table owner. Almost always a configuration mistake. The force-rls-without-policies lint warns on this state.

Tests: tier-1: crates/pgevolve-core/src/lint/rules/force_rls_without_policies.rs::tests; tier-C: objects/policies/lint.

WITH CHECK validity

WITH CHECK is invalid on FOR SELECT and FOR DELETE policies (PG rejects). The source parser pre-empts with a clear error.

Tests: tier-1: crates/pgevolve-core/src/parse/builder/policy_stmt.rs::tests.

Out of scope

• ALTER POLICY ... RENAME TO ... in source — rejected. Operators can drop+create.
• SECURITY LABEL — not planned.
• leakproof / security_barrier on views — future.

Storage parameters / reloptions

pgevolve models PG WITH (storage_parameter = …) reloptions on tables, indexes, and materialized views. Each relkind has a typed *StorageOptions struct with named fields for the well-known options plus an extra: BTreeMap<String, String> for extension-registered or otherwise-unknown keys.

Semantics — None always means "unmanaged"

Every typed field is Option<T>. The semantics follow v0.3.1's owner pattern:

Tests (whole semantics table): tier-1: crates/pgevolve-core/src/diff/reloptions.rs::tests, crates/pgevolve-core/src/ir/reloptions.rs::tests, crates/pgevolve-core/src/ir/canon/reloptions.rs::tests.

Removing a reloption from source does NOT issue RESET. To clear a managed reloption:

1. Issue ALTER TABLE t RESET (fillfactor) out-of-band.
2. On the next plan run, catalog reads None, source is also None, diff is empty.

This is the same trade-off as v0.3.1's owner: Option<Identifier> — "unmanaged" must be safe to declare without triggering destructive resets.

Source surface

-- Inline at creation:
CREATE TABLE app.t (id bigint) WITH (fillfactor = 80, autovacuum_enabled = false);

-- ALTER post-creation:
ALTER TABLE app.t SET (parallel_workers = 4);

-- Indexes:
CREATE INDEX i ON app.t (id) WITH (fillfactor = 70);

-- Materialized views:
CREATE MATERIALIZED VIEW m WITH (fillfactor = 90) AS SELECT * FROM ...;

ALTER ... RESET (...) and ALTER ... RESET () are rejected in source.

Tests: tier-1: crates/pgevolve-core/src/parse/builder/reloptions.rs::tests; tier-2: crates/pgevolve-core/tests/catalog_reloptions.rs; tier-C: objects/reloptions/table-fillfactor, table-autovacuum-disabled, table-multi-set, mv-fillfactor, alter-table-set-after-create, index-fillfactor, index-brin-pages-per-range, index-gin-fastupdate, partition-inherits-reloptions.

Per-relkind validation

Tests: tier-1: crates/pgevolve-core/src/parse/builder/reloptions.rs::tests (per-relkind range checks).

Parser enforces PG's documented ranges at parse time:

• Tables / MVs fillfactor: 10..=100
• B-tree index fillfactor: 50..=100
• GiST / Hash index fillfactor: 10..=100
• SP-GiST index fillfactor: 90..=100
• BRIN / GIN index fillfactor: not supported → ParseError
• parallel_workers: 0..=1024
• toast_tuple_target: 128..=8160
• pages_per_range (BRIN): 1..=131072
• Numeric scale factors: NaN rejected

Supported keys

Tables / Materialized Views

fillfactor, parallel_workers, toast_tuple_target, user_catalog_table, and vacuum_truncate are typed fields. The autovacuum_* keys (including log_autovacuum_min_duration) are carried in the untyped extra: BTreeMap<String, String> bag alongside unknown/extension keys — they are not typed or validated at parse time (Postgres validates autovacuum values on apply).

Indexes

fillfactor, fastupdate (GIN), gin_pending_list_limit (GIN), buffering (GiST/SP-GiST, values: on/off/auto), deduplicate_items (B-tree, PG 13+), pages_per_range + autosummarize (BRIN).

Lint

• unmanaged-reloption (warning, waivable) — catalog has a typed reloption or extra-bag key not declared in source. Per the lenient drift policy, the differ doesn't RESET; the lint surfaces the drift so operators can decide. Tests: tier-1: crates/pgevolve-core/src/lint/rules/unmanaged_reloption.rs::tests; tier-C: objects/reloptions/lint.

Out of scope

• toast.* prefixed options (apply to TOAST tables). Rare; deferred.
• Active RESET via source. Operators clear out-of-band.
• Per-partition tablespace overrides. Per-partition reloptions are supported (partitions are Table in IR).

Known limitation: new objects with inline reloptions

Currently CREATE TABLE … WITH (…) / CREATE INDEX … WITH (…) / CREATE MATERIALIZED VIEW … WITH (…) source statements that target a brand-new (not-yet-in-catalog) object emit only the CREATE step without the WITH (…) clause. The reloptions are picked up on the next plan run as unmanaged-reloption warnings or, if source still declares them, as an ALTER … SET (…) step.

Workaround: run pgevolve twice (or apply once, plan again). Convergent in 2 iterations.

This is the same general gap that affects owner/grants/policies/RLS on new objects in v0.3.x — the inline new-object rendering doesn't yet include cross-cutting state. Tracked for a future v0.3.x maintenance release that closes the gap uniformly.

Publications

pgevolve models CREATE PUBLICATION as a first-class declarative IR object. A publication is a per-database global namespace object (not schema-qualified) that controls which tables and schemas are replicated via Postgres logical replication.

Source surface

All five Postgres syntactic forms are supported:

-- Form 1: FOR ALL TABLES — captures every current and future table
CREATE PUBLICATION pub_all FOR ALL TABLES;

-- Form 2: explicit FOR TABLE list (PG 14+)
CREATE PUBLICATION pub_tables
    FOR TABLE app.orders, app.items
    WITH (publish = 'insert, update, delete');

-- Form 3: FOR TABLES IN SCHEMA (PG 15+; requires min_pg_version = 15)
CREATE PUBLICATION pub_schema
    FOR TABLES IN SCHEMA app, billing;

-- Form 4: row filter on a table (PG 15+; requires min_pg_version = 15)
CREATE PUBLICATION pub_filtered
    FOR TABLE app.orders WHERE (status = 'active');

-- Form 5: explicit column list (PG 15+; requires min_pg_version = 15)
CREATE PUBLICATION pub_columns
    FOR TABLE app.orders (id, status, created_at);

-- Mixed: schemas + tables in one publication (PG 15+)
CREATE PUBLICATION pub_mixed
    FOR TABLE app.users, TABLES IN SCHEMA billing
    WITH (publish = 'insert, update', publish_via_partition_root = true);

The WITH clause parameters are:

Tests: tier-1: crates/pgevolve-core/src/parse/builder/publication_stmt.rs; tier-C: objects/publications/.

Semantics — lenient at the publication grain

Unlike field-level leniency (as in reloptions), pgevolve applies leniency at the whole-publication level:

A publication in source is fully managed (all fields tracked). A publication absent from source is left alone and surfaces via lint. This mirrors the v0.3.1 owner/grants pattern.

Mode-swap: ReplacePublication

When the scope mode changes between AllTables and Selective, no safe in-place ALTER PUBLICATION path exists in Postgres. pgevolve emits DROP PUBLICATION + CREATE PUBLICATION (a ReplacePublication logical operation), which is destructive and requires intent approval.

Tests: tier-C: objects/publications/replace-all-tables-to-selective.

PG-version gating via [managed].min_pg_version

Three source features require Postgres 15+:

• FOR TABLES IN SCHEMA (schema-scope)
• Row filters (WHERE (...) on table entries)
• Explicit column lists

Using any of these features when [managed].min_pg_version is below 15 (the default is 14) is a lint error (publication-feature-requires-pg-version, Error severity, not waivable). This surfaces the version dependency at lint time rather than at apply time with an opaque Postgres syntax error.

# pgevolve.toml
[managed]
min_pg_version = 15

Tests: tier-C: objects/publications/lint-pg-version-schema-scope, lint-pg-version-row-filter, lint-pg-version-column-list.

Supported publish options

At least one kind must be enabled; an all-false PublishKinds is rejected at canon time.

publish_via_partition_root

When true, INSERT/UPDATE/DELETE operations on partition children are reported using the partition root's row identity (the parent table's replica identity). This is PG 13+ behavior; pgevolve tracks it as a plain bool on Publication. No version gate is applied (PG 13 is below our minimum of PG 14).

11 StepKind variants

4 lint rules

Tests: tier-1: crates/pgevolve-core/src/lint/rules/unmanaged_publication.rs::tests, publication_captures_unmanaged_table.rs::tests, publication_row_filter_references_unmanaged_column.rs::tests, publication_feature_requires_pg_version.rs::tests; tier-C: objects/publications/lint-*.

Out of scope

• SUBSCRIPTION — consumer side; implemented as of v0.3.5. See subscriptions.md for the full surface.
• GRANT on publications — Postgres does not support object-level grants on publications; they have no ACL. Out of scope by PG design.
• ALTER PUBLICATION … RENAME TO — not supported. Rename is treated as Drop + Create (old name disappears, new name appears).
• Replication slots and origins — cluster-level admin objects outside the schema-management remit.

Subscriptions

pgevolve models CREATE SUBSCRIPTION as a first-class declarative IR object. A subscription is a per-database global namespace object (not schema-qualified) that controls which publications a database subscribes to via Postgres logical replication.

Source surface

Three representative CREATE forms are supported:

-- Form 1: minimal — subscribe to a single publication
CREATE SUBSCRIPTION sub_main
    CONNECTION 'host=replica.example.com dbname=app user=repl password=${REPL_PWD}'
    PUBLICATION pub_all;

-- Form 2: subscribe to multiple publications with WITH options
CREATE SUBSCRIPTION sub_filtered
    CONNECTION 'host=replica.example.com dbname=app user=repl password=${REPL_PWD}'
    PUBLICATION pub_orders, pub_users
    WITH (binary = true, streaming = on, two_phase = false);

-- Form 3: all currently-supported options
CREATE SUBSCRIPTION sub_full
    CONNECTION 'host=replica.example.com dbname=app user=repl password=${REPL_PWD}'
    PUBLICATION pub_all
    WITH (
        enabled           = true,
        slot_name         = 'myslot',
        binary            = false,
        streaming         = parallel,       -- PG 16+
        two_phase         = false,
        disable_on_error  = true,           -- PG 15+
        origin            = any             -- PG 16+
    );

Operational verb forms are rejected at parse time (see below).

${VAR} env-var interpolation in CONNECTION strings

The CONNECTION string almost always contains credentials that must not be stored in source control. pgevolve supports ${VAR} placeholder syntax anywhere inside the connection string. The literal ${VAR} tokens are:

• Stored verbatim in the source IR and in plan.sql.
• Never logged, persisted, or echoed during plan generation.
• Resolved at apply-time preflight: before pgevolve opens any database connection, it scans every step's SQL for ${...} references, resolves each against the process environment (std::env::var), and fails with a clear error if any reference is unset.

Example workflow:

# Set the credential in the shell before applying — never commit it
export REPL_PWD="$(vault kv get -field=password secret/repl)"
pgevolve apply plans/2026-05-26-abc1234567890123

If REPL_PWD is not set, pgevolve refuses to start the apply and prints:

error: unresolved env-var reference ${REPL_PWD} in step 1 (create_subscription)

Do not use $$-quoting or single-quote escapes inside the password value — the substitution is literal string replacement before the SQL is sent to tokio-postgres. The connection string itself is a libpq DSN, not SQL.

Per-field lenient WITH options

Every field is Option<T>. None means "unmanaged" — pgevolve neither sets nor resets the option. Some(value) means "managed" — the differ emits an ALTER to converge the live subscription.

CREATE-only fields (create_slot, copy_data): these are accepted in source CREATE statements (so users can declare them) but the differ never includes them in AlterSubscriptionSetOptions deltas — pg_subscription does not store the CREATE-time decision, so there is nothing to diff against.

Diff-modulo-password behavior

The connection field on a Subscription stores the raw connection string verbatim, including unresolved ${VAR} placeholders. The differ compares connection strings as opaque strings: if the source string differs from the catalog-read string, an alter_connection step is emitted.

Because pg_subscription.subconninfo stores the live connection string (with the resolved password at subscription-create time), the catalog reader replaces any password=<value> segment with password=${__PGEVOLVE_REDACTED} before diff comparison. This prevents a spurious alter_connection step every plan cycle due to the round-trip asymmetry between ${VAR} in source and a real password in pg_subscription.

Supported lint rules

The last two rules are not waivable — they catch security and compatibility problems that would surface at apply time or in a security audit.

Tests: tier-1: crates/pgevolve-core/src/lint/rules/unmanaged_subscription.rs::tests, subscription_references_undeclared_publication.rs::tests, subscription_feature_requires_pg_version.rs::tests, subscription_password_in_source.rs::tests; tier-C: objects/subscriptions/lint-*.

Operational verb rejection

The following operational SQL forms are rejected at parse time with a clear error message:

ALTER SUBSCRIPTION s REFRESH PUBLICATION;         -- rejected
ALTER SUBSCRIPTION s SKIP (lsn = '0/12345678');  -- rejected
ALTER SUBSCRIPTION s ENABLE;                      -- rejected (use WITH (enabled = true))
ALTER SUBSCRIPTION s DISABLE;                     -- rejected (use WITH (enabled = false))

These are point-in-time operations, not declarative state. pgevolve only accepts the source-state-expressing forms that can be diffed and re-applied safely:

ALTER SUBSCRIPTION s CONNECTION '...';
ALTER SUBSCRIPTION s ADD PUBLICATION pub_b;
ALTER SUBSCRIPTION s DROP PUBLICATION pub_a;
ALTER SUBSCRIPTION s SET PUBLICATION pub_b;
ALTER SUBSCRIPTION s SET (binary = true);
ALTER SUBSCRIPTION s OWNER TO new_owner;

pg_subscription superuser restriction

Postgres restricts pg_subscription catalog access to superusers. pgevolve's catalog reader therefore requires superuser (or pg_monitor) privileges to read existing subscriptions. If the plan user lacks these privileges, subscriptions are treated as absent from the catalog (the differ emits a create_subscription step). Apply with a superuser role or grant pg_monitor to the plan user.

Conformance fixtures

12 fixtures under crates/pgevolve-conformance/tests/cases/objects/subscriptions/. All fixtures carry [fixture] apply = false because subscriptions require a publisher cluster at apply time and the conformance harness targets a single ephemeral Postgres instance. The fixtures validate parse, diff, plan, and lint without attempting to apply.

Tests: tier-C: objects/subscriptions/create-minimal, create-with-options, create-multi-publication, drop, alter-connection, alter-add-publication, alter-drop-publication, alter-set-options, alter-enabled-disable, comment-on, lint-password-in-source, lint-pg-version-gating.

8 StepKind variants

Out of scope

• ALTER SUBSCRIPTION … RENAME TO — not supported. Rename is treated as Drop + Create (old name disappears, new name appears).
• ALTER SUBSCRIPTION … REFRESH PUBLICATION — operational verb; rejected in source. Run out-of-band when needed.
• ALTER SUBSCRIPTION … SKIP (lsn = …) — point-in-time skip of a replication conflict; not a declarative property. Run out-of-band.
• Subscription statistics (pg_stat_subscription, worker tables) — runtime telemetry, not schema management state.
• Replication slots — cluster-level admin objects; see docs/spec/cluster.md for the cluster surface.

Statistics

pgevolve models CREATE STATISTICS as a first-class declarative IR object. A statistic is a schema-qualified object (pg_statistic_ext) that captures cross-column correlations for the Postgres query planner.

Source surface

Four syntactic forms are supported:

-- Form 1: basic — all three kinds enabled (pg default)
CREATE STATISTICS app.orders_s ON (status, region) FROM app.orders;

-- Form 2: explicit kinds — only the requested kinds
CREATE STATISTICS app.orders_dep ON (status, region) FROM app.orders
    (dependencies);

-- Form 3: expression statistic (PG 14+)
CREATE STATISTICS app.orders_expr ON (lower(region), status) FROM app.orders;

-- Form 4: mixed columns + expression, explicit kinds
CREATE STATISTICS app.orders_mixed ON (status, lower(region)) FROM app.orders
    (ndistinct, mcv);

The kinds clause accepts any non-empty subset of:

• ndistinct — multi-column distinct-value counts
• dependencies — functional dependencies between columns
• mcv — most-common-value lists per column combination

Omitting the kinds clause enables all three (Postgres default).

Explicit names required. The anonymous form (CREATE STATISTICS ON (...) FROM t) is rejected at parse time, mirroring the no-anonymous-indexes policy. Every statistic managed by pgevolve must carry a schema-qualified name.

Semantics — lenient at the statistic grain

pgevolve applies leniency at the whole-statistic level:

A statistic present in source is fully managed. A statistic absent from source is left alone and surfaced via lint.

Granular differ — two paths

Postgres has no ALTER STATISTICS for column lists or kinds (it would require rebuilding the statistics object anyway). pgevolve therefore emits:

• AlterStatisticSetTarget (ALTER STATISTICS name SET STATISTICS n) when only statistics_target changed. This is cheap and non-destructive.
• ReplaceStatistic (DROP STATISTICS + CREATE STATISTICS) for any other structural change (columns, kinds, target table rename). This is destructive and requires intent approval.

5 StepKind variants

1 lint rule

Tests: tier-1: crates/pgevolve-core/src/lint/rules/unmanaged_statistic.rs::tests; tier-C: objects/statistics/lint-unmanaged.

Out of scope

• Anonymous form (CREATE STATISTICS ON (...) FROM t) — rejected at parse time. Explicit names are required so pgevolve can track identity across migrations.
• INCLUDE clause (PG 18+) — not yet modeled. Deferred to a future patch.
• ALTER STATISTICS … RENAME TO — not supported. Rename is treated as Drop + Create (old name disappears, new name appears).
• GRANT on statistics — Postgres does not support object-level grants on pg_statistic_ext objects. Out of scope by PG design.

Catalog reader

Statistics are read from pg_statistic_ext joined with pg_namespace:

• stxname, stxnamespace — name + schema.
• stxrelid — target table OID (resolved to QualifiedName via pg_class).
• stxkind — char[] with 'd' (dependencies), 'f' (ndistinct), 'm' (mcv).
• stxkeys — int2vector of column attribute numbers (resolved to Identifier via pg_attribute).
• Expression statistics (PG 14+): pg_get_statisticsobjdef_expressions(oid) returns a text[] of expression SQL; each entry is parsed + canonicalized via NormalizedExpr.
• stxstattarget — the statistics_target override; -1 maps to None.
• pg_description — COMMENT ON STATISTICS.

Tests: tier-2: crates/pgevolve-core/tests/statistics_round_trip.rs; tier-C: objects/statistics/.

Conformance fixtures

9 fixtures under crates/pgevolve-conformance/tests/cases/objects/statistics/:

Collations

pgevolve models CREATE COLLATION as a first-class declarative IR object. A collation is a schema-qualified object (pg_collation) that names a locale-data provider plus the lc_collate / lc_ctype strings used by sort and ctype-aware comparisons. Per-column COLLATE clauses (always supported) reference collations by qname; v0.3.8 adds the ability to manage the collation objects themselves.

Source surface

Three syntactic forms are supported, mirroring Postgres:

-- Form 1: libc + locale shorthand (default provider).
CREATE COLLATION app.de_DE (locale = 'de_DE.utf8');

-- Form 2: explicit provider + locale shorthand.
CREATE COLLATION app.case_insensitive
    (provider = icu, locale = 'und', deterministic = false);

-- Form 3: explicit lc_collate + lc_ctype.
CREATE COLLATION app.mixed
    (provider = libc, lc_collate = 'C', lc_ctype = 'en_US.utf8');

The IR always stores lc_collate + lc_ctype separately. When the source used locale = 'X', the parser normalizes to lc_collate = 'X' + lc_ctype = 'X'. The renderer collapses back to the locale = '...' shorthand when the two are equal, so the round-trip is lossless and canonical.

Tests: tier-1: crates/pgevolve-core/src/parse/builder/create_collation_stmt.rs; tier-C: objects/collations/.

IR shape

Collation is a flat struct in pgevolve-core::ir::collation:

Catalog::collations: Vec<Collation> — flat collection, sorted by qname after canonicalize(). The canon pass also rejects the invalid libc + nondeterministic combination (Postgres would reject at runtime; pgevolve surfaces it at canon time with a clearer error).

BUILTIN_COLLATIONS: &[&str] — default, C, POSIX, und-x-icu, unicode, ucs_basic. These shortnames bypass the column-references-unmanaged-collation lint even when they have no schema qualifier, because Postgres seeds them at initdb and they are always available.

Semantics — lenient at the collation grain

pgevolve applies leniency at the whole-collation level (consistent with publications / subscriptions / statistics):

Postgres has no in-place ALTER COLLATION for provider / locale / deterministic, so any structural change emits ReplaceCollation, which is destructive and requires intent approval.

5 StepKind variants

ALTER COLLATION … OWNER TO is emitted via the standard Change::AlterObjectOwner path with OwnedObjectId::Qualified, shared with every other ownable IR kind.

5 lint rules

Tests: tier-1: crates/pgevolve-core/src/lint/rules/unmanaged_collation.rs::tests, column_references_unmanaged_collation.rs::tests, range_type_references_unmanaged_subtype.rs::tests, nondeterministic_collation_requires_pg_12.rs::tests, builtin_provider_requires_pg_17.rs::tests; tier-C: objects/collations/.

Conformance fixture pointers

7 fixtures cover the collation surface (+1 cross-cutting scenario):

Dependency edges

The edge is DepSource::Structural. Range → Collation is also added when a UserTypeKind::Range carries a collation: Some(qname).

Property tests (v0.3.8)

crates/pgevolve-testkit/src/ir_generator/collation.rs generates 0–2 libc collations per managed schema with a deterministic-only, safe-locale pool. ICU + nondeterministic + builtin variants are deliberately excluded from the proptest soak to avoid PG-version gating; the conformance fixtures cover those paths instead.

Out of scope (deferred to v0.3.9+)

• CREATE COLLATION FROM existing_collation — clone syntax. Round-trip identity is ambiguous; the source declares a clone but the catalog shows the resolved provider / locale. Deferred. See the design doc.
• ALTER COLLATION … REFRESH VERSION — read-only version field is modeled but the differ ignores it. A collation-version-drift lint and explicit REFRESH step are planned for v0.3.9. See the design doc linked above.
• GRANT on collations — Postgres does not support object-level grants on pg_collation objects. Out of scope by PG design.

Cluster-level surface

pgevolve manages cluster-level state — roles (v0.3.0), with tablespaces, cluster settings, foreign servers, and user mappings planned — through a parallel project type and command family separate from per-database projects.

Project shape

my-cluster/
  pgevolve-cluster.toml
  roles/
    app.sql
    ops.sql

Commands

• pgevolve cluster init [path] — scaffold a new cluster project
• pgevolve cluster diff — show diff between source and live cluster
• pgevolve cluster plan — write a cluster plan directory
• pgevolve cluster apply [<plan_id>] — apply a cluster plan
• pgevolve cluster status — list applied/pending plans

Tests: tier-4: crates/pgevolve/tests/cluster_api.rs::build_cluster_plan_empty_roles_dir, build_cluster_plan_surfaces_role_loses_superuser_finding, build_cluster_plan_creates_new_role; tier-1: crates/pgevolve/src/commands/cluster/*::tests, crates/pgevolve/src/cluster_config.rs::tests.

Currently managed

Passwords

Passwords are not stored in source. The catalog reader skips rolpassword; the source parser drops PASSWORD '…' clauses silently. Set passwords out-of-band (psql, secret manager, etc.).

Tests: tier-1: crates/pgevolve-core/src/parse/cluster::tests (PASSWORD-clause silent drop); tier-2: crates/pgevolve-core/tests/cluster_parse.rs.

Bootstrap roles

The [bootstrap].roles list in pgevolve-cluster.toml names roles that pgevolve treats as PG-owned and never diffs in or out. Defaults to ["postgres"]. Cloud Postgres (RDS, Cloud SQL, etc.) typically needs additional entries (e.g. ["postgres", "cloudsqlsuperuser"]).

Tests: tier-1: crates/pgevolve/src/cluster_config.rs::tests; tier-1: crates/pgevolve-core/src/diff/cluster.rs::tests (bootstrap-role filter behavior).

Linking from per-DB projects

Per-DB projects can lint-check grantee role names against the cluster project by setting [cluster].project = "../my-cluster" in pgevolve.toml. See docs/spec/grants.md for details.

Known limitations (v0.3.0)

• Cluster apply does not yet write to a per-DB-style pgevolve.apply_log. The pgevolve cluster status command lists plan directories rather than reading applied state from the DB. Will be addressed when the cluster executor reaches feature-parity with the per-DB one.
• DROP ROLE steps are marked destructive: true in the emit pipeline, but cluster apply does not yet read intent.toml to gate them — it executes whatever is in plan.sql. Operators should review cluster-plans/<id>/plan.sql before running pgevolve cluster apply.
• No advisory lock is taken during cluster apply; concurrent applies against the same cluster are not protected.
• Object-level GRANT/REVOKE (e.g. GRANT SELECT ON TABLE) is per-DB, not cluster. It ships in v0.3.1.
• Row-level security policies ship in v0.3.2.

CLI

The pgevolve binary's command surface, flags, output formats, exit codes, and configuration schema.

See ../README.md for the status legend.

Commands

Tests (whole section): tier-1: crates/pgevolve/src/cli.rs::tests; tier-4: crates/pgevolve/tests/cli_e2e.rs::help_lists_all_nine_commands.

Global flags

Shadow-validation flags (plan, diff, validate)

Output formats

Exit codes

Spec §13. Implemented in commands::apply::run; other commands follow the same convention.

Connection precedence

Mirrors psql. First non-empty source wins.

Tests (whole section): tier-1: crates/pgevolve/src/connection.rs::tests.

pgevolve.toml schema

Tests (whole section): tier-1: crates/pgevolve/src/config.rs::tests.

[planner.online_rewrites] — v0.2 view / MV keys

[shadow] block (full example)

[shadow]
backend = "auto"
url = "..."
url_env = "..."
reset = "drop_schema_cascade"
extensions = ["pgcrypto"]
postgres_version = "17"

intent.toml schema

Beyond the [[intent]] rows written by the planner, intent.toml supports two user-authored table kinds: [[lint_waiver]] and [[step_override]].

Tests (whole section): tier-4: crates/pgevolve/tests/lint_waiver_e2e.rs::lint_waiver_survives_intent_toml_round_trip, plan_proceeds_with_matching_lint_waiver; tier-1: crates/pgevolve-core/src/plan/serialize.rs::tests, deserialize.rs::tests.

[[lint_waiver]]

[[lint_waiver]]
rule = "column-position-drift"
target = "app.users"
reason = "..."

[[step_override]]

Step overrides allow suppressing or modifying individual planner steps. Useful when you want to skip, for example, a refresh_materialized_view step during a maintenance window.

[[step_override]]
kind = "refresh_materialized_view"
target = "app.daily_summary"
suppress = true

Logging

Lint and layout

Universal lint rules (always applied), built-in layout profiles (one of four selected per project), and the custom-profile mechanism.

See ../README.md for the status legend.

Universal rules

These apply regardless of layout profile and most are enforced at parse time. The lint engine runs them defensively over a built SourceTree.

Severity tiers

Tests (whole section): tier-1: crates/pgevolve-core/src/lint/mod.rs (Severity enum), crates/pgevolve-core/src/lint/rules/mod.rs::tests; tier-4: crates/pgevolve/tests/lint_waiver_e2e.rs::plan_refuses_unwaived_column_position_drift, plan_proceeds_with_matching_lint_waiver.

Layout profiles

A profile expresses where an object should live on disk. Selected by [project].layout_profile. All built-ins ship in pgevolve_core::lint::profile.

schema-mirror (strictest)

Tests: tier-1: crates/pgevolve-core/src/lint/profile/schema_mirror.rs::tests.

kind-grouped

Tests: tier-1: crates/pgevolve-core/src/lint/profile/kind_grouped.rs::tests.