PostgreSQL major upgrade fails for databases created before the postgres superuser switch (oid 10 = abc)

[lbellows]

Summary

The PostgreSQL major-version upgrade in rootfs/etc/cont-init.d/80-postgres cannot succeed for databases created before the container switched to running initdb as postgres. The block that is meant to handle exactly that case (# Make sure that the rolname for oid 10 is postgres) issues all of its statements as the postgres role, which in those databases is either absent or not a superuser. Every statement fails, pg_upgrade then aborts, and the container is left crash-looping.

Data is not lost — the return 1 guard added in #1356 leaves $PGDATA untouched — but the container never becomes usable again without manual intervention.

Affected installs

Databases initialised before commit cd4111fa ("connect to db using postgres user", April 2024). That version ran:

s6-setuidgid abc /usr/lib/postgresql/12/bin/initdb
s6-setuidgid abc createuser viseron
s6-setuidgid abc createdb -U abc -O viseron viseron

Because initdb ran as the OS user abc, the bootstrap superuser (oid 10) is abc, the database is owned by a separate viseron role, and there is no postgres role at all.

Such a cluster keeps working indefinitely, because for an already-initialised data directory 80-postgres never connects to it — until the bundled PostgreSQL major version changes and upgrade_db runs.

What happens

Database version (15) is not the same as the installed PostgreSQL version (16).
Upgrading database to new PostgreSQL version...
Installing PostgreSQL 15...
Database has not been initialized. Initializing...
Starting PostgreSQL...
psql: error: FATAL:  role "postgres" does not exist
Changing superuser role name to postgres...
Creating temporary superuser...
psql: error: FATAL:  role "postgres" does not exist
Dropping old postgres user...
psql: error: FATAL:  role "temp" does not exist
Renaming old superuser to postgres...
psql: error: FATAL:  role "temp" does not exist
Dropping temporary superuser...
psql: error: FATAL:  role "postgres" does not exist
Create abc user
createuser: error: FATAL:  role "postgres" does not exist
Stopping PostgreSQL...
Running pg_upgrade...
Performing Consistency Checks
Checking cluster versions                                     ok
connection to server on socket "/tmp/postgresql/.s.PGSQL.50432" failed:
  FATAL:  role "postgres" does not exist
Failure, exiting
pg_upgrade failed. Aborting upgrade to preserve data.

After 80-postgres returns, rootfs/etc/services.d/postgres/run starts the new-version server against the old-version data directory, s6 restarts it on failure, and the container logs this roughly once a second, indefinitely:

Starting PostgreSQL Server...
DETAIL:  The data directory was initialized by PostgreSQL version 15,
         which is not compatible with this version 16.15
/var/run/postgresql:5432 - no response

Root cause

rootfs/etc/cont-init.d/80-postgres on dev, lines 60–77. Every statement in the block runs as s6-setuidgid postgres psql, i.e. connecting as the role postgres:

• L64 — PG_SUPERUSER=$(s6-setuidgid postgres psql ... WHERE oid = 10) cannot connect at all when no postgres role exists, so PG_SUPERUSER ends up empty. The != "postgres" test then passes for the wrong reason.
• L68 — CREATE ROLE "temp" WITH SUPERUSER is issued as postgres. Creating a superuser requires being one, so this fails whether the role is missing or merely unprivileged.
• L70/L72 — the DROP ROLE/ALTER ROLE ... RENAME statements connect with -U temp, which was never created.
• L72 — with PG_SUPERUSER empty this expands to ALTER ROLE RENAME TO postgres, a syntax error even if the connection had worked.
• L83 — pg_upgrade runs as OS user postgres and connects as the postgres role, which still does not exist, so the source postmaster connection fails with must be superuser to connect in binary upgrade mode.

There is a second, closely related variant: if a non-superuser postgres login role does exist alongside the abc superuser, L64 succeeds and returns abc, but L68 still fails with ERROR: must be superuser to create superusers, and the rest of the block fails the same way.

How I reproduced it

Against roflcoopter/amd64-viseron:dev (PostgreSQL 16, Ubuntu noble), with a synthetically seeded old data directory rather than a real legacy install:

1. In a throwaway container from the same image, install postgresql-15 from apt.postgresql.org.
2. Recreate the pre-2024 layout in a volume mounted at /config:
   initdb -U abc -D /config/postgresql, then createuser -U abc viseron, then createdb -U abc -O viseron viseron, plus a marker table so data survival can be checked.
3. Boot the image with that volume at /config (not at $PGDATA — upgrade_db renames $PGDATA, which fails if it is a mount point).

For comparison, the same procedure with initdb -U postgres plus createuser abc — the modern layout — upgrades cleanly: PG_VERSION becomes 16, /config/postgresql-15 is preserved, roles and rows survive, and Viseron boots normally. So the failure is specific to the legacy-superuser branch.

Suggested direction

I am not opening a PR for this, since it is a data-migration path and the right policy is yours to set. The shape that worked in my testing:

1. Discover the superuser without assuming a postgres role. Try connecting as postgres first, fall back to abc, and capture which role actually connected:

   PG_CONNECT_ROLE=""
   for role in postgres abc; do
     if PG_SUPERUSER=$(s6-setuidgid postgres psql -U "$role" -q -d viseron -tAc \
          "SELECT rolname FROM pg_roles WHERE oid = 10;" 2>/dev/null); then
       PG_CONNECT_ROLE="$role"
       break
     fi
   done

2. Abort loudly if no superuser connection can be made, rather than continuing into a pg_upgrade that is guaranteed to fail — same log_error + return 1 treatment as the apt and pg_upgrade guards already use.

3. Run the rename block as -U "$PG_CONNECT_ROLE", so CREATE ROLE "temp" WITH SUPERUSER is issued by an actual superuser.

4. Only DROP ROLE "postgres" if that role exists (SELECT 1 FROM pg_roles WHERE rolname = 'postgres'), since in the true legacy layout it does not.

5. Quote the identifier in the ALTER ROLE on L72 and guard against PG_SUPERUSER being empty.

6. Optionally re-check that oid 10 is postgres after the rename and abort before pg_upgrade if it is not, so the failure is reported in Viseron's own log rather than as a pg_upgrade error.

Worth noting for prioritisation: PG_VERSION is derived at runtime from psql --version (rootfs/etc/cont-init.d/40-set-env-vars L65), not pinned. The upgrade path therefore arms itself whenever the base image's PostgreSQL major version moves, with no commit that obviously signals it.