PostgreSQL Cluster Migrator (any → PG17)¶

A single-purpose Docker image that does nothing but migrate an existing PostgreSQL data cluster to a newer major (default 17) using pg_upgrade. The image bundles the PG13–17 server binaries and auto-detects the source cluster's version from its PG_VERSION file, so one image migrates any bundled source major → a newer target with no per-version rebuild. The target is selectable at run time with --target-major; pg_upgrade is forward-only (no downgrades). The source/target may be Docker volumes or host bind mounts, and the upgrade can be done in place (--inplace). The image is built on an FWD base so it carries the custom FWD locales the clusters use (see Building the image).

What it does¶

This performs a whole-cluster binary upgrade: every database, role (fwd_admin / fwd_user), sequence, grant and user-defined function (the FWD UDFs and word-table functions) is carried across in a single operation. The two things pg_upgrade does not transfer are handled automatically by the entrypoint: optimizer statistics are re-gathered, and the pg_hba.conf / pg_ident.conf auth files are copied from the old cluster.

The image carries the target major's binaries plus the older majors'; the entrypoint reads PG_VERSION to pick the matching old bin/ directory and uses the --target-major binaries for the new cluster. It then probes the old cluster offline to learn the bootstrap superuser, encoding and locale, so the only things you must supply are the mounts and where the data directory lives within them.

Files¶

File	Purpose
`pg_migrate_Dockerfile`	Builds the image: the `postgres:<target>` base plus the bundled older majors' server binaries.
`pg_migrate.sh`	Dual-mode script — the host launcher (builds `docker run` / generates an env file) and, inside the image, the migration engine (probe, initdb, pg_upgrade, ANALYZE, config copy, completion marker, optional in-place swap).
`docker-compose.yml`	Compose service for the migration, driven entirely by an env file (the auto-loaded `.env` or a `--env-file`).
`.env.sample`	Template for the Compose env file; copy to `.env` and edit, or generate one with `pg_migrate.sh --gen-env`.

Prerequisites¶

The source cluster (any bundled major, by default PG13–17 are available), as a Docker volume or host bind mount, shut down cleanly — a lingering postmaster.pid means it is still running or crashed.
Space for the new cluster: a separate target (default mode) or, with --inplace, room for a second copy on the source filesystem.
The target major must be newer than the source. pg_upgrade cannot downgrade.

Building the image¶

The migrator is built on the FWD base image goldencode/base_ubuntu_24.04:latest (produced by docker_build.sh), which carries the custom FWD locales (e.g. en_US@iso88591_fwd_basic), the postgres user and gosu. This base is required: clusters reference those locales in their postgresql.conf, so the migrator must have byte-identical locale definitions to start them (and to avoid index corruption). The base has no PostgreSQL server, so the build adds the PGDG apt repo and installs the target plus the bundled source majors itself.

cd tools/docker/pg-migrate

# Default: base goldencode/base_ubuntu_24.04:latest, bundles PG13-17, target 17
docker build -f pg_migrate_Dockerfile -t goldencode/fwd_pg_migrate:any-to-17 .

# Override the base and/or the version set:
docker build -f pg_migrate_Dockerfile \
   --build-arg BASE_IMAGE=goldencode/base_ubuntu_24.04:latest \
   --build-arg OLD_PG_MAJORS="13 14 15 16" --build-arg NEW_PG_MAJOR=17 \
   -t goldencode/fwd_pg_migrate:any-to-17 .

Note: the base MUST include the custom FWD locales. A stock postgres:NN image will build, but the migration then fails at the probe with invalid value for parameter "lc_messages" when a source cluster uses an FWD locale.

Command-line wrapper (host launcher)¶

pg_migrate.sh is dual-mode: inside the image it performs the migration; run from the host it is a launcher that builds the docker run invocation for you (or generates a Compose env file). The role is chosen automatically from /.dockerenv (override with PG_MIGRATE_MODE=host|container), so it is a single script — the same file shipped in the image and runnable from the repo on the host.

# Volume -> volume (any -> 17); forwards any migration flag unchanged:
./pg_migrate.sh --old-volume=hotel_db_postgres --new-volume=hotel_db_postgres17 \
   --target-major=17 --jobs=8

