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:
How to restore everything (including "postgres" role) from pg_dumpall backup
... 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.