🛡️ 生产数据库备份与恢复完全指南

从零理解——确保每一步操作都可撤回的 5 层防线

更新于 2026-02-16 · 基于 Celoria 多租户 PostgreSQL 架构

前言：为什么"可恢复"如此重要？

在 SaaS 产品中，数据是一切的根基。一次误操作可能导致：

• 误删数据：员工不小心批量删除了客户记录
• 代码 Bug：新版本上线的迁移脚本有错误，改坏了数据
• 恶意操作：被入侵后数据被篡改
• 硬件故障：服务器磁盘损坏
• 人为失误：在生产环境执行了开发环境的 SQL

每一种情况需要不同的恢复手段，这就是为什么需要多层防线。

全局鸟瞰：5 层防线模型

什么是 WAL？

WAL（Write-Ahead Log，预写式日志）是 PostgreSQL 的核心机制：

为什么要这样设计？

• 性能：WAL 是顺序写入（像往笔记本末尾追加），比随机修改数据文件快得多
• 安全：如果服务器突然断电，重启后 PostgreSQL 会"重放"WAL 中已提交但未刷盘的记录，保证数据不丢失
• 可复制：把 WAL 发送给另一台服务器，就实现了主从复制
• 可恢复：把 WAL 存档起来，就能实现时间点恢复（PITR）

什么是 PITR？

PITR（Point-in-Time Recovery）就是利用"基础备份 + WAL 归档"实现的"时间机器"。

工具对比

实际配置方法

第一步：修改 PostgreSQL 配置（启用 WAL 归档）

# postgresql.conf（通常在 /etc/postgresql/15/main/ 或数据目录下）

# 1. 设置 WAL 级别为 replica（记录足够的信息用于恢复和复制）
wal_level = replica

# 2. 开启归档模式（将 WAL 文件自动存档到指定位置）
archive_mode = on

# 3. 归档命令（将 WAL 文件复制到归档目录）
#    %p = WAL 文件的完整路径
#    %f = WAL 文件名
archive_command = 'cp %p /var/lib/postgresql/wal-archive/%f'

# 4. 允许流式复制连接（用于热备份）
max_wal_senders = 3

第二步：创建基础备份

# 使用 pg_basebackup 创建一个完整的物理备份
pg_basebackup \
  -h localhost \
  -p 5433 \
  -U postgres \
  -D /var/backups/pg-base-$(date +%Y%m%d) \
  -Ft \           # tar 格式输出
  -z \            # gzip 压缩
  -P              # 显示进度

第三步：恢复到指定时间点

# 1. 停止 PostgreSQL
sudo systemctl stop postgresql