# In-place upgrade of a host bind-mounted cluster:
./pg_migrate.sh --old-bind=/srv/pg/data --inplace

# Show the docker command without executing it:
./pg_migrate.sh --old-volume=a --new-volume=b --print

Wrapper-specific options: --old-volume= / --old-bind=, --new-volume= / --new-bind=, --old-subdir= (default fwdcluster), --new-subdir=, --image=, --print and --gen-env. Every other flag is forwarded to the migrator verbatim, so the full option set is available. Run ./pg_migrate.sh with no arguments for the wrapper help.

Generating an env file for many deployments¶

--gen-env[=<file>] writes a Compose env file from the same inputs instead of running, so a fleet of deployments can each be driven by the YAML method:

./pg_migrate.sh --old-volume=hotel_db_postgres --new-volume=hotel_db_postgres17 \
   --jobs=8 --target-major=17 --gen-env=hotel.env

docker compose --env-file hotel.env run --rm pg-migrate

Bind mounts are not expressible in the named-volume Compose file, so --gen-env requires --old-volume / --new-volume; for binds use the wrapper's docker run mode.

Running with `docker run`¶

Basic migration¶

Mount each volume at the parent of its data directory, and tell the tool where the actual PGDATA lives via OLD_DATA / NEW_DATA. The FWD cluster usually sits in a fwdcluster/ subdirectory of the volume:

docker run --rm \
   -v hotel_db_postgres:/opt/db \
   -v hotel_db_postgres17:/opt/db17 \
   -e OLD_DATA=/opt/db/fwdcluster \
   -e NEW_DATA=/opt/db17/fwdcluster \
   goldencode/fwd_pg_migrate:any-to-17

If your cluster files sit at the root of the volume instead of in a subdirectory, mount that volume directly at OLD_DATA. When the path you point at has no PG_VERSION, the tool prints where the cluster actually is — copy that path into OLD_DATA.

When it finishes, point a PG17 server at the hotel_db_postgres17 volume and start it. In the default copy mode the old volume is left untouched, so you can roll back by continuing to run the old server against it.

On the directory name: the migrated cluster keeps the original's basename (fwdcluster). pg_upgrade needs both clusters present at once, so they cannot share the exact same path during migration — the new one is built under a different parent. When you run PG17 in production, mount its volume so PGDATA is /opt/db/fwdcluster again and the data directory name is identical.

Choosing the target version (forward only)¶

The target defaults to 17 but is selectable; it must be a bundled major and newer than the source:

# Upgrade a PG13 cluster to PG16 instead of 17
docker run --rm \
   -v hotel_db_postgres:/opt/db -v target_vol:/opt/db16 \
   -e OLD_DATA=/opt/db/fwdcluster -e NEW_DATA=/opt/db16/fwdcluster \
   goldencode/fwd_pg_migrate:any-to-17 --target-major=16

pg_upgrade is forward-only: it cannot downgrade or migrate within the same major. A downgrade or cross-version move needs a logical dump/restore, which is a separate tool (not this one).

In-place migration (reuse the original path)¶

--inplace leaves the upgraded cluster at the original path. Because pg_upgrade needs both clusters at once, the tool builds the new cluster in a temporary sibling and then atomically swaps it in — so the directories must share a filesystem. Mount the volume/host path at the parent, not at the data directory itself. The original is preserved as a sibling backup unless --no-backup:

/opt/db/
  fwdcluster        <- now the new cluster (with completion marker)
  fwdcluster.ori    <- BACKUP_DATA: the original, until you remove it

docker run --rm \
   -v /srv/pg/data:/opt/db \
   -e OLD_DATA=/opt/db/fwdcluster \
   goldencode/fwd_pg_migrate:any-to-17 --inplace

--backup-data=<dir> overrides where the original is moved (default <OLD_DATA>.ori); --no-backup discards the original after the swap (no rollback). --mode=link cannot keep a real backup (the old and new clusters share storage), so --inplace --mode=link is refused unless you also pass --no-backup.

Docker volume or host bind mount¶

