Role Postgres does not exist

[pascalwacker]

I've tried to run an update from PG 12 to PG 14, I currently simply have the db data in a docker volume and there's no postgres user on the host machine.

Am I doing anything wrong?

Full command and output attacked below:

$ docker run --rm -v db-data:/var/lib/postgresql/12/data -v db-data-14:/var/lib/postgresql/14/data tianon/postgres-upgrade:12-to-14

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/14/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok


Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/14/data -l logfile start

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok

connection to server on socket "/var/lib/postgresql/.s.PGSQL.50432" failed: FATAL:  role "postgres" does not exist

could not connect to source postmaster started with the command:
"/usr/lib/postgresql/12/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/12/data" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/lib/postgresql'" start
Failure, exiting

[pascalwacker]

When I try to inject the PGUSER env variable and run it from a local folder instead of the volume, it gets a bit further but still fails in the end:

$ docker run --rm -v /home/pascal/Dockers/api/db-test/12:/var/lib/postgresql/12/data -v /home/pascal/Dockers/api/db-test/14:/var/lib/postgresql/14/data -e PGUSER=api-platform tianon/postgres-upgrade:12-to-14

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for system-defined composite types in user tables  ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for user-defined encoding conversions              ok
Checking for user-defined postfix operators                 ok
Creating dump of global objects                             ok
Creating dump of database schemas
  api
  postgres
  template1
                                                            ok

connection to server on socket "/var/lib/postgresql/.s.PGSQL.50432" failed: FATAL:  role "api-platform" does not exist

could not connect to target postmaster started with the command:
"/usr/lib/postgresql/14/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/14/data" -o "-p 50432 -b -c synchronous_commit=off -c fsync=off -c full_page_writes=off -c vacuum_defer_cleanup_age=0  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/lib/postgresql'" start
Failure, exiting

Is there anything I'm doing wrong?

[tianon]

See #10 for a very similar discussion (the very same error message, in fact). 😅

[enekonieto]

A fix was found in 10.

[rchenzheng]

This is still a problem today, I ended up using the solution linked.

Thanks @enekonieto