database user "postgres" is not the install user

Question

I'm trying to upgrade postgres from 9.5 to 9.6. brew upgrade postgresql succeeds, but when running

pg_upgrade -b /usr/local/Cellar/postgresql/9.5.3/bin/ -B /usr/local/Cellar/postgresql/9.6.1/bin/ -d /usr/local/var/postgres -D /usr/local/var/postgres9.6 -U postgres

I get an error

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user
database user "postgres" is not the install user
Failure, exiting

when trying without -U postgres at the end it gets even weirder

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* system OID user data types                ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for roles starting with 'pg_'                      ok
Creating dump of global objects                             ok
Creating dump of database schemas
                                                            ok
Checking for presence of required libraries                 ok
Checking database user is the install user
database user "dimid" is not the install user

So how come

Checking database user is the install user                  ok

Laurenz Albe · Accepted Answer · 2017-01-25 15:18:32Z

11

The old PostgreSQL cluster was obviously created with

initdb -U dimid

but the new cluster was istalled with a different superuser.

You have to create the new cluster with the same superuser name as the old one.

answered Jan 25, 2017 at 15:18

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

dimid Over a year ago

Thanks, how can I find out which user created a cluster?

Laurenz Albe Over a year ago

You would do that with SELECT rolname FROM pg_roles WHERE oid = 10, but it is not necessary. Just drop the new cluster and recreate it with -U dimid.

dimid Over a year ago

Thanks, that did the trick. Out of curiosity, what does 10 refer to?

Laurenz Albe Over a year ago

That's the hard-coded Oid for the bootstrap superuser. Search the source for BOOTSTRAP_SUPERUSERID.

DylanYoung Over a year ago

Note: if you're using postgres.app, it seems two roles are installed: $USER and 'postgres'. This will also choke pg_upgrade (which expects a single non-system role), so you should run: DROP DATABASE $USER; DROP ROLE $USER

robm · Accepted Answer · 2017-05-08 07:50:10Z

5

Specifically for homebrew postgresql installations, the default user for initdb is $USER -- so if you just followed the instructions initially and did something like

initdb /usr/local/var/postgres -E utf8

the install user is your Unix username. In my case it is 'rob', so just adding '-U rob' to the pg_upgrade works as well:

pg_upgrade -b /usr/local/Cellar/postgresql/9.5.4_1/bin -B /usr/local/Cellar/postgresql/9.6.2/bin -d /usr/local/var/postgres95 -D /usr/local/var/postgres -U rob

answered May 8, 2017 at 7:50

robm

1,4031 gold badge18 silver badges25 bronze badges

2 Comments

engineerDave Over a year ago

for a lot of others the user may be postgres so just use that instead if this error message comes up with your name as not the install user

robm Over a year ago

Yes, 'postgres' is the preferred install user as that is the default, and thus what users (like me) will try first. Using $USER for the initial initdb as described in this answer is specific to homebrew installations of PostgreSQL on OSX / MacOS.

Andi · Accepted Answer · 2024-01-08 19:01:52Z

3

For users of macOS Postgres App:

The upgrade script checks if $USER has the oid 10 in the old database, if this user does not exist – it responds with a corresponding error message. Consult https://support.hashicorp.com/hc/en-us/articles/1500005343202-PostgreSQL-12-Upgrade-Error-database-user-hashicorp-is-not-the-install-user on how to work around this issue.

In my case:

psql postgres -U postgres
SELECT rolname, oid FROM pg_roles;
# check which role has oid 10, in my case role `postgres`
# – as we are postgres right now we have to create an temp user, continue with that:
CREATE ROLE "temp" WITH SUPERUSER CREATEDB CREATEROLE REPLICATION BYPASSRLS LOGIN PASSWORD 'temp';

exit # or Ctrl + D


psql postgres -U temp
ALTER ROLE $USER RENAME TO "$USER_";
ALTER ROLE postgres RENAME TO $USER;
CREATE ROLE "postgres" WITH SUPERUSER CREATEDB CREATEROLE REPLICATION BYPASSRLS;

Depending on your own databases you have to adapt these queries as described in the linked article.

P.S.: Be aware that upgrading old databases with an still installed postgis extension is not really worth the hassle, you have to either uninstall the extension or could another upgrade path as described in https://postgresapp.com/documentation/migrating-data.html.

edited Jan 8, 2024 at 19:01

answered Jan 12, 2023 at 0:28

Andi

2833 silver badges10 bronze badges

2 Comments

László Tóth May 9 at 19:28

Today noticed exactly same problem. In our db was postgres role renamed to x. And was created a new postgres role. I don't know was earlier a tool which made this... or not? We must re-rename the roles... You can't rename logged role! Must consider next time how can you login! indent or password :)

László Tóth May 14 at 10:52

In our postgres9.3 postgres user was renamed and new postgres user was created. This caused our upgrade error.

Charlie · Accepted Answer · 2021-01-26 07:49:16Z

1

I was getting this because the target database contained an extra user.

This was previously part of my install and upgrade init script. I removed it from the upgrade initdb and the upgrade completed successfully.

answered Jan 26, 2021 at 7:49

Charlie

9,2232 gold badges61 silver badges56 bronze badges

Comments

Jayesh · Accepted Answer · 2023-09-22 22:07:31Z

0

check initdb.log for both clusters, check from which older cluster (pg11) was initialized. We need to initialize new cluster with the same user, and there should be no other user created other than the initialized cluster user at target (pg14)

edited Sep 22, 2023 at 22:07

Jayesh

9996 silver badges16 bronze badges

answered Sep 16, 2023 at 6:15

Amit Varma

1

Comments

Aashish · Accepted Answer · 2024-04-04 12:44:20Z

0

During the pg_upgrade process, a check is run to make sure pg_catalog.pg_roles has only a single non pg_* user, typically this is just 'postgres'. If this check fails, the error message is "Only the install user can be defined in the new cluster."; This message implies the user executing pg_upgrade must match the the rolsuper in pg_catalog.pg_roles.

answered Apr 4, 2024 at 12:44

Aashish

1

1 Comment

reinierpost Feb 20 at 11:38

What is a pg_* user? What is rolsuper?

Collectives™ on Stack Overflow

database user "postgres" is not the install user

6 Answers 6

5 Comments

2 Comments

2 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

5 Comments

2 Comments

2 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related