10 to 11 role "postgres" does not exist #54

pmayer · 2018-11-27T13:22:25Z

pmayer
Nov 27, 2018

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 · 2018-11-27T21:38:22Z

tianon
Nov 27, 2018
Maintainer

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

0 replies

pmayer · 2018-11-29T16:19:09Z

pmayer
Nov 29, 2018
Author

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

0 replies

yosifkit · 2018-11-29T23:54:06Z

yosifkit
Nov 29, 2018
Collaborator

$ # 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.

0 replies

pmayer · 2018-11-30T10:13:53Z

pmayer
Nov 30, 2018
Author

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

0 replies

jimklo · 2019-03-22T20:16:54Z

jimklo
Mar 22, 2019

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

0 replies

Freeesia · 2019-08-20T13:40:57Z

Freeesia
Aug 20, 2019

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"

0 replies

ttimasdf · 2019-10-12T03:45:43Z

ttimasdf
Oct 12, 2019

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.

0 replies

ttimasdf · 2019-10-12T04:59:42Z

ttimasdf
Oct 12, 2019

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.

0 replies

Russellmd · 2020-05-07T20:37:41Z

Russellmd
May 7, 2020

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!

0 replies

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

10 to 11 role "postgres" does not exist #54

{{title}}

Replies: 9 comments

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

Select a reply

10 to 11 role "postgres" does not exist #54

pmayer Nov 27, 2018

Replies: 9 comments

tianon Nov 27, 2018 Maintainer

pmayer Nov 29, 2018 Author

yosifkit Nov 29, 2018 Collaborator

pmayer Nov 30, 2018 Author

jimklo Mar 22, 2019

Freeesia Aug 20, 2019

ttimasdf Oct 12, 2019

ttimasdf Oct 12, 2019

Russellmd May 7, 2020

pmayer
Nov 27, 2018

tianon
Nov 27, 2018
Maintainer

pmayer
Nov 29, 2018
Author

yosifkit
Nov 29, 2018
Collaborator

pmayer
Nov 30, 2018
Author

jimklo
Mar 22, 2019

Freeesia
Aug 20, 2019

ttimasdf
Oct 12, 2019

ttimasdf
Oct 12, 2019

Russellmd
May 7, 2020