DOCSEARCHbyAlgolia
Submit Docs

pgautoupgrade Documentation

repository·main·Indexed 22 days ago

https://github.com/pgautoupgrade/docker-pgautoupgrade

A Docker image designed to automatically detect the PostgreSQL version in an existing data directory and upgrade it to a target version using pg_upgrade --link. It supports Alpine and Debian-based images, Kubernetes initContainer integration via a 'One Shot' mode, and specific compatibility adjustments for Bitnami PostgreSQL containers.

Tokens
1.6K
Snippets
4
Records
7
Agent score
28%

What's inside pgautoupgrade

  1. Use "One Shot" mode to perform upgrades only

    main

    If you want to perform the database upgrade without starting the PostgreSQL server afterwards, use "One Shot" mode by setting the PGAUTO_ONESHOT environment variable to yes.

    Kubernetes Integration

    You can run pgautoupgrade as an initContainer in Kubernetes to perform the upgrade before your main database container starts. For Bitnami containers, ensure you set runAsUser: 0 so the container can restore original file permissions.

    initContainers:
- env:
  - name: PGAUTO_ONESHOT
    value: "yes"
  - name: POSTGRES_DB
    value: XXX
  - name: PGDATA
    value: /bitnami/postgresql/data
  - name: POSTGRES_PASSWORD
    value: password
image: pgautoupgrade/pgautoupgrade:18-trixie
name: upgrade-postgres
securityContext:
  runAsUser: 0
volumeMounts:
  - mountPath: /bitnami/postgresql
    name: YYY
    $ 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 \
	<NAME_OF_THE_PGAUTOUPGRADE_IMAGE>

  2. Build, test, and customize the pgautoupgrade image

    main

    Building for development

    To create a local development image named pgautoupgrade:local:

    $ make dev

    Running tests

    To run the automated test suite (which performs sequential upgrades from PG 9.5 through 17.x using the AdventureWorks database):

    $ make test

    Customizing with extensions

    Instructions for including custom extensions (like PostGIS) are available in the project wiki: https://github.com/pgautoupgrade/docker-pgautoupgrade/wiki/Including-Extensions-(PostGIS)

  3. Install and use pgautoupgrade images

    main

    pgautoupgrade is a Docker image designed to automatically detect the PostgreSQL version in an existing data directory and upgrade it to a target version using pg_upgrade --link. After the upgrade, the old cluster data is removed and the PostgreSQL server starts normally.

    Important Precautions

    • Backup your data! The image performs an in-place upgrade. If the process fails, you must have backups to restore.
    • Remove healthchecks. The upgrade process requires a custom healthcheck implementation; do not provide your own.

    Choosing an Image Tag

    • Latest PostgreSQL: Use pgautoupgrade/pgautoupgrade:latest (Note: this is Alpine-based).
    • Specific PostgreSQL version (Alpine): Use tags like pgautoupgrade/pgautoupgrade:18-alpine.
    • Debian-based (Recommended for official Postgres migrations): If you are upgrading from the official Debian-based Docker Postgres image, use a Debian-based tag to avoid compatibility issues, such as pgautoupgrade/pgautoupgrade:18-trixie.

    Basic Usage Example

    # Example using PostgreSQL 18 on Alpine
docker run --name pgauto -it \
	--mount type=bind,source=/path/to/your/database/directory,target=/var/lib/postgresql/data \
	-e POSTGRES_PASSWORD=password \
	pgautoupgrade/pgautoupgrade:18-alpine
    pgautoupgrade/pgautoupgrade:latest
pgautoupgrade/pgautoupgrade:18-alpine
pgautoupgrade/pgautoupgrade:18-trixie

  4. Use breakpoints for debugging the upgrade process

    main

    For developers or users debugging an upgrade, the image provides two predefined breakpoints. When triggered, the docker-entrypoint script pauses, allowing you to docker exec into the container to inspect the state.

    • before breakpoint: Stops just before the pg_upgrade command runs. Use this to test alternative configurations or manual steps.
    • server breakpoint: Stops after pg_upgrade has completed but before the PostgreSQL server starts. Use this to investigate the upgraded data files before the server attempts to use them.

    To use these, run the corresponding make command (if building locally) or trigger the breakpoint logic via your entrypoint configuration.

    $ make before
$ make server

  5. Upgrade from Bitnami PostgreSQL containers

    main

    pgautoupgrade supports upgrading from Bitnami PostgreSQL containers with the following adjustments:

    • Configuration Files: If postgresql.conf and pg_hba.conf are missing from the data directory (common in Bitnami), the image copies default versions into the directory. In "One Shot" mode, these are removed after the upgrade.
    • Permissions: Bitnami uses UID 1001, while the official Postgres image uses 999. During upgrade, data is copied with UID 999. If using "One Shot" mode, the container must run as root to restore the original Bitnami permissions. If running as user 999, you must manually restore permissions afterward.
    • Environment Variable Mapping:
      • POSTGRESQL_PASSWORD $\rightarrow$ POSTGRES_PASSWORD
      • POSTGRESQL_DATA_DIR $\rightarrow$ PGDATA (automatically set to /bitnami/postgresql/data if not empty).

  6. Configure reindexing behavior

    main

    By default, all databases are reindexed after the migration. This can be time-consuming for large databases. To skip this step, set the PGAUTO_REINDEX environment variable to no.

    WARNING

    PostgreSQL versions 15 and below do not support concurrent reindexing of system tables. This causes database locks that prevent modifications while reindexing is running. It is highly recommended to use PostgreSQL 16 or later to avoid this issue.

    $ 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 \
	<NAME_OF_THE_PGAUTOUPGRADE_IMAGE>

  7. Fix mount errors for PostgreSQL v18+ upgrades

    main

    When upgrading to PostgreSQL v18+, you may encounter an error when mounting data to /var/lib/postgresql/data (e.g., no such file or directory).

    Reason: PostgreSQL v18+ enforces a new data directory structure where PGDATA points to /var/lib/postgresql/MAJOR/docker instead of /var/lib/postgresql/data.

    Solution: Adjust your volume mount from /var/lib/postgresql/data to /var/lib/postgresql. The pgautoupgrade image will detect the old installation in the parent directory and move the data into the new required structure.