PostgreSQL 18 upgrade

Source of truth: docs/runbooks/postgres-18-upgrade.md (synced at build time — edit the original, not this copy). Suite releases after 0.26.x run suite-postgres on PostgreSQL 18 (timescale/timescaledb:2.29.2-pg18). Earlier ones ran PostgreSQL 16. A PostgreSQL server won’t start on another major’s data directory, so an existing install’s data has to move. veltro upgrade does it.

How the move works

It’s a dump and restore through the same code as veltro backup and veltro restore. We don’t use pg_upgrade: it needs both majors’ binaries in one image, plus TimescaleDB built for both, and no image ships that. TimescaleDB stays at 2.29.2 on both sides, which a TimescaleDB dump and restore needs.

When the installed data was written by another major than the release’s suite-postgres, veltro upgrade --to <release>:

1. Stops every service except suite-postgres, suite-redis and OpenSearch. Nothing writes from here on, so the backup in the next step is final. Anything else in the project still running a minute later, an orphan from an earlier release included, gets docker stop; if it survives that, the upgrade refuses before the backup and veltro up starts the stopped services again.
2. Runs ALTER EXTENSION timescaledb UPDATE in the vectorflow database, so an install created on an older image dumps the same TimescaleDB version the new image restores into. That catalog update is the only change the old volume gets. Then it takes the usual automatic backup under backups/.
3. Counts the rows in every table of every suite database and reads each migration ledger (identity’s checksums, VectorFlow’s Prisma table, Sigmatic’s and Warden’s Alembic head), along with identity’s install id and its own account of its schema (veltro.persistence_evidence(): the migration head, its checksum and the schema fingerprint). That goes in backups/upgrade-<time>/postgres-inventory-before.json.
4. Copies the installed release’s files to .veltro-upgrade-rollback/, writes .veltro-datastore-upgrade.json and stops the stack.
5. Puts the new release’s files in place and starts PostgreSQL 18 on a new volume, veltro-suite-postgres-data, mounted at /var/lib/postgresql, where PG18 images keep their data. It then restores the roles and databases from the backup (veltro_backup.py restore --postgres-only) and gathers planner statistics with vacuumdb --analyze-only, since a dump carries none.
6. Takes the same inventory again and refuses unless every row count, every ledger, the install id and identity’s schema evidence match. An identity older than migration 035 (0.25.x and earlier) hashes PostgreSQL 18’s extra NOT NULL constraint rows into its schema fingerprint, so for those the fingerprint is left out and the migration head and checksum still have to match.
7. Removes the marker, starts the suite, checks the image digests and checks that identity’s install id is the one from step 3.

Apart from that extension update, the PG16 volume, veltro-suite-pgdata, is never written to. It stays until you remove it.

The data move costs downtime roughly in proportion to the database size. A suite with about 2.5 GB in PostgreSQL takes minutes.

If it stops part way

While .veltro-datastore-upgrade.json exists, veltro up refuses to start the suite, so no server starts on a half-moved datastore. You can:

• run veltro upgrade --to <release> again. It finishes the move from the recorded backup and inventory and doesn’t take a new backup;
• run veltro upgrade --rollback. It puts the previous release’s files back and starts it on its untouched PG16 volume.

veltro upgrade --rollback keeps working after a successful upgrade too, until you delete .veltro-upgrade-rollback/. Anything written after the upgrade exists only in veltro-suite-postgres-data and doesn’t come back with a rollback.

After a rollback, veltro upgrade refuses to start a new move while veltro-suite-postgres-data holds data, because the restore would replace it. Copy out anything you need, run docker volume rm veltro-suite-postgres-data and upgrade again.

veltro up also refuses a release whose new volume doesn’t exist yet while veltro-suite-pgdata still holds data. That’s what you’d get from putting a new release’s files in place by hand: its server would initialise an empty database and every product would start on nothing. If you really don’t want the old data, remove the volume first.

Once you’re satisfied

docker volume rm veltro-suite-pgdata
rm -rf .veltro-upgrade-rollback

The install id

Identity writes a random install id once, into veltro.installation, on the first install. A backup and restore carries it, while a freshly initialised datastore gets a new one. veltro upgrade records it in .veltro-install-id and refuses an upgrade that comes up with a different one. An identity from before the install id (0.25.x and earlier) passes, and the upgrade prints why the id wasn’t compared. Homelab promotion makes the same check against /api/suite/peer-reads.

Helm

The product charts’ bundled Bitnami postgresql subchart (chart 18.12.4) already runs PostgreSQL 18.6, so VectorFlow, Sigmatic and Warden installs using it have nothing to move. The identity chart’s bundled PostgreSQL moves from 16 to 18 with a scripted dump and restore; see “Bundled Postgres 18” in docs/kubernetes.md. If you run your own database (postgresql.enabled=false), upgrading it is up to you. Identity runs on PostgreSQL 16 and 18. Compare installId in /api/suite/peer-reads before and after your upgrade.

Homelab

The homelab moved to PostgreSQL 18 on 2026-10-03 (run 37122935443, #1512) with a one-off workflow that has since been deleted; it’s in git history if you need it. The PG16 data is still in the veltro-suite-pgdata volume and in /data/backups/veltro-pg18-20261003T123917Z/ on the homelab. Remove both once you’re satisfied.

Doing it by hand

If you can’t use veltro upgrade, the same steps work by hand on the release you’re leaving:

# Stop everything but the datastores, so nothing writes after the backup.
dc() { docker compose --env-file versions.env --env-file .env --profile '*' "$@"; }
dc stop $(dc config --services | grep -v -x -e suite-postgres -e suite-redis -e opensearch)
dc exec -T suite-postgres psql -U suite_admin -d vectorflow -c 'ALTER EXTENSION timescaledb UPDATE'
veltro backup --to ./before-pg18.tar
python3 scripts/veltro_backup.py inventory --dir . --to ./before.json
veltro down
# put the new release's files in place, then:
python3 scripts/veltro_backup.py restore ./before-pg18.tar --dir . --yes --skip-start --postgres-only
python3 scripts/veltro_backup.py inventory --dir . --to ./after.json --compare ./before.json
veltro up

Run the inventory and restore commands with the new release’s scripts/veltro_backup.py; the old one doesn’t have them.