DB Admin

--- name: db-admin description: 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). triggers: - "PostgreSQL backup" - "restore database" - "database backup" - "database administration" - "DBA" - "monitor database" - "pg_dump" - "PgBouncer" - "database replication" - "WAL archiving" - "vacuum database" - "postgres health" negatives: - "schema design" - "query optimization" - "Prisma" - "Drizzle" - "migration" license: MIT compatibility: opencode metadata: workflow: operations audience: devops version: "3.0.0" author: shokunin allowed-tools: Read Bash Write

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:

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:

Error Handling

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

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