postgresql如何避免数据丢失

在 PostgreSQL 中，“避免数据丢失”通常从可靠性、备份、容灾、运维规范几个层面来做。下面按优先级和实用性给你一个较完整的技术清单。

一、基础可靠性配置（防止意外丢失）

1. 开启并正确配置 WAL（Write-Ahead Logging）

WAL 是 PG 防止崩溃丢数据的核心。

wal_level = replica
fsync = on
synchronous_commit = on

• fsync=on：确保 WAL 写入磁盘
• synchronous_commit=on：事务提交不丢（默认）
• 若要求零丢失，可考虑：

synchronous_commit = remote_apply

（配合同步备库）

2. 使用同步复制（防止主库宕机丢数据）

synchronous_standby_names = 'standby1'

• 主库提交需至少一个同步备库确认
• 适合金融、订单等关键系统

⚠️ 同步复制会影响写入性能

二、备份（应对人为/逻辑错误）

3. 定期物理备份（基础必须）

使用 pg_basebackup 或工具（pgBackRest / Barman）

pg_basebackup -D /backup/pgdata -Ft -z -P

建议：

• 每天一次全量
• 结合 WAL 归档做 PITR

4. 开启 WAL 归档（支持时间点恢复）

archive_mode = on
archive_command = 'cp %p /archive/%f'

可用于：

• 恢复到某一秒（PITR）
• 误删表、误更新后恢复

5. 逻辑备份（防逻辑错误）

pg_dump dbname > db.sql
pg_dumpall > all.sql

适合：

• 小库
• 单表恢复
• 跨版本迁移

⚠️ 不能替代物理备份

三、高可用与容灾

6. 主从复制（Streaming Replication）

• 异步：性能好
• 同步：不丢数据

工具：

• 原生流复制
• Patroni + etcd
• repmgr

7. 自动故障转移

避免“主库挂了没人切”

• Patroni
• pg_auto_failover
• Keepalived + VIP（简单场景）

四、应用与运维层面防丢

8. 事务正确使用

避免：

 autocommit + 误操作

建议：

• 关键操作显式事务
• 批量删除先 SELECT 确认

9. 权限与防误删

• 限制 DROP / TRUNCATE 权限
• 使用角色分离
• 生产环境禁用 DROP DATABASE

10. 软删除代替硬删除

UPDATE user SET deleted = true WHERE id = 1;

五、监控与演练（非常重要）

11. 监控

• WAL 是否正常归档
• 复制延迟
• 磁盘空间
• 备份是否成功

12. 定期恢复演练

没有演练的备份 = 没有备份

• 定期恢复备份到测试库
• 验证数据完整性

六、典型安全组合（推荐）

关键业务推荐架构：

• 主库（同步提交）
• 1 个同步备库
• 1 个异步备库（异地）
• 每日物理备份 + WAL 归档
• 每月恢复演练

如果你愿意，我可以：

• 给你一套 生产级 postgresql.conf
• 或针对 单机 / 云数据库 / 容器 给方案
• 或讲 误删数据如何恢复

你现在的 PG 是单机还是已有主从？