删除数据库异常处理
当你遇到 PostgreSQL 报错 "ERROR: database is being accessed by other users DETAIL: There is 1 other session using the database." 时，意味着有其他会话正在使用该数据库，导致你无法进行某些操作，比如删除数据库。
为了解决这个问题，你可以采取以下步骤：
-
断开所有连接：在 PostgreSQL 9.2 及以上版本，你可以执行以下 SQL 语句来终止所有连接到目标数据库的会话，除了当前会话之外：
| SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname='your_database_name' AND pid<>pg_backend_pid();
|
'your_database_name' 替换成你的数据库名称 。 -
终止单个连接：如果你只想终止特定的会话，可以先查询 pg_stat_activity 视图来找到特定的会话 PID，然后使用 pg_terminate_backend 函数来终止该会话：
| SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'your_database_name';
|
以下为实际运行结果：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37 | postgres=# drop database way361;
ERROR: database "way361" is being accessed by other users
DETAIL: There is 1 other session using the database.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres+
| | | | | test=CTc/postgres
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres+
| | | | | test=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres+
| | | | | test=CTc/postgres
way361 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
postgres=# SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'way361';
pg_terminate_backend
----------------------
t
(1 row)
postgres=# SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname='way361' AND pid<>pg_backend_pid();
pg_terminate_backend
----------------------
(0 rows)
postgres=# drop database way361;
DROP DATABASE
postgres=#
|
捐赠本站(Donate)
如您感觉文章有用，可扫码捐赠本站！(If the article useful, you can scan the QR code to donate))
