升级过程中postgres超级用户问题

Question

我在Mac OSX上使用自制软件来管理postgres数据库。我正在尝试使用pg_upgrade将postgres安装从11.9升级到13.0。当我运行pg_upgrade时，我会收到以下关于不是超级用户的错误。如果我尝试以"；postgres"；用户，我得到一个错误；postgres"；用户不是安装用户。

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

尝试作为"；postgres"；用户

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

我的系统用户名是"；Brian"最初的11.9数据库是作为该用户安装的，但不知何故，它没有超级用户权限。我不知道那是怎么发生的，但直到现在我才完全意识到。我的13.0数据库似乎是正确设置的；Brian"作为超级用户。

template1=# du+
List of roles
Role name   |             Attributes              | Member of  | Description
---------------+-------------------------------------+------------+-------------
Brian         | Create role, Create DB, Replication | {}         |

当我使用psql -d template1 -U postgres登录并尝试更改角色时，会出现以下错误。

template1=# alter role Brian with superuser;
ERROR:  role "brian" does not exist
Time: 0.415 ms

如果我以sudo -u postgres -i的身份登录并尝试更改或创建用户，我还会收到以下错误：

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

有人知道postgres为什么对"；Brian"以及"；brian"以及我如何赋予超级用户权限角色"；Brian"这样我就可以正确地执行升级了？当我尝试brew postgresql-upgrade-database时，它最初给了我相同的超级用户错误，现在，在重新运行时，它说一切都已经升级了。

Answer 1

SQL中SQL标识符的情况被忽略，除非使用双引号：

alter role "Brian" with superuser;

但是，当在命令行中指定(例如使用-U(时，大小写不会被忽略。