Files
2026-10-30 16:35:46 +02:00

423 lines
11 KiB
Go

package collector
import (
"context"
"database/sql"
"fmt"
"time"
_ "github.com/lib/pq"
)
// Metrics holds all collected PostgreSQL metrics
type Metrics struct {
// Identity
DatabaseName string `json:"database_name"`
Host string `json:"host"`
CollectedAt int64 `json:"collected_at"`
// Availability
Status string `json:"status"` // "UP" or "DOWN"
LatencyMs float64 `json:"latency_ms"`
Error string `json:"error,omitempty"`
// Version & Uptime
Version string `json:"version"`
UptimeSeconds float64 `json:"uptime_seconds"`
// Connections
ActiveConns int `json:"active_connections"`
IdleConns int `json:"idle_connections"`
TotalConns int `json:"total_connections"`
MaxConns int `json:"max_connections"`
ConnUsagePct float64 `json:"connection_usage_pct"`
// Queries
ActiveQueries int `json:"active_queries"`
LongRunning int `json:"long_running_queries"`
WaitingQueries int `json:"waiting_queries"`
// Database Size
DatabaseSizeBytes int64 `json:"database_size_bytes"`
DatabaseSizeHuman string `json:"database_size_human"`
// Transactions
TxCommit int64 `json:"tx_commit"`
TxRollback int64 `json:"tx_rollback"`
TxTotal int64 `json:"tx_total"`
// Cache
CacheHitRatio float64 `json:"cache_hit_ratio"`
// Locks
ActiveLocks int `json:"active_locks"`
WaitingLocks int `json:"waiting_locks"`
Deadlocks int64 `json:"deadlocks"`
// Replication
ReplicationLagMs float64 `json:"replication_lag_ms"`
IsReplica bool `json:"is_replica"`
// Row Activity
TupInserted int64 `json:"tup_inserted"`
TupUpdated int64 `json:"tup_updated"`
TupDeleted int64 `json:"tup_deleted"`
TupFetched int64 `json:"tup_fetched"`
// Block I/O
BlksRead int64 `json:"blks_read"`
BlksHit int64 `json:"blks_hit"`
// Temp Files
TempFiles int64 `json:"temp_files"`
TempBytes int64 `json:"temp_bytes"`
// Table Stats (top 10 by size)
TableStats []TableStat `json:"table_stats"`
// Active Queries (live)
RunningQueries []RunningQuery `json:"running_queries"`
}
// TableStat holds per-table statistics
type TableStat struct {
SchemaName string `json:"schema_name"`
TableName string `json:"table_name"`
SizeBytes int64 `json:"size_bytes"`
SizeHuman string `json:"size_human"`
RowEstimate int64 `json:"row_estimate"`
DeadTuples int64 `json:"dead_tuples"`
LastVacuum string `json:"last_vacuum"`
LastAutovacuum string `json:"last_autovacuum"`
}
// RunningQuery holds info about a currently running query
type RunningQuery struct {
PID int `json:"pid"`
DurationMs float64 `json:"duration_ms"`
State string `json:"state"`
Query string `json:"query"`
WaitEvent string `json:"wait_event"`
Username string `json:"username"`
}
// PostgresCollector collects metrics from a PostgreSQL database
type PostgresCollector struct {
DSN string
DBName string
db *sql.DB
}
// NewPostgresCollector creates a new collector for the given DSN
func NewPostgresCollector(dsn string) (*PostgresCollector, error) {
db, err := sql.Open("postgres", dsn)
if err != nil {
return nil, fmt.Errorf("failed to open connection: %w", err)
}
db.SetMaxOpenConns(3)
db.SetMaxIdleConns(1)
db.SetConnMaxLifetime(5 * time.Minute)
// Extract database name from DSN
dbName := extractDBName(dsn)
return &PostgresCollector{
DSN: dsn,
DBName: dbName,
db: db,
}, nil
}
// Collect gathers all metrics from the PostgreSQL instance
func (c *PostgresCollector) Collect() *Metrics {
m := &Metrics{
DatabaseName: c.DBName,
CollectedAt: time.Now().Unix(),
}
ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
defer cancel()
// 1. Status & Latency
start := time.Now()
err := c.db.PingContext(ctx)
m.LatencyMs = float64(time.Since(start).Microseconds()) / 1000.0
if err != nil {
m.Status = "DOWN"
m.Error = err.Error()
return m
}
m.Status = "UP"
// 2. Version
c.collectVersion(ctx, m)
// 3. Uptime
c.collectUptime(ctx, m)
// 4. Connections
c.collectConnections(ctx, m)
// 5. Queries
c.collectQueries(ctx, m)
// 6. Database Size
c.collectDatabaseSize(ctx, m)
// 7. Transactions & Cache
c.collectTransactions(ctx, m)
// 8. Locks
c.collectLocks(ctx, m)
// 9. Replication
c.collectReplication(ctx, m)
// 10. Row Activity & I/O
c.collectRowActivity(ctx, m)
// 11. Table Stats
c.collectTableStats(ctx, m)
// 12. Running Queries
c.collectRunningQueries(ctx, m)
return m
}
func (c *PostgresCollector) collectVersion(ctx context.Context, m *Metrics) {
_ = c.db.QueryRowContext(ctx, "SELECT version()").Scan(&m.Version)
}
func (c *PostgresCollector) collectUptime(ctx context.Context, m *Metrics) {
var startTime time.Time
err := c.db.QueryRowContext(ctx, "SELECT pg_postmaster_start_time()").Scan(&startTime)
if err == nil {
m.UptimeSeconds = time.Since(startTime).Seconds()
}
}
func (c *PostgresCollector) collectConnections(ctx context.Context, m *Metrics) {
// Active connections
_ = c.db.QueryRowContext(ctx,
"SELECT count(*) FROM pg_stat_activity WHERE state = 'active'").Scan(&m.ActiveConns)
// Idle connections
_ = c.db.QueryRowContext(ctx,
"SELECT count(*) FROM pg_stat_activity WHERE state = 'idle'").Scan(&m.IdleConns)
// Total connections
_ = c.db.QueryRowContext(ctx,
"SELECT count(*) FROM pg_stat_activity").Scan(&m.TotalConns)
// Max connections
var maxStr string
err := c.db.QueryRowContext(ctx, "SHOW max_connections").Scan(&maxStr)
if err == nil {
fmt.Sscanf(maxStr, "%d", &m.MaxConns)
}
// Connection usage percentage
if m.MaxConns > 0 {
m.ConnUsagePct = float64(m.TotalConns) / float64(m.MaxConns) * 100.0
}
}
func (c *PostgresCollector) collectQueries(ctx context.Context, m *Metrics) {
// Active queries (excluding our own)
_ = c.db.QueryRowContext(ctx,
`SELECT count(*) FROM pg_stat_activity
WHERE state = 'active' AND pid != pg_backend_pid()`).Scan(&m.ActiveQueries)
// Long running queries (> 5 minutes)
_ = c.db.QueryRowContext(ctx,
`SELECT count(*) FROM pg_stat_activity
WHERE state = 'active'
AND query_start < NOW() - INTERVAL '5 minutes'
AND pid != pg_backend_pid()`).Scan(&m.LongRunning)
// Waiting queries
_ = c.db.QueryRowContext(ctx,
`SELECT count(*) FROM pg_stat_activity
WHERE wait_event IS NOT NULL
AND state = 'active'`).Scan(&m.WaitingQueries)
}
func (c *PostgresCollector) collectDatabaseSize(ctx context.Context, m *Metrics) {
_ = c.db.QueryRowContext(ctx,
"SELECT pg_database_size(current_database())").Scan(&m.DatabaseSizeBytes)
m.DatabaseSizeHuman = humanizeBytes(m.DatabaseSizeBytes)
}
func (c *PostgresCollector) collectTransactions(ctx context.Context, m *Metrics) {
_ = c.db.QueryRowContext(ctx,
`SELECT xact_commit, xact_rollback,
CASE WHEN blks_hit + blks_read > 0
THEN round(blks_hit::numeric / (blks_hit + blks_read) * 100, 2)
ELSE 0 END,
blks_read, blks_hit,
temp_files, temp_bytes
FROM pg_stat_database WHERE datname = current_database()`).
Scan(&m.TxCommit, &m.TxRollback, &m.CacheHitRatio,
&m.BlksRead, &m.BlksHit, &m.TempFiles, &m.TempBytes)
m.TxTotal = m.TxCommit + m.TxRollback
}
func (c *PostgresCollector) collectLocks(ctx context.Context, m *Metrics) {
_ = c.db.QueryRowContext(ctx,
"SELECT count(*) FROM pg_locks WHERE granted = true").Scan(&m.ActiveLocks)
_ = c.db.QueryRowContext(ctx,
"SELECT count(*) FROM pg_locks WHERE granted = false").Scan(&m.WaitingLocks)
_ = c.db.QueryRowContext(ctx,
"SELECT deadlocks FROM pg_stat_database WHERE datname = current_database()").Scan(&m.Deadlocks)
}
func (c *PostgresCollector) collectReplication(ctx context.Context, m *Metrics) {
var inRecovery bool
err := c.db.QueryRowContext(ctx, "SELECT pg_is_in_recovery()").Scan(&inRecovery)
if err != nil {
return
}
m.IsReplica = inRecovery
if inRecovery {
// This is a replica — get replication lag
var lagInterval *float64
err = c.db.QueryRowContext(ctx,
`SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) * 1000`).Scan(&lagInterval)
if err == nil && lagInterval != nil {
m.ReplicationLagMs = *lagInterval
}
}
}
func (c *PostgresCollector) collectRowActivity(ctx context.Context, m *Metrics) {
_ = c.db.QueryRowContext(ctx,
`SELECT tup_inserted, tup_updated, tup_deleted, tup_fetched
FROM pg_stat_database WHERE datname = current_database()`).
Scan(&m.TupInserted, &m.TupUpdated, &m.TupDeleted, &m.TupFetched)
}
func (c *PostgresCollector) collectTableStats(ctx context.Context, m *Metrics) {
rows, err := c.db.QueryContext(ctx,
`SELECT schemaname, relname,
pg_total_relation_size(relid) as size_bytes,
n_live_tup, n_dead_tup,
COALESCE(last_vacuum::text, 'never'),
COALESCE(last_autovacuum::text, 'never')
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10`)
if err != nil {
return
}
defer rows.Close()
for rows.Next() {
var t TableStat
if err := rows.Scan(&t.SchemaName, &t.TableName, &t.SizeBytes,
&t.RowEstimate, &t.DeadTuples, &t.LastVacuum, &t.LastAutovacuum); err != nil {
continue
}
t.SizeHuman = humanizeBytes(t.SizeBytes)
m.TableStats = append(m.TableStats, t)
}
}
func (c *PostgresCollector) collectRunningQueries(ctx context.Context, m *Metrics) {
rows, err := c.db.QueryContext(ctx,
`SELECT pid,
EXTRACT(EPOCH FROM (now() - query_start)) * 1000 as duration_ms,
COALESCE(state, 'unknown'),
LEFT(COALESCE(query, ''), 200),
COALESCE(wait_event, ''),
COALESCE(usename, '')
FROM pg_stat_activity
WHERE state != 'idle'
AND pid != pg_backend_pid()
AND query NOT LIKE '%pg_stat_activity%'
ORDER BY query_start ASC
LIMIT 20`)
if err != nil {
return
}
defer rows.Close()
for rows.Next() {
var q RunningQuery
if err := rows.Scan(&q.PID, &q.DurationMs, &q.State, &q.Query, &q.WaitEvent, &q.Username); err != nil {
continue
}
m.RunningQueries = append(m.RunningQueries, q)
}
}
// Close cleans up the database connection
func (c *PostgresCollector) Close() error {
if c.db != nil {
return c.db.Close()
}
return nil
}
// extractDBName attempts to extract the database name from a DSN
func extractDBName(dsn string) string {
// Try to parse key=value format
for _, part := range splitDSN(dsn) {
if len(part) > 7 && part[:7] == "dbname=" {
return part[7:]
}
}
// Fallback: use last path component of URL format
for i := len(dsn) - 1; i >= 0; i-- {
if dsn[i] == '/' {
name := dsn[i+1:]
// Remove query parameters
for j, c := range name {
if c == '?' {
name = name[:j]
break
}
}
if name != "" {
return name
}
}
}
return "postgres"
}
func splitDSN(dsn string) []string {
var parts []string
current := ""
for _, c := range dsn {
if c == ' ' {
if current != "" {
parts = append(parts, current)
current = ""
}
} else {
current += string(c)
}
}
if current != "" {
parts = append(parts, current)
}
return parts
}
func humanizeBytes(b int64) string {
const unit = 1024
if b < unit {
return fmt.Sprintf("%d B", b)
}
div, exp := int64(unit), 0
for n := b / unit; n >= unit; n /= unit {
div *= unit
exp++
}
return fmt.Sprintf("%.1f %cB", float64(b)/float64(div), "KMGTPE"[exp])
}