version: "1.0.1"

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