I'm using homebrew on Mac OSX to manage a postgres database. I'm trying to upgrade my postgres install from 11.9 to 13.0 using pg_upgrade. When I run pg_upgrade I get the following error about not being a superuser. If I try running as the "postgres" user, I get an error that the "postgres" user is not the install user.
pg_upgrade --old-datadir /usr/local/var/postgresql@11 --new-datadir /usr/local/var/postgres --old-bindir /usr/local/Cellar/postgresql@11/11.9/bin --new-bindir /usr/local/Cellar/postgresql/13.0/bin -c -U Brian
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
connection to database failed: FATAL: must be superuser to connect in binary upgrade mode
could not connect to source postmaster started with the command:
"/usr/local/Cellar/postgresql@11/11.9/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/usr/local/var/postgresql@11" -o "-p 50432 -b -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/usr/local/Cellar'" start
Failure, exiting
Try as the "postgres" user
pg_upgrade --old-datadir /usr/local/var/postgresql@11 --new-datadir /usr/local/var/postgres --old-bindir /usr/local/Cellar/postgresql@11/11.9/bin --new-bindir /usr/local/Cellar/postgresql/13.0/bin -c -U postgres
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is the install user
database user "postgres" is not the install user
My system username is "Brian" and the original 11.9 database was installed as that user, but somehow it does not have superuser rights. I'm not sure how that happened but never quite realized it until now. My 13.0 database seems to be correctly set up with "Brian" as a superuser.
template1=# \du+
List of roles
Role name | Attributes | Member of | Description
---------------+-------------------------------------+------------+-------------
Brian | Create role, Create DB, Replication | {} |
When I login with psql -d template1 -U postgres and try to alter the role I get the following error.
template1=# alter role Brian with superuser;
ERROR: role "brian" does not exist
Time: 0.415 ms
If I log in as sudo -u postgres -i and try to alter or create a user, I also get the following errors:
psql -c "alter role Brian with superuser;"
ERROR: role "brian" does not exist
createuser -s Brian
createuser: error: creation of new role failed: ERROR: role "Brian" already exists
Does anyone know why postgres is confused over "Brian" and "brian" and how I can give superuser rights the role "Brian" so I can properly perform a pg_upgrade? When I tried brew postgresql-upgrade-database it initially gave me the same superuser error and now, upon rerun, it says that everything is already upgraded.
Case of SQL identifiers from within SQL is ignored except when in double quotes:
alter role "Brian" with superuser;
But when specified on the command line (with -U, for example) case is not ignored.