PostgreSQL 在 Ubuntu 上的数据完整性保障
一 核心机制与原理
- WAL 先写日志与强制刷盘：所有对数据页的修改都会先写入 WAL（Write-Ahead Logging），并在将脏页刷入磁盘前确保对应的 REDO 记录已落盘。后台写入器（bgwriter）会依据页面的 LSN 保证“先日志、后数据页”的顺序，崩溃后可通过重放 WAL 恢复到一致状态。即便将 synchronous_commit=off，也只是可能丢失最近的少量已提交事务的 WAL 记录，不会导致数据页不一致。
- 检查点（Checkpoint）：定期执行 Checkpoint 将已提交修改的脏页刷盘，并推进 WAL 写入位置，缩短崩溃恢复时间窗口，降低需要重放的日志量。
- 事务与 MVCC：通过 MVCC（多版本并发控制） 与严格的 事务隔离，确保并发读写下的可串行化视图与一致性读，避免脏读、不可重复读与幻读（取决于隔离级别）。
二 表级与应用层约束
- NOT NULL：列值不允许为 NULL。
- UNIQUE：列或列组合的值唯一，允许 NULL（多 NULL 不冲突）。
- PRIMARY KEY：NOT NULL + UNIQUE 的组合，唯一标识行。
- FOREIGN KEY：维护表间 参照完整性，子表外键值必须在父表主键/唯一键中存在。
- CHECK：对列或行满足布尔表达式进行约束（如 price > 0）；注意检查表达式应避免跨表依赖与不可变函数，以保证可重放与转储/重载的一致性。
- EXCLUSION：基于 GiST 等索引实现“互斥”语义，常用于 时间段不重叠 等场景。
三 页级校验和与损坏检测
- 数据页校验和（data checksums）：在初始化集群时通过 initdb --data-checksums 启用；启用后每个 8KB 数据页在写出前计算并写入校验和，读出时校验，用于发现磁盘/IO 导致的“静默损坏”。该功能需在集群初始化阶段决定，启用后不可关闭，通常带来约 ~5% CPU/IO 开销（视负载而定）。
- 监控与定位：通过 SHOW data_checksums 查看是否开启；在 pg_stat_database 中观察 checksum_failures / checksum_last_failure 指标来发现校验失败。注意：校验和仅在页离开/进入缓冲区的 I/O 边界生效，WAL 回放 full page image 时不进行校验。
四 备份恢复与复制的高可用保障
- 逻辑备份与恢复：使用 pg_dump / pg_restore 进行库/表级备份与迁移，适合跨版本升级、选择性恢复与开发测试；恢复后建议进行 数据一致性核验（如行数与关键列摘要比对）。
- 物理备份与时间点恢复（PITR）：使用 pg_basebackup 获取一致性基础备份，配合 WAL 归档与 recovery.conf / recovery.signal 实现任意时间点恢复，可恢复到故障前任意秒级时间点。
- 流复制与备机：基于 WAL 流复制的一主一从/一主多从架构，主机写入 WAL 即向备机发送，备机回放保持与主机的 崩溃一致性；常见配置包含 hot_standby、wal_level=hot_standby、archive_mode=on、archive_command、max_wal_senders、wal_keep_segments 等参数。
五 Ubuntu 上的落地配置建议
- 初始化阶段：若尚未初始化，建议使用 initdb --data-checksums -D /var/lib/postgresql/16/main 开启页级校验（注意：仅对新集群有效，已有集群无法就地开启）。
- WAL 与归档：在 /etc/postgresql/16/main/postgresql.conf 中设置关键参数（示例值可按需调整）：
- wal_level = replica（或 logical，若需逻辑复制）
- archive_mode = on
- archive_command = ‘test ! -f /var/lib/postgresql/16/archive/%f && cp %p /var/lib/postgresql/16/archive/%f’
- max_wal_senders = 16
- wal_keep_segments = 100（或使用 replication slot）
- 创建归档目录并设定权限：mkdir -p /var/lib/postgresql/16/archive && chown postgres:postgres /var/lib/postgresql/16/archive && chmod 700 /var/lib/postgresql/16/archive
- 备份策略：每日/每小时使用 pg_basebackup 做全量基础备份，并持续归档 WAL；每周做一次恢复演练与校验，确保备份可用。
- 监控与告警：在监控中关注 pg_stat_database.checksum_failures、复制延迟与 WAL 堆积；校验失败应立即调查磁盘/存储/内存健康状态，并从最新可用备份恢复。