Skip to content

EliasOulkadi/shokunin / .pack/skills/db-admin

db-admin

Unverified

PostgreSQL database administration — backup/restore (pg_dump, PITR, WAL archiving), health monitoring (connections, bloat, cache hit ratio, dead tuples), connection pooling (PgBouncer), replication (streaming, logical), vacuum/autovacuum tuning, and scheduled backups with retention. Use when user asks to backup a database, restore from backup, monitor database health, set up replication, or perform DBA tasks. Do NOT use for schema design (use db-sculptor), query optimization (use db-sculptor), or migration planning (use db-sculptor).

Database Administrator

PostgreSQL production operations: backup, restore, monitoring, replication, and maintenance.

Workflow

Step 1: Run health check

scripts/monitor-db.ps1 -Database myapp

Checks 7 metrics:

MetricWarningFail
Connection count> 80% of max> 95% of max
Active queries> 10> 20
Long-running (>5m)Any> 3
Cache hit ratio< 99%< 95%
Dead tuple ratio> 20%> 40%
Index usage (unused)> 5> 10

If any metric is FAIL: investigate immediately. See references/postgres-admin.md for diagnosis and fix procedures.

Step 2: Backup database

# Full backup
scripts/backup-db.ps1 -Database myapp

# Custom output directory
scripts/backup-db.ps1 -Database myapp -OutputDir D:\backups

# Exclude large log tables
scripts/backup-db.ps1 -Database myapp -ExcludeTables @("audit_logs", "analytics_events")

The script creates: myapp_2026-05-13_143022.dump (custom format, compressed) and verifies the backup by restoring to a temp database and running a count query.

Step 3: Set up scheduled backups

See assets/backup-schedule-template.ps1 for the schedule template.

# Schedule daily backup at 2 AM
schtasks /Create /SC DAILY /TN "DBBackup-myapp" /TR "powershell.exe -File C:\scripts\backup-schedule.ps1" /ST 02:00

Retention policy:

  • Daily: keep 7
  • Weekly: keep 4 (Sundays)
  • Monthly: keep 3 (1st of month)

Step 4: Configure connection pooling

# pgbouncer.ini for PostgreSQL pooling
[databases]
myapp = host=localhost port=5432 dbname=myapp

[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = scram-sha-256
pool_mode = transaction
default_pool_size = 25
max_client_conn = 100
max_db_connections = 50

Step 5: Set up replication (if needed)

See references/postgres-admin.md for streaming and logical replication setup.

Decision:

NeedReplication type
Read scaling + HAStreaming replication
Selective table syncLogical replication
Upgrade with minimal downtimeLogical replication
Cross-version replicationLogical replication

Error Handling

ScenarioCauseFix
Backup fails with "out of memory"Work_mem too high for dumpReduce work_mem, increase maintenance_work_mem
PgBouncer rejects connectionspool_size exhaustedIncrease default_pool_size, add more connections
Replication lag growingWAL generation > apply rateAdd replicas, increase max_wal_size, tune apply workers
Long-running query blocking vacuumsnapshot held too longKill with pg_terminate_backend, tune idle_in_transaction_session_timeout
WAL directory filling diskarchive_command failing or wal_keep_size too highFix archiving, reduce wal_keep_size, increase max_wal_size

Production Checklist

  • Automated daily backups with verification
  • Retention policy configured (7 daily, 4 weekly, 3 monthly)
  • PgBouncer with transaction pooling
  • Monitoring dashboard (connection count, cache hit, bloat)
  • Alerts for: connection > 80%, cache hit < 99%, replication lag > 60s
  • Autovacuum tuned (scale_factor, threshold, naptime)
  • WAL archiving for PITR
  • Connection limits per database
  • Statement timeout configured (30s default)
  • Backups stored in different location than DB server

Anti-Patterns

Anti-patternFix
No automated backupsSchedule pg_dump with retention
No backup verificationAlways restore-test backups
Default autovacuum settingsTune for your workload
Direct connections in productionAlways use PgBouncer
No replication monitoringCheck lag, WAL generation rate
Running out of disk for WALMonitor WAL directory, tune archiving

Streaming Replication Setup

Primary server (postgresql.conf)

wal_level = replica
max_wal_senders = 5
wal_keep_size = 1024    # MB of WAL to retain
hot_standby = on        # Allow read-only queries on replica

Primary server (pg_hba.conf)

host replication replicator 192.168.0.0/24 md5

Replica setup

# 1. Create base backup
pg_basebackup -h primary_host -D /var/lib/postgresql/data -U replicator -P -R

# 2. Start replica
pg_ctl start -D /var/lib/postgresql/data

# 3. Verify replication
psql -c "SELECT client_addr, state, sync_state FROM pg_stat_replication;"

PITR (Point-in-Time Recovery)

# On primary: configure WAL archiving
archive_mode = on
archive_command = 'cp %p /archive/%f'

# Take base backup
pg_basebackup -h localhost -D /backup/base -Ft -z -P

# Restore to point in time
# 1. Stop PostgreSQL
# 2. Restore base backup to data directory
# 3. Create recovery.signal file
# 4. Set recovery_target_time in postgresql.conf:
#    recovery_target_time = '2026-05-18 14:30:00'
# 5. Start PostgreSQL

Vacuum Strategy

-- Check table bloat
SELECT schemaname, relname, n_live_tup, n_dead_tup,
       round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;

-- Aggressive autovacuum for high-write tables
ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_cost_limit = 2000);

-- Manual VACUUM (non-blocking)
VACUUM (VERBOSE, ANALYZE) events;

-- VACUUM FULL (blocking, recovers disk space)
VACUUM FULL events;  -- Requires table lock. Only use during maintenance windows.

Monitoring Queries

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

-- Long-running queries (>5 min)
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity WHERE state != 'idle' AND now() - query_start > INTERVAL '5 minutes';

-- Cache hit ratio (should be >95%)
SELECT sum(blks_hit) * 100.0 / NULLIF(sum(blks_hit) + sum(blks_read), 0) AS cache_hit_ratio
FROM pg_stat_database WHERE datname = current_database();

-- Dead tuples accumulating
SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables WHERE n_dead_tup > 10000 ORDER BY n_dead_tup DESC;

Sources

  • PostgreSQL docs — Backup/Restore (postgresql.org/docs)
  • PgBouncer documentation
  • PostgreSQL wiki — Performance tuning
  • WAL archiving and PITR guide
  • Replication documentation
  • pganalyze — Monitoring and tuning

Checklist

  • Skill loads without errors in the AI agent
  • YAML frontmatter is valid (description, compatibility, audience)
  • Workflow section provides clear step-by-step instructions
  • Error handling section covers common failure modes
  • All referenced files (references/, scripts/, assets/) exist
  • Skill triggers correctly for intended use cases
  • No broken links or missing resources
deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall