10 to 11 role "postgres" does not exist

[pmayer]

When trying to upgrade from 10 to 11 - the following error pops up:

...

Success. You can now start the database server using:

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


WARNING: enabling "trust" authentication for local connections
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

connection to database failed: FATAL:  role "postgres" does not exist

could not connect to source postmaster started with the command:
"/usr/lib/postgresql/10/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/10/data" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/lib/postgresql'" start
Failure, exiting

I tried setting the POSTGRES_* environment variables, but still getting the same error. Any idea why this is happening?

[tianon]

I can't seem to reproduce: 😕

$ docker pull postgres:10
10: Pulling from library/postgres
Digest: sha256:6222435cc15460634d6a59499a66db3181162cfa27448dbbdc65c07ccbeb8b88
Status: Image is up to date for postgres:10
$ docker pull postgres:11
11: Pulling from library/postgres
Digest: sha256:40b48cf04185e69c5a9183a986fcbbfd105e5991a70b1980e9a641b53ba5ead7
Status: Image is up to date for postgres:11
$ docker pull tianon/postgres-upgrade:10-to-11
10-to-11: Pulling from tianon/postgres-upgrade
Digest: sha256:ce4cacad5419bceaf975c09ab9f397d3f0ce9e29e13b2097f799f3af14cdf7b3
Status: Image is up to date for tianon/postgres-upgrade:10-to-11

$ mkdir -p 10/data 11/data

$ docker run -dit -v "$PWD/10/data":/var/lib/postgresql/data --name psql postgres:10
37707c965c063233b0b810b8a6035013f9a8273f4c9612bbc1505cb26317e38f

$ docker logs --tail=1 psql
2018-11-27 21:34:50.489 UTC [1] LOG:  database system is ready to accept connections

$ docker stop psql
psql

$ docker rm psql
psql

$ docker run --rm -v "$PWD":/var/lib/postgresql tianon/postgres-upgrade:10-to-11 --link
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/11/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
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/11/data -l logfile start


WARNING: enabling "trust" authentication for local connections
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                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Creating dump of global objects                             ok
Creating dump of database schemas
  postgres
  template1
                                                            ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows in the new cluster                        ok
Deleting files from new pg_xact                             ok
Copying old pg_xact to new server                           ok
Setting next transaction ID and epoch for new cluster       ok
Deleting files from new pg_multixact/offsets                ok
Copying old pg_multixact/offsets to new server              ok
Deleting files from new pg_multixact/members                ok
Copying old pg_multixact/members to new server              ok
Setting next multixact ID and offset for new cluster        ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Restoring database schemas in the new cluster
  template1
  postgres
                                                            ok
Adding ".old" suffix to old global/pg_control               ok

If you want to start the old cluster, you will need to remove
the ".old" suffix from /var/lib/postgresql/10/data/global/pg_control.old.
Because "link" mode was used, the old cluster cannot be safely
started once the new cluster has been started.

Linking user relation files
  /var/lib/postgresql/10/data/base/12994/2613
  /var/lib/postgresql/10/data/base/12994/2683
  /var/lib/postgresql/10/data/base/12994/2995
  /var/lib/postgresql/10/data/base/12994/2996
  /var/lib/postgresql/10/data/base/1/2613
  /var/lib/postgresql/10/data/base/1/2683
  /var/lib/postgresql/10/data/base/1/2995
  /var/lib/postgresql/10/data/base/1/2996
                                                            ok
Setting next OID for new cluster                            ok
Sync data directory to disk                                 ok
Creating script to analyze new cluster                      ok
Creating script to delete old cluster                       ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    ./analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh

$ docker run -it --rm -v "$PWD/11/data":/var/lib/postgresql/data --name psql postgres:11
2018-11-27 21:37:39.639 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2018-11-27 21:37:39.639 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2018-11-27 21:37:39.641 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2018-11-27 21:37:39.650 UTC [25] LOG:  database system was shut down at 2018-11-27 21:37:12 UTC
2018-11-27 21:37:39.653 UTC [1] LOG:  database system is ready to accept connections

[pmayer]

steps to reproduce:

$ mkdir -p 10/data 11/data

$ docker run -dit -v "$PWD/10/data":/var/lib/postgresql/data -e POSTGRES_USER=test -e POSTGRES_PASSWORD=test -e POSTGRES_DB=test --name psql postgres:10
d87127b183c812cd6bc8599dfb7851e8f55e45c4636936280b4976b5249243bd

$ docker logs --tail=1 psql
2018-11-29 16:07:25.596 UTC [1] LOG:  database system is ready to accept connections

$ docker stop psql
psql

$ docker rm psql
psql

$ docker run --rm -v "$PWD":/var/lib/postgresql -e POSTGRES_USER=test -e POSTGRES_PASSWORD=test -e POSTGRES_DB=test tianon/postgres-upgrade:10-to-11 --link
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/11/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
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/11/data -l logfile start


WARNING: enabling "trust" authentication for local connections
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

connection to database failed: FATAL:  role "postgres" does not exist

could not connect to source postmaster started with the command:
"/usr/lib/postgresql/10/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/10/data" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/lib/postgresql'" start
Failure, exiting

Same error for running docker run --rm -v "$PWD":/var/lib/postgresql tianon/postgres-upgrade:10-to-11 --link

[yosifkit]

$ # try this
$ docker run --rm -v "$PWD":/var/lib/postgresql tianon/postgres-upgrade:10-to-11 --link --username test
$ # or this
$ docker run --rm -v "$PWD":/var/lib/postgresql -e PGUSER=test tianon/postgres-upgrade:10-to-11 --link

It looks like these images were relying on the postgres role always existing even if the database had a different one which was fixed in the upstream images in docker-library/postgres#493.

[pmayer]

Thanks for the suggestions, but unfortunately both don't work either. But at least the execution got a bit further:

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/11/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
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/11/data -l logfile start

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 reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Creating dump of global objects                             ok
Creating dump of database schemas
  postgres
  template1
  test
                                                            ok

connection to database failed: FATAL:  role "test" does not exist

could not connect to target postmaster started with the command:
"/usr/lib/postgresql/11/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/11/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

[jimklo]

I'm having the same issue using 9.2 to 11.

Here's my bash script I'm running:

#!/bin/bash

OLD='9.2'
NEW='11'

ORIG_DATA='postgres_data'
OLD_DATA='postgres_9_data'
NEW_DATA='postgres_11_data'


VOLUMES=( "$OLD_DATA" "$NEW_DATA" )
for v in ${VOLUMES[@]}; do
  echo "Remove existing volume: ${v}"
  sudo docker volume rm -f $v
  echo "Create new backup volume: ${v}"
  sudo docker volume create $v
done

sudo docker run --rm \
	-v "$ORIG_DATA":/from \
	-v "$OLD_DATA":/to \
  debian:latest bash -c "cd /from && cp -av . /to"

#sudo docker pull "tianon/postgres-upgrade:$OLD-to-$NEW"
sudo docker run --rm \
	-v "$OLD_DATA":/var/lib/postgresql/9.2/data \
	-v "$NEW_DATA":/var/lib/postgresql/11/data \
	"tianon/postgres-upgrade:$OLD-to-$NEW" \
	--link

And the relevant part of the log:

'./base/16384/11844_fsm' -> '/to/./base/16384/11844_fsm'
'./base/16384/20911_fsm' -> '/to/./base/16384/20911_fsm'
'./base/16384/21124_fsm' -> '/to/./base/16384/21124_fsm'
'./base/16384/21124_vm' -> '/to/./base/16384/21124_vm'
'./base/pgsql_tmp' -> '/to/./base/pgsql_tmp'
'./pg_tblspc' -> '/to/./pg_tblspc'
'./pg_stat_tmp' -> '/to/./pg_stat_tmp'
'./PG_VERSION' -> '/to/./PG_VERSION'
'./postgresql.conf' -> '/to/./postgresql.conf'
'./postmaster.opts' -> '/to/./postmaster.opts'
9.2-to-11: Pulling from tianon/postgres-upgrade
Digest: sha256:cc84eb65bd6e5836b050dc91e60b70d19b7cea38669e98e76e0b9d478a060eb1
Status: Image is up to date for tianon/postgres-upgrade:9.2-to-11
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 reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for invalid "unknown" user columns                 ok
Checking for roles starting with "pg_"                      ok
Checking for incompatible "line" data type                  ok
Creating dump of global objects                             ok
Creating dump of database schemas
  postgres
  template1
  xnat
                                                            ok

