10.7 외부 모니터링 도구

# RHEL/Rocky 또는 PGDG
sudo dnf install -y postgres_exporter
# 또는 GitHub release에서 바이너리 받음

[Service]
User=postgres
Environment=DATA_SOURCE_NAME=postgresql:///?host=/var/run/postgresql&sslmode=disable
ExecStart=/usr/local/bin/postgres_exporter --web.listen-address=:9187
Restart=always

sudo systemctl enable --now postgres_exporter
curl http://localhost:9187/metrics | head

CREATE ROLE monitoring LOGIN PASSWORD 'secret';
GRANT pg_monitor TO monitoring;     -- PG 10+의 통합 권한 (9.1)

scrape_configs:
  - job_name: 'postgres'
    static_configs:
      - targets: ['db1.example.com:9187', 'db2.example.com:9187']
    scrape_interval: 30s

pg_replication_slots:
  query: |
    SELECT slot_name, slot_type, active::int,
           pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS lag_bytes
      FROM pg_replication_slots;
  metrics:
    - slot_name: { usage: LABEL }
    - slot_type: { usage: LABEL }
    - active: { usage: GAUGE, description: "Slot active 여부" }
    - lag_bytes: { usage: GAUGE, description: "WAL 지연 바이트" }

groups:
- name: postgres
  rules:
  - alert: PostgresDown
    expr: pg_up == 0
    for: 1m
    annotations:
      summary: "PostgreSQL down"

  - alert: ReplicationLagHigh
    expr: pg_replication_lag > 100*1024*1024   # 100MB
    for: 5m

  - alert: LongRunningTransaction
    expr: pg_stat_activity_max_tx_duration{state="active"} > 300
    for: 1m

docker run -d --name pgwatch2 \
  -p 3000:3000 -p 8080:8080 \
  -e PW2_TESTDB=true \
  cybertec/pgwatch2:latest

# datadog agent의 postgres integration
cat << EOF > /etc/datadog-agent/conf.d/postgres.d/conf.yaml
init_config:
instances:
  - host: localhost
    port: 5432
    username: datadog
    password: secret
    dbm: true     # Database Monitoring 활성
EOF