Skip to content
Q
QuoteNode

Installation

Migrating data to PostgreSQL 18

One-time, data-safe procedure to move an existing PostgreSQL 16 deployment to PostgreSQL 18 without losing data.

Migrating data to PostgreSQL 18

Who needs this: only deployments that already run on PostgreSQL 16 and want to move to a QuoteNode build that requires PostgreSQL 18. Fresh installs need nothing — they start on PostgreSQL 18 directly.

A PostgreSQL 18 server cannot start on a PostgreSQL 16 data directory. The app refuses to start on an incompatible directory and never rewrites it automatically, so an existing install must move its data forward once. This is a deliberate, one-time operator procedure.

The migration is data-safe by design: your existing data volume (call it A) is never deleted, recreated, or written over. The data is copied into a new PostgreSQL 18 volume (B); A stays intact as an instant rollback until you confirm B works.

There are three paths (Path C is a discouraged last resort). Pick by whether you have a host shell:

PathUse whenMechanism
A — host CLIYou can run docker on the host (Ubuntu VPS, LAN Linux, plain Compose)scripts/migrate-postgres-major.sh
B — UI-only stackYou only have a UI (Coolify, Portainer, QNAP, Synology)infra/docker-compose.upgrade.yml
C — in-place auto-upgrade ⚠️Last resort only; neither A nor B works and you accept in-place riskpgautoupgrade image (not recommended)

Paths A and B are data-safe: they copy into a new volume and keep your original intact. Path C upgrades in place — there is no untouched rollback, only your off-host backup. Prefer A or B.

Before you start (all paths)

  1. Back up .env. It holds secrets that cannot be regenerated, especially DB_ENCRYPTION_KEY.
  2. Take a database backup (in addition to the automatic safety copy the migration makes): 
    docker compose exec postgres pg_dump -U quotenode quotenode > backup-before-pg18.sql
  3. Pause auto-deploy if your platform redeploys on a moving tag (e.g. Coolify auto-deploy, Portainer :latest). See Release order — a PG18 image must not land before the migration runs.
  4. Stop the application stack (without deleting volumes). Never run docker compose down -v.

Path A — host CLI (recommended when you have a shell)

This is the simplest path for Ubuntu VPS, LAN Linux, and any plain Docker Compose host.

  1. Find the data volume name: 
    docker volume ls | grep postgres
  2. Stop the stack (keep volumes): 
    docker compose down
  3. Run the migration. It captures two safety artifacts (a logical dump and a raw tarball of the volume) and verifies both before touching anything, runs a fail-closed preflight on the live cluster, then recreates the volume on PostgreSQL 18 and restores: 
    DB_NAME=quotenode DB_USERNAME=quotenode DB_PASSWORD='...' \
POSTGRES_VOLUME=<your_postgres_volume> \
DB_MAJOR_MIGRATE_CONFIRM=yes \
  ./scripts/migrate-postgres-major.sh
    The script refuses to do anything destructive unless DB_MAJOR_MIGRATE_CONFIRM=yes, and aborts (leaving your data untouched) if it finds unexpected roles/extensions or not enough disk space.
  4. Start the stack normally. It will come up on PostgreSQL 18 and apply any pending migrations: 
    docker compose up -d
  5. Verify, then delete the safety artifacts printed by the script once you are confident.

Non-default locale: if your source database uses a locale other than the image default, the script fail-closes on the encoding/collation check (a silent collation change can corrupt text indexes). Re-run with a matching POSTGRES_INITDB_ARGS (e.g. --locale=...).

Notes for Coolify (managed hosts)

