try upgrade with one shot from a postgesql:14 to 17 failed with must be superuser to connect in binary upgrade mode

[rodinux]

I try on preproduction upgrade a container postgresql in a stack with nextcloud...

this is a part of the docker-compose.yml

services:
  nextcloud:
    image: numericoop/nc_full:30.0.16-apache
    restart: 'no'
    volumes:
      - /mnt/${NAME_ENV}/${NAME_CLIENT}${DISK1}/${NAME_APP}/nextcloud_html:/var/www/html
      - /mnt/${NAME_ENV}/${NAME_CLIENT}${DISK1}/${NAME_APP}/www-data:/var/spool/cron/crontabs/www-data
      - /mnt/${NAME_ENV}/${NAME_CLIENT}${DISK2}/${NAME_APP}/nextcloud_data:/var/www/data
      - /mnt/${NAME_ENV}/${NAME_CLIENT}${DISK1}/${NAME_APP}/smtp_${NAME_ENV}.config.php:/var/www/html/config/smtp_${NAME_ENV}.config.php
      - /mnt/${NAME_ENV}/${NAME_CLIENT}${DISK1}/${NAME_APP}/domain_${NAME_ENV}.config.php:/var/www/html/config/domain_${NAME_ENV}.config.php
#     - /media/snp-tmp_${NAME_CLIENT}-${NAME_APP}-${NAME_ENV}/_data/:/var/www/data
    depends_on:
      - redis
      - pgsql
    labels:
      - traefik.enable=true
      - traefik.http.routers.${NAME_CLIENT}-${NAME_APP}-${NAME_ENV}.entrypoints=https
      - traefik.http.routers.${NAME_CLIENT}-${NAME_APP}-${NAME_ENV}.rule=Host(`${NEXTCLOUD_DOMAIN}`)
      - traefik.http.routers.${NAME_CLIENT}-${NAME_APP}-${NAME_ENV}.tls.certresolver=letsencrypt
      - traefik.http.routers.${NAME_CLIENT}-${NAME_APP}-${NAME_ENV}.tls=true
      - traefik.docker.network=proxy
      - traefik.http.routers.${NAME_CLIENT}-${NAME_APP}-${NAME_ENV}.middlewares=${NAME_CLIENT}-${NAME_APP}-${NAME_ENV}_redir,${NAME_CLIENT}-${NAME_APP}-${NAME_ENV}_ssl
      - traefik.http.middlewares.${NAME_CLIENT}-${NAME_APP}-${NAME_ENV}_redir.redirectregex.permanent=true
      - traefik.http.middlewares.${NAME_CLIENT}-${NAME_APP}-${NAME_ENV}_redir.redirectregex.regex=https://(.*)/.well-known/(?:card|cal)dav
      - traefik.http.middlewares.${NAME_CLIENT}-${NAME_APP}-${NAME_ENV}_redir.redirectregex.replacement=https://$$1/remote.php/dav/
      - traefik.http.middlewares.${NAME_CLIENT}-${NAME_APP}-${NAME_ENV}_ssl.headers.customFrameOptionsValue=SAMEORIGIN
      - traefik.http.middlewares.${NAME_CLIENT}-${NAME_APP}-${NAME_ENV}_ssl.headers.framedeny=true
      - traefik.http.middlewares.${NAME_CLIENT}-${NAME_APP}-${NAME_ENV}_ssl.headers.contentTypeNosniff=true
      - traefik.http.middlewares.${NAME_CLIENT}-${NAME_APP}-${NAME_ENV}_ssl.headers.forceSTSHeader=true
      - traefik.http.middlewares.${NAME_CLIENT}-${NAME_APP}-${NAME_ENV}_ssl.headers.stsPreload=true
      - traefik.http.middlewares.${NAME_CLIENT}-${NAME_APP}-${NAME_ENV}_ssl.headers.stsSeconds=315360000
      - traefik.http.middlewares.${NAME_CLIENT}-${NAME_APP}-${NAME_ENV}_ssl.headers.stsIncludeSubdomains=true
      - traefik.http.middlewares.${NAME_CLIENT}-${NAME_APP}-${NAME_ENV}_ssl.headers.sslProxyHeaders.X-Forwarded-Proto=https
      - traefik.http.middlewares.${NAME_CLIENT}-${NAME_APP}-${NAME_ENV}_ssl.headers.sslredirect=true
    environment:
      - REDIS_HOST=redis
      - REDIS_HOST_PASSWORD=${REDIS_HOST_PASSWORD}
      - TRUSTED_PROXIES=${TRUSTED_PROXIES}
      - OVERWRITEPROTOCOL=${OVERWRITEPROTOCOL}
      - OVERWRITECLIURL=${OVERWRITECLIURL}
    networks:
      - proxy
      - db

  redis:
    image: redis:7.4.2
    restart: 'no'
    command: >
      --requirepass ${REDIS_HOST_PASSWORD}
    networks:
      - db

  pgsql:
    image: "postgres:${POSTGRES_VERSION}"
    restart: 'no'
    volumes:
      - /etc/timezone:/etc/timezone:ro
      - /etc/localtime:/etc/localtime:ro
      - /mnt/${NAME_ENV}/${NAME_CLIENT}${DISK1}/${NAME_APP}/postgresql_data:/var/lib/postgresql/data
    environment:
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_DB: ${POSTGRES_DB}
    labels:
      - "traefik.enable=true"
      - "traefik.tcp.routers.${NAME_CLIENT}-${NAME_APP}-${NAME_ENV}_pg.tls=true"
      - "traefik.tcp.routers.${NAME_CLIENT}-${NAME_APP}-${NAME_ENV}_pg.rule=HostSNI(`pgsql.localhost`)"
      - "traefik.tcp.routers.${NAME_CLIENT}-${NAME_APP}-${NAME_ENV}_pg.service=${NAME_CLIENT}-${NAME_APP}-${NAME_ENV}_pg"
      - "traefik.tcp.services.${NAME_CLIENT}-${NAME_APP}-${NAME_ENV}_pg.loadbalancer.server.port=5432"
    networks:
      - db
    security_opt:
      - no-new-privileges:true

  pgbackups:
    image: prodrigestivill/postgres-backup-local:${POSTGRES_VERSION}
    restart: 'no'
    user: postgres:postgres
    volumes:
      - /etc/timezone:/etc/timezone:ro
      - /etc/localtime:/etc/localtime:ro
      - /mnt/${NAME_ENV}/${NAME_CLIENT}${DISK2}/${NAME_APP}/pgbackups_dump:/backups
    links:
      - pgsql
    depends_on:
      - pgsql
    environment:
      - POSTGRES_HOST=${PGBACKUP_HOST}
      - POSTGRES_DB=${PGBACKUP_DB}
      - POSTGRES_USER=${PGBACKUP_USER}
      - POSTGRES_PASSWORD=${PGBACKUP_PASSWORD}
      - POSTGRES_EXTRA_OPTS=-Z6 --schema=public --blobs
      - TZ="Europe/Paris"
      - SCHEDULE=20 19 * * *
      - BACKUP_KEEP_DAYS=7
      - BACKUP_KEEP_WEEKS=4
      - BACKUP_KEEP_MONTHS=6
      - HEALTHCHECK_PORT=8080
    networks:
      - db
    security_opt:
      - no-new-privileges:true

