Upgrading PostgreSQL in Docker with pgautoupgrade

Upgrading a PostgreSQL major version inside Docker has historically meant stopping your stack, dumping data, spinning up a fresh container, and restoring — a multi-step process where a single mistake can leave you with corrupted or missing data. The pgautoupgrade Docker image automates that entire sequence: on startup it detects whether your data directory needs upgrading, runs pg_upgrade --link if it does, then hands off to the normal PostgreSQL server — all without you writing a single migration script.

This guide covers how to configure pgautoupgrade, which image tags to choose, the environment variables that control upgrade behaviour, the Debian-vs-Alpine gotcha that trips up the most users, and what to do when things go wrong.

1. How pgautoupgrade Works Under the Hood

When the container starts, pgautoupgrade reads the PG_VERSION file inside your data directory and compares it to the PostgreSQL version baked into the image. Three paths follow:

• No upgrade needed — the versions match; the container skips the upgrade logic entirely and starts PostgreSQL normally. There is zero overhead for an already-current database.
• Upgrade needed — pg_upgrade is invoked with the --link flag, which hard-links data files between the old and new data clusters instead of copying them. This makes the upgrade as fast as possible regardless of database size, but it also means the original files are modified in place. This is why a backup before upgrading is non-negotiable.
• Unsupported source version — versions older than PostgreSQL 9.5 are not supported and the container will exit with an error.

The upgrade process was originally developed to unblock the Redash project, which was pinned to the end-of-life PostgreSQL 9.5 for years because a standard image swap would have broken existing user installations. pgautoupgrade solved this by making the upgrade transparent at container start-up.

2. Choosing the Right Image Tag

The image is published to Docker Hub at pgautoupgrade/pgautoupgrade and currently supports PostgreSQL 9.5 through 17.x.

⚠️ The Debian-vs-Alpine Incompatibility

This is the most common production gotcha. The official postgres image is Debian-based. If your existing data directory was created by a Debian image and you switch to an Alpine-based pgautoupgrade tag, pg_upgrade may fail or produce subtly broken results due to locale and library differences between the two libc implementations (glibc vs musl).

Rule of thumb: if your current postgres image is Debian-based (the default), use a *-bookworm tag for pgautoupgrade. Only use *-alpine if you have been running Alpine throughout.

3. Getting Started: Docker Compose Configuration

3.1 Always-Latest Setup

services:
  db:
    image: "pgautoupgrade/pgautoupgrade:latest"
    volumes:
      - postgres_data:/var/lib/postgresql/data/
    environment:
      POSTGRES_PASSWORD: mysecretpassword

volumes:
  postgres_data:

Every time this container starts, it will auto-upgrade to the newest PostgreSQL version included in the latest tag. Suitable when you have no hard extension-version dependencies.

3.2 Pinned Version — Alpine

services:
  db:
    image: "pgautoupgrade/pgautoupgrade:15-alpine"
    volumes:
      - postgres_data:/var/lib/postgresql/data/
    environment:
      POSTGRES_PASSWORD: mysecretpassword

Use a pinned tag in production when you depend on a specific PostgreSQL minor version, or when your extensions (e.g. PostGIS, pgvector) need to be tested against a fixed target before you upgrade.

3.3 Pinned Version — Debian (Recommended for Most Migrations)

services:
  db:
    image: "pgautoupgrade/pgautoupgrade:17-bookworm"
    volumes:
      - postgres_data:/var/lib/postgresql/data/
    environment:
      POSTGRES_PASSWORD: mysecretpassword

This is the safest choice when migrating an existing stack that used the standard postgres:N (Debian) image.

Back up before you change the image tag. pgautoupgrade uses pg_upgrade --link, which modifies data files in place. There is no automatic rollback.

4. Environment Variables Reference

PGAUTO_ONESHOT

One-shot mode is useful when you want to upgrade the data directory as a discrete CI step or in a Kubernetes init container, before your application starts. The container exits with code 0 on success, non-zero on failure — easy to integrate into scripts.

docker run --name pgauto -it \
  --mount type=bind,source=/path/to/your/database/directory,target=/var/lib/postgresql/data \
  -e POSTGRES_PASSWORD=password \
  -e PGAUTO_ONESHOT=yes \
  pgautoupgrade/pgautoupgrade:17-bookworm