Path A run over SSH on the Coolify server is the simplest route on a Coolify host. A few Coolify specifics, confirmed in practice:

  • A “Failed” deployment before you migrate is expected. When Coolify deploys a build that requires PostgreSQL 18 onto an existing PostgreSQL 16 volume, the postgres container crash-loops (FATAL: database files are incompatible with server) and Coolify marks the deployment Failed. Your data is not touched — PostgreSQL is simply refusing to start on an older data directory.
  • You must remove the postgres container to migrate. Path A recreates the data volume, and Docker refuses to remove a volume while any container — even a stopped one — still references it. So the procedure removes the Coolify postgres container first; stopping it is not enough.
  • After the database is migrated, bring the stack back with “Redeploy” — not “Restart”. Because the postgres container was removed, Coolify has to recreate it. Click Redeploy on the resource; Coolify then starts postgres:18-alpine on the migrated volume and brings the app back up.
  • Coolify reuses the migrated volume. Recreating the volume drops Coolify’s compose labels, but Docker Compose (and therefore Coolify’s redeploy) matches volumes by name and reuses the existing one — your data stays. Keep the safety artifacts until you have verified the redeployed app.
  • Find the real volume name in the resource’s Storages tab; it is UUID-prefixed, e.g. <uuid>_postgres-prod-data (note the hyphens). If you run several stacks (e.g. a dev and a main install) on one host, migrate each by its own volume name and don’t touch the other.

Path B — UI-only migration stack (Coolify, Portainer, NAS)

When you have no host shell, use the dedicated, temporary migration stack infra/docker-compose.upgrade.yml. It is not part of any normal deployment — bring it up only for the migration and remove it afterwards. It uses stock PostgreSQL images, no Docker socket, and is driven entirely by container entrypoints.

It runs these phases automatically and stops at a verified report — it never promotes by itself:

  1. cold-backup — an immutable tar of volume A (rollback image), taken before anything reads A.
  2. dump — a fail-closed preflight of the live cluster + a logical dump.
  3. restore — restore into a fresh PostgreSQL 18 volume B.
  4. verify — checks major version, schema, extensions, and locale parity, and writes REPORT.txt to the artifacts volume.

Steps

  1. Stop the application stack in your platform UI (do not delete volumes).

  2. Find the existing data volume name (this is A, used as SOURCE_VOLUME_NAME):

    • Coolify: the volume is prefixed with the resource UUID, e.g. <uuid>_postgres_prod_data.
    • Portainer / QNAP / Synology: the stack name is the prefix, e.g. <stack>_postgres_data.
    • Compose default: <project>_postgres_prod_data.

  3. Deploy the migration stack with these environment variables:

    VariableValue
    SOURCE_VOLUME_NAMEthe existing volume A from step 2
    DB_NAME DB_USERNAME DB_PASSWORDyour existing database credentials
    TARGET_VOLUME_NAMEoptional; defaults to quotenode_postgres_upgraded (this is B)
    # plain Docker equivalent of what the UI runs:
docker compose -f infra/docker-compose.upgrade.yml up

  4. Read the report. When the verify phase finishes, read REPORT.txt from the artifacts volume (quotenode_pg_upgrade_artifacts). It must say PASS. If any phase fails, the stack stops safely; A is untouched — fix the cause and retry (delete B first, see Recovery).

  5. Remove the migration stack (keep its volumes), then promote B.

Platform volume visibility (verify on your platform): Path B assumes the migration stack can mount the application’s existing volume A as an external volume by name. Most Docker hosts allow this. If your platform isolates volumes per stack/resource and the migration stack cannot see A, use the in-place variant: temporarily replace the application’s compose with docker-compose.upgrade.yml inside the same resource/project so it inherits the same volume namespace. Confirm this on your specific platform before relying on it in production.

Path C — in-place auto-upgrade image (simplest-looking, but risky — not recommended) ⚠️

Last resort only. Use this only when neither Path A nor Path B is workable and you accept the risks below. Unlike A and B, this path upgrades in place on your existing volume A, so there is no untouched rollback volume — your off-host backup is the only safety net.

The idea looks the simplest: let an auto-upgrading image (pgautoupgrade) run pg_upgrade once on your existing volume, then start the normal postgres:18-alpine on it. But it is not a one-line tag change in your normal compose — the postgres service there has a command: override (which suppresses the upgrade entrypoint entirely), restart: unless-stopped (which would loop the finished one-shot), and a healthcheck (which flags the upgrading server as unhealthy). All three break an in-place upgrade.

