I want to have an easy, repeatable process of upgrading a dockerized postgres database to a new version. Minor and patch versions are easy -- the container can simply be upgraded to the new version, and postgres will read the existing data directory without issues. Major versions are harder because the data directory format is not compatible between versions. The versions I am working with at present are 11 (old) and 14 (new).
I don't think I can use
pg_upgrade because from within an off-the-shelf container, I won't have access to the binary dirs for both versions, so I'm going with the backup-restore approach.
On my new version's container, I want to take advantage of the
/docker-entrypoint-initdb.d/ feature of many dockerhub images, where one can mount a set of script files into that directory from the host, and they will be executed the first time the container is run. So, if a
db_dump.sql file, for example, is mounted into that directory, the container will run it with
psql -f the first time it is run, and the contents of that script can be used to populate the data directory of the new, upgraded container.
The benefit of this would be that I don't have to do any manual fuddling with the upgraded container -- it will simply restore the dump when it launches.
My issue has been in generating a dump that will restore in the new container without failing errors. If I use
pg_dumpall from my old version, the script file includes
CREATE ROLE postgres; statements which fail because the
postgres role already exists in a fresh container. If I use
pg_dumpall --clean, the script includes
DROP DATABASE statements for the user databases which fail because those databases (obviously) don't exist in the fresh container.
I did find this answer:
... which suggests to me that
sed-ing the output of
pg_dumpall to remove alterations to the
postgres role might be the least worst option, but this feels like I must be missing a much better option.