The tool operates on paths, so the source/target can be Docker volumes (-v name:/opt/db) or host bind mounts (-v /srv/pg/data:/opt/db) — identical behavior either way. It runs every PostgreSQL binary as the uid:gid that owns the data directory, so host-owned files are handled correctly.

Pre-flight check and dry run¶

# Print every command without executing anything:
docker run --rm \
   -v hotel_db_postgres:/opt/db -v hotel_db_postgres17:/opt/db17 \
   -e OLD_DATA=/opt/db/fwdcluster -e NEW_DATA=/opt/db17/fwdcluster \
   goldencode/fwd_pg_migrate:any-to-17 --dry

# Run only pg_upgrade --check (verifies compatibility, then removes the
# throwaway cluster, leaving the target empty):
docker run --rm \
   -v hotel_db_postgres:/opt/db -v hotel_db_postgres17:/opt/db17 \
   -e OLD_DATA=/opt/db/fwdcluster -e NEW_DATA=/opt/db17/fwdcluster \
   goldencode/fwd_pg_migrate:any-to-17 --check

Tuning the run¶

Settings can be passed as environment variables (-e) and/or flags. Flags win over environment.

docker run --rm \
   -e MODE=clone -e JOBS=8 \
   -v hotel_db_postgres:/opt/db -v hotel_db_postgres17:/opt/db17 \
   -e OLD_DATA=/opt/db/fwdcluster -e NEW_DATA=/opt/db17/fwdcluster \
   goldencode/fwd_pg_migrate:any-to-17 --copy-main-conf

Running with Docker Compose¶

The Compose setup is driven entirely by an env file. The service builds the container's environment by ${...} substitution, so a single env file fully parameterizes a deployment: the auto-loaded .env (default), or any file passed with docker compose --env-file <file> run ... — convenient when you have many deployments to migrate. (For --inplace, add it to MIGRATE_FLAGS; the new_cluster mount is then simply unused. For a different target, set NEW_PG_MAJOR.) The wrapper's --gen-env (below) writes these files for you.

Step 1 — create the `.env`¶

Copy the sample and adjust it. The full sample is .env.sample in this directory:

# .env

# --- Image ---
IMAGE=goldencode/fwd_pg_migrate:any-to-17

# --- Volumes ---
# OLD_VOLUME must already exist; NEW_VOLUME is created by Compose.
OLD_VOLUME=hotel_db_postgres
NEW_VOLUME=hotel_db_postgres17

# --- Mount points: where each VOLUME is mounted in the container ---
OLD_MOUNT=/opt/db
NEW_MOUNT=/opt/db17

# --- Data directories: the actual PGDATA (must live under the mount above) ---
OLD_DATA=/opt/db/fwdcluster
NEW_DATA=/opt/db17/fwdcluster

# --- Migration behavior ---
MODE=copy
JOBS=4
# Target major (default 17); must be bundled and newer than the source.
#NEW_PG_MAJOR=17

# Extra entrypoint flags (boolean options with no env form), e.g. "--check" 
# or "--inplace" or "--inplace --no-backup".
MIGRATE_FLAGS=

# --- Optional overrides (leave unset to auto-probe the old cluster) ---
#OLD_SUPERUSER=fwd_admin
#OLD_ENCODING=UTF8
#OLD_COLLATE=en_US.utf8
#OLD_CTYPE=en_US.utf8

Step 2 — the `docker-compose.yml`¶

The full file is docker-compose.yml in this directory:

