Upgrading between major versions?

[roosmaa]

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?

[md5]

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

[roosmaa]

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?)

[tianon]

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?

[md5]

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

[kajmagnus]

+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.

[mpe]

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

[belak]

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

[mkarg]

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?

[tianon]

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).

[dcbishop]

+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.

[Dirbaio]

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.

[asprega]

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?

[donnykurnia]

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.

[julienmathevet]

+1