So I have stop the stack and try this command

docker run --name pgauto -it -v /mnt/pp/ntest/nxc/postgresql_data:/var/lib/postgresql/data -e POSTGRES_USER=userdbnextcloud -e POSTGRES_PASSWORD=xxxxxxxxxxxxxxxxxxxxxxxxx -e POSTGRES_DB=dbname -e PGAUTO_ONESHOT=yes pgautoupgrade/pgautoupgrade:16-trixie

the output gibe me something like

PostgreSQL Database directory appears to contain a database; Skipping initialization

************************************
PostgreSQL data directory: /var/lib/postgresql/data
************************************
*******************************************************************************************
Performing PG upgrade on version 14 database files.  Upgrading to version 16.10
*******************************************************************************************
----------------------------------------------------------------------
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/data/upgrade_in_progress.lock
---------------------------------------
Creating OLD temporary directory /var/lib/postgresql/data/old
---------------------------------------
--------------------------------------------
Creating OLD temporary directory is complete
--------------------------------------------
-------------------------------------------------------
Moving existing data files into OLD temporary directory
-------------------------------------------------------
renamed '/var/lib/postgresql/data/base' -> '/var/lib/postgresql/data/old/base'
renamed '/var/lib/postgresql/data/global' -> '/var/lib/postgresql/data/old/global'
mv: cannot move '/var/lib/postgresql/data/old' to a subdirectory of itself, '/var/lib/postgresql/data/old/old'
renamed '/var/lib/postgresql/data/pg_commit_ts' -> '/var/lib/postgresql/data/old/pg_commit_ts'
renamed '/var/lib/postgresql/data/pg_dynshmem' -> '/var/lib/postgresql/data/old/pg_dynshmem'
renamed '/var/lib/postgresql/data/pg_hba.conf' -> '/var/lib/postgresql/data/old/pg_hba.conf'
renamed '/var/lib/postgresql/data/pg_ident.conf' -> '/var/lib/postgresql/data/old/pg_ident.conf'
renamed '/var/lib/postgresql/data/pg_logical' -> '/var/lib/postgresql/data/old/pg_logical'
renamed '/var/lib/postgresql/data/pg_multixact' -> '/var/lib/postgresql/data/old/pg_multixact'
renamed '/var/lib/postgresql/data/pg_notify' -> '/var/lib/postgresql/data/old/pg_notify'
renamed '/var/lib/postgresql/data/pg_replslot' -> '/var/lib/postgresql/data/old/pg_replslot'
renamed '/var/lib/postgresql/data/pg_serial' -> '/var/lib/postgresql/data/old/pg_serial'
renamed '/var/lib/postgresql/data/pg_snapshots' -> '/var/lib/postgresql/data/old/pg_snapshots'
renamed '/var/lib/postgresql/data/pg_stat' -> '/var/lib/postgresql/data/old/pg_stat'
renamed '/var/lib/postgresql/data/pg_stat_tmp' -> '/var/lib/postgresql/data/old/pg_stat_tmp'
renamed '/var/lib/postgresql/data/pg_subtrans' -> '/var/lib/postgresql/data/old/pg_subtrans'
renamed '/var/lib/postgresql/data/pg_tblspc' -> '/var/lib/postgresql/data/old/pg_tblspc'
renamed '/var/lib/postgresql/data/pg_twophase' -> '/var/lib/postgresql/data/old/pg_twophase'
renamed '/var/lib/postgresql/data/PG_VERSION' -> '/var/lib/postgresql/data/old/PG_VERSION'
renamed '/var/lib/postgresql/data/pg_wal' -> '/var/lib/postgresql/data/old/pg_wal'
renamed '/var/lib/postgresql/data/pg_xact' -> '/var/lib/postgresql/data/old/pg_xact'
renamed '/var/lib/postgresql/data/postgresql.auto.conf' -> '/var/lib/postgresql/data/old/postgresql.auto.conf'
renamed '/var/lib/postgresql/data/postgresql.conf' -> '/var/lib/postgresql/data/old/postgresql.conf'
renamed '/var/lib/postgresql/data/postmaster.opts' -> '/var/lib/postgresql/data/old/postmaster.opts'
renamed '/var/lib/postgresql/data/upgrade_in_progress.lock' -> '/var/lib/postgresql/data/old/upgrade_in_progress.lock'
-------------------------------------------------------------------
Moving existing data files into OLD temporary directory is complete
-------------------------------------------------------------------
---------------------------------------
Creating NEW temporary directory /var/lib/postgresql/data/new
---------------------------------------
--------------------------------------------
Creating NEW temporary directory is complete
--------------------------------------------
-----------------------------------------------------
Changing permissions of temporary directories to 0700
-----------------------------------------------------
---------------------------------------------------------
Changing permissions of temporary directories is complete
---------------------------------------------------------
-------------------------------------------------
Remove postmaster.pid file from PG data directory
-------------------------------------------------
------------------------------------
Determining our own initdb arguments
------------------------------------
---------------------------------------------------------------
The initdb arguments we determined are: --encoding=UTF8  
---------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
Old database using collation settings: '--encoding=UTF8  '.  Initialising new database with those settings too
--------------------------------------------------------------------------------------------------------------------
Initialising PostgreSQL 16.10 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/data/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/16/bin/pg_ctl -D /var/lib/postgresql/data/new -l logfile start

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

connection to server on socket "/var/run/postgresql/.s.PGSQL.50432" failed: FATAL:  must be superuser to connect in binary upgrade mode


could not connect to source postmaster started with the command:
"/usr/local-pg14/bin/pg_ctl" -w -l "/var/lib/postgresql/data/new/pg_upgrade_output.d/20251112T122346.056/log/pg_upgrade_server.log" -D "/var/lib/postgresql/data/old" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/run/postgresql'" start
Failure, exiting
-------------------------------------------------------------------------------
Restoring original data permissions to /var/lib/postgresql/data
-------------------------------------------------------------------------------

what's going wrong ? perhaps because the container pgsql is stopped but must be running ?

[rodinux]

I try the command with a user of the nextcloud DB, not the admin Postgresl...

[andyundso]

you need to run pgautoupgrade as the Postgres superuser, otherwise it does not have all required permissions to run pg_upgrade. From your compose file that should be whatever is behind ${POSTGRES_USER} and ${POSTGRES_PASSWORD}. You can also check with \du in the psql console which user has superuser permissions.