services:
  pg-migrate:
    image: ${IMAGE:-goldencode/fwd_pg_migrate:any-to-17}
    command: ${MIGRATE_FLAGS:-}
    restart: "no" 
    environment:
      OLD_DATA: ${OLD_DATA:?set OLD_DATA in your env file (.env or --env-file)}
      NEW_DATA: ${NEW_DATA:-}
      MODE: ${MODE:-copy}
      JOBS: ${JOBS:-}
      NEW_PG_MAJOR: ${NEW_PG_MAJOR:-17}
      BACKUP_DATA: ${BACKUP_DATA:-}
      OLD_SUPERUSER: ${OLD_SUPERUSER:-}
      OLD_ENCODING: ${OLD_ENCODING:-}
      OLD_COLLATE: ${OLD_COLLATE:-}
      OLD_CTYPE: ${OLD_CTYPE:-}
      OLD_PG_MAJOR: ${OLD_PG_MAJOR:-}
      # Boolean options (true/false); empty falls back to the entrypoint default.
      INPLACE: ${INPLACE:-}
      NO_BACKUP: ${NO_BACKUP:-}
      CHECK: ${CHECK:-}
      FORCE: ${FORCE:-}
      ANALYZE: ${ANALYZE:-}
      COPY_CONFIG: ${COPY_CONFIG:-}
      COPY_AUTO_CONF: ${COPY_AUTO_CONF:-}
      COPY_MAIN_CONF: ${COPY_MAIN_CONF:-}
      DRY: ${DRY:-}
    volumes:
      - old_cluster:${OLD_MOUNT:-/opt/db}
      - new_cluster:${NEW_MOUNT:-/opt/db17}

volumes:
  old_cluster:
    external: true
    name: ${OLD_VOLUME:-hotel_db_postgres}
  new_cluster:
    name: ${NEW_VOLUME:-hotel_db_postgres17}

Step 3 — run it¶

This is a one-shot job, so run --rm is the natural invocation — it returns the migrator's exit code and removes the container afterward:

cp .env.sample .env      # first time only
docker compose run --rm pg-migrate

To do a pre-flight check or dry run instead, set MIGRATE_FLAGS in .env (no compose edits needed):

# in .env
MIGRATE_FLAGS=--check

Note: old_cluster is declared external: true, so Compose will refuse to run unless the OLD_VOLUME named in .env already exists. This is deliberate — it prevents Compose from silently creating an empty source volume.

Options and environment variables¶

Every option has both a command-line flag and an environment variable, so a .env (hand-written or produced by --gen-env) can fully drive a deployment. Flags win over the environment. MIGRATE_FLAGS remains only as an escape hatch for anything not listed here.

Flag	Environment	Default	Meaning
`--old-data=`	`OLD_DATA`	`/opt/db/fwdcluster`	Old (source) data directory (the dir holding PG_VERSION).
`--new-data=`	`NEW_DATA`	`/opt/db17/fwdcluster`	New (target) data directory; ignored with `--inplace`.
`--target-major=`	`NEW_PG_MAJOR`	`17`	Major to upgrade to. Must be bundled and newer than the source.
`--old-major=`	`OLD_PG_MAJOR`	(detected)	Source major version; auto-detected from `PG_VERSION`.
`--old-bin=`	`OLD_BIN`	(detected)	Old server bindir; auto-selected for the detected major.
`--new-bin=`	`NEW_BIN`	(derived)	New server bindir; derived from `--target-major`.
`--inplace`	`INPLACE`	`false`	Reuse the original path: build in a temp sibling, then swap into `OLD_DATA`.
`--backup-data=`	`BACKUP_DATA`	`<OLD_DATA>.ori`	Where `--inplace` moves the original cluster.
`--no-backup`	`NO_BACKUP`	`false`	With `--inplace`, do not keep the original (no rollback).
`--mode=`	`MODE`	`copy`	Transfer mode: `copy` (safe), `link` (fastest; old cluster unusable after), `clone` (CoW reflink).
`--jobs=`	`JOBS`	`nproc`	Parallel jobs for `pg_upgrade`.
`--superuser=`	`OLD_SUPERUSER`	(probed)	Bootstrap superuser of the old cluster.
`--encoding=`	`OLD_ENCODING`	(probed)	Server encoding for the new cluster.
`--lc-collate=`	`OLD_COLLATE`	(probed)	`LC_COLLATE` for the new cluster.
`--lc-ctype=`	`OLD_CTYPE`	(probed)	`LC_CTYPE` for the new cluster.
`--locale=`	—	(probed)	Shortcut that sets both `--lc-collate` and `--lc-ctype`.
`--check`	`CHECK`	`false`	Run `pg_upgrade --check` only; migrate nothing (target left empty).
`--force`	`FORCE`	`false`	Overwrite a non-empty target directory.
`--no-analyze`	`ANALYZE`	`true`	`ANALYZE=false` skips the post-migration `ANALYZE`.
`--copy-main-conf`	`COPY_MAIN_CONF`	`false`	Also copy the whole `postgresql.conf` from the old cluster (see the caveat below).
`--copy-auto-conf`	`COPY_AUTO_CONF`	`false`	Also copy `postgresql.auto.conf` (`ALTER SYSTEM` settings).
`--no-copy-config`	`COPY_CONFIG`	`true`	`COPY_CONFIG=false` skips copying `pg_hba.conf` / `pg_ident.conf`.
`--no-carry-settings`	`CARRY_SETTINGS`	`true`	Carry a curated allowlist of `postgresql.conf` settings from the source; `false` disables it.
`--carry-gucs=`	`CARRY_GUCS`	(list)	Space-separated allowlist of GUCs to carry (listen_addresses, port, max_connections, password_encryption, locale `lc_*`, logging).
`--dry`	`DRY`	`false`	Print the commands without executing them.

