1. 查看连接使用情况以及杀掉相应的连接
查看哪些用户在链接数据库
select * from pg_stat_activity ;
杀死连接 , 因为每个连接都是一个进程,所以要指定杀死连接时,需要pg_stat_activity的pid列信息:
postgres=# SELECT pg_terminate_backend(1973) ;-[ RECORD 1 ]--------+--pg_terminate_backend | t
- pg_terminate_backend 执行需要具有SUPERUSER权限 , 否则只能查看和杀当前用户的连接
可以批量杀连接的方法 , 指定时间和连接状态来批量杀连接:
# 删指定时间范围的空闲连接select pg_terminate_backend(pid) from pg_stat_activity where backend_type='client backend' and state='idle' and state_change > '2020-10-01 00:00:00' and state_change < '2021-01-01 00:00:00' ;# 删超过一天没请求的空闲连接select pg_terminate_backend(pid) from pg_stat_activity where backend_type='client backend' and state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled') and (current_timestamp - state_change > interval '1 day');
2. 查看服务器的IP地址
一般用于调试用
select inet_server_addr() as "connected_to";
3. 查看数据库版本号
postgres=# SHOW server_version_num; server_version_num -------------------- 120004(1 row)postgres=# select version(); version ----------------------------------------------------------------------------------------------------------------- PostgreSQL 12.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit, TelePG(1 row)postgres=# show server_version; server_version ---------------- 12.4(1 row)postgres=# SELECT current_setting('server_version_num'); current_setting ----------------- 120004(1 row)
4. 获取当前连接的PID
postgres=# SELECT pg_backend_pid(); pg_backend_pid ---------------- 11394(1 row)