15 -> 18 upgrade : `role "postgres" does not exist` on the DB upgrade

[wakatara]

First off, thanks for all the work on this. As pointed out in your docs, doing this manually would be painful, so hoping this is going to make a move from 15 to 18 (relatively) painless.

I am getting the following error: connection to server on socket "/var/run/postgresql/.s.PGSQL.50432" failed: FATAL: role "postgres" does not exist on the DB upgrade running the command below (which I believe is correct).

I'm testing pgautoupgrade on my local machine (OSX but running docker for all dev work) before attempting a much larger database upgrade on our target server. The app is a Go application running postgres inside its docker compose config. Currently, the postgres is 15.14 which has been working great. I have set the new directories properly for old postgres 15 to the new directory structure in 18 and am using the following command, attempting an "in place, one-shot" upgrade:

docker run --name pgauto -it \
                     --mount type=bind,source=/var/lib/postgresql/data,target=/var/lib/postgresql \
                     -e POSTGRES_PASSWORD=KindaSekritThingie \
                     -e PGAUTO_ONESHOT=yes \
                     pgautoupgrade/pgautoupgrade:18-trixie

Things seem to be going well right up until the actual upgrade portion... the postgres 18 gets set and then starts up but then I am getting a postgres user error near the end. Is it not possible to upgrade in place due to the db initialization? Log follows.

~/Code/UHawaii ❯ docker run --name pgauto -it \
                     --mount type=bind,source=/var/lib/postgresql/data,target=/var/lib/postgresql \
                     -e POSTGRES_PASSWORD=KindaSekritThingie \
                     -e PGAUTO_ONESHOT=yes \
                     pgautoupgrade/pgautoupgrade:18-trixie
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 enabled.

fixing permissions on existing directory /var/lib/postgresql/18/docker ... 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

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: 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.

Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/18/docker -l logfile start

waiting for server to start....2025-10-19 03:22:48.359 UTC [50] LOG:  starting PostgreSQL 18.0 (Debian 18.0-1.pgdg13+3) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
2025-10-19 03:22:48.359 UTC [50] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2025-10-19 03:22:48.361 UTC [56] LOG:  database system was shut down at 2025-10-19 03:22:48 UTC
2025-10-19 03:22:48.362 UTC [50] LOG:  database system is ready to accept connections
 done
server started

/usr/local/bin/postgres-docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*

waiting for server to shut down....2025-10-19 03:22:48.471 UTC [50] LOG:  received fast shutdown request
2025-10-19 03:22:48.472 UTC [50] LOG:  aborting any active transactions
2025-10-19 03:22:48.473 UTC [50] LOG:  background worker "logical replication launcher" (PID 59) exited with exit code 1
2025-10-19 03:22:48.473 UTC [54] LOG:  shutting down
2025-10-19 03:22:48.474 UTC [54] LOG:  checkpoint starting: shutdown immediate
2025-10-19 03:22:48.479 UTC [54] LOG:  checkpoint complete: wrote 0 buffers (0.0%), wrote 3 SLRU buffers; 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.001 s, total=0.006 s; sync files=2, longest=0.001 s, average=0.001 s; distance=0 kB, estimate=0 kB; lsn=0/175F8E8, redo lsn=0/175F8E8
2025-10-19 03:22:48.481 UTC [50] LOG:  database system is shut down
 done
server stopped

PostgreSQL init process complete; ready for start up.

