Migrate a Database with different credentials than postgres

[Basti-Fantasti]

I wanted to (or have to) migrate a database from version 16 to version 17.
It seems as in the original there is no database user "postgres" available.

According to the configuration of the original application, the database user is dbroot instead of postgres and the password is a quite random string.
Is there a way to pass in these credentials into your migration tool, too?

Because when trying to run the migration script on this database, I get the following error:
The original database data folder has been copied to the 16/data folder as needed

Commands:

OLD='16'
NEW='17'
docker run --rm -v "$PWD":/var/lib/postgresql "tianon/postgres-upgrade:$OLD-to-$NEW" --link

Output:

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/17/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 ... 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.
ok


Success. You can now start the database server using:

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

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/16/bin/pg_ctl" -w -l "/var/lib/postgresql/17/data/pg_upgrade_output.d/20250122T110645.796/log/pg_upgrade_server.log" -D "/var/lib/postgresql/16/data" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/lib/postgresql'" start
Failure, exiting

The basic test with migrating from PG16 to PG17 as written in your README file worked flawlessly. So I assume it has to be related to the settings of the source database.

[ieugen]

We've also hit this and not sure how to proceed.
The database was created with another default user - not postgres.
Even creating the postgres user after does not work. It errors with: postgres is not the install user
Reading through the comments https://stackoverflow.com/questions/41854387/database-user-postgres-is-not-the-install-user .

I think this can be fixed by running the pg_upgrade command manually from inside the container and create the cluster with the same insall user by passing the proper option
https://www.postgresql.org/docs/current/pgupgrade.html

I will try this later.

OLD='16'
NEW='17'
docker run --rm -ti -u postgres  -v "$PWD":/var/lib/postgresql "tianon/postgres-upgrade:$OLD-to-$NEW" bash 

postgres@2796bc357d3e:~$ /usr/lib/postgresql/17/bin/pg_upgrade --help
pg_upgrade upgrades a PostgreSQL cluster to a different major version.

Usage:
  pg_upgrade [OPTION]...

Options:
  -b, --old-bindir=BINDIR       old cluster executable directory
  -B, --new-bindir=BINDIR       new cluster executable directory (default
                                same directory as pg_upgrade)
  -c, --check                   check clusters only, don't change any data
  -d, --old-datadir=DATADIR     old cluster data directory
  -D, --new-datadir=DATADIR     new cluster data directory
  -j, --jobs=NUM                number of simultaneous processes or threads to use
  -k, --link                    link instead of copying files to new cluster
  -N, --no-sync                 do not wait for changes to be written safely to disk
  -o, --old-options=OPTIONS     old cluster options to pass to the server
  -O, --new-options=OPTIONS     new cluster options to pass to the server
  -p, --old-port=PORT           old cluster port number (default 50432)
  -P, --new-port=PORT           new cluster port number (default 50432)
  -r, --retain                  retain SQL and log files after success
  -s, --socketdir=DIR           socket directory to use (default current dir.)
  -U, --username=NAME           cluster superuser (default "postgres")

I believe I will try this over the weekend and report back.

[ieugen]

I think this PR is related to the issue: #72

[ieugen]

I've successfully upgraded my databases using a custom upgrade command.

I believe you can also upgrade your DB by changing the bootstrap user:
Someone on postgres slack answered:

• You can create a new super user;
• Authenticate with it and then rename the original bootstrap user;

create user temp_user with superuser, login;

-- authenticate with temp_user and then:
alter role dfs_rw rename to postgres;

I repeat: I did not try this method yet since I did upgrade my db using the manual method:

• stop the cluster and start the migration image in bash
• init the upgrade cluster using the same bootstrap user
• run pg_upgrade manually

# start migration image in bash
docker run --rm -ti -u postgres --name pg-upgrade \
  	 -v /my-db:/var/lib/postgresql \
  	 tianon/postgres-upgrade:16-to-17 bash

# init the 
 /usr/lib/postgresql/17/bin/initdb -D /var/lib/postgresql/17/data -U my_user_name

# Run the migration manually 
/usr/lib/postgresql/17/bin/pg_upgrade \
      --old-bindir /usr/lib/postgresql/16/bin \
      --new-bindir=/usr/lib/postgresql/17/bin \
      --old-datadir=/var/lib/postgresql/16/data \
      --new-datadir=/var/lib/postgresql/17/data \
      --link -U my_user_name