Upgrading between major versions?

There doesn't seem to be a good way to upgrade between major versions of postgres. When sharing the volume with a new container with a newer version of postgres it won't run as the data directory hasn't been upgraded. pg_upgrade on the other hand requires (?) old installation binary files, so upgrading the data files from new server container is also difficult.

It would be nice if there was some suggested way of doing this in the readme. Maybe even some meta container which does the upgrading from version to version?

@Jonast

@Jonast, The only complete way is to have images for each set of supported versions; to ensure users can upgrade between them. @tianon, has a set of them in tianon/docker-postgres-upgrade. The other major problem is that it requires two folders (volumes) in specific locations to upgrade your database, since pg_upgrade requires both servers to be running and cannot just upgrade in place. Tianon's repo has more information on the requirements of this, which includes how to do it with one volume (properly setup), so that pg_upgrade can take advantage of it being a single drive and "use hard links instead of copying files to the new cluster".

The reason mariadb works to "upgrade automatically…

The "Usage" section of this document describes what pg_upgrade does: http://www.postgresql.org/docs/9.4/static/pgupgrade.html

I guess my concern does not communicate well in the first comment... Lets try again.

pg_upgrade functionality is perfectly clear: you need to have both postgres [old] and [new] versions installed at the same time to upgrade the data structures on disk. But what is not clear is the best way of going about the upgrade when using containers, since every container only has a single version of postgres available/installed.

Should one create a custom upgrade-from-X-to-Y container with both X and Y versions of postgres installed?
Or should one just apt-get install new postgres version into the existing container, upgrade the data and then replace the container with one of the "vanilla" containers?
Or some even more clever way of doing this?

It would be fairly useful to have a section in the Readme regarding how to approach this. (And what to avoid?)

I've been wondering about/looking out for a good way to handle this
myself. If someone's got a good flow that works well for them, I'm very
interested in getting something documented (and "transition images" added
if that's what's necessary here -- this is a common enough thing IMO that
it doesn't really make sense to have everyone create their own for their
one-off needs).

Perhaps for each "supported" version, we could add a variant that also
installs the latest major release, and then we document how to create your
own one-off transition container if you need a transition more esoteric
than that?

@roosmaa

@roosmaa: my comment wasn't so much directed at you as intended as background.

+1 for major version upgrade instructions.

Yet another alternative, for smaller databases (?), should be that the old container runs pg_dumpall and saves the dump somewhere in the volume. Then one stops the container, starts a new one with the new major version, and it imports the dump.

Yeah this is a major pain ... at the moment. Would be great to get some official way to do it.

Yeah, just nuked my postgres install on accident with an upgrade. Are there any plans for this?

I am not a docker guru, but I could imagine that it is possible to add some script to the container that is started when the container starts. That script should check the database files, and when it detects the correct version, simply startup the postgresql engine. If not, it should decide to run pgupgrade or dump/reload. Shouldn't be rocket science?

