Pg_dumpall error 'FATAL: role "postgres" does not exist'

I need some help here. We have an application (odoo) with a postgres database. This all runs in containers on an AWS EC2 instance. Last week we upgraded odoo, and with that we also upgraded postgres. We used to run postgres:13, and now we have postgres:15. Since the upgrade, our backup script does not work anymore.

The backup gets started from the instance like this:

docker exec --user postgres risktool_db /var/lib/scripts/backup-db.sh

Then, inside the container, it basically starts this command:

pg_dumpall --verbose 2>${LOGFILE} | gzip -c | openssl smime -encrypt \
    -aes256 -binary -outform DEM \
    -out ${BACKUPFILE} "${BACKUPKEY}"

This always used to work. However, since upgrading the container for postgres we get the following error:

pg_dumpall: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "postgres" does not exist

I already rebooted the instance, but that didn't work. I also checked for double postgres processed, but that seems to be all fine. Some googling then told me that I could check the role, by using \du and indeed the role does not exist. I also found the command to recreate the role, but I'm unsure about a lot of the options:

CREATE ROLE postgres LOGIN PASSWORD 'your_password';
GRANT CONNECT ON DATABASE dbname TO postgres;
GRANT USAGE ON SCHEMA public TO postgres;
ALTER DATABASE dbname OWNER TO postgres;

We have about 150 databases, should I repeat the 2nd and 4th command for all these databases? Are these commands even correct?

Another manual mentioned that maybe doing this should be enough:

CREATE ROLE postgres WITH SUPERUSER LOGIN PASSWORD 'your_password';

As clear by now, I'm not really a postgres admin, and have received this environment from an old friend who is no longer with us, so I can't really reach out. Any advice would be appreciated.

Solution

You cannot drop the bootstrap superuser in PostgreSQL. So either the user got renamed, or the database cluster somehow got removed, and a new cluster got created with a different bootstrap superuser.

Connect to PostgreSQL as a superuser and run

SELECT rolname FROM pg_roles WHERE oid = 10;

That will give you the current name of the bootstrap superuser. Either fix your backup to use that user or rename it:

ALTER ROLE whatever RENAME TO postgres;