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.

[markus-kuepper]

We did run into the same scenario.

Our Old DB hat a different username, However the newly Created DB has the "postgres" username (independent of the -U parameter).
Omitting -U the migration fails because it cannot connect to the OLD.
Passing the -U the dump of the OLD is successful, but as the initdb for the NEW does not pass the username, it is created with "postgres" independent of the -U parameter (as this is the current user at that time of creation, I guess). This results in pg_upgrade failing to connect to the NEW with the -U user, because the user is not there.

Workaround

we used is passing the user and password trough to the initdb:

 docker run --rm -it \
    -v $PGDATAOLD:/var/lib/postgresql/$OLD/data \
    -v $PGDATANEW:/var/lib/postgresql/$NEW/data \
    -e POSTGRES_INITDB_ARGS="-U $USR --auth-host=$POSTGRES_HOST_AUTH_METHOD --pwfile=<(printf '%s\n' '$POSTGRES_PASSWORD')" \
	"tianon/postgres-upgrade:$OLD-to-$NEW" \
	-U $USR


docker run --rm \
    -v $PGDATANEW:/data \
    postgres:$NEW \
    sh -c "echo 'host all all all $POSTGRES_HOST_AUTH_METHOD' >> /data/pg_hba.conf"

The last part is related to #1 as the initdb does not setup the NEW to be externally accessible we needed to add this (as it is also done in the Postgres Docker image).

Reproducing

This should be easy to reproduce: create a new DB with the main User not being "postgres" (and not in trust mode). And try to migrate.

[cheggerdev]

Thank you @markus-kuepper. Your solution works for me, too.

In the logs I see "database ... has no actual collation version, but a version was recorded"

postgresql |
postgresql | PostgreSQL Database directory appears to contain a database; Skipping initialization
postgresql |
postgresql | 2025-01-12 22:00:15.037 UTC [1] LOG: starting PostgreSQL 17.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 14.2.0) 14.2.0, 64-bit

postgresql | 2025-01-12 22:04:50.495 UTC [1] LOG: database system is ready to accept connections
postgresql | 2025-01-12 22:05:16.377 UTC [40] WARNING: database "${PG_DB}" has no actual collation version, but a version was recorded
postgresql | 2025-01-12 22:05:20.530 UTC [41] WARNING: database "template1" has no actual collation version, but a version was recorded
postgresql | 2025-01-12 22:05:35.528 UTC [42] WARNING: database "postgres" has no actual collation version, but a version was recorded
postgresql | 2025-01-12 22:05:47.450 UTC [49] WARNING: database "${PG_DB}" has no actual collation version, but a version was recorded

I think, I run into the issue described in https://blog.nuvotex.de/postgres-2/ ?

I indeed use postgresql:<version>-alpine
and https://github.com/tianon/docker-postgres-upgrade/blob/master/Dockerfile.template shows it is debian or ubuntu.

[cheggerdev]

Based on https://github.com/tianon/docker-postgres-upgrade/blob/master/16-to-17/Dockerfile and https://github.com/tianon/docker-postgres-upgrade/blob/master/16-to-17/docker-upgrade
I adapted the Dockerfile and docker-upgrade which addresses this issue and #1 and works with alpine

docker-upgrade contains the solution from @markus-kuepper.

The switch to alpine indeed solved the "database has no actual collation version, but a version was recorded."

I would be really glad if the changes were integrated into the official image.

To run it:

docker run --rm \
	-e POSTGRES_USER=${PG_USER} \
	-e POSTGRES_DB=${PG_DB} \
	-e POSTGRES_PASSWORD=${PG_PASS} \
	-v /volume/database-16/:/var/lib/postgresql/16/data \
	-v /volume/database-17/:/var/lib/postgresql/17/data \
	${IMAGE_NAME}

The Dockerfile to upgrade from postgresql 16 to 17:

ARG VERSION=17
FROM postgres:$VERSION-alpine

RUN set -eux; \
	apk update; \
	apk add musl-locales; \
	apk add postgresql16 
	
ENV POSTGRES_USER=postgres
ENV POSTGRES_DB=postgres
ENV POSTGRES_PASSWORD=""
ENV POSTGRES_HOST_AUTH_METHOD=scram-sha-256
ENV EXTENSIONS=""

ENV PGBINOLD /usr/libexec/postgresql16
ENV PGBINNEW /usr/local/bin

ENV PGDATAOLD /var/lib/postgresql/16/data
ENV PGDATANEW /var/lib/postgresql/17/data

RUN set -eux; \
	mkdir -p "${PGDATAOLD}" "${PGDATANEW}"; \
	chown -R postgres:postgres /var/lib/postgresql

WORKDIR /var/lib/postgresql

COPY docker-upgrade /usr/local/bin/

ENTRYPOINT ["docker-upgrade"]

CMD ["pg_upgrade", "-U", "${POSTGRES_USER}" ]

The docker-upgrade:

#!/bin/bash
set -e

