Upgrading postgresql from 9 to 11 - role issues

[cm-vdp]

Hello,

I am upgrading postgresql 9 to 11 using the command pg_upgrade.
postgresql 9 is using the role pgsql and 11 the role postgres.
When I run this command:

su - postgres
pg_upgrade --check --link --old-bindir "/usr/local/postgres/9.5/usr/local/bin/" --new-bindir "/usr/local/bin" --old-datadir "/usr/local/pgsql/9.5/data/" --new-datadir "/usr/local/pgsql/data"

I see the following error: "connection to database failed: FATAL: role "postgres" does not exist"
That's because the role postgres does not exist in postgresql 9
When I run this command:

su - postgres
pg_upgrade --check --link --old-bindir "/usr/local/postgres/9.5/usr/local/bin/" --new-bindir "/usr/local/bin" --old-datadir "/usr/local/pgsql/9.5/data/" --new-datadir "/usr/local/pgsql/data" -U pgsql

output:

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 hash indexes                                   ok
Checking for roles starting with "pg_"                      ok

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

could not connect to target postmaster started with the command:
"/usr/local/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/usr/local/pgsql/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/db/postgres'" start
Failure, exiting

Now he can connect to the old database but not to the new one.
How can I fix this ? Or is there a better way to do this upgrade ?

Maybe there is a fix. I can start the old postgresql 9 server :

su -pgsql
/usr/local/postgres/9.5/usr/local/bin/pg_ctl -D /usr/local/pgsql/9.5/data/ start

And create the role postgres but I think I cannot change the owner of the postgres database.

 

[obsigna]

I cannot remember all the very details, however, this issue hit me as well 2 or so years ago, when I upgraded PostgreSQL from 9 to 10. To me it seemed an arbitrary decision to change the PostgreSQL user from pgsql to postgres, and I simply ignored that. So, I maintained the system user 70 pgsql and put into my /etc/rc.conf:

...
postgresql_enable="YES"
postgresql_user="pgsql"
postgresql_data="/usr/local/pgsql/data"
...

Here I still have:
# pw showuser pgsql

pgsql:*:70:70::0:0:PostgreSQL pseudo-user:/usr/local/pgsql:/bin/sh

 

[cm-vdp]

I have found a solution for this issuse.
First start the old postgresql:

su - pgsql
/usr/local/postgres/9.5/usr/local/bin/pg_ctl -D /usr/local/pgsql/9.5/data/ start

Create a new superuser role

pgsql -U pgsql postgres
CREATE ROLE newsu LOGIN SUPERUSER;

Login with this user and rename the pgsql role to postgres

psql -U newsu postgres
ALTER USER pgsql RENAME to postgres;

From this moment pg_upgrade works fine