Database Administrator

数据库管理员

Purpose

用途

Provides senior-level database administration expertise for production database systems including PostgreSQL, MySQL, MongoDB, and enterprise databases. Specializes in high availability architectures, performance tuning, backup strategies, disaster recovery, and database security for mission-critical environments.

为PostgreSQL、MySQL、MongoDB及企业级数据库等生产数据库系统提供资深级别的数据库管理专业支持。专注于关键业务环境下的高可用架构、性能调优、备份策略、灾难恢复与数据库安全。

When to Use

适用场景

Setting up production databases with high availability and disaster recovery
Optimizing database performance (slow queries, indexing, configuration tuning)
Implementing backup and recovery strategies (PITR, cross-region backups)
Migrating databases (PostgreSQL, MySQL, MongoDB) to cloud or between versions
Hardening database security (encryption, access control, audit logging)
Troubleshooting database issues (locks, replication lag, corruption)
Designing database architectures for scalability and reliability

搭建具备高可用与灾难恢复能力的生产数据库
优化数据库性能（慢查询、索引、配置调优）
实施备份与恢复策略（PITR、跨区域备份）
迁移数据库（PostgreSQL、MySQL、MongoDB）至云端或进行版本升级
强化数据库安全（加密、访问控制、审计日志）
排查数据库问题（锁、复制延迟、数据损坏）
设计具备可扩展性与可靠性的数据库架构

Quick Start

快速开始

Invoke this skill when:

Setting up production databases with high availability and disaster recovery
Optimizing database performance (slow queries, indexing, configuration tuning)
Implementing backup and recovery strategies (PITR, cross-region backups)
Migrating databases (PostgreSQL, MySQL, MongoDB) to cloud or between versions
Hardening database security (encryption, access control, audit logging)
Troubleshooting database issues (locks, replication lag, corruption)

Do NOT invoke when:

Only application-level ORM queries need optimization (use backend-developer)
Data pipeline development (use data-engineer for ETL/ELT)
Data modeling and schema design for analytics (use data-engineer)
Database selection for new projects (use cloud-architect for strategy)
Simple SQL queries or data analysis (use data-analyst)

在以下场景调用此技能：

搭建具备高可用与灾难恢复能力的生产数据库
优化数据库性能（慢查询、索引、配置调优）
实施备份与恢复策略（PITR、跨区域备份）
迁移数据库（PostgreSQL、MySQL、MongoDB）至云端或进行版本升级
强化数据库安全（加密、访问控制、审计日志）
排查数据库问题（锁、复制延迟、数据损坏）

请勿在以下场景调用：

仅需优化应用层ORM查询（请使用backend-developer）
开发数据管道（请使用data-engineer处理ETL/ELT）
为分析场景设计数据模型与 schema
为新项目选择数据库（请使用cloud-architect制定策略）
简单SQL查询或数据分析（请使用data-analyst）

Decision Framework

决策框架

Database Selection

数据库选择

Use Case	Database	Why
Transactional (OLTP)	PostgreSQL	ACID, extensions, JSON support
High-read web apps	MySQL/MariaDB	Fast reads, mature replication
Flexible schema	MongoDB	Document model, horizontal scale
Key-value cache	Redis	Sub-ms latency, data structures
Time-series data	TimescaleDB/InfluxDB	Optimized for time-based queries
Analytics (OLAP)	Snowflake/BigQuery	Columnar, massive scale

适用场景	数据库	原因
事务处理（OLTP）	PostgreSQL	ACID特性、扩展支持、JSON兼容
高读量Web应用	MySQL/MariaDB	读取速度快、复制机制成熟
灵活Schema	MongoDB	文档模型、水平扩展
键值缓存	Redis	亚毫秒级延迟、丰富数据结构
时序数据	TimescaleDB/InfluxDB	针对时间维度查询优化
分析处理（OLAP）	Snowflake/BigQuery	列存储、海量扩展能力

High Availability Architecture

高可用架构

├─ Single Region HA?
│   ├─ Managed service → RDS Multi-AZ / Cloud SQL HA
│   │   Pros: Automatic failover, managed backups
│   │   Cost: 2x compute (standby instance)
│   │
│   └─ Self-managed → Patroni + etcd (PostgreSQL)
│       Pros: Full control, no vendor lock-in
│       Cost: Operational overhead
│
├─ Multi-Region HA?
│   ├─ Active-Passive → Cross-region read replicas
│   │   Pros: Simple, low cost
│   │   Cons: Manual failover, data lag
│   │
│   └─ Active-Active → CockroachDB / Spanner
│       Pros: True global distribution
│       Cons: Complexity, cost
│
└─ Horizontal Scaling?
    ├─ Read scaling → Read replicas
    ├─ Write scaling → Sharding (MongoDB, Vitess)
    └─ Both → Distributed SQL (CockroachDB, TiDB)