The main problem as I understand it is that we need both the old version and the new version of the postgres binaries simultaneously in the same container (otherwise you can't pgdump the old data).

+1.

I'm just going to nuke what I have since it's just a personal server I don't really use, but this seems like a major pain maintenance wise.

I've had success upgrading launching another postgres instance with the new version, and then using dumpall and psql to move all the data piping:

docker exec postgres-old pg_dumpall -U postgres | docker exec -i postgres-new psql -U postgres

It's rather simple :)

pg_upgrade is supposed to be faster though, I'm interested if someone has an easy way of using it.

I was attempting to hack up a bash script to manage the upgrade process between two containers of different versions by using pg_upgrade, but I hit a roadblock:

#!/bin/bash

#
# Script to migrate PostgreSQL data from one version to another
#

set -e

OLD_CONTAINER=$1
OLD_VOLUME=$2

NEW_CONTAINER=$3
NEW_VOLUME=$4

if [ -z "$OLD_CONTAINER" ] || [ -z "$OLD_VOLUME" ] || [ -z "$NEW_CONTAINER" ] || [ -z "$NEW_VOLUME" ]; then
  echo -e
  echo -e "Usage: ./pg_upgrade_docker.sh [old container name] [old volume name] [new container name] [new volume name]"
  echo -e "Example: ./pg_upgrade_docker.sh postgres94 postgres94_data postgres95 postgres95_data"
  echo -e
  exit 1;
fi

# Get the major version and the pg binaries out of the old postgres container
(docker start "$OLD_CONTAINER" || true) > /dev/null
OLD_MAJOR="$(docker exec "$OLD_CONTAINER" bash -c 'echo "$PG_MAJOR"')"

OLD_BIN_DIR="/tmp/pg_upgrade/bin/$OLD_MAJOR"
mkdir -p "$OLD_BIN_DIR"
rm -rf "$OLD_BIN_DIR"/*
docker cp "$OLD_CONTAINER":"/usr/lib/postgresql/$OLD_MAJOR/bin/." "$OLD_BIN_DIR"

(docker stop "$OLD_CONTAINER" || true) > /dev/null

# Get the major version out of the new postgres container
(docker start "$NEW_CONTAINER" || true) > /dev/null
NEW_MAJOR="$(docker exec "$NEW_CONTAINER" bash -c 'echo "$PG_MAJOR"')"
(docker stop "$NEW_CONTAINER" || true) > /dev/null

# Create a temp container running the new postgres version which we'll just use to migrate data from one volume to another.
# This container will use the old binaries we just extracted from the old container.
# We can't reuse the existing "new" container because we have to bind extra volumes for the update to work.
NEW_IMAGE="$(docker ps -a --filter "name=$NEW_CONTAINER" --format "{{.Image}}")"
docker run -v "$OLD_BIN_DIR":/tmp/old-pg-bin -v "$OLD_VOLUME":/tmp/old-pg-data -v "$NEW_VOLUME":/tmp/new-pg-data \
  --name temp_postgres_util "$NEW_IMAGE" su - postgres -c "cd /tmp && /usr/lib/postgresql/$NEW_MAJOR/bin/pg_upgrade \
    -b /tmp/old-pg-bin -B /usr/lib/postgresql/$NEW_MAJOR/bin \
    -d /tmp/old-pg-data/ -D /tmp/new-pg-data/ \
    -o \"-c config_file=/tmp/old-pg-data/postgresql.conf\" -O \"-c config_file=/tmp/new-pg-data/postgresql.conf\""

# Remove temp container
(docker stop temp_postgres_util) > /dev/null
(docker rm temp_postgres_util) > /dev/null

rm -rf "$OLD_BIN_DIR"

echo -e "Data migration from $OLD_MAJOR to $NEW_MAJOR is complete!"

the idea is a bit convoluted, because I'm extracting the binaries from the old version and mounting them into a new temporary container created from the same image of the container with the new version, along with data volumes from existing containers (the old and the new ones).
My idea was then to use that container to run pg_upgrade, then throw it away (and data would have been migrated through the two volumes).
When running the script, though, I get the following error:

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok

*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.

connection to database failed: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/.s.PGSQL.50432"?


could not connect to old postmaster started with the command:
"/tmp/old-pg-bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/tmp/old-pg-data/" -o "-p 50432 -b -c config_file=/tmp/old-pg-data/postgresql.conf -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/tmp'" start
Failure, exiting

Any ideas?

@Dirbaio

I'm using @Dirbaio tricks to dump all data from old container and restore it into the new container. Of course, this needs separate data volume and run fast with small data sets. I hope that pr_upgrade can be more 'intelligent', even better if the Postgres itself could do the upgrade by itself. I mean, when installing the new version, the apps should also know how old version format looks like. Maybe also include a necessary old version binary to do the data 'upgrade' then after the upgrade finished, just delete that old version binary.

I think we should have a complete solution or none at all, as I doubt that most people always use the latest version.

From: Jonas Thiem [mailto:notifications@github.com]
Sent: Freitag, 20. Mai 2016 08:53
To: docker-library/postgres
Cc: Markus KARG; Comment
Subject: Re: [docker-library/postgres] Upgrading between major versions? (#37)

Ok so why can't the container be changed to simply have both the latest and the second-to-latest postgresql version installed? (e.g. just put a chroot into the docker container and install the older package from the distribution there or something. Once a script for that has be made that just needs to be passed the name of the respective older apt package, it shouldn't really be a lengthy process) At least that would cover the majority of users and it would allow to successfully convert the database for them automatically by the container.

—
You are receiving this because you commented.
Reply to this email directly or view it on GitHub #37 (comment) https://github.com/notifications/beacon/ABn3tyqP3YQcazFg6O98ZNdeqZx_FYJwks5qDVpUgaJpZM4C_kVy.gif

@subzero911

PostgreSQL will create a subfolder with it's version after start.
@subzero911 Just drop the /data at the end, like kalimkhan87 tried to explain with inline Code in #37 (comment) or ByteBolt556 already posted #37 (comment) last year in a screenshot (unfortunately auto hidden by GitHub).

Here is an example for a compose project.

services:
  db:
    image: docker.io/postgres:18-alpine
    #ports:
    #  - 5432:5432/tcp
    env_file:
      - .env
    volumes:
      - pgsql:/var/lib/postgresql
      - ./initdb.d:/docker-entrypoint-initdb.d

volumes:
  pgsql:

fix(infra,deploy): two fixes for Kamal end-to-end on the local DinD s…

bump postgres 13.1 -> 18.3 with explicit PGDATA

Change tmpfs mount directory for postgress 18 compatibility

feat: add audio_recording and analysis coverage reports

Fix volume path for PostgreSQL data

Fix volume path for PostgreSQL data (#250)

@tianon

The issue was revieved a bit and it yet again remindes me my the annoyance I have when I read about new PosgreSQL version being announced.

Alas:

If anyone's interested in trying an "automatic upgrade" image that works for Debian based PostgreSQL containers, we've just added 16-bookworm and 15-bookworm docker images here:

https://hub.docker.com/r/pgautoupgrade/pgautoupgrade

@tianon -- would it be totally impossible to adopt above solution from @justinclift in the main PostgreSQL image?

@tianon -- would it be totally impossible to adopt above solution from @justinclift in the main PostgreSQL image?

The maintainers have made it clear many times that there are no plans to do so, so better not expect it. As for why, to reiterate, in order to perform the upgrade you need both the old version and the new version of PostgreSQL, in other words every image will have to have multiple versions of the engine, which explodes the image size, build times, and complexity. Since 99% of the time the image will just be running the single engine in production, the decision was made to keep the main image lean and simpler to manage/maintain. For the once a few years case where an upgrade is needed, using a separate dedicated image for the upgrade is fine. In some ways even preferable. Auto updates are generally good when they are bug fixes, but for major upgrades I've personally had breaks (in general with other projects) requiring manual intervention, so it's preferable for that to be a very deliberate process with supervision. With that said, I too wish for a more official and simpler way to upgrade, but I understand why this isn't done on the main image.

@tianon -- would it be totally impossible to adopt above solution from @justinclift in the main PostgreSQL image?

The maintainers have made it clear many times that there are no plans to do so, so better not expect it. As for why, to reiterate, in order to perform the upgrade you need both the old version and the new version of PostgreSQL, in other words every image will have to have multiple versions of the engine, which explodes the image size, build times, and complexity. Since 99% of the time the image will just be running the single engine in production, the decision was made to keep the main image lean and simpler to manage/maintain. For the once a few years case where an upgrade is needed, using a separate dedicated image for the upgrade is fine. In some ways even preferable. Auto updates are generally good when they are bug fixes, but for major upgrades I've personally had breaks (in general with other projects) requiring manual intervention, so it's preferable for that to be a very deliberate process with supervision. With that said, I too wish for a more official and simpler way to upgrade, but I understand why this isn't done on the main image.

I think that's fair. Having just gone through this process of trying to find out how to upgrade from one major version to another and finding this issue with references to pgautoupgrade buried in the long conversation history, maybe there could be a compromise to add a section about upgrading to the image's README (so it shows up on Docker Hub)?

The maintainers have made it clear many times that there are no plans to do so

Ok, that's acceptable for this image. But in any case it would be useful to have another official PostgreSQL image for upgrades... maybe a pgupgrade official image.

Navigation Menu

Repository navigation

Upgrading between major versions? #37

163 remaining items

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Upgrading between major versions? #37

Description

Activity

md5 commented on Nov 23, 2014

roosmaa commented on Nov 23, 2014

tianon commented on Nov 23, 2014

md5 commented on Nov 23, 2014

kajmagnus commented on Jan 13, 2015

mpe commented on Mar 20, 2015

belak commented on Jun 16, 2015

mkarg commented on Dec 29, 2015

tianon commented on Dec 29, 2015

dcbishop commented on Jan 31, 2016

Dirbaio commented on Feb 5, 2016

asprega commented on Mar 8, 2016

donnykurnia commented on Mar 11, 2016

julienmathevet commented on Apr 8, 2016

mkarg commented on May 20, 2016

163 remaining items

subzero911 commented on May 12, 2026

zierf commented on May 12, 2026

FunkyKwak commented on Jul 2, 2026

zierf commented on Jul 3, 2026

woj-tek commented on Jul 3, 2026

Guiorgy commented on Aug 24, 2026

mschoettle commented on Aug 28, 2026

collimarco commented on Aug 28, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions