Последний этап — мониторинг. Потому что оптимизировать вслепую можно бесконечно, а команда клиента должна сама видеть, что происходит с базой прямо сейчас, а не узнавать о проблемах от пользователей.
-- Топ-20 самых тяжёлых запросов (требуется pg_stat_statements)
SELECT
queryid,
LEFT(query, 100) AS query_preview,
calls,
ROUND(total_exec_time::numeric / 1000, 2) AS total_time_sec,
ROUND(mean_exec_time::numeric, 2) AS mean_time_ms,
rows,
ROUND(shared_blks_hit::numeric / NULLIF(shared_blks_hit + shared_blks_read, 0) * 100, 2) AS cache_hit_pct
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
-- Текущие активные запросы
SELECT
pid,
now() - pg_stat_activity.query_start AS duration,
usename,
datname,
state,
LEFT(query, 200) AS query
FROM pg_stat_activity
WHERE state != 'idle'
AND pid != pg_backend_pid()
ORDER BY duration DESC;
-- Блокировки
SELECT
blocked.pid AS blocked_pid,
blocked.usename AS blocked_user,
blocking.pid AS blocking_pid,
blocking.usename AS blocking_user,
LEFT(blocked.query, 100) AS blocked_query,
LEFT(blocking.query, 100) AS blocking_query
FROM pg_stat_activity AS blocked
JOIN pg_locks AS bl ON bl.pid = blocked.pid
JOIN pg_locks AS kl ON kl.locktype = bl.locktype
AND kl.database IS NOT DISTINCT FROM bl.database
AND kl.relation IS NOT DISTINCT FROM bl.relation
AND kl.page IS NOT DISTINCT FROM bl.page
AND kl.tuple IS NOT DISTINCT FROM bl.tuple
AND kl.pid != bl.pid
AND NOT kl.granted
JOIN pg_stat_activity AS blocking ON blocking.pid = kl.pid
WHERE NOT bl.granted;
-- Размеры баз данных
SELECT
datname,
pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;
# Устанавливаем postgres_exporter
wget https://github.com/prometheus-community/postgres_exporter/releases/latest/download/postgres_exporter-linux-amd64.tar.gz
tar xzf postgres_exporter-linux-amd64.tar.gz
sudo mv postgres_exporter /usr/local/bin/
# Создаём пользователя мониторинга
sudo -u postgres psql -c "
CREATE USER monitoring WITH PASSWORD 'MonitorP@ss!';
GRANT pg_monitor TO monitoring;"
# Создаём systemd unit
sudo cat > /etc/systemd/system/postgres-exporter.service <<EOF
[Unit]
Description=Prometheus PostgreSQL Exporter
After=postgresql.service
[Service]
Environment=DATA_SOURCE_NAME=postgresql://monitoring:MonitorP@ss!@localhost:5432/postgres?sslmode=disable
ExecStart=/usr/local/bin/postgres_exporter --web.listen-address=:9187
Restart=always
User=nobody
[Install]
WantedBy=multi-user.target
EOF
sudo systemctl daemon-reload
sudo systemctl enable --now postgres-exporter
Подключили postgres_exporter, настроили Prometheus и импортировали в Grafana готовый дашборд ID 9628 (PostgreSQL Database). На нём сразу видно всё важное: количество подключений, cache hit ratio, TPS, размер WAL, задержка репликации, bloat таблиц. Никакой магии — просто нормальная видимость.
