PostgreSQL中在忘记密码或无权限登录数据库时怎么recover administrator

这篇文章主要介绍"PostgreSQL中在忘记密码或无权限登录数据库时怎么recover administrator"，在日常操作中，相信很多人在PostgreSQL中在忘记密码或无权限登录数据库时怎么recover administrator问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答"PostgreSQL中在忘记密码或无权限登录数据库时怎么recover administrator"的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

创建数据库,把管理员设置为NOLOGIN

[pg12@localhost ~]$ initdb -D /data/pg12/tmpdbThe files belonging to this database system will be owned by user "pg12".This user must also own the server process.The database cluster will be initialized with locale "en_US.UTF-8".The default database encoding has accordingly been set to "UTF8".The default text search configuration will be set to "english".Data page checksums are disabled.creating directory /data/pg12/tmpdb ... okcreating subdirectories ... okselecting dynamic shared memory implementation ... posixselecting default max_connections ... 100selecting default shared_buffers ... 128MBselecting default timezone ... PRCcreating configuration files ... okrunning bootstrap script ... okperforming post-bootstrap initialization ... oksyncing data to disk ... okinitdb: warning: enabling "trust" authentication for local connectionsYou can change this by editing pg_hba.conf or using the option -A, or--auth-local and --auth-host, the next time you run initdb.Success. You can now start the database server using:    pg_ctl -D /data/pg12/tmpdb -l logfile start[pg12@localhost ~]$ pg_ctl -D /data/pg12/tmpdb -l logfile startwaiting for server to start.... doneserver started[pg12@localhost ~]$ psql -U pg12 -c "ALTER ROLE pg12 WITH NOLOGIN" -d postgresALTER ROLE

尝试登录数据库,提示无法登录

[pg12@localhost ~]$ psql -d postgrespsql: error: could not connect to server: FATAL:  role "pg12" is not permitted to log in

可通过single user mode处理此类"事故"
关闭数据库

[pg12@localhost ~]$  pg_ctl -D /data/pg12/tmpdb -l logfile stopwaiting for server to shut down.... doneserver stopped

以single user mode模式启动数据库

[pg12@localhost ~]$ postgres --single -D /data/pg12/tmpdb postgresPostgreSQL stand-alone backend 12beta1backend>

执行维护任务

backend> alter role pg12 with loginbackend>

启动数据库,登录

[pg12@localhost ~]$ pg_ctl -D /data/pg12/tmpdb -l logfile startwaiting for server to start.... doneserver started[pg12@localhost ~]$ psql -U pg12 -d postgrespsql (12beta1)Type "help" for help.postgres=#

到此，关于"PostgreSQL中在忘记密码或无权限登录数据库时怎么recover administrator"的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注网站，小编会继续努力为大家带来更多实用的文章！