How it works¶

Identity — detects the uid:gid that owns the source data directory and runs every PostgreSQL binary as that user (via gosu); PostgreSQL refuses to run as root.
Detect version — reads OLD_DATA/PG_VERSION to learn the source major and selects the matching server binaries bundled in the image (/usr/lib/postgresql/<major>/bin). Override with --old-major if needed.
Probe — reads the old cluster offline with postgres --single (no server start, no auth, no network) to learn the bootstrap superuser, encoding and LC_COLLATE/LC_CTYPE; reads checksum status from pg_controldata. These are the values pg_upgrade rejects on mismatch.
initdb — initializes the new cluster matching all of the above (forcing --locale-provider=libc, since pre-PG15 clusters predate ICU).
pg_upgrade — runs --check first, then the real upgrade. Its transient servers are pointed at a trust pg_hba (via --old-options/--new-options -c hba_file=) so a password-protected source cluster upgrades without credentials; the cluster's real pg_hba.conf is never modified.
ANALYZE — pg_upgrade never carries optimizer statistics, so the new cluster is briefly started and vacuumdb --all --analyze-in-stages is run.
Config — copies pg_hba.conf / pg_ident.conf from the old cluster (always safe), and carries a curated allowlist of postgresql.conf settings (listen_addresses, port, max_connections, password_encryption, the locale GUCs lc_messages/lc_monetary/lc_numeric/lc_time, and logging) by re-applying them to the new cluster. Copying the whole postgresql.conf / postgresql.auto.conf is opt-in (--copy-main-conf / --copy-auto-conf), because a GUC removed in the newer major can stop the cluster from starting.
Marker — writes .fwd_pg_migrate.done into the new cluster as the very last step. Because every earlier step exits non-zero on failure, the marker's presence is a reliable "completed" flag (see Re-running below).
Swap (--inplace only) — moves the original aside to BACKUP_DATA and renames the freshly built cluster onto OLD_DATA (atomic, same filesystem).

Re-running (idempotency)¶

A successful run writes a completion marker (.fwd_pg_migrate.done) into the new cluster as its final step (in --inplace mode the marker rides through the swap into the final path). The run is safe to repeat — before touching the source, the tool inspects the result location:

marker present — the migration already completed; it prints an "Already migrated" summary and exits 0 (a no-op; the source is never started, even when an in-place backup is still present).
a cluster but no marker — a previous run did not finish (or it is an unrelated cluster); it refuses and asks you to re-run with --force.
empty / absent — it proceeds.

--force discards the target and migrates again from scratch. The marker (not merely a PG_VERSION file) is what marks the target "done", so a run that died after initdb is never mistaken for complete.

Notes and caveats¶

