postgres=# SHOW config_file;
                  config_file                  
-----------------------------------------------
 /var/lib/postgresql/18/docker/postgresql.conf
(1 row)
		
			

		
[root@development ~]# docker exec -it postgres psql -h 127.0.0.1 -U postgres
psql (18.3 (Debian 18.3-1.pgdg13+1))
Type "help" for help.

postgres=# SHOW max_connections;
 max_connections 
-----------------
 100
(1 row)		
		
			

连接数/使用率

		
postgres=# SELECT
  current_setting('max_connections')::int AS max_conn,
  count(*) AS current_conn,
  round(count(*)::numeric / current_setting('max_connections')::numeric * 100, 1) AS usage_pct
FROM pg_stat_activity;
 max_conn | current_conn | usage_pct 
----------+--------------+-----------
      100 |           58 |      58.0
(1 row)
		
			

按用户/库连接数

		
SELECT datname, usename, count(*) FROM pg_stat_activity GROUP BY 1,2;
		
			

长事务

		
SELECT pid, datname, usename, now() - xact_start AS xact_duration, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_duration DESC;
		
			

查看当前总连接数，可使用如下SQL语句：

		
SELECT COUNT(*) AS total_connections FROM pg_stat_activity;		
		
			

查看连接状态分布

		
连接状态通常包括active（活跃）、idle（空闲）、idle in transaction（事务中空闲）等。可通过以下SQL统计各类连接状态：
SELECT state, COUNT(*) AS count FROM pg_stat_activity GROUP BY state;

State	描述
active	当前正在执行SQL语句
idle	连接空闲，未执行任何操作
idle in transaction	连接处于事务中但未执行任何操作
fastpath function call	使用快速路径调用函数		
		
			

		
-- 空闲 > 5 分钟的连接
SELECT pid, datname, usename, state, query_start, now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'idle' AND now() - query_start > interval '5 min'
ORDER BY duration DESC;

-- 执行终止（替换 PID）
SELECT pg_terminate_backend(PID);
		
			

		
-- 运行超过 1 分钟的活跃查询
SELECT pid, datname, usename, state, query, now() - query_start AS duration
FROM pg_stat_activity
WHERE state IN ('active', 'idle in transaction')
  AND now() - query_start > interval '1 min'
ORDER BY duration DESC;

-- 终止
SELECT pg_terminate_backend(PID);
		
			

		
SELECT * FROM pg_available_extensions;			
		
			

		
postgres=# SELECT * FROM pg_available_extensions;
  name   | default_version | installed_version |           comment
---------+-----------------+-------------------+------------------------------
 plpgsql | 1.0             | 1.0               | PL/pgSQL procedural language
(1 row)
		
			

		
postgres=# SELECT * FROM pg_shadow;
 usename  | usesysid | usecreatedb | usesuper | usecatupd | userepl |               passwd                | valuntil | useconfig
----------+----------+-------------+----------+-----------+---------+-------------------------------------+----------+-----------
 postgres |       10 | t           | t        | t         | t       |                                     |          |
 wechat   |    16385 | f           | f        | f         | f       | md55bf335eef50073dbc3a4a2103e7b3757 |          |
 test     |    16387 | f           | f        | f         | f       | md505a671c66aefea124cc08b76ea6d30bb |          |
 dba      |    16384 | t           | t        | t         | f       | md5a1453e5a69943d1674eb2f7100efa04c |          |
(4 rows)