合 PG或GP删除数据库报错dropdb: database removal failed: ERROR: database "db1" is being accessed by other users DETAIL: There are 3 other sessions using the database.
Tags： 故障处理GreenPlumPostgreSQLPG 13dropdb
👉 本文共约478个字，系统预计阅读时间或需2分钟。
现象
1 2 3 4 5 6 7 8 9 | [gpadmin@mdw ~]$ dropdb db1 dropdb: database removal failed: ERROR: database "db1" is being accessed by other users DETAIL: There are 3 other sessions using the database. [gpadmin@mdw ~]$ lhrdb=# drop database db66; ERROR: database "db66" is being accessed by other users DETAIL: There are 14 other sessions using the database. |
在PostgreSQL13之前的版本，如果数据库有连接存在是无法直接删除数据库，必须先断开所有连接才能删除，但是如果有应用程序连接上来，断开后马上又会自动连接上，此时只能暂时停止应用程序或者拒绝应用程序连接（配置防火墙或pg_hba规则拒绝连接或修改端口号），操作起来比较麻烦，PostgreSQL13对这种情况进行了优化，删除数据库语法添加了可选项FORCE，如下：
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 | DROP DATABASE [ IF EXISTS ] name [ [ WITH ] ( option [, …] ) ] where option can be: FORCE postgres@lhrpg14:~$ dropdb --help dropdb removes a PostgreSQL database. Usage: dropdb [OPTION]... DBNAME Options: -e, --echo show the commands being sent to the server -f, --force try to terminate other connections before dropping -i, --interactive prompt before deleting anything -V, --version output version information, then exit --if-exists don't report error if database doesn't exist -?, --help show this help, then exit Connection options: -h, --host=HOSTNAME database server host or socket directory -p, --port=PORT database server port -U, --username=USERNAME user name to connect as -w, --no-password never prompt for password -W, --password force password prompt --maintenance-db=DBNAME alternate maintenance database Report bugs to <pgsql-bugs@lists.postgresql.org>. PostgreSQL home page: <https://www.postgresql.org/> postgres@lhrpg14:~$ |
GP实验
若是GreenPlum数据库，则可以通过配置防火墙或pg_hba规则拒绝连接或修改端口号来操作，这里建议修改端口号，缺点是需要重启库，流程如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | gpstop -M fast -a # 修改端口号为port 15432 vi /opt/greenplum/data/master/gpseg-1/postgresql.conf gpstart -a dropdb tmp_db1 gpstop -M fast -a # 修改端口号为port 5432 vi /opt/greenplum/data/master/gpseg-1/postgresql.conf gpstart -a |
PG实验
下面演示在PG10和13版本中分别删除有连接的数据库
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 | -- 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_db1; CREATE DATABASE postgres=# \c tmp_db1 You are now connected to database "tmp_db1" 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_db1; ERROR: database "tmp_db1" 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_db1'; pg_terminate_backend ---------------------- t (1 row) -- 删除数据库成功 postgres=# DROP DATABASE tmp_db1; DROP DATABASE |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
