13 · 数据库运维¶

PostgreSQL 文档：https://www.postgresql.org/docs/ Redis 文档：https://redis.io/docs/ PgBouncer 文档：https://www.pgbouncer.org/

1. PostgreSQL 运维¶

常用诊断查询¶

-- 活跃连接数 & 等待情况
SELECT state, wait_event_type, wait_event, count(*)
FROM pg_stat_activity
GROUP BY state, wait_event_type, wait_event
ORDER BY count DESC;

-- 慢查询（执行超 1 秒）
SELECT pid, now() - query_start as duration, query, state
FROM pg_stat_activity
WHERE state != 'idle' AND now() - query_start > interval '1 second'
ORDER BY duration DESC;

-- 表大小排名
SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 20;

-- 索引使用率（找未使用的索引）
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- 锁等待（排障关键）
SELECT bl.pid AS blocked_pid, a.query AS blocked_query,
       kl.pid AS blocking_pid, ka.query AS blocking_query
FROM pg_catalog.pg_locks bl
JOIN pg_catalog.pg_stat_activity a ON a.pid = bl.pid
JOIN pg_catalog.pg_locks kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
WHERE NOT bl.granted;

-- 终止指定 PID
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid = 12345;

-- 终止所有空闲超过 10 分钟的连接
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE state = 'idle' AND now() - state_change > interval '10 minutes';

PgBouncer 连接池配置¶

# /etc/pgbouncer/pgbouncer.ini
# 官方文档：https://www.pgbouncer.org/config.html

[databases]
igaming = host=rds-endpoint.amazonaws.com port=5432 dbname=igaming

[pgbouncer]
listen_port = 5432
listen_addr = 0.0.0.0
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction    # 事务级连接池（推荐，比 session 效率高）
max_client_conn = 1000     # 最大客户端连接
default_pool_size = 25     # 每个数据库+用户的池大小
reserve_pool_size = 5      # 保留连接（突发用）
server_idle_timeout = 600  # 服务端空闲连接超时

# 状态查看
PGPASSWORD=xxx psql -p 5432 -U pgbouncer pgbouncer -c "SHOW POOLS;"
PGPASSWORD=xxx psql -p 5432 -U pgbouncer pgbouncer -c "SHOW CLIENTS;"

备份脚本¶

#!/bin/bash
# pg_backup.sh - 每日全量备份到 S3

DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="igaming"
S3_BUCKET="s3://igaming-db-backups"

# 备份
PGPASSWORD=$DB_PASSWORD pg_dump \
  -h $DB_HOST -U $DB_USER -d $DB_NAME \
  --format=custom \
  --compress=9 \
  -f /tmp/${DB_NAME}_${DATE}.dump

# 上传 S3
aws s3 cp /tmp/${DB_NAME}_${DATE}.dump \
  ${S3_BUCKET}/postgres/${DATE}/${DB_NAME}.dump \
  --storage-class STANDARD_IA

# 清理本地
rm -f /tmp/${DB_NAME}_${DATE}.dump

# 验证
aws s3 ls ${S3_BUCKET}/postgres/ | tail -5
echo "备份完成：${DB_NAME}_${DATE}.dump"

2. Redis 运维¶

常用命令¶

redis-cli -h $REDIS_HOST -a $REDIS_PASSWORD

# 状态查看
INFO all                          # 全面状态
INFO memory                       # 内存使用
INFO replication                  # 主从状态
INFO stats                        # 统计信息

# 慢日志分析
SLOWLOG GET 10                    # 最近10条慢命令
SLOWLOG LEN                       # 慢日志数量
CONFIG SET slowlog-log-slower-than 1000  # 超过1ms记录

# Key 分析（生产谨慎使用）
DBSIZE                            # Key 总数
SCAN 0 COUNT 100 MATCH "odds:*"   # 安全遍历（代替 KEYS）

# 内存分析
MEMORY USAGE key:name             # 单个 key 内存占用
MEMORY DOCTOR                     # 内存问题诊断建议

Redis 集群管理¶

# 查看集群状态
redis-cli -h $REDIS_HOST -a $REDIS_PASSWORD cluster info
redis-cli -h $REDIS_HOST -a $REDIS_PASSWORD cluster nodes

# 检查集群健康
redis-cli --cluster check $REDIS_HOST:6379 -a $REDIS_PASSWORD

# 博彩场景 Key 设计规范
odds:{match_id}:{market}     TTL=5s    # 实时赔率
session:{session_id}          TTL=1800s # 用户 Session
ratelimit:{ip}:{minute}       TTL=60s   # 速率限制
user:{user_id}:wallet         TTL=300s  # 钱包缓存

官方文档¶

PostgreSQL 文档：https://www.postgresql.org/docs/current/
Redis 命令参考：https://redis.io/commands/
PgBouncer 文档：https://www.pgbouncer.org/
Redis University 免费课程：https://university.redis.com/
Percona 监控（PMM）：https://www.percona.com/software/database-tools/percona-monitoring-and-management
redis-insight（可视化）：https://github.com/RedisInsight/RedisInsight

最后更新：2025-04