PostgreSQL：暂时禁用连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3185266/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL: Temporarily disable connections
提问by Konrad Garus
I have a script in PostgreSQL which restores test database from dump every night. The database is accessed by app servers and processes with connection pool which keeps a few connections alive at all times.
我在 PostgreSQL 中有一个脚本，它每晚从转储中恢复测试数据库。数据库由应用程序服务器和带有连接池的进程访问，连接池始终保持一些连接处于活动状态。
So the script restores dump into my_temp_database. Then it should rename my_databaseto my_old_database, my_temp_databaseto my_database, and eventually drop my_old_database.
所以脚本将转储恢复到my_temp_database. 然后它应该重命名my_database为my_old_database，my_temp_database到my_database，并最终删除my_old_database。
How can I disconnect all clients, superuser or not, from my_database, so that it can be renamed? How I can I temporarily prevent them from reconnecting?
如何断开所有客户端（my_database无论是否为超级用户）与，以便可以重命名？我怎样才能暂时阻止他们重新连接？
Is there a better way to do what I need?
有没有更好的方法来做我需要的事情？
回答by araqnid
To mark database 'applogs' as not accepting new connections:
将数据库“applogs”标记为不接受新连接：
update pg_database set datallowconn = false where datname = 'applogs';
Another possibility would be to revoke 'connect' access on the database for the client role(s).
另一种可能性是撤销客户端角色对数据库的“连接”访问权限。
Disconnect users from database = kill backend. So to disconnect all other users from "applogs" database, for example:
断开用户与数据库的连接 = 终止后端。因此，要断开所有其他用户与“applogs”数据库的连接，例如：
select pg_terminate_backend(procpid)
from pg_stat_activity
where datname = 'applogs' and procpid <> pg_backend_pid();
Once you've done both of those, you are the only user connected to 'applogs'. Although there might actually be a delay before the backends actually finish disconnecting?
完成这两项操作后，您就是唯一连接到“应用程序日志”的用户。尽管在后端实际完成断开连接之前实际上可能会有延迟？
回答by Kuberchaun
After you have terminated the current active connections you can also issue this command that will only allow super users to login. This assumes you are ok with all super users having access still. Hopefully you don't hand out super user rights to just anyone.
终止当前活动连接后，您还可以发出此命令，只允许超级用户登录。这假设您对所有具有访问权限的超级用户都没有问题。希望您不要将超级用户权限授予任何人。
ALTER DATABASE your_db CONNECTION LIMIT 0;
回答by Zorg
Starting PostgreSQL 9.5 we finally can:
从 PostgreSQL 9.5 开始，我们终于可以：
ALTER DATABASE db WITH ALLOW_CONNECTIONS false;
回答by mivk
I had a different usage scenario, in which I wanted to disable a DB for everyone (including superuser) and forever, but not plainly drop it just yet, to be able to reactivate it quickly if needed.
我有一个不同的使用场景，我想永久禁用每个人（包括超级用户）的数据库，但现在还没有简单地删除它，以便能够在需要时快速重新激活它。
This worked fine on an old 8.3 Postgres:
这在旧的 8.3 Postgres 上运行良好：
UPDATE pg_database SET datallowconn=false WHERE datname='my_db_name';
