---
title: "PostgreSQL schema migration"
description: "systhema migrate --schema for databases that only used dev push."
requested_language: hu
language: en
translation_notice: "This page isn't translated yet"
url: https://docs.systhema.app/hu/payload/database/schema-migration
version: unreleased (main)
docs_index: https://docs.systhema.app/hu/llms.txt
---
> This page isn't translated yet. Showing English.


`systhema migrate --schema` adds missing PostgreSQL schema objects from the project's resolved Payload config. It works on databases that have only used dev push, including databases whose only `payload_migrations` entry is `dev` with batch `-1`.

```bash
NODE_ENV=production systhema migrate --schema --dry-run </dev/null
NODE_ENV=production systhema migrate --schema </dev/null
```

Both commands print SQL. Dry-run reads the catalog in a read-only transaction and applies nothing. Without `--dry-run`, the command prints the complete DDL before executing it in one transaction. Rerunning it after success prints `Applied 0 schema statement(s)`.

The CLI and the project's installed `@systhemaui/payload` must both contain this feature. The equivalent project-local command is `pnpm exec -- payload systhema-migrate-schema [--dry-run]`. Use the `exec --` separator with npm, pnpm, or Yarn Classic so `--dry-run` reaches Payload. This is an explicit database operation; `systhema upgrade` does not run it automatically. This guide also ships at `node_modules/@systhemaui/payload/docs/schema-migration.md`.

## Worked example: canary upgrade on a push-only database

A consumer upgrading from `1.6.0-canary.4` to `1.7.0-canary.14` reported one missing `general_settings` table and 55 missing columns. Its legacy `seo` table still existed, and the database had no real migration history. Those counts describe that consumer's config, not a universal Systhema delta.

Install CLI and Payload package versions containing this command first. Rehearse against a restored staging copy with the same config and connection options. Run it with the production environment loaded: a module configured from environment variables (AI, analytics, Cloudflare, email, a cloud storage adapter) registers its tables and columns only when its variables are present, so a plan built on a machine without them omits exactly the additions the deployment needs. The same applies to `payload generate:types` and to any schema diff you build by hand. Run the following from the target application's checkout, using the intended environment's database connection. Keep the old application deployment available until the planned transition is ready.

```bash
# Upgrade files and packages without opting into database hooks.
systhema upgrade --yes

# Reconcile known nav names before creating any replacement tables.
NODE_ENV=production systhema migrate migrate-header-nav-dbnames </dev/null

# For projects requiring the per-locale status move, run its dedicated helper
# here, before generic additions. It needs a coordinated code/database deploy.
# NODE_ENV=production systhema migrate migrate-localize-status </dev/null

# Review the actual live-catalog difference. This prints retained legacy tables.
NODE_ENV=production systhema migrate --schema --dry-run </dev/null

# Apply additions, including safe enum-value additions, then prove that a rerun is a no-op.
NODE_ENV=production systhema migrate --schema </dev/null
NODE_ENV=production systhema migrate --schema </dev/null

# Rewrite per-user capability values after new enum labels exist. Run this before
# an operator-owned enum recreate that removes legacy capability labels.
NODE_ENV=production systhema migrate migrate-capability-strings </dev/null

# Destination tables and columns now exist. Copy the legacy data afterwards.
NODE_ENV=production systhema migrate migrate-seo-data </dev/null
NODE_ENV=production systhema migrate migrate-emails-data </dev/null
```

The schema command reads connection settings from the resolved PostgreSQL adapter, including `pool` options and `schemaName`. Existing data hooks use `DATABASE_URI` and have their own schema assumptions. For the worked example those hooks target `public`; a custom-schema project must check each hook's support separately and must not assume it follows the schema command's connection settings.

Verify the copied settings and retained content before switching traffic to the new code. Run any other data hooks selected by the upgrade plan at their documented stage. Do not use the old blanket advice to move all data first: a copy into `general_settings` needs its destination, while the nav rename must precede creation of new nav tables. The current SEO helper can skip when that destination table is missing; rerun it after DDL even if an earlier upgrade reported success.

`seo` and other extra tables remain in place, and the command reports them. It never asks whether `seo` should be renamed to `general_settings`. A legacy nav table with an expected replacement blocks the plan and names the nav helper. If both nav tables already exist, reconcile their data before retrying; the schema command will not guess which contains authoritative content.

## Supported changes and refusal cases

The command adds missing schemas, tables, columns, enum types, indexes, foreign keys, unique constraints, composite primary keys, and check constraints represented by the resolved Drizzle schema. Serial columns create their sequences through Drizzle's generated DDL. `CREATE TABLE` and `ADD COLUMN` use `IF NOT EXISTS`; catalog planning makes the other additions idempotent.

