data-operations-safety

Data Operations Safety

References: Google "Site Reliability Engineering" Postmortem culture, AWS Well-Architected Framework, PostgreSQL Backup documentation, MySQL Operational Best Practices, GDPR / CCPA compliance specifications.

Applicable Scenarios

• Checks before production DDL, indexes, backfills, batch UPDATE / DELETE
• Data repair scripts, import/export, masking
• Backup, recovery, recovery drills, and verification
• Impact assessment for high-risk changes
• Output a pre-release database gate checklist
• Emergency data repair (production incidents)

Core Principles

1. 能恢复，才允许变更
   未验证恢复的备份 = 没有备份

1. Four-eyes principle Production DELETE / UPDATE / DROP must be confirmed by 2 people

2. Canary before full rollout 1 row → 100 rows → 10000 rows → all

3. Impact estimation must be done COUNT affected rows / table lock duration / disk increment

4. Every operation has an audit trail Who, when, on what, why, and with what result

5. Irreversible operations must be backed up DROP TABLE / DROP COLUMN / large DELETE

6. Masking is never reversible Production data in test / development environments must be masked

7. Operation scripts go into version control Do not write impromptu in the production console

## 操作前检查清单（必填）

```text
□ 1. 授权
  □ 操作目的
  □ 操作人 + 审批人
  □ 目标环境（dev / staging / prod）
  □ 业务方知悉

□ 2. 影响面
  □ 受影响表 / 行数
  □ 锁表预估时间
  □ 磁盘增量预估
  □ 复制延迟影响
  □ 受影响业务（哪些 API / 用户）

□ 3. 备份
  □ 最近备份时间（< 24h）
  □ 备份是否已验证恢复
  □ 备份恢复 RTO（多久能恢复）
  □ 必要时新建备份点

□ 4. 脚本质量
  □ 脚本幂等
  □ 脚本可暂停
  □ 脚本可恢复
  □ 分批 + 限速
  □ 进度可观测

□ 5. 监控
  □ 数据库 CPU / IO / 锁等待
  □ 主从复制延迟
  □ 磁盘空间
  □ 业务指标（错误率 / P99）

□ 6. 回滚
  □ 回滚条件清晰（错误率 > X / 延迟 > Y）
  □ 回滚步骤可执行
  □ 数据恢复路径

□ 7. 灰度
  □ 1 行测试（dry-run）
  □ 100 行
  □ 10000 行
  □ 全部

High-Risk Operation Checklist

Operation	Mandatory Checks	Canary Strategy
DDL Changes	Table lock risk, compatibility, rollback path	dev → staging → prod
Large Table Backfill	Batching, rate limiting, idempotency, progress logging	1k → 10k → 100k → all
Batch UPDATE	WHERE review, affected row count, backup	1 row → 100 → 10000 → all
Batch DELETE	Same as UPDATE + mandatory backup	Same as above
Index Changes	Index creation time, disk, write impact	CONCURRENTLY single table
Data Export	Authorization, masking, storage location, destruction policy	Small sample → full volume
Recovery Operation	Backup validity, recovery point, data consistency	Verify in an isolated environment first
TRUNCATE	Backup + three-person confirmation	None (irreversible)

Canary Execution Template

-- 阶段 1：DRY RUN（看影响行数）
BEGIN;
SELECT COUNT(*) FROM orders
WHERE status = 'unknown' AND created_at < '2025-01-01';
ROLLBACK;
-- 看到：影响 1234567 行

-- 阶段 2：1 行测试
BEGIN;
UPDATE orders SET status = 'archived'
WHERE id = (SELECT id FROM orders WHERE status = 'unknown' LIMIT 1);
-- 验证结果
SELECT * FROM orders WHERE status = 'archived' LIMIT 5;
COMMIT;

-- 阶段 3：100 行
BEGIN;
UPDATE orders SET status = 'archived'
WHERE id IN (
  SELECT id FROM orders
  WHERE status = 'unknown' AND created_at < '2025-01-01'
  LIMIT 100
);
COMMIT;

-- 阶段 4：分批全量（限速）
DO $$
DECLARE
  rows_updated INT;
  total INT := 0;
BEGIN
  LOOP
    UPDATE orders SET status = 'archived'
    WHERE id IN (
      SELECT id FROM orders
      WHERE status = 'unknown' AND created_at < '2025-01-01'
      LIMIT 1000
    );
    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    total := total + rows_updated;
    
    -- 进度
    RAISE NOTICE 'Updated total: %', total;
    
    EXIT WHEN rows_updated = 0;
    PERFORM pg_sleep(0.1);  -- 限速
  END LOOP;
END $$;

Backup Strategy

Three-Layer Backup

1. 物理备份（每天）
   - PostgreSQL: pg_basebackup / pgBackRest
   - MySQL: xtrabackup / mysqlbackup
   - 用途：完整恢复、克隆环境

2. 逻辑备份（每周或按需）
   - PostgreSQL: pg_dump
   - MySQL: mysqldump
   - 用途：单表恢复、跨版本迁移

3. 持续归档（实时）
   - PostgreSQL: WAL 归档（PITR 基础）
   - MySQL: binlog
   - 用途：Point-In-Time Recovery

Backup Verification (Critical)

#!/bin/bash
# 每月备份恢复演练

# 1. 在隔离环境恢复最近备份
restore_backup_to_test_env

# 2. 验证数据完整性
psql -h test_env -c "SELECT COUNT(*) FROM critical_tables"

# 3. 验证关键查询
psql -h test_env -c "SELECT COUNT(*) FROM orders WHERE created_at > now() - INTERVAL '1 day'"

# 4. 计算 RTO（恢复时间）
# 5. 计算 RPO（数据损失窗口）

# 6. 记录结果
echo "Backup test: PASS, RTO=15min, RPO=5min" >> /var/log/backup-test.log

Point-In-Time Recovery（PITR）

# PostgreSQL PITR：恢复到任意时间点
pg_basebackup -D /backup/base/

# WAL 持续归档（postgresql.conf）
archive_mode = on
archive_command = 'cp %p /backup/wal/%f'

# 恢复到 2026-05-18 14:30:00
restore_command = 'cp /backup/wal/%f %p'
recovery_target_time = '2026-05-18 14:30:00'

Data Export and Masking

Masking Rules

-- 不可逆脱敏脚本（生产 → 测试）

-- 邮箱（保留域名）
UPDATE users SET email = MD5(email) || '@test.example.com';

-- 手机号（替换中间 4 位）
UPDATE users SET phone = SUBSTRING(phone, 1, 3) || '****' || SUBSTRING(phone, 8);

-- 姓名（保留姓 + 假名）
UPDATE users SET name = SUBSTRING(name, 1, 1) || '****';

-- 身份证（替换中间）
UPDATE users SET id_card = SUBSTRING(id_card, 1, 6) || '********' || SUBSTRING(id_card, 15);

-- 地址（仅保留城市级）
UPDATE users SET address = REGEXP_REPLACE(address, '(.*?[市县区]).*', '\1');

-- 银行卡（仅保留前 6 + 后 4）
UPDATE users SET card_number = SUBSTRING(card_number, 1, 6) || '******' || SUBSTRING(card_number, -4);

-- 完全删除
UPDATE users SET 
  password = NULL,
  api_token = NULL,
  refresh_token = NULL,
  notes = NULL;  -- 用户备注可能含 PII

-- 跳过最近的活跃用户（避免泄露）
DELETE FROM users WHERE last_login_at > now() - INTERVAL '30 days';

Export Audit

-- 记录所有导出
CREATE TABLE export_audit (
  id bigserial PRIMARY KEY,
  exported_by varchar(64) NOT NULL,
  exported_at timestamptz DEFAULT now(),
  table_name varchar(64) NOT NULL,
  row_count bigint,
  destination varchar(255) NOT NULL,
  reason text,
  approved_by varchar(64) NOT NULL,
  retention_days integer DEFAULT 30
);

Data Repair (Production Incidents)

Repair Process (Mandatory)

1. 评估影响
   - 影响行数 / 用户数
   - 业务影响
   - 是否需要客户沟通

2. 备份当前状态
   - 即使数据已损坏，备份是修复依据
   - CREATE TABLE backup_before_fix AS SELECT * FROM ...

3. 在 staging 验证修复脚本
   - 完整 dry-run

4. 双人复核
   - 第二人 review SQL
   - 第二人执行（如可能）

5. 生产执行（灰度）
   - 1 行 → 100 行 → 全量

6. 验证
   - 修复后状态符合预期
   - 业务指标恢复

7. 通知
   - 业务方
   - 受影响用户（如适用）

8. 复盘
   - 写 postmortem
   - 沉淀到 field-journal
   - 评估流程改进

Repair Script Example

-- 场景：bug 导致 500 个订单状态错误

-- 阶段 1：备份
CREATE TABLE _backup_orders_2026_05_18 AS
SELECT * FROM orders
WHERE id IN (...)  -- 受影响 ID 列表
;

-- 阶段 2：dry run
SELECT id, status, expected_status
FROM (
  SELECT id, status,
    CASE WHEN ... THEN 'paid' ELSE 'cancelled' END AS expected_status
  FROM orders
  WHERE id IN (...)
) sub
WHERE status != expected_status;
-- 验证 500 行符合预期

-- 阶段 3：1 行修复
UPDATE orders SET status = 'paid'
WHERE id = (SELECT id FROM ... LIMIT 1);
-- 业务验证：1 个用户的订单状态正常

-- 阶段 4：批量
BEGIN;
UPDATE orders SET status = ... WHERE id IN (...);
-- COMMIT 前再 SELECT 验证
SELECT COUNT(*), status FROM orders WHERE id IN (...) GROUP BY status;
COMMIT;

-- 阶段 5：清理
-- 30 天后删除备份表
DROP TABLE _backup_orders_2026_05_18;

Operation Command Template

Must Run Before Changing SQL

-- PostgreSQL：dry-run 看影响
EXPLAIN (ANALYZE FALSE)
UPDATE orders SET status = 'archived'
WHERE created_at < '2025-01-01';

-- 看 actual rows 估算

-- 实际跑：包在事务里看结果
BEGIN;
UPDATE orders SET status = 'archived'
WHERE created_at < '2025-01-01' LIMIT 100;
SELECT COUNT(*) FROM orders WHERE status = 'archived';
ROLLBACK;  -- 或 COMMIT

MySQL Safe Mode

-- 防止 UPDATE/DELETE 不带 WHERE
SET sql_safe_updates = 1;

-- 现在以下会报错：
DELETE FROM orders;  -- ERROR 1175

-- 必须明确条件
DELETE FROM orders WHERE id = 123;  -- OK

Supporting Templates

• templates/db-change-safety-checklist-template.md — complete pre-operation checks + impact assessment + backup confirmation + canary plan + rollback + audit

Quality Self-Check

□ 明确环境、授权范围和负责人
□ 双人复核（生产 DELETE / DROP）
□ 备份和恢复验证
□ 估算影响行数、锁、磁盘、耗时
□ 脚本幂等、分批、可重试、可停止
□ 设置监控和回滚条件
□ 灰度执行（1 → 100 → 10k → 全部）
□ 避免泄露敏感数据
□ 记录执行结果和复盘项
□ 操作脚本进版本控制
□ 不在 production console 即兴写 SQL
□ 修复脚本在 staging 验证过

Common Pitfalls

1. UPDATE/DELETE without WHERE review——Wipe the entire table in one second
2. Backups exist but recovery has never been verified——When recovery is actually needed, the backup is corrupted
3. Backfill script cannot be paused——Cannot resume after failure
4. Production data exported without masking——PII leak
5. No destruction policy for exported production data——PII retained permanently in test environments
6. DDL risk is left solely to the application release process——DBAs have no authority to review SQL
7. Fix scripts leave no backup——Cannot roll back a bad fix
8. TRUNCATE without three-person confirmation——Irreversible
9. DROP TABLE treated as an ordinary operation——Cascading deletes cause disaster
10. Adding an index to a large table without monitoring——Disk full / replication broken
11. Writing SQL on the fly in the production console——No version control, no audit
12. Fix script has no dry run——Full-scale errors immediately
13. Cross-tenant operations without audit——Difficult to trace later
14. Masking script is incomplete——Nicknames / remarks / metadata still contain PII

Collaboration with other skills

上游：
  migration-rollout → 提供迁移计划
  consistency-multitenancy → 提供租户和权限边界
  schema-design → 提供 DDL

下游：
  devops-engineer → 执行窗口、监控、CI/CD
  sre-ops → 监控、告警、恢复
  security-engineer → 敏感数据和权限审查
  field-journal → 记录真实操作经验
  qa-engineer → 数据修复后的回归

Related references

• references/data-operations-safety-guide.md — SRE Postmortem, production incident case library, backup and recovery drills, complete masking script library, two-person review process