PostgreSQL小知识--drop database失败了

知识点

• pg_stat_activity 系统视图
  显示所有连接数据的进程信息，每个服务器进程为一行，字段含义参考官方文档：https://www.postgresql.org/docs/13/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW
  其中state字段代表进程的状态：

active: The backend is executing a query.
idle: The backend is waiting for a new client command.
idle in transaction: The backend is in a transaction, but is not currently executing a query.
idle in transaction (aborted): This state is similar to idle in transaction, except one of the statements in the transaction caused an error.
fastpath function call: The backend is executing a fast-path function.
disabled: This state is reported if track_activities is disabled in this backend.

• pg_terminate_backend (pid integer) 服务器的信号(Signaling)函数，向其他服务器进程发送SIGTERM控制信号。
  终止指定的PID进程，回滚未提交事物。
  PID就是pg_stat_activity系统视图中的pid字段，同时也是操作系统ps -ef postgres的供客户端连接的子进程ID，后面提供示例。
  PostgreSQL还提供了一个 pg_cancel_backend 函数，此函数向指定的PID进程发送SIGINT控制信号。
  另外，PostgreSQL 不建议使用 kill -9 终止进程，有宕机风险，PostgreSQL 没有 Oracle 的pmon进程，可以处理这些异常的进程。

示例

删库失败了，有 session 在使用数据库。

postgres=# drop database sysbench;
ERROR:  database "sysbench" is being accessed by other users
DETAIL:  There are 4 other sessions using the database.

怎么找到使用数据库的这些 session，就要用到 pg_stat_activity 视图过滤数据库名。

postgres=# select datid,datname,pid,usesysid,usename,application_name,client_addr,wait_event,state,query from pg_stat_activity where datname = 'sysbench';
 datid | datname  |  pid  | usesysid | usename  |            application_name             |  client_addr  | wait_event | state |                    query                     
-------+----------+-------+----------+----------+-----------------------------------------+---------------+------------+-------+----------------------------------------------
 25648 | sysbench | 50364 |       10 | postgres | psql                                    |               | ClientRead | idle  | 
 25648 | sysbench | 50543 |       10 | postgres | DBeaver 21.2.5 - Main <sysbench>        | 192.168.0.105 | ClientRead | idle  | SHOW search_path
 25648 | sysbench | 50544 |       10 | postgres | DBeaver 21.2.5 - Metadata <sysbench>    | 192.168.0.105 | ClientRead | idle  | SELECT a.oid,a.* FROM pg_catalog.pg_roles a ORDER BY a.rolname
 25648 | sysbench | 50545 |       10 | postgres | DBeaver 21.2.5 - SQLEditor <Script.sql> | 192.168.0.105 | ClientRead | idle  | SET search_path = public,public,"$user"
(4 rows)

pg_stat_activity 视图的 PID 字段就是操作系统上的进程ID号。

怎么取消这些连接呢？ 不建议直接 kill -9 ，使用函数 pg_terminate_backend

postgres=# SELECT datname, pid, pg_terminate_backend(pg_stat_activity.pid)
postgres-# FROM pg_stat_activity
postgres-# WHERE datname = 'sysbench';
 datname  |  pid  | pg_terminate_backend 
----------+-------+----------------------
 sysbench | 50364 | t
 sysbench | 50543 | t
 sysbench | 50544 | t
 sysbench | 50545 | t
(4 rows)

postgres=# select datid,datname,pid,usesysid,usename,application_name,client_addr,wait_event,state,query from pg_stat_activity where datname = 'sysbench';
 datid | datname | pid | usesysid | usename | application_name | client_addr | wait_event | state | query 
-------+---------+-----+----------+---------+------------------+-------------+------------+-------+-------
(0 rows)

此时进程已经被终止掉，操作系统上也查不到这些进程，但是被终止掉的客户端再次查询时还会连接数据库，比如：

sysbench=# \l
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: Succeeded.
sysbench=# 

此时提示连接终止，Attempting reset: Succeeded，尝试连接成功，pg_stat_activity 又能看到这个进程信息，那么怎么防止其他进程连接数据库呢？
可以 ALTER DATABASE 更改数据库的属性，禁止其他人连接数据库

postgres=# ALTER DATABASE sysbench ALLOW_CONNECTIONS=false;
ALTER DATABASE

修改完成后再次执行pg_terminate_backend函数终止进程。
此时其他客户端再次进行连接将失败：

sysbench=# \l
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.

最后，可以安心的删除数据库了

postgres=# drop database sysbench;
DROP DATABASE