PostgreSQL：暂时禁用连接 [英] PostgreSQL: Temporarily disable connections

问题描述

我在PostgreSQL中有一个脚本，该脚本每天晚上从转储中恢复测试数据库。应用服务器和具有连接池的进程可以通过应用服务器访问该数据库，该连接池始终保持一些连接处于活动状态。

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.

因此该脚本将转储还原到 my_temp_database 。然后应将 my_database 重命名为 my_old_database ，将 my_temp_database 重命名为 my_database ，并最终删除 my_old_database 。

So the script restores dump into my_temp_database. Then it should rename my_database to my_old_database, my_temp_database to my_database, and eventually drop my_old_database.

我该如何从 my_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?

是否有更好的方法来执行我需要的操作？

Is there a better way to do what I need?

推荐答案

将数据库'applogs'标记为不接受新连接：

To mark database 'applogs' as not accepting new connections:

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).

断开用户与数据库的连接=杀死后端。因此，要断开所有其他用户与 applogs数据库的连接，例如：

Disconnect users from database = kill backend. So to disconnect all other users from "applogs" database, for example:

select pg_terminate_backend(procpid)
from pg_stat_activity
where datname = 'applogs' and procpid <> pg_backend_pid();

完成这两个操作后，您将是唯一连接到 applogs的用户。尽管在后端实际断开连接之前实际上可能会有延迟？

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?

这篇关于PostgreSQL：暂时禁用连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！