# 2. 配置Prometheus
$ vi /usr/local/prometheus/prometheus.yml

global:
scrape_interval: 15s
evaluation_interval: 15s

rule_files:
– “alert_rules.yml”

scrape_configs:
– job_name: ‘postgresql’
static_configs:
– targets: [‘192.168.1.11:9187’]

# 3. 启动Prometheus
$ cd /usr/local/prometheus
$ ./prometheus –config.file=prometheus.yml &

# 4. 配置PostgreSQL Exporter
$ vi /etc/systemd/system/postgres_exporter.service

[Unit]
Description=PostgreSQL Exporter
After=network.target

[Service]
Type=simple
User=postgres
Environment=DATA_SOURCE_NAME=”postgresql://postgres:postgres_pass@fgedu.localhost:5432/postgres?sslmode=disable”
ExecStart=/usr/local/postgres_exporter/postgres_exporter
Restart=always

[Install]
WantedBy=multi-user.target

# 5. 启动PostgreSQL Exporter
$ systemctl daemon-reload
$ systemctl start postgres_exporter
$ systemctl enable postgres_exporter

# 6. 配置Grafana
# 打开浏览器，访问 http://192.168.1.10:3000
# 添加Prometheus数据源，导入PostgreSQL仪表盘

# 7. 验证监控
# 查看Grafana仪表盘，确认监控数据正常显示

# 启用统计信息收集
shared_preload_libraries = ‘pg_stat_statements’
pg_stat_statements.max = 10000
pg_stat_statements.track = all

# 2. 重启PostgreSQL
$ systemctl restart postgresql

# 3. 创建pg_stat_statements扩展
$ psql -U postgres

postgres=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION

# 4. 查看统计信息
$ psql -U postgres -c “SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;”

# 5. 配置Prometheus监控统计信息
$ vi /usr/local/postgres_exporter/queries.yaml

pg_stat_statements:
query: |
SELECT
queryid,
query,
calls,
total_exec_time,
mean_exec_time,
rows
FROM pg_stat_statements
metrics:
– queryid:
usage: “LABEL”
description: “Query ID”
– query:
usage: “LABEL”
description: “Query text”
– calls:
usage: “COUNTER”
description: “Number of times query was executed”
– total_exec_time:
usage: “COUNTER”
description: “Total execution time”
– mean_exec_time:
usage: “GAUGE”
description: “Mean execution time”
– rows:
usage: “COUNTER”
description: “Number of rows returned”

# 6. 重启PostgreSQL Exporter
$ systemctl restart postgres_exporter

# 2. 监控连接数
$ psql -U postgres -c “SELECT count(*) FROM pg_stat_activity;”

# 3. 监控表空间使用情况
$ psql -U postgres -c “SELECT pg_size_pretty(pg_tablespace_size(‘pg_default’));”

# 4. 监控缓存命中率
$ psql -U postgres -c “SELECT
sum(heap_blks_hit) as heap_hit,
sum(heap_blks_read) as heap_read,
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as hit_ratio
FROM pg_stat_database;”

# 5. 监控锁等待
$ psql -U postgres -c “SELECT
blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_query,
blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;”