Existing target columns must agree in type, nullability, identity and generated definition. A column that differs only in its default gets `ALTER TABLE ... ALTER COLUMN ... SET DEFAULT` (or `DROP DEFAULT`): it changes no stored row and is metadata-only, so a plugin release that moves a default (`@payloadcms/plugin-import-export` 3.90 did, on `exports` and `imports`) no longer needs a hand-written migration. Defaults PostgreSQL writes back in a different form, such as reordered `jsonb` keys, are compared by value, so they are not re-planned on every run. Table, column, index and constraint names longer than PostgreSQL's identifier limit (63 bytes by default), which Payload generates for deeply nested arrays and groups, are matched by the truncated name PostgreSQL actually stored; two names that collide after truncation block the run. Existing enums must have the same values. A physical-order-only difference is reported as informational because it affects `ORDER BY` but needs no DDL. Missing enum values are added when no generated statement references them in the same transaction. A value added to several enums at once, as a new locale is to `enum__locales` and the per-locale publishing enums, is added to each of them in the same run, also alongside a new localized-drafts collection whose own enum already lists it. Any other generated statement that names the new value, as a plain or array literal, or casts to the enum being extended, still blocks, even when it belongs to an unrelated text column. A removed enum value is counted per dependent column: if any row still holds it, the run is blocked, with the value-level usage counts and the operator-owned recreate SQL, also commented out: its cast fails on exactly the rows that caused the block, so it is never safe to run before those rows are updated. If no row anywhere holds it, nothing is at risk, so the run continues and the same recreate SQL is printed as a warning — commented out, so the output stays safe to pipe into `psql` — for whenever you want the type itself cleaned up. Constraints are matched by definition when their names differ, reported without repair, and never re-added solely because their names differ. Rename a matching constraint manually with `ALTER TABLE ... RENAME CONSTRAINT` when matching names matter. The command refuses incompatible definitions instead of changing them. An unfamiliar but equivalent SQL expression may therefore require a project-owned migration. Extra live objects are retained rather than used as rename candidates. A blocked run still prints the additive statements it would have applied, as SQL comments beneath the blockers, so unrelated older drift cannot hide the one addition a release needs; nothing is applied until the blockers are resolved.

It also refuses populated-table additions that require a value but have no default, custom views, explicit sequences, roles, policies, row-level security, identity/generated columns, and concurrent index creation. Missing single-column primary keys on existing columns require a project-owned migration. Missing extensions must be provisioned separately. `@payloadcms/db-postgres` is the only supported adapter. `@payloadcms/db-vercel-postgres` also identifies itself as PostgreSQL, but uses a different driver and is rejected with a clear error and a nonzero exit code.

There is no generic destructive override. Renames and drops require a dedicated, explicitly invoked data hook or a reviewed Payload migration. The command adds enum values only on PostgreSQL 12 or later and only when no generated statement references the new label in the same transaction. Recreating an enum to remove labels stays operator-owned because it rewrites columns and can lock large tables — an unused label is reported as a warning rather than a blocker, but it is still never dropped for you.

The command uses one dedicated connection, a transaction-scoped advisory lock to reject competing schema runs, a five-second lock timeout, and a sixty-second timeout per SQL statement. A failed statement or failed final catalog verification rolls back all DDL in this run. Once committed, a failure to print the final `Applied N` status does not turn a successful migration into a failed run; rerun to verify the catalog if output was interrupted.

Run in a low-traffic window. `ADD COLUMN` needs an `ACCESS EXCLUSIVE` lock: an open transaction holding a conflicting lock on that table can block it. While the DDL waits, its lock request can also queue application traffic for that table. Each attempt can stall that traffic for up to five seconds per lock acquisition; the timeout is not a five-second limit on the whole run. Acquired locks remain until the transaction ends, so total traffic disruption can last longer. A lock timeout (`55P03`) rolls back the transaction and is safe to retry after the conflicting transaction finishes. The command reports this guidance and does not retry automatically, so it does not repeatedly queue traffic without operator control. See [PostgreSQL's lock timeout documentation](https://www.postgresql.org/docs/17/runtime-config-client.html#GUC-LOCK-TIMEOUT).

The plan warns when missing indexes or foreign keys use existing columns on a populated table. It uses `pg_class.reltuples` estimates, with a one-row probe when statistics do not show rows; estimates may be stale. These warnings do not measure lock duration. Indexes on newly added nullable columns still require work, and index creation or constraint validation can scan or lock large tables. Those changes may need a project-owned deployment migration with concurrent indexes or staged validation.

Session-pooled and transaction-pooled connection strings are expected to work: all database work stays on one client inside one explicit transaction, uses only `SET LOCAL` and a transaction-scoped advisory lock, and sends no named prepared statements. This is a design expectation, not verification against a real pooler. The direct database port is the conservative choice. Statement pooling is unsupported because it disallows multi-statement transactions; check the endpoint's pooling mode and provider restrictions. See [PgBouncer's pooling modes](https://www.pgbouncer.org/features.html).

Dry-run does not freeze an approval artifact: apply computes the current difference again and prints it. Keep schema/config changes serialized during deployment. Application config and schema hooks are executable project code; they still run to resolve the schema. Systhema suppresses Payload's connection, dev push, production migrations, `onInit`, automatic type generation, and scheduled jobs during this command.

## Relationship to Payload migration history

This command neither writes Payload snapshot files nor changes `payload_migrations`. A push-only database stays push-only. A project already maintaining Payload migrations should keep generating, reviewing, committing, and deploying them as usual; applying the same additions separately can make those pending files fail.

After reconciliation, adopting Payload migration history is a separate baseline exercise. Preserve an initial migration for provisioning fresh databases, verify the existing database against it, and record that baseline through the project's chosen migration process. Do not run the initial `CREATE TABLE` body against an existing database. This command does not pretend to solve baselining by recording an unapplied migration.