# 2. 清空当前数据目录（危险操作！确保有备份）
rm -rf /var/lib/postgresql/15/main/*

# 3. 从基础备份恢复
tar xzf /var/backups/pg-base-20260216/base.tar.gz -C /var/lib/postgresql/15/main/

# 4. 创建恢复配置文件
cat > /var/lib/postgresql/15/main/recovery.signal

# 5. 在 postgresql.conf 中指定恢复目标
# restore_command = 'cp /var/lib/postgresql/wal-archive/%f %p'
# recovery_target_time = '2026-02-16 10:15:00+00'  ← 恢复到这个时间点

# 6. 启动 PostgreSQL（会自动开始恢复流程）
sudo systemctl start postgresql

pgbackrest restore \
  --stanza=celoria \
  --type=time \
  --target="2026-02-16 10:15:00+00"

物理备份 vs 逻辑备份

结论：两者不是替代关系，而是互补关系。物理备份做全局兜底，逻辑备份做精细恢复。

Celoria 现有体系分析

缺口与改进

psql -c "DROP SCHEMA IF EXISTS tenant_xxx CASCADE"

迁移是什么？

在 Celoria 项目中，我们使用 node-pg-migrate 管理数据库迁移。每个迁移文件包含两个函数：

// 一个典型的迁移文件结构

// exports.up = 向前迁移（应用变更）
exports.up = (pgm) => {
  pgm.createTable('appointments', {
    id: { type: 'uuid', primaryKey: true },
    guest_name: { type: 'varchar(100)', notNull: true },
    start_time: { type: 'timestamptz', notNull: true },
  });
};

// exports.down = 回退迁移（撤销变更）
exports.down = (pgm) => {
  pgm.dropTable('appointments');
};

不可逆迁移的安全处理

有些数据库变更天然是不可逆的。比如：

# 在部署脚本中

# 第一步：迁移前备份
pg_dump -Fc -f pre_migration_$(date +%Y%m%d_%H%M%S).dump

# 第二步：执行迁移
npm run migrate:up

# 第三步：验证迁移成功
npm run test:integration

# 如果验证失败，可以：
# 方案 A: npm run migrate:down（回退迁移）
# 方案 B: pg_restore pre_migration_xxx.dump（从备份恢复）

前三层都是数据库级别的保护，但有些场景用数据库恢复是"杀鸡用牛刀"：

• 用户不小心删了一个客户——你不需要恢复整个数据库，只需要"撤销删除"
• 有人改错了一条记录——你不需要回滚到昨天，只需要"看看改之前是什么，改回来"

这就是应用级保护要解决的问题。

软删除（Soft Delete）

-- ❌ 硬删除：数据直接消失，无法恢复
DELETE FROM guests WHERE id = 'abc-123';

-- ✅ 软删除：只是标记为"已删除"，数据还在
UPDATE guests
SET deleted_at = NOW(),
    deleted_by = 'employee-456'
WHERE id = 'abc-123';

-- 恢复？直接把标记清掉
UPDATE guests
SET deleted_at = NULL,
    deleted_by = NULL
WHERE id = 'abc-123';

行级变更审计（Change Data Capture）

-- 创建变更日志表
CREATE TABLE data_change_log (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  table_name  TEXT NOT NULL,        -- 哪张表
  record_id   TEXT NOT NULL,        -- 哪条记录
  operation   TEXT NOT NULL,        -- INSERT / UPDATE / DELETE
  old_data    JSONB,                -- 修改前的完整行（JSON 格式）
  new_data    JSONB,                -- 修改后的完整行
  changed_by  VARCHAR(255),         -- 谁改的
  changed_at  TIMESTAMPTZ DEFAULT NOW()  -- 什么时候改的
);

-- 用触发器自动记录（以 guests 表为例）
CREATE OR REPLACE FUNCTION log_guest_changes()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'UPDATE' THEN
    INSERT INTO data_change_log (table_name, record_id, operation, old_data, new_data, changed_by)
    VALUES ('guests', OLD.id, 'UPDATE', row_to_json(OLD)::jsonb, row_to_json(NEW)::jsonb,
            current_setting('app.current_user', true));
  ELSIF TG_OP = 'DELETE' THEN
    INSERT INTO data_change_log (table_name, record_id, operation, old_data, changed_by)
    VALUES ('guests', OLD.id, 'DELETE', row_to_json(OLD)::jsonb,
            current_setting('app.current_user', true));
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER guests_change_trigger
  AFTER UPDATE OR DELETE ON guests
  FOR EACH ROW EXECUTE FUNCTION log_guest_changes();

-- 场景：有人误改了 guest abc-123 的手机号，要恢复

-- 1. 查看变更历史
SELECT operation, old_data->>'phone' AS old_phone, new_data->>'phone' AS new_phone,
       changed_by, changed_at
FROM data_change_log
WHERE table_name = 'guests' AND record_id = 'abc-123'
ORDER BY changed_at DESC
LIMIT 5;

-- 结果：
-- UPDATE | 1234567890 | 0000000000 | employee-456 | 2026-02-16 10:15:00

-- 2. 从 old_data 中恢复
UPDATE guests
SET phone = '1234567890'   -- 从 old_data 中读取原始值
WHERE id = 'abc-123';

事务安全网

// 对高风险批量操作使用事务 + 预览确认模式

async function batchUpdatePrices(req, services) {
  // 开启事务
  return await tenantDb.transaction(req, async (client) => {

    // 第一步：执行变更（还没提交）
    for (const svc of services) {
      await client.query(
        'UPDATE services SET price = $1 WHERE id = $2',
        [svc.newPrice, svc.id]
      );
    }

    // 第二步：查询变更后的结果
    const preview = await client.query(
      'SELECT id, name, price FROM services WHERE id = ANY($1)',
      [services.map(s => s.id)]
    );

    // 第三步：返回预览（事务尚未提交）
    // 如果用户确认，事务自动提交
    // 如果代码抛出异常或用户取消，事务自动回滚
    return preview.rows;

  }); // ← 事务在这里自动提交或回滚
}

DR Plan 的核心是两个指标：

灾难恢复演练清单

恢复场景决策矩阵

遇到问题时，不同场景应该使用不同的恢复方式。以下是速查表：

Celoria 优先级建议