前面写了GP超级管理用户被禁用后该如何操作进行恢复，现在是PG同样情况下的操作，相对于GP来说步骤更少更简单一些
环境复现，基于 PostgreSQL 12.11
[postgres@pg1 data]$ psql
psql (12.11)
Type "help" for help.
postgres=# alter role postgres with nologin;
ALTER ROLE
再次默认登陆提示没有登陆权限
[postgres@pg1 data]$ psql
psql: error: FATAL: role "postgres" is not permitted to log in
同样有两个方法，第一先看是否存在其他活跃管理权限用户，如果有的话可以登录该用户进去，进行以下操作
[postgres@pg1 data]$ psql -U test
psql (12.11)
Type "help" for help.
test=# alter role postgres with login;
ALTER ROLE
test=# \q
[postgres@pg1 data]$ psql
psql (12.11)
Type "help" for help.
postgres=# select user;
user
----------
postgres
(1 row)
如果没有其他活跃管理权限用户，则采用postgres数据库自带的postres维护工具进入单用户模式进行修复
再次复现禁用状态
[postgres@pg1 data]$ psql
psql (12.11)
Type "help" for help.
postgres=# alter role postgres with nologin;
ALTER ROLE
查看进程并kill当前数据库进程
[postgres@pg1 ~]$ ps -ef |grep postgres
postgres 1688 1 0 10:48 ? 00:00:00 /opt/pg1211/bin/postgres
postgres 1689 1688 0 10:48 ? 00:00:00 postgres: logger
postgres 1691 1688 0 10:48 ? 00:00:00 postgres: checkpointer
postgres 1692 1688 0 10:48 ? 00:00:00 postgres: background writer
postgres 1693 1688 0 10:48 ? 00:00:00 postgres: walwriter
postgres 1694 1688 0 10:48 ? 00:00:00 postgres: autovacuum launcher
postgres 1695 1688 0 10:48 ? 00:00:00 postgres: archiver
postgres 1696 1688 0 10:48 ? 00:00:00 postgres: stats collector
postgres 1697 1688 0 10:48 ? 00:00:00 postgres: logical replication launcher
postgres 1698 1688 0 10:48 ? 00:00:00 postgres: walsender repuser 192.168.126.131(58704) streaming 0/240072E0
root 1738 1639 0 10:50 pts/1 00:00:00 su - postgres
postgres 1739 1738 0 10:50 pts/1 00:00:00 -bash
root 1793 1608 0 10:59 pts/0 00:00:00 su - postgres
postgres 1794 1793 0 10:59 pts/0 00:00:00 -bash
postgres 1823 1794 0 10:59 pts/0 00:00:00 ps -ef
postgres 1824 1794 0 10:59 pts/0 00:00:00 grep --color=auto postgres
[postgres@pg1 ~]$ kill 1688
[postgres@pg1 ~]$ ps -ef |grep postgres
root 1738 1639 0 10:50 pts/1 00:00:00 su - postgres
postgres 1739 1738 0 10:50 pts/1 00:00:00 -bash
root 1793 1608 0 10:59 pts/0 00:00:00 su - postgres
postgres 1794 1793 0 10:59 pts/0 00:00:00 -bash
postgres 1828 1794 0 11:00 pts/0 00:00:00 ps -ef
postgres 1829 1794 0 11:00 pts/0 00:00:00 grep --color=auto postgres
维护模式进行修改恢复postgres登陆权限
[postgres@pg1 data]$ postgres --single -O -D /data/ -p 5432 postgres
PostgreSQL stand-alone backend 12.11
backend> alter role postgres with login;
ctrl+D 退出
启动数据库服务
[postgres@pg1 data]$ pg_ctl start
waiting for server to start....2022-12-14 11:03:54.267 CST [1853] LOG: starting PostgreSQL 12.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2022-12-14 11:03:54.267 CST [1853] LOG: listening on IPv4 address "0.0.0.0", port 5432
2022-12-14 11:03:54.267 CST [1853] LOG: listening on IPv6 address "::", port 5432
2022-12-14 11:03:54.269 CST [1853] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-12-14 11:03:54.284 CST [1853] LOG: redirecting log output to logging collector process
2022-12-14 11:03:54.284 CST [1853] HINT: Future log output will appear in directory "pg_log".
done
server started
默认登陆
[postgres@pg1 data]$ psql
psql (12.11)
Type "help" for help.
postgres=# select user;
user
----------
postgres
(1 row)
至此恢复完成，再次提醒，在禁用或者删除等有可能造成数据丢失或无法使用的操作时，一定要谨慎、谨慎、再谨慎！！！
点击此处阅读原文
↓↓↓
文章转载自开源软件联盟PostgreSQL分会，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