if test "$#" -eq 0 -o "${1:0:1}" = '-'; then
	set -- pg_upgrade "$@" -U ${POSTGRES_USER}
fi

OPT=$1

case "${OPT}" in
pg_upgrade)
	if test "$(id -u)" = '0'; then
		mkdir -p "${PGDATAOLD}" "${PGDATANEW}"
		chmod 700 "${PGDATAOLD}" "${PGDATANEW}"
		chown postgres .
		chown -R postgres "${PGDATAOLD}" "${PGDATANEW}"
		exec gosu postgres "${BASH_SOURCE}" "$@" -U ${POSTGRES_USER}
	fi

	# Address https://github.com/tianon/docker-postgres-upgrade/issues/100
	if test ! -s "${PGDATANEW}/PG_VERSION"; then
		PGDATA="${PGDATANEW}" eval "initdb -U ${POSTGRES_USER} --auth-host=${POSTGRES_HOST_AUTH_METHOD} --pwfile=<(printf '%s\n' '${POSTGRES_PASSWORD}') ${POSTGRES_INITDB_ARGS}"
	fi
	;;
esac

# Address https://github.com/tianon/docker-postgres-upgrade/issues/1
echo "host all all all ${POSTGRES_HOST_AUTH_METHOD}" >> ${PGDATANEW}/pg_hba.conf

echo "Perform upgrade"
eval "$@" 
echo "Upgrade done"

if test -n "${EXTENSIONS}"; then
	echo "Update extensions: ${EXTENSIONS}"
	for EXT in ${EXTENSIONS}
	do
cat << EOF >>/tmp/update_extensions.sql
\\connect template1
ALTER EXTENSION "${EXT}" UPDATE;
\\connect postgres 
ALTER EXTENSION "${EXT}" UPDATE;
\\connect ${POSTGRES_DB}
ALTER EXTENSION "${EXT}" UPDATE;
EOF
	done
	psql -U ${POSTGRES_USER} -d ${POSTGRES_DB} -a -f /tmp/update_extensions.sql
	echo "Update extensions done"
else
	echo "No extensions to update"
fi

[cheggerdev]

I just found this #108 (comment) .
Very sad.
Are you willing to apply the fixes for #100 (username) and #1 (pg_hba) ?

[cheggerdev]

The Dockerfile to upgrade from postgresql 16 to 17 with timescaledb:

The docker-upgrade is the same from #100 (comment).

Important: timescale/timescaledb:latest-pg17 comes only with timescaledb 2.17 therefore upgrade to timescaledb 2.17 before you upgrade postgresql.

FROM timescale/timescaledb:latest-pg16 AS timescaledb16-base

RUN apk update
RUN apk add musl-locales
RUN apk add postgresql16

FROM timescale/timescaledb:latest-pg17

RUN set -eux; \
	apk update; \
	apk add musl-locales; \
	apk add postgresql16 



COPY --from=timescaledb16-base /usr/local/lib/postgresql/timescaledb-2.17.0.so /usr/lib/postgresql16/timescaledb-2.17.0.so
COPY --from=timescaledb16-base /usr/local/lib/postgresql/timescaledb-2.17.1.so /usr/lib/postgresql16/timescaledb-2.17.1.so
COPY --from=timescaledb16-base /usr/local/lib/postgresql/timescaledb-2.17.2.so /usr/lib/postgresql16/timescaledb-2.17.2.so
COPY --from=timescaledb16-base /usr/local/lib/postgresql/timescaledb.so /usr/lib/postgresql16/timescaledb.so
COPY --from=timescaledb16-base /usr/local/lib/postgresql/timescaledb-tsl-2.17.0.so /usr/lib/postgresql16/timescaledb-tsl-2.17.0.so
COPY --from=timescaledb16-base /usr/local/lib/postgresql/timescaledb-tsl-2.17.1.so /usr/lib/postgresql16/timescaledb-tsl-2.17.1.so
COPY --from=timescaledb16-base /usr/local/lib/postgresql/timescaledb-tsl-2.17.2.so /usr/lib/postgresql16/timescaledb-tsl-2.17.2.so



# Override these when running this image
ENV POSTGRES_USER=postgres
ENV POSTGRES_DB=postgres
ENV POSTGRES_PASSWORD=""
ENV POSTGRES_HOST_AUTH_METHOD=scram-sha-256
ENV EXTENSIONS=""

ENV PGBINOLD /usr/libexec/postgresql16
ENV PGBINNEW /usr/local/bin

ENV PGDATAOLD /var/lib/postgresql/16/data
ENV PGDATANEW /var/lib/postgresql/17/data

RUN set -eux; \
	mkdir -p "${PGDATAOLD}" "${PGDATANEW}"; \
	chown -R postgres:postgres /var/lib/postgresql

WORKDIR /var/lib/postgresql

COPY docker-upgrade /usr/local/bin/

ENTRYPOINT ["docker-upgrade"]

# recommended: --link
CMD ["pg_upgrade", "-U", "${POSTGRES_USER}" ]