# Upgrading postgresql from 9 to 11 - role issues



## cm-vdp (Nov 30, 2019)

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 (Dec 1, 2019)

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 (Dec 1, 2019)

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


----------

