PG数据库负载高检查慢SQL

一、查询当前正在执行所有SQL语句
SELECT pid, datname, usename, client_addr, application_name, STATE, backend_start, xact_start, xact_stay, query_start, query_stay, REPLACE ( query, chr( 10 ), ' ' ) AS query FROM ( SELECT pgsa.pid AS pid, pgsa.datname AS datname, pgsa.usename AS usename, pgsa.client_addr client_addr, pgsa.application_name AS application_name, pgsa.STATE AS STATE, pgsa.backend_start AS backend_start, pgsa.xact_start AS xact_start, EXTRACT ( epoch FROM ( now( ) - pgsa.xact_start ) ) AS xact_stay, pgsa.query_start AS query_start, EXTRACT ( epoch FROM ( now( ) - pgsa.query_start ) ) AS query_stay, pgsa.query AS query FROM pg_stat_activity AS pgsa WHERE pgsa.STATE != 'idle' AND pgsa.STATE != 'idle in transaction' AND pgsa.STATE != 'idle in transaction (aborted)' ) idleconnections ORDER BY query_stay DESC

二、判断是否存在慢查询语句
``

字段	解释
PID	数据库查询进程ID
query_stay	查询时长秒
query	查询SQL语句
``

三、按查询进程，杀掉慢查询释放资源
SELECT pg_terminate_backend(PID); SELECT pg_terminate_backend(6289);

四、扩展query的显示长度能展示出全部语句

vi /var/lib/pgsql/12/data/postgresql.conf	header

五、杀掉某个数据库的所有数据库连接
SELECT pg_terminate_backend(pg_stat_activity.pid)FROM pg_stat_activity WHERE datname='数据库名称' AND pid<>pg_backend_pid();

六、导出数据库某个表
pg_dump -t 某个表名 "host=192.168.16.31 port=18921 user=postgres password=数据库密码 dbname=数据库" -f /app/zjfbeifen/1.sql

转载至:https://www.jb51.net/article/272572.htm