Database and migrations
What the engine needs from PostgreSQL, the connection pool settings, how engine migrations and schema apply run as deploy steps, what the engine refuses at boot, and how destructive schema changes are approved.
Meridian keeps everything except media files in one PostgreSQL database: content and its revisions, users, the CRM, analytics, the job queue and the scheduler’s lease. Two kinds of change reach that database, and both are explicit deploy steps:
| Change | Comes from | Command |
|---|---|---|
| Engine migrations | A new engine version: changes to the engine’s own tables | engine-dmo migrate up |
| The schema | Your configuration: types, fields, sites, locales, roles and the rest | engine-dmo schema apply |
engine-dmo migrate up # the engine's tables, for this binary
engine-dmo schema apply # your configuration, into the database
engine-dmo serve PostgreSQL requirements
| Requirement | Detail |
|---|---|
| Version | 14 or later. The engine reads the server version before anything else and refuses an older server, naming the version it found |
| Encoding | UTF8. migrate up refuses any other encoding. Create the database with --encoding=UTF8, for example POSTGRES_INITDB_ARGS="--encoding=UTF8 --locale=C.UTF-8" in the official image |
| ICU collations | Recommended. Sorting text in a locale’s own order uses the server’s ICU collations; without ICU the engine runs, logs a warning and sorts in code point order |
| Privileges | The connecting role owns the database’s objects: migrations create tables, indexes and partitions, and analytics creates a monthly partition ahead of each month |
| One database per project | Two projects must not share a database |
Managed services (Amazon RDS, Cloud SQL, Azure Database for PostgreSQL, Neon, Crunchy Bridge and others) work. Use sslmode=require or stricter in their connection strings.
Every session the engine opens runs in UTC (timezone=UTC) with application_name set to meridian <version>, so pg_stat_activity shows which version each connection belongs to.
Connecting
database:
url: ${DATABASE_URL}
max_connections: 20
min_connections: 2
max_conn_lifetime: 1h
statement_timeout: 30s export DATABASE_URL='postgres://meridian:secret@db.internal:5432/meridian?sslmode=require' | Key | Default | Meaning |
|---|---|---|
database.url | Required | A PostgreSQL URL or keyword/value connection string. A secret: always ${VAR} |
database.max_connections | 20 | The most connections one engine process holds |
database.min_connections | 2 | Connections kept open when idle |
database.max_conn_lifetime | 1h | A connection is closed and replaced after this long |
database.statement_timeout | 30s | Set as the session’s statement_timeout; a query running longer is cancelled. 0s leaves the server’s setting |
analytics.statement_timeout | 10s | A lower limit for report and dashboard queries, so reporting cannot starve the public site. Must be below database.statement_timeout |
Each replica opens its own pool, and every one-off command (migrate up, schema apply, warm and the rest) opens one too. Keep replicas × max_connections, plus a few for one-off commands, below the server’s max_connections. With a connection pooler such as PgBouncer, use session pooling, not transaction pooling: the engine sets session parameters (time zone, statement timeout) and holds session-level advisory locks (for migrations and image derivative generation).
engine-dmo doctor reports the server’s version, encoding, collation, whether ICU is present, the pool and the largest tables.
Engine migrations
A migration is a numbered SQL file compiled into the binary. They come in streams: the core stream, and one per module the bundle compiles (crm, portal, ai and the rest). A migration is named by its stream and number: 00023_mcp_bulk in core, crm/00001_baseline in a module.
engine-dmo migrate status # every migration, applied or pending; exits 2 when any is pending
engine-dmo migrate up # apply every pending migration
engine-dmo migrate to 23 # apply core up to and including 23
engine-dmo migrate to crm/1 # apply a module's stream up to and including its 1 applied 00022_asset_framing in 41ms (metadata)
applied 00023_mcp_bulk in 18ms (metadata) How they behave:
| Property | Detail |
|---|---|
| Forward only | There are no down migrations. The way back is the backup you took before |
| One at a time | Every run takes one PostgreSQL advisory lock first, so two processes starting migrate up together (a rolling deploy) cannot race; the second waits and then finds nothing to do |
| One transaction each | Each file applies in its own transaction, and migrate up prints each one with how long it took and its class |
| Checksummed | The checksum of each applied file is recorded. A binary whose file differs from what was applied reports DRIFT and refuses to start |
| Classified | metadata changes only the catalogue or new tables and holds brief locks whatever the data size; bounded reads or rewrites existing rows under a lock (an index build, a validated constraint); online follows a plan written into the migration. The first migration of each stream is baseline |
migrate status also lists migrations recorded in the database for a module the binary does not compile (not compiled), such as a DMO database read by engine-cms. Their tables stay until you drop them by hand.
migrate status --json prints applied, pending, drift and not_compiled arrays, each row with module, version and name.
What the server does with a pending migration
By default engine-dmo serve refuses to start while a migration is pending, lists them, and exits 2:
2 migrations pending and migrate.on_boot is refuse:
00022_asset_framing
00023_mcp_bulk
run `engine-dmo migrate up` as the deploy step, or `engine-dmo serve --migrate` for a simple deployment | Setting | Effect |
|---|---|
migrate.on_boot: refuse | The default. Pending migrations stop the boot |
migrate.on_boot: apply | Every boot applies pending migrations first |
engine-dmo serve --migrate | Apply pending migrations, then serve, for this run only |
Applying on boot is convenient for a single small install. Avoid it with several replicas or a large database: the migration then runs inside a restart, under the orchestrator’s start timeout, with no backup step before it.
While serving, the readiness check (/health?readiness) fails if any migration the binary knows is missing, for example after a restore from an older backup under a running engine.
The schema
Your configuration describes types, fields, sites, locales, networks, roles and the rest; the database records the version of it that was applied. schema plan shows the difference and schema apply makes it:
engine-dmo schema plan # read only; exits 2 when there are changes
engine-dmo schema apply # apply the plan in one transaction At boot the engine compares the configuration with the database. What it does with a difference is schema.on_drift:
| Value | Effect |
|---|---|
refuse | The default. The engine prints the plan and exits 2: run engine-dmo schema apply as the deploy step |
apply | The engine applies the plan at boot. A destructive plan is applied only when the approval file holds its digest; otherwise the boot fails |
warn | The engine applies a plan that is not destructive at boot; a destructive one is logged as a warning and left unapplied, and the engine starts. Refused in production |
Destructive changes
A change is destructive when it touches stored data: removing a type, site, locale, field, block or select option that documents use; changing a field so stored values no longer fit (a type change that loses values, multiple to single); changing a field’s translate mode on documents that hold values; or any change to a protected field. The same change with no documents affected is not destructive. A removed field’s values are kept under the document’s orphaned values, and removed blocks stay in the revisions, but the plan still needs approval. schema apply refuses a destructive plan until someone approves that exact plan:
| Route | How |
|---|---|
| An approval file | schema apply prints the plan and its digest and refuses. Write the digest to the file schema.destructive_approval names (default config/schema/.destructive-approved), commit it, deploy. The next schema apply applies that plan, and only that plan |
| On a terminal | engine-dmo schema apply --allow-destructive shows the plan and asks for confirmation |
| In a script | engine-dmo schema apply --allow-destructive --yes applies without asking |
The approval file is the route for production: the approval is reviewed and committed with the change that needs it, and a different plan (a second change slipped in) has a different digest and is refused.
Other refusals, each with exit 2 and the plan printed:
| Refusal | Fix |
|---|---|
| The plan would leave nobody able to manage users, or nobody holding the owner role | Change config/access so someone active still does. This one exits 1 |
| An element’s id in the files differs from the stored one under the same handle | Put the stored id back in the file, or name the handle with --replace (such as --replace listing.priceRange) to let the new element replace the stored one |
| The plan holds a change this engine cannot apply yet | Undo that change in the files to apply the rest |
Every schema apply, applied or refused, is written to the audit log.
Databases from before stable ids
engine-dmo schema adopt-ids gives a database made before configuration elements carried ids the ids the files now carry: it writes any missing ids into the files and binds them, and refuses a plan with any other change.
Related
- Upgrades: the whole sequence for a new engine version.
- Backups and restore.
- Schema changes: what each kind of configuration change does to stored content.
Single-node deployment
A complete production deployment on one virtual machine with Docker Compose (Caddy for TLS, the engine, PostgreSQL and Meilisearch), with every file to copy, the first start, backups and the same setup under systemd without containers.
Storage and media
Where uploaded originals and generated image derivatives are stored (a local or shared filesystem, or an S3-compatible bucket), how media is served and cached, putting a CDN in front, upload limits, and keeping derivatives in check.