Database Admin Skill | Agent Skills

Database Admin

Audience: Rails operators managing PostgreSQL or SQLite in production. Goal: Provide ready-to-run commands for backup, monitoring, connection management, and emergency recovery.

Detailed PostgreSQL commands: references/postgresql.md. SQLite commands: references/sqlite.md.

PostgreSQL Quick Reference

| Task | Command | |------|---------| | Backup | pg_dump -Fc -Z9 dbname > backup.dump | | Restore | pg_restore -d dbname backup.dump | | Vacuum | VACUUM ANALYZE | | Kill query | SELECT pg_terminate_backend(pid) |

Monitoring Queries

-- Slow queries (requires pg_stat_statements)
SELECT calls, mean_exec_time, query FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;

-- Active connections
SELECT state, COUNT(*) FROM pg_stat_activity GROUP BY state;

-- Cache hit ratio (target > 99%)
SELECT sum(heap_blks_hit) / NULLIF(sum(heap_blks_hit + heap_blks_read), 0)
FROM pg_statio_user_tables;

-- Table bloat
SELECT tablename, n_dead_tup FROM pg_stat_user_tables
WHERE n_dead_tup > 1000 ORDER BY n_dead_tup DESC;

Connection Pooling

PgBouncer with pool_mode = transaction
Rails requires prepared_statements: false with PgBouncer

Tools

| Tool | Purpose | |------|---------| | pghero gem | Slow queries, missing indexes dashboard | | pg_stat_statements | Query performance tracking | | pganalyze | Automated index recommendations |

SQLite Quick Reference

Production PRAGMAs

ActiveRecord::Base.connection.execute("PRAGMA journal_mode=WAL")
ActiveRecord::Base.connection.execute("PRAGMA synchronous=NORMAL")
ActiveRecord::Base.connection.execute("PRAGMA busy_timeout=5000")
ActiveRecord::Base.connection.execute("PRAGMA cache_size=-64000")

Backup Strategy

ActiveRecord::Base.connection.execute("PRAGMA wal_checkpoint(TRUNCATE)")
FileUtils.cp(db_path, backup_path)

Maintenance

ActiveRecord::Base.connection.execute("VACUUM")
ActiveRecord::Base.connection.execute("ANALYZE")

Backup Schedule

| Strategy | Frequency | Retention | |----------|-----------|-----------| | Hourly | Every hour | 24 hours | | Daily | Midnight | 7 days | | Weekly | Sunday | 4 weeks | | Monthly | 1st of month | 12 months |

Test restores monthly. Untested backups don't exist.

Data Lifecycle

| Strategy | When | |----------|------| | Archival tables | Move old data to *_archive | | Table partitioning | Time-series data, instant partition drops | | Materialized views | Pre-compute expensive aggregations | | Rollups | Aggregate detail → summary tables |

Emergency Procedures (PostgreSQL)

-- Kill long queries
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE state = 'active' AND query_start < now() - interval '10 minutes';

-- Emergency read-only
ALTER DATABASE production SET default_transaction_read_only = on;

Output Schema

database_status:
  size_gb: number
  connections: { active: int, max: int }
  cache_hit_ratio: float    # 0.0–1.0
  dead_tuples: { total: int, tables: int }
issues:
  - title: string
    impact: critical | high | medium | low
    resolution: string      # specific commands
maintenance_recommendations:
  - action: string
    command: string
backup_status:
  last_backup: timestamp
  last_tested_restore: date

Always provide both PostgreSQL and SQLite alternatives where applicable.

Agent Skills: Database Admin

Install this agent skill to your local

Skill Files