PostgreSQL复制数据库杀死会话后无法登录问题求助

• 最常见的原因是复制库操作前后未正确处理数据库连接权限：很多用户复制库时会先执行ALTER DATABASE originaldb WITH ALLOW_CONNECTIONS false;禁止新连接涌入，完成复制后忘记改回配置，就会出现输入密码后无响应的情况。
• 其次是终止会话触发了大量未提交事务的回滚操作，如果原库数据量较大，回滚期间数据库会处于半挂起状态，新连接会被阻塞等待恢复完成，看起来就是输入密码后无反应。
• 小概率情况是终止会话时误触发了数据库的一致性检查流程，或者PostgreSQL的连接池、后台进程出现异常，导致新连接无法正常分配资源。

1. 优先使用本地socket登录数据库
   登录到PostgreSQL部署的物理/云服务器，切换到系统postgres用户，执行本地连接命令，不需要走TCP协议，可绕过远程连接的限制：

   su - postgres
   psql -p 9763 -d postgres
   

   本地连接默认不需要密码，只要服务正常运行就能登录成功。

2. 检查数据库连接权限配置
   登录后执行以下命令查看原库的连接权限：

   SELECT datname, datallowconn FROM pg_database WHERE datname = 'originaldb';
   

   如果返回的datallowconn值为f，说明连接权限被关闭，执行以下命令恢复即可：

   ALTER DATABASE originaldb WITH ALLOW_CONNECTIONS true;
   

   执行完成后即可正常远程连接原库。

3. 检查数据库是否处于恢复状态
   如果连接权限正常，执行命令检查是否处于事务回滚/恢复流程：

   SELECT pg_is_in_recovery();
   

   如果返回值为t，说明还在恢复过程中，不需要额外操作，等待恢复完成即可，可查看PostgreSQL日志确认恢复进度，日志默认路径为/var/log/postgresql/。

4. 检查是否有异常阻塞进程
   执行以下命令查看所有运行中的进程，确认是否有僵死进程占用连接资源：

   SELECT pid, usename, state, query FROM pg_stat_activity WHERE datname = 'originaldb';
   

   如有长时间处于idle in transaction状态的异常进程，执行以下命令终止即可：

   SELECT pg_terminate_backend(对应进程pid);
   

5. 重启服务（最后兜底方案）
   以上操作都无法恢复的话，执行重启操作即可：

   • Debian/Ubuntu系统：systemctl restart postgresql
   • CentOS/RHEL系统：systemctl restart postgresql-<你的PG主版本号>
   • 源码安装的实例：pg_ctl restart -D <你的PG数据目录路径>

避免再出现同类问题，可按照以下步骤复制同实例数据库：

1. 禁止原库接收新连接：ALTER DATABASE originaldb WITH ALLOW_CONNECTIONS false;
2. 终止原库所有现有连接，即你之前使用的pg_terminate_backend命令
3. 执行CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;完成复制
4. 立即恢复原库连接权限：ALTER DATABASE originaldb WITH ALLOW_CONNECTIONS true;

内容的提问来源于stack exchange，提问作者The Great