The repo ships a ready, standalone one-shot file that avoids all of them: infra/docker-compose.pgautoupgrade.unsafe.yml (the .unsafe is deliberate). Key facts:

  • pg_upgrade is destructive in place — on success the old cluster is gone, and a mid-way failure can leave the data directory half-converted. Your off-host backup is the only rollback.
  • The file keeps PGAUTO_ONESHOT=yes, the legacy PGDATA=/var/lib/postgresql/data, no healthcheck, and restart: no, and pins the image by digest.

Steps

  1. Stop the stack and take a raw, off-host copy of volume A — this is your only rollback: 
    docker run --rm -v <your_postgres_volume>:/d:ro -v "$PWD:/out" alpine:3.23 \
  tar czf /out/pg16-volume-backup.tar.gz -C /d .
    Copy pg16-volume-backup.tar.gz off the host.
  2. Run the ready one-shot file against your live volume (SOURCE_VOLUME_NAME = volume A): 
    SOURCE_VOLUME_NAME=<your_postgres_volume> \
DB_NAME=quotenode DB_USERNAME=quotenode DB_PASSWORD='...' \
  docker compose -f infra/docker-compose.pgautoupgrade.unsafe.yml up
    Wait for the container to exit 0.
  3. Verify the layout — the volume must now be PostgreSQL 18 on the legacy path: 
    docker run --rm -v <your_postgres_volume>:/d:ro alpine:3.23 cat /d/PG_VERSION   # -> 18
  4. Start your normal stack (postgres:18-alpine). It uses the same volume, now upgraded — no promotion step is needed (the upgrade was in place).
  5. Verify. If anything is wrong, restore A from pg16-volume-backup.tar.gz.

UI-only platforms (Coolify/Portainer): paste the contents of infra/docker-compose.pgautoupgrade.unsafe.yml into a new temporary stack, set the env vars above, deploy once, confirm it exited, delete that stack, then point your normal stack at the same volume.

Promote B

After verification, point the main stack at the migrated volume B. The base compose files are never edited (so fresh installs are unaffected); promotion is an override.

  • Plain Docker / Compose: add the promotion override and set POSTGRES_VOLUME_NAME to B: 
    POSTGRES_VOLUME_NAME=quotenode_postgres_upgraded \
  docker compose -f infra/docker-compose.coolify.yml \
                 -f infra/docker-compose.promote.yml up -d
    (use docker-compose.prod.yml instead of coolify.yml for the prod file). To roll back, drop the -f docker-compose.promote.yml and start again — you are back on A.
  • Coolify / Portainer UI: there is no -f chaining in the UI. Instead, remap the postgres service storage to volume B in the platform’s storage settings, or add external: true + name: <B> for the postgres volume in the stack’s compose editor.

Verify

After the main stack is running on B:

  1. Open /health — should return UP.
  2. Log in and confirm your existing data (offers, customers, settings) is present.
  3. Create a test offer and generate a PDF.
  4. Open a public link to confirm routing.

Only after this should you delete volume A and the migration artifacts.

Release order (important)

If your platform auto-deploys on a moving tag, a PG18-requiring image could land before you migrate, breaking the stack. Always:

  1. Pause auto-deploy / pin the current image tag.
  2. Run the migration (Path A or B) and promote B.
  3. Then upgrade the application to the PostgreSQL 18 build and re-enable auto-deploy.

Recovery

The source volume A is never modified, so recovery is simple:

  • Migration interrupted before promotion (timeout, redeploy, stop): delete volume B and the artifacts volume, then start over. There is no partial state to resume.
  • Problem after promotion: revert the promotion (drop the override / unset POSTGRES_VOLUME_NAME or re-point storage to A) and start the stack — you are back on PostgreSQL 16 with all data.
  • Last resort: restore from backup-before-pg18.sql (see Upgrading QuoteNode → Rolling back).

Never run docker compose down -v during this procedure — it deletes volumes.

Last reviewed: Recently