Regenerating db/structure.sql on a PostgreSQL 18 workstation
This repo uses schema_format = :sql, so db/structure.sql is the canonical schema
and bin/rails db:migrate rewrites it from pg_dump. CI runs PostgreSQL 15
(.github/workflows/backend-ci.yml). If your workstation runs 18, its pg_dump
rewrites unrelated existing DDL, and committing the raw output buries a two-line
migration under a couple of hundred lines of churn.
This is not cosmetic: it makes the diff unreviewable, and it is how a defect gets through on a schema change nobody could read.
What PG18 rewrites
| Artifact | PG15 emits | PG18 emits |
|---|---|---|
| NOT NULL columns | foo_id uuid NOT NULL | foo_id uuid CONSTRAINT some_generated_name_not_null NOT NULL |
IN (...) CHECKs | ANY ((ARRAY[a, b])::text[]) | ANY (ARRAY[(a)::text, (b)::text]) |
| Partition bounds | +05:30 | rendered in local time (-07/-08) |
PG15 parses the named-constraint syntax fine but discards the names, so a dump regenerated on CI will never match one regenerated on an 18 workstation. The base file carries zero named NOT NULL constraints — keep it that way.
The procedure
- Snapshot the base before migrating:
git show <base-sha>:backend/db/structure.sql > /tmp/base.sql - Run
bin/rails db:migrate, then save the dump:cp db/structure.sql /tmp/dump.sql git checkout db/structure.sql- Diff base against dump, keeping only hunks that belong to your new objects, and normalize any PG18 constraint naming that lands inside them.
- Verify — this is the step that catches the mistakes:
bin/check-structure-sql-currentandbin/check-structure-sql-migration-paritybin/rails db:test:preparemust load without error (a dropped column line whose CHECK survived will fail loudly here)- query
pg_class,pg_policy,pg_indexes,pg_constrainton the loaded DB and compare against what the migration says it creates git diff db/structure.sqlmust show nothing outside your own objects
The strongest check, worth it for anything non-trivial: build two scratch databases —
one loaded from the committed structure.sql, one from base plus your migration — dump
both and diff. They should be identical.
The trap that has caught us twice
Filter on the schema-qualified name, not on a substring.
A filter of "keep hunks mentioning tiered" leaked a PG18 constraint name onto
public.tiered_calculation_services — a legacy table the migration never touched —
because its name contains the word. Match on billing.tiered_ and explicitly exclude
public.tiered_.
The related failure: filtering by name alone can keep a CHECK constraint while dropping
the column it references, because the bare column line inside CREATE TABLE does not
contain the table name. db:test:prepare catches this — run it.
Why not just commit the PG18 output
You can, and the schema will be correct. But the diff will contain ~20 unrelated hunks, every subsequent PG15-generated dump will churn them back, and reviewers lose the ability to see what actually changed. The parity guard passes either way, so nothing mechanical will stop you.
Related: backend-ci-local.md, rls-dev-setup.md.