变更概述
- 变更类型与描述
- 类型：结构化数据库变更（分区改造、索引优化、函数并行化、安全加固）
- 内容：PostgreSQL 13 预发布环境（staging）对日志库 events 表进行单表→按日范围分区改造；用原生分区替代触发器分片；新增复合索引 (user_id, created_at) 与部分索引 WHERE status='active'；历史数据分区重写并进行行数/校验和一致性校验；聚合报表函数并行友好重写；校正序列与默认值；最小权限角色与审计日志启用；准备回滚快照
- 影响系统清单
- 事件采集/写入服务（依赖 events 表 INSERT）
- 分析/报表作业（依赖对 events 的聚合查询与函数）
- ETL/离线导出作业（全表/分区扫描）
- BI/可视化查询（基于 user_id/created_at 的查询）
- 运维审计与监控组件（依赖数据库审计日志/pg_stat 数据）
- 预期业务价值
- 写入与查询性能提升（分区裁剪、索引精准使用、并行聚合）
- 历史数据生命周期管理优化（分区级维护/归档/删除）
- 降低维护成本（去除触发器分片逻辑，统一原生分区）
- 提升合规性（最小权限与审计）
风险评估
- 风险等级评估
- 数据一致性风险：中（迁移与校验需严格执行）
- 性能回退风险：中（索引选择与并行执行计划需验证）
- 兼容性风险：中（原有触发器/函数/ETL 依赖需适配）
- 安全合规风险：低（启用审计、最小权限，严格变更窗口）
- 变更可回滚性：可回滚（保留旧表、快照/备份）
- 缓解措施
- 严格前置依赖检查：触发器、外键、视图、函数依赖清单化
- 分阶段校验：分区级别行数/校验和对比、查询计划回归检查
- 索引在切换后即刻验证使用情况（pg_stat_user_indexes、EXPLAIN）
- DDL 全程变更脚本化，关键检查点人工签署后继续
- 预创建默认分区捕获越界写入，避免写入失败
- 应急预案
- 保留 events_legacy（重命名旧表），可在分钟级切回
- 保留增量数据回迁脚本（新→旧）避免数据丢失
- 可选：pgaudit 不可用时使用会话级日志作为替代
- 预制回滚脚本（见回滚计划）
实施计划
- 阶段划分与时间线（以检查点驱动，逐步推进）
- 准备阶段（T0 前）
- 确认 pgaudit 已在 shared_preload_libraries 中启用（若未启用，安排单独维护窗口重启）
- 导出当前依赖清单、统计信息与基线性能
- 生成覆盖整个历史数据范围的日分区创建脚本
- 架构搭建阶段（T0）
- 创建分区父表、默认分区与权限框架
- 创建最小权限角色、绑定必要对象
- 数据迁移阶段
- 索引与函数阶段
- 创建分区化索引（复合/部分索引）
- 部署并行友好函数，执行回归校验
- 切换阶段
- 验证与观察期
- 收尾阶段
- 资源需求（固定）
- 1 名 DBA（主导变更与回滚）
- 1 名应用负责人（联调与验证）
- 1 名数据/报表负责人（函数回归与性能验收）
- 关键里程碑
- M1：父表与分区创建完成
- M2：历史数据迁移与校验通过
- M3：索引与函数上线并验收
- M4：切换成功，生产等效流量回放通过
- M5：观察期通过，确认回滚窗口关闭
测试策略
- 测试类型与范围
- 架构一致性：列/约束/默认值/序列所有权一致
- 数据一致性：按日分区的行数一致、主键集合一致、校验和一致
- 查询回归：TOP N 关键查询（写入、按 user_id/created_at 范围查、status='active' 查询、聚合报表函数）
- 并行执行：聚合函数在 parallel setup 下的执行计划与正确性
- 权限与审计：最小权限角色写读覆盖测试，审计日志记录有效
- 验收标准（全部满足方可通过）
- 分区后总行数=迁移前总行数；任一日分区行数差异=0
- 校验和一致（见校验 SQL）
- 关键查询 95 百分位延迟不劣于改造前 ±10%
- EXPLAIN 显示分区裁剪生效（Pruning: on）
- 部分索引被利用（针对 status='active' 查询）
- 并行计划出现（Gather/Gather Merge），结果正确
- 审计日志记录 DDL 与 DML（针对 events）
- 测试环境要求
- staging 与生产同版本（PostgreSQL 13）、相同参数模板（尤其并行相关：max_parallel_workers、max_parallel_workers_per_gather）
- pgaudit 已预加载（或采用替代日志方案）
- pg_stat_statements 已启用用于回归分析
部署方案
- 环境部署顺序
- 部署检查点
- C0：依赖检查完成
- C1：父表/分区/权限创建完成
- C2：迁移+校验通过
- C3：索引/函数上线通过
- C4：切换成功
- C5：观察期通过
- 发布验证方法
- 执行验证脚本（数据一致性、查询计划、并行与索引使用、权限与审计）
以下为推荐的详细执行脚本与步骤（按检查点推进）
- 前置依赖与基线采集（C0）
-- 表结构、索引、触发器、依赖对象清单
SELECT objid::regclass AS object, deptype, refobjid::regclass AS depends_on
FROM pg_depend WHERE objid = 'public.events'::regclass;
-- 外键/约束
SELECT conname, contype, conrelid::regclass AS on_table
FROM pg_constraint WHERE conrelid = 'public.events'::regclass;
-- 触发器
SELECT tgname, tgenabled, tgtype FROM pg_trigger
WHERE tgrelid = 'public.events'::regclass AND NOT tgisinternal;
-- 统计基线
SELECT now() ts, relname, n_live_tup, n_dead_tup, seq_scan, idx_scan
FROM pg_stat_user_tables WHERE relname = 'events';
-- 数据范围
SELECT min(created_at) AS min_ts, max(created_at) AS max_ts, count(*) AS cnt
FROM public.events;
- 安全与审计（若 pgaudit 已预加载）
CREATE EXTENSION IF NOT EXISTS pgaudit;
ALTER SYSTEM SET pgaudit.log = 'ddl, role, write';
ALTER SYSTEM SET pgaudit.log_relation = 'on';
SELECT pg_reload_conf();
若 pgaudit 不可用：对本次变更会话启用严格日志
SET log_statement = 'all';
SET log_min_duration_statement = 0;
- 创建分区父表与默认分区（C1）
注意：以 created_at（timestamp/timestamptz）为分区键，日粒度
-- 1) 创建新父表（暂名）
BEGIN;
CREATE TABLE public.events_new (LIKE public.events INCLUDING ALL);
-- 移除旧触发器相关的路由逻辑（如果 LIKE 包含了触发器，需删除）
-- 在父表上重建保留的业务触发器（非路由类），父表触发器将对分区生效
-- 示例：DROP TRIGGER IF EXISTS trg_route ON public.events_new;
-- 定义为分区表
ALTER TABLE public.events_new
PARTITION BY RANGE (created_at);
-- 默认分区，兜底越界写入
CREATE TABLE public.events_new_default
PARTITION OF public.events_new DEFAULT;
COMMIT;
- 生成并创建日分区（覆盖历史数据范围）
建议通过 DO 块一次创建；范围以整日边界 [00:00, 00:00)
DO $$
DECLARE
d_start date;
d_end date;
d date;
p text;
BEGIN
SELECT date_trunc('day', min(created_at))::date,
(date_trunc('day', max(created_at)) + interval '1 day')::date
INTO d_start, d_end
FROM public.events;
d := d_start;
WHILE d < d_end LOOP
p := format('public.events_y%sm%sd%s', to_char(d,'YYYY'), to_char(d,'MM'), to_char(d,'DD'));
EXECUTE format(
'CREATE TABLE IF NOT EXISTS %I PARTITION OF public.events_new FOR VALUES FROM (%L) TO (%L);',
p, d::timestamptz, (d + 1)::timestamptz
);
d := d + 1;
END LOOP;
END$$;
- 最小权限角色与授权（父表与所有分区）
注意：PostgreSQL 13 对父表授权不会自动传播到分区，需对每个分区执行 GRANT
-- 角色
CREATE ROLE app_events_reader NOLOGIN;
CREATE ROLE app_events_writer NOLOGIN;
-- 将应用连接角色加入上述组角色（举例）
-- GRANT app_events_reader TO app_ro;
-- GRANT app_events_writer TO app_rw;
-- 父表授权
GRANT SELECT ON TABLE public.events_new TO app_events_reader;
GRANT INSERT, UPDATE, DELETE ON TABLE public.events_new TO app_events_writer;
-- 所有现有分区授权
DO $$
DECLARE r record;
BEGIN
FOR r IN SELECT inhrelid::regclass AS part
FROM pg_inherits WHERE inhparent = 'public.events_new'::regclass LOOP
EXECUTE format('GRANT SELECT ON TABLE %s TO app_events_reader;', r.part);
EXECUTE format('GRANT INSERT, UPDATE, DELETE ON TABLE %s TO app_events_writer;', r.part);
END LOOP;
END$$;
- 数据迁移（C2）
分批迁移，按天或按自增主键区间；迁移后对比分区行数与校验和
-- 示例：全量一次性迁移（staging 可接受）
INSERT INTO public.events_new
SELECT * FROM public.events;
-- 行数校验（总量）
SELECT (SELECT count(*) FROM public.events) AS before_cnt,
(SELECT count(*) FROM public.events_new) AS after_cnt;
-- 按日校验（抽样或全量）
WITH src AS (
SELECT date_trunc('day', created_at) d, count(*) c
FROM public.events GROUP BY 1
),
dst AS (
SELECT date_trunc('day', created_at) d, count(*) c
FROM public.events_new GROUP BY 1
)
SELECT s.d, s.c AS before_c, d.c AS after_c, (s.c - d.c) AS diff
FROM src s FULL JOIN dst d USING(d)
ORDER BY d;
-- 校验和（示例：基于主键和时间的稳定哈希；根据实际主键列名替换 id）
-- 注意：大表计算需监控资源
SELECT
(SELECT coalesce(bit_xor(hashtext(id::text || '|' || created_at::text)), 0) FROM public.events) AS before_crc,
(SELECT coalesce(bit_xor(hashtext(id::text || '|' || created_at::text)), 0) FROM public.events_new) AS after_crc;
如无内置 bit_xor，可改用 sum(hashtextextended(...)) % 2^63 近似校验。
- 分区化索引创建（C3）
注意：PostgreSQL 13 不支持在父表上使用 CONCURRENTLY 创建分区化索引，创建期间对分区可能有锁；staging 可按维护窗口执行
-- 复合索引
CREATE INDEX idx_events_user_created ON public.events_new USING btree (user_id, created_at);
-- 部分索引（status='active'）
CREATE INDEX idx_events_active_created ON public.events_new USING btree (created_at)
WHERE status = 'active';
-- 如需要：对特定查询增加覆盖列或方向（根据执行计划再定）
- 并行友好函数部署与校验（C3）
原则：只读聚合函数标注 PARALLEL SAFE，避免副作用；使用 set-returning SQL/聚合替换 PL/pgSQL 游标循环
-- 示例：将报表函数改为并行安全（根据实际函数名/逻辑调整）
CREATE OR REPLACE FUNCTION public.rpt_daily_user_events(_d date)
RETURNS TABLE(user_id bigint, cnt bigint)
LANGUAGE sql
PARALLEL SAFE
AS $$
SELECT user_id, count(*)::bigint
FROM public.events_new
WHERE created_at >= _d::timestamptz
AND created_at < (_d + 1)::timestamptz
GROUP BY user_id
$$;
-- 并行计划验证
SET max_parallel_workers_per_gather = 4;
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM public.rpt_daily_user_events(current_date - 1);
- 切换（C4）
以原子重命名方式保持同名对象对上层透明
BEGIN;
-- 冻结写入（staging 可暂停写入服务；如需保序，可短暂停服）
-- SELECT pg_terminate_backend(pid) ... （不建议在演练中使用，推荐应用层停写）
ALTER TABLE public.events RENAME TO events_legacy;
ALTER TABLE public.events_new RENAME TO events;
-- 序列与默认值（如 id 为 bigserial）
-- 确保序列所有权与 nextval 继承正确，如需要：
-- 1) 查找旧序列名
-- 2) 将新表列默认值设置为 nextval('同名序列')；并设置 OWNED BY
-- 示例（根据实际对象名调整）：
-- ALTER SEQUENCE public.events_id_seq OWNED BY public.events.id;
-- ALTER TABLE public.events ALTER COLUMN id SET DEFAULT nextval('public.events_id_seq');
COMMIT;
-- 校正序列当前位置（防止主键冲突）
SELECT setval('public.events_id_seq',
(SELECT max(id) FROM public.events), true);
- 迁移后索引与查询校验（C4）
-- 索引使用统计
SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname LIKE 'events%';
-- 分区裁剪是否生效
SET enable_seqscan = off;
EXPLAIN SELECT * FROM public.events
WHERE created_at >= current_date - interval '1 day'
AND created_at < current_date;
-- 部分索引命中（status='active'）
EXPLAIN SELECT * FROM public.events
WHERE status='active'
AND created_at >= current_date - interval '7 day';
RESET enable_seqscan;
- 权限核对与审计验证（C4）
-- 应用只读角色验证
SET ROLE app_events_reader;
SELECT count(*) FROM public.events LIMIT 1;
RESET ROLE;
-- 应用写角色验证
SET ROLE app_events_writer;
INSERT INTO public.events (/*必要列*/) VALUES (/*测试数据*/);
ROLLBACK; -- 仅校验写权限不保留数据
RESET ROLE;
-- 审计日志在服务器端确认已产出（检查日志文件）
- 观察期与收尾（C5）
- 保持 events_legacy 与迁移前备份/快照保留 7 天
- 期间持续监控（见监控与验证）
- 通过后，清理旧触发器/旧表（或根据策略延长保留）
回滚计划
- 回滚触发条件（任一满足立即回滚）
- 数据校验失败（总量或任意分区行数差异>0，或校验和不一致）
- 关键查询 P95 延迟劣化超过 20% 且无法在 30 分钟内定位缓解
- 应用功能异常（写入失败/报表错误）
- 审计/权限不可用影响验证
- 回滚步骤（原子重命名+增量回迁）
BEGIN;
-- 切回旧表名
ALTER TABLE public.events RENAME TO events_new_failed;
ALTER TABLE public.events_legacy RENAME TO events;
COMMIT;
-- 若切换窗口期间产生新数据（写入到了新表），需回填到旧表
INSERT INTO public.events
SELECT * FROM public.events_new_failed
WHERE created_at >= :cutover_ts; -- 切换时间戳（记录于变更单）
-- 恢复旧函数版本/旧索引（如有变更）
-- 关闭/回退审计设置至变更前状态（如有需要）
- 数据恢复方案
- 方案A：直接使用保留的 events_legacy 数据（切回即生效）
- 方案B：若出现不可逆损坏，使用变更前 pg_dump 备份恢复相关对象
- pg_dump -Fc -t public.events -f events_pre_change.dump
- 使用 pg_restore 恢复至 staging（注意依赖顺序）
- 保证序列 setval 与数据对齐，避免主键冲突
监控与验证
- 性能监控指标
- 系统：CPU、IOPS、WAL 生成速率、检查点频率
- 数据库：pg_stat_activity 等待事件、锁等待、autovacuum 活动
- 表/索引：pg_stat_user_tables（seq_scan/idx_scan）、pg_stat_user_indexes、膨胀与可见性映射
- 分区：分区数量、最新/最老分区边界、默认分区写入比例（应接近 0）
- 并行：pg_stat_statements 顶级慢查询的计划节点中 Gather/Gather Merge 出现率
- 业务验证方法
- 用真实或回放的 UAT 流量对常用查询进行端到端验证
- 按日/按用户抽样业务报表比对结果一致性
- 写入→查询一致性（写入后在目标分区可见）
- 问题上报流程
- 发现问题→收集证据（SQL、EXPLAIN、pg_stat* 视图、日志片段）→即时在变更群同步
- DBA 5 分钟内响应；如达回滚触发条件，立即执行回滚脚本
- 形成 RCA 文档并更新运行手册/脚本
附：注意事项与版本要点
- PostgreSQL 13 对父表 CREATE INDEX 不支持 CONCURRENTLY；staging 可在维护窗口统一创建
- 父表 GRANT 不自动传播到分区，需批量 GRANT；新增分区需伴随授权流程
- 触发器应在父表创建，以应用于所有分区（非路由触发器）
- 默认分区用于兜底空洞时间写入，生产上线前应补齐未来分区并清理默认分区内越界数据
- 并行函数务必 PARALLEL SAFE 且只读；避免访问临时表/非并行安全函数
至此，方案能在预发布环境完成端到端可回滚的分区与索引改造演练，满足数据一致性、性能与合规要求。
