423 lines
11 KiB
Go
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])
|
|
}
|