在 CentOS 上监控 PostgreSQL 状态,可以从系统层、数据库层、日志层、第三方工具几个角度来做。下面按实用程度给你一套方案。
ps -ef | grep postgres
或
systemctl status postgresql
ss -lntp | grep 5432
# 或
netstat -lntp | grep 5432
top -u postgres
或
iostat -x 1
systemctl status postgresql
systemctl is-active postgresql
设置开机自启并监控:
systemctl enable postgresql
su - postgres
psql
SELECT count(*) FROM pg_stat_activity;
SELECT pid, state, query, now()-query_start AS duration
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY duration DESC;
SELECT * FROM pg_stat_user_tables;
SELECT datname, pg_size_pretty(pg_database_size(datname))
FROM pg_database;
通常在:
/var/lib/pgsql/data/log/
# 或
/var/log/postgresql/
查看错误日志:
tail -f /var/lib/pgsql/data/log/postgresql-*.log
postgresql.conf)logging_collector = on
log_min_duration_statement = 1000 # 记录超过1秒的SQL
log_connections = on
log_disconnections = on
修改后重启:
systemctl restart postgresql
CREATE EXTENSION pg_stat_statements;
shared_preload_libraries = 'pg_stat_statements'
重启数据库后查看:
SELECT query, calls, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
#!/bin/bash
count=$(psql -tAc "SELECT count(*) FROM pg_stat_activity")
if [ "$count" -gt 100 ]; then
echo "警告:连接数过多 $count"
fi
加 crontab:
* * * * * /root/check_pg.sh
systemctl + psql + 日志pg_stat_statements + 日志 + Prometheus + Grafana如果你愿意,我可以:
你现在是开发环境还是生产环境?