connection to database failed: FATAL:  role "postgres" does not exist

could not connect to target postmaster started with the command:
"/usr/lib/postgresql/11/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/11/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

[Freeesia]

I found the following workaround.

# $POSTGRES_USER is the old data role
sudo docker run --rm \
	-e PGUSER=$POSTGRES_USER
	-e POSTGRES_INITDB_ARGS="-U $POSTGRES_USER"
	-v "$OLD_DATA":/var/lib/postgresql/$OLD/data \
	-v "$NEW_DATA":/var/lib/postgresql/$NEW/data \
	"tianon/postgres-upgrade:$OLD-to-$NEW" 

[ttimasdf]

steps to reproduce:

$ mkdir -p 10/data 11/data

$ docker run -dit -v "$PWD/10/data":/var/lib/postgresql/data -e POSTGRES_USER=test -e POSTGRES_PASSWORD=test -e POSTGRES_DB=test --name psql postgres:10
d87127b183c812cd6bc8599dfb7851e8f55e45c4636936280b4976b5249243bd

$ docker logs --tail=1 psql
2018-11-29 16:07:25.596 UTC [1] LOG:  database system is ready to accept connections

$ docker stop psql
psql

$ docker rm psql
psql

$ docker run --rm -v "$PWD":/var/lib/postgresql -e POSTGRES_USER=test -e POSTGRES_PASSWORD=test -e POSTGRES_DB=test tianon/postgres-upgrade:10-to-11 --link
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/11/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
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/11/data -l logfile start


WARNING: enabling "trust" authentication for local connections
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

connection to database failed: FATAL:  role "postgres" does not exist

could not connect to source postmaster started with the command:
"/usr/lib/postgresql/10/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/10/data" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/lib/postgresql'" start
Failure, exiting

Same error for running docker run --rm -v "$PWD":/var/lib/postgresql tianon/postgres-upgrade:10-to-11 --link

Reproducible upgrading from 9.5 to 12.

[ttimasdf]

I found the following workaround.

# $POSTGRES_USER is the old data role
sudo docker run --rm \
	-e PGUSER=$POSTGRES_USER
	-e POSTGRES_INITDB_ARGS="-U $POSTGRES_USER"
	-v "$OLD_DATA":/var/lib/postgresql/$OLD/data \
	-v "$NEW_DATA":/var/lib/postgresql/$NEW/data \
	"tianon/postgres-upgrade:$OLD-to-$NEW" 

Your workaround works. FYI there's something worth being mentioned. Before running docker run , make sure that there's nothing inside $NEW_DATA. TL,DR: rm -r "$NEW_DATA" before anything.

I once came into a situation that the workaround above not work because I previously run another docker run without environment POSTGRES_INITDB_ARGS, which cause an error and a newly created database with wrong user role in directory $NEW_DATA. Deleting that directory and everything works again.

[Russellmd]

I found the following workaround.

# $POSTGRES_USER is the old data role
sudo docker run --rm \
	-e PGUSER=$POSTGRES_USER
	-e POSTGRES_INITDB_ARGS="-U $POSTGRES_USER"
	-v "$OLD_DATA":/var/lib/postgresql/$OLD/data \
	-v "$NEW_DATA":/var/lib/postgresql/$NEW/data \
	"tianon/postgres-upgrade:$OLD-to-$NEW" 

Thanks, It works!

I created docker container with specific DB user, so I used it as $POSTGRES_USER from your example. Also I copied old "pg_hba.conf" to new data folder. Now everything works!