PGAUTO_REINDEX

After every major-version upgrade, pgautoupgrade re-indexes all databases by default. This is the safe default because index formats can change between major versions, but on large databases it can add significant time to the first start. If you have already verified index integrity — or if you are running the upgrade in a staging environment and plan to rebuild indexes separately — you can skip this step:

docker run --name pgauto -it \
  --mount type=bind,source=/path/to/your/database/directory,target=/var/lib/postgresql/data \
  -e POSTGRES_PASSWORD=password \
  -e PGAUTO_REINDEX=no \
  pgautoupgrade/pgautoupgrade:17-bookworm

5. Common Failure Modes and How to Handle Them

5.1 Container Exits Immediately on First Start

Symptom: Container starts, logs show pg_upgrade output, then exits with a non-zero code.

Most likely cause: Locale or library mismatch — almost always the Debian-vs-Alpine problem described above. Switch to the corresponding -bookworm tag and retry.

Second most likely cause: The data directory is owned by root rather than the postgres user. Fix permissions before starting:

docker run --rm -v postgres_data:/var/lib/postgresql/data \
  alpine chown -R 999:999 /var/lib/postgresql/data

5.2 Extensions Fail to Load After Upgrade

pg_upgrade does not upgrade extensions — it migrates the schema references but the shared libraries must be present in the new image. If you use third-party extensions (PostGIS, pgvector, TimescaleDB, etc.), confirm the target pgautoupgrade image ships those extension packages. If it does not, you will need a custom image built FROM pgautoupgrade/pgautoupgrade:17-bookworm that installs the extension packages.

5.3 Upgrade Hangs on Reindex of a Large Database

The default post-upgrade reindex can take tens of minutes on databases with many large indexes. If startup-time SLAs matter, run the upgrade with PGAUTO_REINDEX=no, start your application, then run REINDEX DATABASE your_db_name; during a maintenance window instead.

5.4 Data Directory Shows "is not empty" Error

This happens when POSTGRES_PASSWORD (or other init vars) cause the entrypoint to try to re-initialise a data directory that already contains files. pgautoupgrade handles this correctly on its own — do not pass POSTGRES_INITDB_ARGS or any init-related environment variables that would trigger re-initialisation on a non-empty data directory.

6. Pre-Upgrade Backup Strategy

Because pgautoupgrade operates on your live data directory with --link (no copy step), recovering from a failed upgrade requires a pre-existing backup. Two practical approaches:

Option A — Volume snapshot (fastest): Before changing the image tag, stop the stack and snapshot the named volume or bind-mount directory at the filesystem level. On Linux with Docker volumes:

# Stop the stack
docker compose down

# Archive the volume data
docker run --rm \
  -v postgres_data:/source:ro \
  -v $(pwd)/backups:/backup \
  alpine tar czf /backup/postgres_data_$(date +%Y%m%d).tar.gz -C /source .

Option B — Logical dump (portable): Use pg_dump while the old database is still running to produce a portable dump you can restore into any target version:

docker compose exec db \
  pg_dump -Fc -U postgres mydb > backups/mydb_$(date +%Y%m%d).dump

Restore with pg_restore if the upgrade fails:

docker compose exec db \
  pg_restore -U postgres -d mydb /path/to/mydb_YYYYMMDD.dump

7. Key Features Summary

8. When pgautoupgrade Is Not the Right Tool

pgautoupgrade is the right fit when:

• You want zero-touch upgrades across multiple environments (dev, staging, prod) via image tag changes.
• Your PostgreSQL data lives in a Docker-managed volume or bind mount.
• You are managing a product (like Redash) distributed as a docker-compose.yml and cannot predict what version each user is running.

Consider a manual pg_dump / pg_restore workflow instead when:

• You use extensions that are not bundled in the pgautoupgrade image.
• You need fine-grained control over the upgrade window and rollback plan.
• You are moving between very disparate base OS environments and cannot switch to the Bookworm variant.

For the full source, open issues, and version history, see the pgautoupgrade GitHub repository.