The image bundles the PG13–17 binaries and auto-detects the source version; the target (--target-major) defaults to 17. Add other majors via the OLD_PG_MAJORS build arg; pg_upgrade can read clusters back to 9.2 but only ever moves forward.
The old cluster must have been shut down cleanly. Stop the original server normally before migrating.
--mode=link makes the migration nearly instant but destroys the old cluster's usability once the new server starts — only use it when you have a separate backup (and it is incompatible with keeping an --inplace backup).
The new postgresql.conf starts from target-version defaults, then the migrator re-applies a curated allowlist of settings carried from the source (CARRY_SETTINGS, on by default -- listen_addresses, port, max_connections, password_encryption, the locale GUCs lc_messages/lc_monetary/lc_numeric/lc_time, and logging). Settings outside that allowlist (e.g. shared_buffers, work_mem) are not carried; add them to CARRY_GUCS, re-apply them by hand, or use --copy-main-conf to copy the whole file (review it first -- a GUC removed in the new major will stop the cluster from starting). Auth is preserved separately via the copied pg_hba.conf.
In copy and clone modes the source is left intact; with --inplace the original is preserved at BACKUP_DATA unless --no-backup. Reclaim that space only after verifying the application against the new version.

Troubleshooting¶

"... does not look like a PostgreSQL data directory (no PG_VERSION)" — OLD_DATA is not the cluster directory. Usually the cluster is a subdirectory of the mounted volume (the volume root holds fwdcluster/). The error lists where PG_VERSION actually is; mount the volume at the parent and set OLD_DATA to that path. This is not a permissions / sudo issue — the container already runs as root and drops to the data-dir owner via gosu.
"Source major (N) is not older than the target" — pg_upgrade is forward-only; pick a newer --target-major. Downgrades need a logical dump/restore (a separate tool).
"This image has no PostgreSQL <n> server/target binaries" — that major is not bundled. Rebuild adjusting the OLD_PG_MAJORS / NEW_PG_MAJOR build args.
invalid value for parameter "lc_messages" (or lc_monetary / lc_numeric / lc_time) — the image lacks the custom FWD locale that the source cluster's postgresql.conf references. Build on the FWD base (--build-arg BASE_IMAGE=goldencode/base_ubuntu_24.04:latest), not a stock postgres:NN image.
"--inplace requires ... not itself a mount point" — mount the volume/host path at the parent of the cluster directory so --inplace can swap it.
"--inplace --mode=link cannot keep a usable backup" — use --no-backup (the original is discarded), or --mode=copy / --mode=clone to keep a real backup.
pg_upgrade reports a server is running — shut the source PostgreSQL down cleanly (do not just delete postmaster.pid) and retry.
"... already contains a PostgreSQL N cluster but no completion marker" — the target holds a cluster from a run that did not finish (or an unrelated cluster). Re-run with --force to discard and migrate again, or point --new-data at an empty directory. A successfully migrated target instead reports "Already migrated … nothing to do" and exits 0.

Project

General

Profile

FWD

Wiki

PostgreSQL Cluster Migrator (any → PG17)¶

What it does¶

Files¶

Prerequisites¶

Building the image¶

Command-line wrapper (host launcher)¶

Generating an env file for many deployments¶

Running with `docker run`¶

Basic migration¶

Choosing the target version (forward only)¶

In-place migration (reuse the original path)¶

Docker volume or host bind mount¶

Pre-flight check and dry run¶

Tuning the run¶

Running with Docker Compose¶

Step 1 — create the `.env`¶

Step 2 — the `docker-compose.yml`¶

Step 3 — run it¶

Options and environment variables¶

How it works¶

Re-running (idempotency)¶

Notes and caveats¶

Troubleshooting¶

Project

General

Profile

FWD

Wiki

PostgreSQL Cluster Migrator (any → PG17)¶

What it does¶

Files¶

Prerequisites¶

Building the image¶

Command-line wrapper (host launcher)¶

Generating an env file for many deployments¶

Running with docker run¶

Basic migration¶

Choosing the target version (forward only)¶

In-place migration (reuse the original path)¶

Docker volume or host bind mount¶

Pre-flight check and dry run¶

Tuning the run¶

Running with Docker Compose¶

Step 1 — create the .env¶

Step 2 — the docker-compose.yml¶

Step 3 — run it¶

Options and environment variables¶

How it works¶

Re-running (idempotency)¶

Notes and caveats¶

Troubleshooting¶

Running with `docker run`¶

Step 1 — create the `.env`¶

Step 2 — the `docker-compose.yml`¶