New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Upgrading between major versions? #37

Open
roosmaa opened this Issue Nov 23, 2014 · 70 comments

Comments

Projects
None yet
@roosmaa

roosmaa commented Nov 23, 2014

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

This comment has been minimized.

Contributor

md5 commented Nov 23, 2014

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

@roosmaa

This comment has been minimized.

roosmaa commented Nov 23, 2014

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

This comment has been minimized.

Member

tianon commented Nov 23, 2014

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

This comment has been minimized.

Contributor

md5 commented Nov 23, 2014

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

@kajmagnus

This comment has been minimized.

kajmagnus commented Jan 13, 2015

+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

This comment has been minimized.

mpe commented Mar 20, 2015

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

@belak

This comment has been minimized.

belak commented Jun 16, 2015

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

@mkarg

This comment has been minimized.

mkarg commented Dec 29, 2015

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

This comment has been minimized.

Member

tianon commented Dec 29, 2015

@dcbishop

This comment has been minimized.

dcbishop commented Jan 31, 2016

+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

This comment has been minimized.

Dirbaio commented Feb 5, 2016

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

This comment has been minimized.

asprega commented Mar 8, 2016

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

This comment has been minimized.

donnykurnia commented Mar 11, 2016

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.

@easyrasta

This comment has been minimized.

easyrasta commented Apr 8, 2016

+1

@JonasT

This comment has been minimized.

JonasT commented May 20, 2016

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.

@mkarg

This comment has been minimized.

mkarg commented May 20, 2016

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

@JonasT

This comment has been minimized.

JonasT commented May 23, 2016

Why is none at all any better? I would assume most people running a production server with some basic security consciousness will probably use at least some version of somewhere in the latest release cycle - such major upgrades aren't happening that often, right? (I'm thinking of the usual docker environment here optimized for flexibility and repeatability where upgrading and reverting if it goes wrong is usually less painful than for a regular oldschool server administrator) And if doing it for all versions is not feasible, at least doing it for the respective second-to-newest one should be doable..

If for the other cases where the upgrade can't be done there is a descriptive error message like there is now, I don't see how this wouldn't be at least a considerable improvement - although I agree that being able to upgrade from any arbitrary previous version automatically is of course better if it can be done.

@mkarg

This comment has been minimized.

mkarg commented May 23, 2016

It binds resources better invested into developing a general solution, and it possibly could stand in the way when coming up with the general solution.

-Markus

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

Why is none at all any better? I would assume most people running a production server with some basic security consciousness will probably use at least the latest release - they aren't happening that often, right? And if doing it for all versions is not feasible, at least doing it for the respective second-to-newest one should be doable..

If for the other cases where the upgrade can't be done there is a descriptive error message like there is now, I don't see how this wouldn't be at least a considerable improvement - although I agree that being able to upgrade from any arbitrary previous version automatically is of course better if it can be done.


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

@JonasT

This comment has been minimized.

JonasT commented May 23, 2016

Well sure, if there are any plans to find such a generic good solution I'd prefer that too...
however, the only solution @tianon suggested so far seems IMHO inferior to making the main container itself always contain the second-to-last version as well, because it wouldn't run automatically (hence being more general than what I suggested, true, but also IMHO much less useful in the practical use of running a dockerized environment where I usually expect the services to deal with their own updates properly if I regularly upgrade the docker containers - see for example the official gitab-ce container by gitlab.com folks which seems to do this without issues).
And apart from that the state of this ticket seems to be more resignated rather than any useful automated general solution upcoming...

@mkarg

This comment has been minimized.

mkarg commented May 23, 2016

Yes, resignation is the right word. We just do not see how it could be solved in a good way.

@JonasT

This comment has been minimized.

JonasT commented May 24, 2016

Well what about my suggestion then? Wouldn't make things worse, would work fully automatically, and would cover at least a notable part of users..

@mkarg

This comment has been minimized.

mkarg commented May 24, 2016

If you have time you can do whatever you like -- this is the nature of open source. Whether or not the project leads will accept your PR is a different question and not up to me to decide.

@tianon

This comment has been minimized.

Member

tianon commented May 24, 2016

