即使有连接 PostgreSQL13也允许删除库

在PostgreSQL13之前的版本，如果数据库有连接存在是无法直接删除数据库，必须先断开所有连接才能删除，但是如果有应用程序连接上来，断开后马上又会自动连接上，此时只能暂时停止应用程序或者拒绝应用程序连接（配置防火墙或pg_hba规则拒绝连接），操作起来比较麻烦，PostgreSQL13对这种情况进行了优化，删除数据库语法添加了可选项FORCE，如下：
DROP DATABASE [ IF EXISTS ] name [ [ WITH ] ( option [, …] ) ]
where option can be: FORCE

下面演示在PG10和13版本中分别删除有连接的数据库

--PG10窗口1
postgres=# SELECT version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.15 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit
(1 row)
postgres=# CREATE DATABASE tmp_test;
CREATE DATABASE
postgres=# \c tmp_test 
You are now connected to database "tmp_test" as user "postgres".

--PG10窗口2
postgres=# SELECT version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.15 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit
(1 row)
--有连接，删除数据库失败
postgres=# DROP DATABASE tmp_test;
ERROR:  database "tmp_test" is being accessed by other users
DETAIL:  There is 1 other session using the database.
--终止数据库的所有连接
postgres=# SELECT pg_terminate_backend(pid)
postgres-#   FROM pg_stat_activity
postgres-#  WHERE pid != pg_backend_pid()
postgres-#    AND datname = 'tmp_test';
 pg_terminate_backend 
----------------------
 t
(1 row)
--删除数据库成功
postgres=# DROP DATABASE tmp_test;
DROP DATABASE


--PG13窗口1
postgres=# SELECT version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 13.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)
postgres=# CREATE DATABASE tmp_test;
CREATE DATABASE
postgres=# \c tmp_test
You are now connected to database "tmp_test" as user "postgres".

--PG13窗口2
postgres=# SELECT version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 13.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)
--不加选项 删除库，一样报错
postgres=# DROP DATABASE tmp_test;
ERROR:  database "tmp_test" is being accessed by other users
DETAIL:  There is 1 other session using the database.
--加上强制删除选项项，删除成功
postgres=# DROP DATABASE tmp_test WITH(FORCE);
DROP DATABASE

--PG13窗口1
--窗口1会话已断开
tmp_test=# SELECT 1;
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

这里有两点需要注意：

• 如果当前用户没有权限断开连接，那么删除将失败；
• 如果目标库有预事务、活跃的逻辑复制槽或订阅终止连接将失败。