ERROR: source database "template1" is being accessed by other users

一开始，开发童鞋说他在测试环境没有创建数据库的权限。心想，不对呀，开发环境没有怎么做权限管控，明明给予授权了。
上来一看：

?

1 2 3 4

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：

?

1 2 3 4 5 6 7 8 9 10 11

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掉

?

1 2 3 4 5

postgres=# SELECT pg_terminate_backend( 23498);  pg_terminate_backend ----------------------  t (1 row)

　　

也可以使用一条语句，直接将使用template1的会话kill掉：

?

1	select pg_terminate_backend(pid) from pg_stat_activity where DATNAME = 'template1';

　　

然后再执行数据库创建语句即可！