@JonasT

This comment has been minimized.

JonasT commented May 24, 2016

If postgres can be installed from source without a fully fledged chroot with basically a copy of the distribution in a subfolder (which would be rather unfortunate to be required) which depends on the build system (e.g. autoconf has the install --prefix option which allows this), then I guess it would be around the install size of postgresql itself? If that is still too large for your taste, then I suppose it's not worth trying...

@tianon

This comment has been minimized.

Member

tianon commented May 25, 2016

I spent a little time working on a generic way to run pg_upgrade for somewhat arbitrary version combinations (only increasing, ie 9.0 to 9.5, but not the reverse), and here's the result:
tianon/postgres-upgrade
(might be easier to read directly over at https://github.com/tianon/docker-postgres-upgrade/blob/master/README.md)

@rosenfeld

This comment has been minimized.

rosenfeld commented Aug 23, 2016

I haven't tested this yet but, since it's built on top of Debian, maybe an option would be to add some script to /docker-entrypoint-initdb.d like this, during the upgrade process (ideally making an snapshot of the original data first when using BTRFS for example):

Considering the current DB is on 9.4 and you want to migrate it to 9.5

apt-get update
apt-get install -y postgresql-9.5 postgresql-contrib-9.5
pg_dropcluster 9.5 main
pg_upgradecluster 9.4 main -m upgrade -k

This is the basic idea. However, this will add some unneccessary downtime. We could save some time by creating a temporary image:

FROM postgres:9.4
RUN apt-get update && apt-get install -y postgresql-9.5 postgresql-contrib-9.5
RUN echo "pg_dropcluster 9.5 main; pg_upgradecluster 9.4 main -m upgrade -k" > /docker-entrypoint-initdb.d/zzz-upgrade.sh

This is just to simplify the explanation, I'd probably use COPY rather than echo ... > ......

The container running from this new image would then only execute the remaining steps and would be run only once during the upgrade after stopping the previous 9.4 container and before starting the new 9.5 container. pg_upgradecluster will reuse the previous configuration when creating the new cluster.

Maybe to reduce the impact on users, one might want to run the 9.4 cluster in a read-only transaction during the upgrade so that it would mostly work and handle the write errors in the application to tell the users the database is being upgraded and that it's currently not possible to write new data to it... If upgrading the cluster would take a while even with the --link/-k option, maybe a read-only mode might give users a better experience rather than presenting them a static maintainance page for a long while... Maybe it could switch the header to let users know that it's in read-only mode during the upgrade in the meanwhile... It's more effort, but a better user experience for some sort of applications.

@JonasT

This comment has been minimized.

JonasT commented Oct 2, 2016

Doesn't all of upgrading work automatically in the mariadb container though? I think I'll simply use mariadb for everything until this is addressed properly.

It is nice to have extra helper containers to assist with the upgrading, but I think not doing this automatically is pretty bad for a ready-to-go container meant for actual painless real world use. Also is an old postgres install really that large? If it's just 100mb or something, the database is likely much larger than the supposed size bloat would cause...

I think none of the arguments are compelling enough for not simply doing this for the user (at least for the latest previous major release) - after all the container is named "postgres", not "postgres-minimal".

@yosifkit

This comment has been minimized.

Member

yosifkit commented Oct 3, 2016

@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" is that the mariadb team has to put in extra work to make newer versions able to read older data directories, whereas postgres can make backwards incompatible changes in newer releases and not have to worry about file formats from older versions. This is why postgres data is usually stored under a directory of its version, but that would over complicate volume mounts if every version of postgres had a different volume.

@rosenfeld, unfortunately pg_dropcluster and pg_upgradecluster are specific tooling for the Debian provided packages and do not work with the Postgres provided apt/deb packages. Even after writing a new entrypoint to run the upgrade I just get failures, since it is assume specific directory structures and probably an init system:

$ # script:
#!/bin/bash

gosu postgres pg_ctl -D "$PGDATA" \
    -o "-c listen_addresses='localhost'" \
    -w start

gosu postgres pg_dropcluster 9.5 main
gosu postgres pg_upgradecluster 9.4 main -m upgrade -k

gosu postgres pg_ctl -D "$PGDATA" -m fast -w stop
exec gosu postgres "$@"

$ # shortened ouput (after initializing the db folder with a regular postgres image)
...
waiting for server to start....LOG:  database system was shut down at 2016-10-03 18:22:33 UTC
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started
 done
server started
Error: specified cluster does not exist
Error: specified cluster does not exist
@monotek

This comment has been minimized.

monotek commented Jan 5, 2018

Imho export & remimport via sql would be easiest way.
One could just put the old sql file in some volume and it could be imported automatically on start.

@tianon

This comment has been minimized.

Member

tianon commented Jan 5, 2018

It seems to me what makes this "hard" is the need to mount two data directories from the host

Not exactly -- for me, the thing that makes this "hard" (and unacceptable by default for the official images) is that it requires having both the target old and target new versions of PostgreSQL installed in the image. To support arbitrary version bumps from an older version to a newer version, we'd have to install all versions (because the "export and reimport" method described is essentially how pg_upgrade works, in its most basic form).

@JonasT

This comment has been minimized.

JonasT commented Jan 5, 2018

@tianon well you could still always support the latest major version upgrade, and cover 99% of your users... (see my comment above for a more detailed motivation)

@claytondaley

This comment has been minimized.

claytondaley commented Jan 5, 2018

To support arbitrary version bumps from an older version to a newer version, we'd have to install all versions

Why can't the upgrade script install the appropriate version (as I described), run the upgrade, then uninstall?

@JonasT

This comment has been minimized.

JonasT commented Jan 5, 2018

@claytondaley for security reasons, I'm quite sure a lot of database containers don't run with internet access

@claytondaley

This comment has been minimized.

claytondaley commented Jan 5, 2018

@JonasT I figure people with additional constraints like that are sophisticated enough to understand how to adjust their Docker workflow. Best to design a default for the "plug and play" users who aren't going to be able to troubleshoot something harder.

If you want to make it "even easier", offer a 10.0-upgrade image that pre-installs everything. A no-internet user can use that imagine for the upgrade. If they're concerned about space, they can switch back to 10.0 when done. Since the versions match, the upgrade logic will not run and the lack of internet will not matter.

Philosophically, I think the ideal strategy allows most people to change 9.4 to 10.0 without changing mount points or running any special commands. In my proposal, internet-enabled users can. Internet-disabled users have to use the -upgrade image (either always or transiently).

@JonasT

This comment has been minimized.

JonasT commented Jan 5, 2018

@claytondaley yes but it is better to default to something that works in a properly secured production environment. Therefore, I think at least the second-to-latest version should be baked for an upgrade with no need for internet access. Otherwise, you're encouraging bad practices (because some users will be motivated to enable internet access on their databases just to make this feature available to them, or avoid running without because they think they might need it)

@claytondaley

This comment has been minimized.

claytondaley commented Jan 5, 2018

@JonasT whether or not you bake in the second-to-last is orthogonal to the substance of my proposal. For that matter, why not include all of the binaries and... after the upgrade logic runs (or is skipped)... uninstall all of them? Won't that simultaneously support all upgrades (including your "best practice") and still minimize the final footprint?

My (orthogonal) question is how do you "allow... people to change 9.4 to 10.0 without changing mount points or running any special commands".

  • I don't think there's any debate that the right script can handle the "no special commands" part.
  • But I don't see how it works with the current binding/storage/$PGDATA strategy.

The more user-friendly versions of @tianon's scripts address the binding issue by binding "up" a level (or two) so the versions are found in $PG_MAJOR folders. I think it makes sense to generalize @tianon's solution for the actual postgres containers (with some backwards compatibility).

@JonasT

This comment has been minimized.

JonasT commented Jan 5, 2018

@claytondaley

For that matter, why not include all of the binaries

Because the image size will blow up if you have them installed there and only remove them as soon as the container launches. I wouldn't mind it much, but others in this thread have expressed concern.

@wglambert wglambert added the question label Apr 24, 2018

@jedie

This comment has been minimized.

jedie commented Apr 30, 2018

Currently there is no official statement how to upgrade, isn't it?!?

@tianon

This comment has been minimized.

Member

tianon commented Apr 30, 2018

The "official statement" is that you upgrade in Docker just like you would any other instance of PostgreSQL: via pg_upgrade.

The only thing that makes doing so tricky in the case of these images is the need to have both the new and old versions installed concurrently. See https://github.com/tianon/docker-postgres-upgrade (which is linked a couple times in the previous comments) for where I'm maintaining a set of images which accomplish this in the bare minimum way possible (which you can either use directly or refer to in building your own solution).

@monotek

This comment has been minimized.

monotek commented Apr 30, 2018

Is there no way of a SQL dump in the old version & reimport it in the new version to do an upgrade?

@chulkilee

This comment has been minimized.

chulkilee commented Apr 30, 2018

Here's my two cents:

  • dump & restore may work, but you should prefer pg_upgrade for many reasons (search it)
  • upgrading PostgreSQL data into the version of current running docker automatically should not happen; it's always safer to require explicit instruction to perform backward-incompatible changes

PostgreSQL has the official way to upgrade, and you can do it. The question is whether this "official" postgres docker image should support it out of box. It's good to have, technically it needs binary in both version - the current version and your version, which is impractical to include it in docker image.

I believe the consensus of "official docker image" is "minimum wrapper of standard package". It is not full installer with upgrade feature. This upgrade problem is nothing special with docker..

I think the documentation of postgres docker image just need to mention pg_upgrade - and probably link to this issue (or even https://github.com/tianon/docker-postgres-upgrade).

@tianon

This comment has been minimized.

Member

tianon commented Apr 30, 2018

Dump and reload is an OK solution (although definitely sub-optimal, as described above) -- just use pg_dump like you normally would, then import it into the new version and you're good to go. As noted above, that's basically how pg_upgrade works in many cases (and why it needs both versions installed).

@srvrguy

This comment has been minimized.

srvrguy commented Jul 12, 2018

Sarusso, it's complicated. In general, upgrades to a new minor release within the same major release won't change the on-disk data format. So, upgrading from ~~~9.5 to 9.6 or~~~ 10.3 to 10.4 should work just fine keeping the same data files. Upgrading a major release, say from a 9.x.y release to a 10.x, is more complex. The data file structure can change, sometimes significantly, between major releases. I'm not a developer for the project, but I can guess that it would be a lot of work to maintain a way to read the old format and then re-write the files in the new format. I can't even imagine the bugs and the possible data loss concerns of something like that. Luckily, they have very long support policies for releases, giving lots of time to figure out a migration strategy.

There actually is support for migrating data files in PostgreSQL, it's called pg_upgrade. The Docker container is special in that it's difficult to use this support because it needs both the older and the newer versions installed on the same system. The Docker container isn't set up to work this way. Alternately, you can also look into replication if you have large data sets or absolutely can't have any downtime. Options are actually discussed on this official documentation page.

I'd suggest that if you don't need any new feature in 10, move to the latest 9.x release you can (9.3.23 is latest in the 9.3 series right now) and then read the information about ways to upgrade and plan something out. You have a ~~~while to do so, until 2021~~~ bit of time, until September 2018 if you care about active support. Of course, if it's working fine, then you have even longer if you don't mind no active support.

Edit: Corrected myself a bit as I was confused on the pre-10 version scheme.

@sarusso

This comment has been minimized.

sarusso commented Jul 12, 2018

@srvrguy sorry I deleted my comment just few minutes after posting as on second thoughts it seemed to me kind of useless for the thread, but your (very well structured) answer actually says otherwise.

Here is my original comment for future reference:

"Why Postgres (not the containers!) does not include support for migrating its own data files from the previous major version completely autonomously? Too complicated?

I just upgraded my Postgres from 9.3 to 10 and I was expecting everything to work by just renaming the data directory. I might have been a bit too optimistic hoping to be able to jump from 9.3 to 10, but Postgres 10 being able to accept data files from 9.6 was the expected behaviour. And so on backwards, i.e. 9.3 to 9.4, 9.4 to 9.5, and 9.5 to 9.6.

Dumping the database is not an option, just too much data..."

@sarusso

This comment has been minimized.

sarusso commented Jul 12, 2018

@srvrguy thank you for your reply.

My understanding is that even upgrading from 9.3 to 9.4 is not possible without running pg_dump or pg_upgrade as they are major, not minor. So the last minor where I can update without too much hassle is 9.3.23 (supported until September).

By the way they changed major/minor numbering from version 10 (questionable choice) :)

We have no replication (nor interest in diving into it) since we run a single Postgres on our staging platform (where we would like to upgrade) and just use RDS in production.

I will give it a proper try with pg_upgrade, for my specific use case it would not be a problem at all to have two Postgres version in the same container, but for the official containers the problem remains..

@srvrguy

This comment has been minimized.

srvrguy commented Jul 12, 2018

Sorry, I did mis-read. It looks like you are correct on the 9.x releases. You do have until September before 9.3 is officially "unsupported", at least.

If you're using the Docker images and can't do a dump and restore, you could try the images at https://github.com/tianon/docker-postgres-upgrade which are designed to allow pg_upgrade to work. Of course, I'd try with a copy of the data first to make sure it works and you have the steps worked out.

@jonaseberle

This comment has been minimized.

jonaseberle commented Oct 19, 2018

I had Postgresql 10 upgraded to 11 in a docker-compose environment.

I went the dump-and-import path and that is frustration-free. My DB's service name is db and the old volume was also db

  1. I duplicated the old service definition, changed the name and chose a new volume.
  db11:
    image: postgres:11
    ...
    volumes:
      - db11:/var/lib/postgresql/data
  1. Then I dumped the old DB and imported into the new
docker-compose up --no-deps -d db db11

docker-compose exec db pg_dumpall -U USERNAME > pgdump10
docker-compose exec -T db11 psql -U USERNAME < pgdump10
# if you are fancy, you can pipe them
# docker-compose exec db pg_dumpall -U USERNAME | \
#   docker-compose exec -T db11 psql -U USERNAME
  1. After adapting the docker-compose.yml to use the new DB under the old name
  db:
    image: postgres:11
    ...
    volumes:
      - db11:/var/lib/postgresql/data
  1. I deleted the old volume after checking the new one is error-free
docker volume rm ....._db

-- I feel very safe with this way because on error you can always go back to the old data and PG version.

I am wondering about automation possiblities. I guess we could have a service that watches Postgresql-containers for the "incompatible versions" error via the Docker API and starts a fitting Postgresql-version for the dump and import (or pg_upgrade).

@kajmagnus

This comment has been minimized.

kajmagnus commented Oct 19, 2018

@jonaseberle Great idea, to have two containers in parallel: pg10 and pg11. I think I'll do that me too. Thanks for the dump-import code snippet :- )

@LouisMT

This comment has been minimized.

LouisMT commented Oct 21, 2018

@jonaseberle Thank you very much! Worked great! Just had to add the -T flag to the restore command (because I got "the input device is not a TTY", see docker/compose#5696) like this:

docker-compose exec -T db11 psql -U USERNAME < pgdump10
@jonaseberle

This comment has been minimized.

jonaseberle commented Oct 21, 2018

@LouisMT thank you for pointing that out. I had to add -T, too but forgot to write it. I editted my comment.

@ambiso

This comment has been minimized.

ambiso commented Nov 9, 2018

@ainiml

This comment has been minimized.

ainiml commented Nov 14, 2018

Upgrading Docker image: postgres:10 -> postgres:latest

I just tried following the upgrade documentation (https://www.postgresql.org/docs/10/pgupgrade.html)

However, when stopping the database with pg_ctl it'll also signal for the container to stop (thus breaking the upgrade process before you can get to the pg_upgrade step

The postgres docker image needs an environmental variable to explicitly upgrade the database to the latest version if postgres server is started with an incompatible database version

Or there needs to be a docker image to do the upgrade, like postgress:db10to11 or something

@yosifkit

This comment has been minimized.

Member

yosifkit commented Nov 14, 2018

Or there needs to be a docker image to do the upgrade, like postgress:db10to11 or something

This PoC seems to work: https://github.com/tianon/docker-postgres-upgrade

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment