How can I create a sql dump from postgres that I can restore on a new server with no psql options?

Question

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.

Answer 1

I came up with two options. The first is very similar to the question I linked:

pg_dumpall -U postgres | sed '/^CREATE ROLE postgres/d' > dump.sql

This will cause the postgres role to be altered to match that from the dump, but just strips out the one line which causes a conflict on a fresh postgres container.

The second option is to launch the consuming container with an environment variable specifying a different default username:

docker run --rm -it \
-e POSTGRES_USER=someotheruser \
-e POSTGRES_PASSWORD=12345 \
-v $(pwd):/docker-entrypoint-initdb.d \
postgres

I guess what I have to conclude from this is that pg_dumpall just expects that you'll always import its output on a server with a non-conflicting default role.