database-admin_skill

Overview

This skill manages database operations with a focus on reliability, recoverability, and operational automation. It handles backups, replication, user permissions, performance monitoring, maintenance, and disaster recovery planning. Use it proactively for setup, ongoing operations, or emergency recovery. Deliverables include scripts, configuration guidance, monitoring queries, and runbooks.

How this skill works

I inspect current database topology, configuration, and operational metrics to produce actionable changes and automation. I generate backup scripts with retention policies, replication configuration, connection pooling setup, and monitoring queries with alert thresholds. I create a maintenance schedule (vacuum/analyze/optimize) and a disaster recovery runbook that includes RTO/RPO, automated restore steps, and manual escalation procedures. I also produce a least-privilege user permission matrix and recommended automation for routine tasks.

When to use it

• Setting up a new production or staging database cluster
• Implementing backups, retention, and regular recovery testing
• Configuring replication or reconfiguring failover/HA
• Troubleshooting performance issues or tuning queries
• Preparing or executing disaster recovery and restore

Best practices

• Automate routine maintenance (vacuum/analyze, backups, indexing) and run in maintenance windows
• Test backups regularly; validate restores against known datasets
• Monitor key metrics: connections, locks, replication lag, CPU, I/O, and slow queries
• Apply least-privilege access with a documented permission matrix and role separation
• Plan capacity ahead of limits and implement connection pooling to reduce resource pressure
• Document 3am runbooks with step-by-step automated and manual recovery procedures

Example use cases

• Create a backup script family with incremental/full rotation and retention policy for a PostgreSQL cluster
• Configure master-slave or multi-master replication with monitoring and automated failover scripts
• Design connection pooling (pgbouncer or equivalent) and tune pool sizes for a web application
• Produce monitoring queries and alert thresholds for replication lag, long locks, and slow queries
• Build a disaster recovery runbook that defines RTO/RPO, automated restore commands, and manual escalation steps