PostgreSQL versions
This page says which PostgreSQL majors this build supports, what it reads and writes at each one, and what the engine version governs. Only the major is modeled. Each guide states its own verb’s row and links here.
Supported majors
| Major | Tested on a live server | Reads its pg_dump | Writes SQL it runs | Reads its new syntax | Writes its new syntax |
|---|---|---|---|---|---|
| 15 | Yes | Yes | Yes | Yes | Yes |
| 16 | Yes | Yes | Yes | Yes | Yes |
| 17 (default) | Yes | Yes | Yes | Yes | Yes |
| 18 | Yes | Yes | Yes | No | No |
This build reads and writes PostgreSQL 17 syntax at every engine version.
PostgreSQL 18’s own syntax (VIRTUAL generated columns, NOT ENFORCED
constraints, WITHOUT OVERLAPS and PERIOD keys, and ALTER CONSTRAINT … [NO] INHERIT) is not available: op add and op revise refuse it with
syntax-not-available at every engine version, a verb that reads a log
holding it refuses with the same code, and import and drift exit 1 with
parse-error on it.
17 is the value init writes to [project] engine_version, and the one a
schema resolves to when nothing names another. Any other value refuses with
unknown-engine-version. The value is a bare major: 14, 19, 18.1,
018, v18 and 18 all refuse.
The engine version is set by any of:
schema create --engine-version <major>orimport --engine-version <major>, which set a new schema’s pin and emit target together;schema engine <major>, which moves an existing schema’s pin and emit target (--config-onlymoves the target alone);engine_versionunder[project](the value a new schema takes when no flag names one) or under a[[schema]]row (that schema’s emit target);--using=postgres:<major>onsnapshotormigrate, which sets the emit target for that run.
What the engine version governs
Each schema carries two values.
- The pin is the oplog’s
# engine-version: <major>header: the major production runs.op addrefuses an op the pin’s major does not take: below 18, aDropForeignKeywhose reverse re-adds a partitioned table’s keyNOT VALIDrefuses withreverse-policy-partitioned-fk. - The emit target is the major
snapshotandmigratewrite SQL for. It resolves from--using=postgres:<major>, then the schema’s[[schema]] engine_version, then[project] engine_version, then 17. The snapshot banner names it (engine_version=<major>) and everymigratefile opens with-- engine: postgres <major>.
The target may not lag the pin: a schema whose [[schema]] engine_version
is below its oplog’s header fails at config load with
engine-version-target-below-oplog.
No command connects to a database, so neither value is read from a server. The header line states the major the script was written for; PostgreSQL at that major decides whether its DDL is valid.
Per verb
Every verb does the same at each supported major, and snapshot and
migrate emit the same SQL at each; only the banner and the header line
name the major. The exceptions are a NOT VALID foreign key on a
partitioned table, a partition’s columns at ATTACH, and the identity ops
on a partition’s column. The known gaps for 15 and 16 also
apply.
Only 18 accepts a NOT VALID foreign key on a partitioned table: migrate
renders it at 18, and 15 to 17 refuse it with
migrate-blocked-on-partitioned-fk-not-valid.
PostgreSQL’s ATTACH PARTITION takes a different set of partition columns
at each major, and the AttachPartition column check follows the pin:
| Partition column | 15 | 16 | 17 and 18 |
|---|---|---|---|
| Its own identity | Attaches, kept | Attaches, kept | Refused |
STORED generated under a plain parent column | Attaches, kept | Refused | Refused |
| Generated with its own expression, not its parent’s | Refused | Attaches, kept | Attaches, kept |
import and op add refuse a refused shape with
attach-partition-columns-mismatch. snapshot and migrate at an emit
target other than the pin refuse an ATTACH the pin accepts and the target
does not, with unsupported-on-engine-version, and schema engine refuses
to move the pin to such a major. retrofit migrate lists each case.
On a 15 or 16 pin, AddIdentity, DropIdentity, SetIdentityGeneration
and SetIdentityOptions on a partition’s column (<partition>.<column>)
alter that partition’s own identity in place: op add records the op with
partition=, and migrate renders it as ALTER TABLE <partition> ALTER COLUMN …. The identity’s sequence options (START WITH, CACHE, …) stay
with the partition through the ATTACH and the DETACH. drift plans one
for a partition that lost its own identity, holds it under another
generation or with other sequence options, and import writes one for such
a statement after the ATTACH. From 17 on, op add refuses them on a
partition’s column with column-op-on-partition, naming the partitioned
root, and migrate at a 17 or 18 target refuses one in the window it
emits, forward or as a --down inverse, with
unsupported-on-engine-version.
The PostgreSQL 18 syntax this build refuses with syntax-not-available is
the SetConstraintEnforced and SetConstraintInherit operators and these
parameter values: generated_kind=virtual on AddColumn; enforced=false
on AddForeignKey and AddCheck; period=true on AddForeignKey; and
without_overlaps=true on AddPrimaryKey, AddUnique and
ChangePrimaryKey. op info marks each as not available. validate,
op seal, identify, snapshot and migrate refuse a log that holds
one, and drift refuses to append to one; revise each op to its PostgreSQL 17 value
(generated_kind=stored, enforced=true, period=false,
without_overlaps=false), or regenerate the log without it.
Known gaps
15 and 16:
RenameOwnedSequenceon the sequence of a partition’s own identity refuses withcolumn-op-on-partition, though PostgreSQL renames it. The hint names the workaround:DetachPartitionthe partition, runRenameOwnedSequenceon it, thenAttachPartitionit again.