Support custom installed username

[McDuck]

Reproduce

Able to migrate from old postgresql db which has a custom installed username .

$ docker run --rm -v "$PWD/postgresql_data.old":/var/lib/postgresql/13/data -v "$PWD/postgresql_data.new":/var/lib/postgresql/16/data "tianon/postgres-upgrade:$OLD-to-$NEW"

Current

It fails with database user "postgres" is not the install user:

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/16/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 ... Europe/Amsterdam
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/16/data -l logfile start

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.
Performing Consistency Checks
-----------------------------
Checking cluster versions                                     ok
Checking database user is the install user
database user "postgres" is not the install user
Failure, exiting

With --username=somecustomusername it fails with FATAL: role "somecustomusername" does not exist:

$ docker run --rm -v "$PWD/postgresql_data.old":/var/lib/postgresql/13/data -v "$PWD/postgresql_data.new":/var/lib/postgresql/16/data "tianon/postgres-upgrade:$OLD-to-$NEW" --username=somecustomusername
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 incompatible "aclitem" data type in user tables  ok
Checking for user-defined encoding conversions                ok
Checking for user-defined postfix operators                   ok
Checking for incompatible polymorphic functions               ok
Creating dump of global objects                               ok
Creating dump of database schemas                             ok

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


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

Reason

Hardcoded username postgres.
E.g.

docker-postgres-upgrade/13-to-16/Dockerfile

Line 26 in 5ac0d88

chown -R postgres:postgres /var/lib/postgresql

Workaround

Edit the Dockerfile:

useradd somecustomusername
chown -R somecustomusername:somecustomusername /var/lib/postgresql
# Before running pg_upgrade
su - somecustomusername

[tianon]

Does the username actually matter? The filesystem doesn't store the username -- I'm guessing it's actually complaining about the UID (not the username), and that passing --user explicit-UID:explicit-GID on your docker run line would fix it (use something like stat -c '%u:%g' postgresql_data.old to find the correct value).

[typkrft]

It does seem to matter. Permissions are set correctly. Passing the correct --user fails identically.

[tianon]

Do you have a full reproducer? (Just the conversion doesn't exactly help me reproduce.)

[typkrft]

I can comment broadly on the scenario I ran in to.

The containers were databases for an Authentik compose project.

OLD

  some-db:
    container_name: some-db
    image: postgres:12-alpine
    volumes:
      - ./storage/db/data:/var/lib/postgresql/data
    environment:
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_DB: some-db

NEW

  some-db:
    container_name: some-db
    image: postgres:16-alpine
    volumes:
      - ./storage/db/data:/var/lib/postgresql/data
    environment:
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_DB: some-db

The volume folders were created during the initial run of the container.

This resulted in the error above.

The next step I took, was to find the UID:GID of the data using the command you outlined. I then added user: xxx:xxx to the compose file. This resulted in an identical error. I then made the changes suggested by OP and the error resolved. I'm trying to understand why this would work too. But it did seem to work. Unfortunately I've migrated removed OLD. So I can't recreate it.

In the NEW container and on the host both permissions show 70:70 which is the postgres user from the container as expected.

It's very possible I also didn't do something correctly, but then again I am not sure why the fix would have worked for me either.