一开始,开发童鞋说他在测试环境没有创建数据库的权限。心想,不对呀,开发环境没有怎么做权限管控,明明给予授权了。
上来一看:
postgres=# CREATE DATABASE "abce" WITH OWNER = "a_admin" postgres-# ; ERROR: source database "template1" is being accessed by other users DETAIL: There is 1 other session using the database.
原来不是权限的问题!
查看一下,谁在使用template1:
postgres=# select * from pg_stat_activity where DATNAME = 'template1'; datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_ start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query | backend_type -------+-----------+-------+----------+-----------+------------------+--------------+-----------------+-------------+------------------------------+------------+---------------- ---------------+-------------------------------+-----------------+------------+-------+-------------+--------------+------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------+---------------- 1 | template1 | 23498 | 16384 | a_admin | NAVICAT | xx.xx.xx.xxx | | 61664 | 2019-10-08 16:15:06.46307+08 | | 2019-10-08 16:1 5:16.542588+08 | 2019-10-08 16:15:16.545124+08 | Client | ClientRead | idle | | | SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcrea tedb, rolcanlogin, rolconnlimit, rolvaliduntil, rolconfig, oid , pg_catalog.shobj_description(oid, 'pg_authid') AS comment FROM pg_roles | client backend (1 row)
将查出的pid kill掉
postgres=# SELECT pg_terminate_backend( 23498); pg_terminate_backend ---------------------- t (1 row)
也可以使用一条语句,直接将使用template1的会话kill掉:
select pg_terminate_backend(pid) from pg_stat_activity where DATNAME = 'template1';
然后再执行数据库创建语句即可!