************************************
PostgreSQL data directory: /var/lib/postgresql/18/docker
************************************
*******************************************************************************************
Performing PG upgrade on version 15 database files.  Upgrading to version 18.0
*******************************************************************************************
----------------------------------------------------------------------
Checking for left over artifacts from a failed previous autoupgrade...
----------------------------------------------------------------------
-------------------------------------------------------------------------------
No artifacts found from a failed previous autoupgrade.  Continuing the process.
-------------------------------------------------------------------------------
Creating upgrade lock file at /var/lib/postgresql/18/docker/upgrade_in_progress.lock
---------------------------------------
Creating OLD temporary directory /var/lib/postgresql/18/docker/old
---------------------------------------
--------------------------------------------
Creating OLD temporary directory is complete
--------------------------------------------
-------------------------------------------------------
Moving existing data files into OLD temporary directory
-------------------------------------------------------
-------------------------------------------------------------------
Moving existing data files into OLD temporary directory is complete
-------------------------------------------------------------------
---------------------------------------
Creating NEW temporary directory /var/lib/postgresql/18/docker/new
---------------------------------------
--------------------------------------------
Creating NEW temporary directory is complete
--------------------------------------------
-----------------------------------------------------
Changing permissions of temporary directories to 0700
-----------------------------------------------------
---------------------------------------------------------
Changing permissions of temporary directories is complete
---------------------------------------------------------
2025-10-19 03:22:48.592 UTC [99] LOG:  database system was interrupted; last known up at 2025-10-19 02:53:57 UTC
2025-10-19 03:22:48.636 UTC [99] LOG:  database system was not properly shut down; automatic recovery in progress
2025-10-19 03:22:48.637 UTC [99] LOG:  redo starts at 1/E0CCE2A8
2025-10-19 03:22:48.637 UTC [99] LOG:  invalid record length at 1/E0CCE390: wanted 24, got 0
2025-10-19 03:22:48.637 UTC [99] LOG:  redo done at 1/E0CCE358 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2025-10-19 03:22:48.638 UTC [99] LOG:  checkpoint starting: end-of-recovery immediate wait
2025-10-19 03:22:48.642 UTC [99] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.003 s, sync=0.001 s, total=0.004 s; sync files=2, longest=0.001 s, average=0.001 s; distance=0 kB, estimate=0 kB
2025-10-19 03:22:48.645 UTC [99] LOG:  checkpoint starting: shutdown immediate
2025-10-19 03:22:48.646 UTC [99] LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.000 s, total=0.002 s; sync files=0, longest=0.000 s, average=0.000 s; distance=0 kB, estimate=0 kB
-------------------------------------------------
Remove postmaster.pid file from PG data directory
-------------------------------------------------
------------------------------------
Determining our own initdb arguments
------------------------------------
2025-10-19 03:22:48.654 UTC [105] LOG:  checkpoint starting: shutdown immediate
2025-10-19 03:22:48.656 UTC [105] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.001 s, total=0.003 s; sync files=2, longest=0.001 s, average=0.001 s; distance=0 kB, estimate=0 kB
---------------------------------------------------------------
The initdb arguments we determined are: --encoding=UTF8 --no-data-checksums
---------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
Old database using collation settings: '--encoding=UTF8 --no-data-checksums '.  Initialising new database with those settings too
--------------------------------------------------------------------------------------------------------------------
Initialising PostgreSQL 18.0 data directory
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 text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/18/docker/new ... 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

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: 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.

Success. You can now start the database server using:

    /usr/lib/postgresql/18/bin/pg_ctl -D /var/lib/postgresql/18/docker/new -l logfile start

------------------------------------
New database initialisation complete
------------------------------------
---------------------------------------
Running pg_upgrade command, from /var/lib/postgresql/18/docker
---------------------------------------
Performing Consistency Checks
-----------------------------
Checking cluster versions                                     ok

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


could not connect to source postmaster started with the command:
"/usr/local-pg15/bin/pg_ctl" -w -l "/var/lib/postgresql/18/docker/new/pg_upgrade_output.d/20251019T032248.925/log/pg_upgrade_server.log" -D "/var/lib/postgresql/18/docker/old" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/run/postgresql'" start
Failure, exiting

I'm gathering I've either misunderstood something about the way pgautoupgrade works (his was my sceond shot at this) or there is a quirk around the postgres 18 upgrade.

Could use a pointer on this one after nuking my local dev database 3 times.

Thanks and apologies if the reason for the fail is obvious to everyone but me.
Again though... this autoupgrade docker is awesome. I am excited to get it working and to upgrade our server.

[justinclift]

I'm guessing you're hitting the same problem that was resolved in #18. Take a look there, and see if that fixes it for you. 😄

[wakatara]

@justinclift
Ah, I see... ok, so just pass in another -e with POSTGRES_USER then, such as?

docker run --name pgauto -it \
                     --mount type=bind,source=/var/lib/postgresql/data,target=/var/lib/postgresql \
                     -e POSTGRES_USER=TheUserThatIsNotPostgres \
                     -e POSTGRES_PASSWORD=KindaSekritThingie \
                     -e PGAUTO_ONESHOT=yes \
                     pgautoupgrade/pgautoupgrade:18-trixie

The actual prod database I'll be using this on is over 1 TB, so trying to get it right locally before I go nuts on Prod. =]

(also, apologies, I did search on the "role postgres does not exist" but think I must have accidentallyl limited it to open issues since nothing came up. Thanks for the super speedy reply btw... =] ).

[justinclift]

trying to get it right locally before I go nuts on Prod.

Good idea. 😄

Btw, if this does fix the problem for you (ie after you've confirmed in prod), please update/close this issue, etc. 😄

[drzraf]

Same issue (during a weblate docker upgrade: postgres 15 -> 18). -e POSTGRES_USER=.... fixed it.

(Worked around previous failed attempt with a mv /var/lib/postgresql/18/docker/old/* /var/lib/postgresql/ before rerunning pgautoupgrade)

[wakatara]

Yeah, I have to admit I took a couple of kicks at this locally in-place and could not get it to work so we upgraded manually - which was hairy. So, I think there still may be a problem. I imagine it is because the pg18 directory is one higher than the pg15 directory, so clobbers /data when it's made.

(on the plus side, now we've upgraded, I'm quite surprised at the obvious query performance improvement.).

[wakatara]

So, role was not the only issue. Using the user we had for the db (coma) did not solve this problem.