database-admin

What this skill does

# 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

```sql
-- 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

```ruby
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

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

### Maintenance

```ruby
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)

```sql
-- 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

```yaml
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.