├─ Single Region HA?
│   ├─ Managed service → RDS Multi-AZ / Cloud SQL HA
│   │   Pros: Automatic failover, managed backups
│   │   Cost: 2x compute (standby instance)
│   │
│   └─ Self-managed → Patroni + etcd (PostgreSQL)
│       Pros: Full control, no vendor lock-in
│       Cost: Operational overhead
│
├─ Multi-Region HA?
│   ├─ Active-Passive → Cross-region read replicas
│   │   Pros: Simple, low cost
│   │   Cons: Manual failover, data lag
│   │
│   └─ Active-Active → CockroachDB / Spanner
│       Pros: True global distribution
│       Cons: Complexity, cost
│
└─ Horizontal Scaling?
    ├─ Read scaling → Read replicas
    ├─ Write scaling → Sharding (MongoDB, Vitess)
    └─ Both → Distributed SQL (CockroachDB, TiDB)

Backup Strategy Matrix

备份策略矩阵

RPO Requirement	Strategy	Implementation
< 1 minute	Synchronous replication	Patroni sync mode
< 5 minutes	Continuous WAL archiving	pg_basebackup + WAL-G
< 1 hour	Automated snapshots	RDS automated backups
< 24 hours	Daily backups	pg_dump + S3

RPO要求	策略	实现方式
< 1分钟	同步复制	Patroni同步模式
< 5分钟	持续WAL归档	pg_basebackup + WAL-G
< 1小时	自动快照	RDS自动备份
< 24小时	每日备份	pg_dump + S3

Performance Tuning Priorities

性能调优优先级

Query optimization (biggest impact, lowest cost)
Indexing strategy (moderate effort, high impact)
Configuration tuning (one-time, moderate impact)
Hardware upgrades (high cost, last resort)

查询优化（影响最大，成本最低）
索引策略（中等投入，高回报）
配置调优（一次性操作，中等影响）
硬件升级（成本高，最后选择）

Quality Checklist

质量检查清单

Production Readiness

生产就绪性

High availability configured (multi-AZ or multi-region)
Automated backups enabled (daily + continuous WAL)
Backup restoration tested (monthly disaster recovery drill)
Connection pooling configured (PgBouncer/ProxySQL)
Monitoring and alerting active (slow queries, replication lag)

已配置高可用（多AZ或多区域）
已启用自动备份（每日备份+持续WAL归档）
已测试备份恢复（每月灾难恢复演练）
已配置连接池（PgBouncer/ProxySQL）
已启用监控与告警（慢查询、复制延迟）

Performance

性能

Indexes created for all query patterns
Table statistics up-to-date (autovacuum tuned)
Query plans reviewed (no full table scans on large tables)
Connection pooling optimized (min/max pool size)
Database configuration tuned (shared_buffers, work_mem)

已为所有查询模式创建索引
表统计信息已更新（自动清理已调优）
已审核查询计划（大表无全表扫描）
连接池已优化（最小/最大连接数）
数据库配置已调优（shared_buffers、work_mem）

Security

安全

Encryption at rest enabled
Encryption in transit (SSL/TLS) enforced
Least privilege access (no superuser for applications)
Audit logging enabled (failed logins, DDL changes)
Regular security patching scheduled

已启用静态数据加密
已强制启用传输加密（SSL/TLS）
已实现最小权限访问（应用无超级用户权限）
已启用审计日志（登录失败、DDL变更）
已安排定期安全补丁更新

Disaster Recovery

灾难恢复

RTO/RPO documented and tested
Cross-region backups enabled
Failover procedure documented and tested
Data retention policy enforced
Point-in-time recovery validated

已记录并测试RTO/RPO
已启用跨区域备份
已记录并测试故障转移流程
已执行数据保留策略
已验证时点恢复（PITR）

Additional Resources

额外资源

Detailed Technical Reference: See REFERENCE.md
Code Examples & Patterns: See EXAMPLES.md

详细技术参考：参见REFERENCE.md
代码示例与模式：参见EXAMPLES.md

database-administrator

Original

Translation