一: 各文件目录:
所有配置:
show all
config_file目录:
show config_file
data 目录
show data_directory
日志是否开启:
logging_collector
off 关闭时, 在 config_file 中添加配置开启
日志目录
show log_directory
data 目录下的 log 目录
事务日志查看:
事务日志在data目录的 pg_wal目录下
内容: 内容是二进制的, 用pg_waldump 转换
如:
pg_waldump 00000001000000D6000000A9 > 00000001000000D6000000A9.dump
查看:
tail -n 500 00000001000000D6000000A9.dump
描述:
查询正在执行的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
结果:
按查询进程,杀掉慢查询释放资源
SELECT pg_terminate_backend(PID);
SELECT pg_terminate_backend(6289);