OpenDBWorks

PostgreSQL

PostgreSQL 设计 · 高可用 · 调优 · 迁移

凭借可扩展性与标准兼容性，PostgreSQL 已成为商用数据库的主流替代。自动故障切换的高可用集群、可恢复到任意时间点的备份、基于证据的调优，以及从 Oracle/Tibero 的迁移，都可以交给我们。

PostgreSQL 咨询迁移步骤

Architecture

推荐的高可用架构

最常见的开源组合：Patroni + 分布式配置存储（DCS）+ PgBouncer/HAProxy + pgBackRest。

Patroni 借助 etcd、Consul、ZooKeeper 等 DCS 选举主节点，主库故障时自动提升最合适的副本。应用通过 HAProxy（或 VIP）始终连接当前主库，PgBouncer 控制连接数。

WAL 由 pgBackRest 持续归档，因此可通过全量备份 + WAL 重放恢复到任意指定时间点。

  • DCS 使用奇数（3 台以上）节点，防止脑裂
  • 同步/异步复制按 RPO 与写延迟权衡选择
  • 确认应用对延迟的容忍度后，将读负载分流到副本
  • 定期演练故障切换与主动切换
图：PostgreSQL 高可用参考架构（示例，按规模与 RPO 调整）

Scope

PostgreSQL 服务范围

安装与设计

版本选择（考虑支持周期）、包/源码安装、目录·表空间·参数标准、扩展（extension）策略。

高可用与复制

流复制（物理）、逻辑复制（publication/subscription）、Patroni 自动切换、复制槽管理。

备份与 PITR

pgBackRest、Barman、pg_basebackup，WAL 归档，指定时间点恢复与定期验证。

调优

shared_buffers、work_mem、WAL 与检查点、autovacuum、执行计划、索引与分区。

监控

pg_stat_statements、pg_stat_activity、等待事件、复制延迟、膨胀、Grafana/PMM 仪表盘。

升级

pg_upgrade（如 --link）或基于逻辑复制的低停机大版本升级。

安全

SCRAM-SHA-256 认证、最小化 pg_hba.conf、角色设计、TLS、行级安全（RLS）、pgaudit。

Oracle·Tibero 迁移

ora2pg 评估与转换、PL/SQL → PL/pgSQL、数据校验、性能重新调优。

Replication

复制方式比较

PostgreSQL 复制与高可用选项
方式特点适用场景注意事项
流复制逐字节传输 WAL 的物理复制，可选同步/异步高可用、只读副本、灾备要求相同大版本与平台
逻辑复制以表为单位通过 publication/subscription 传输变更部分复制、大版本升级、整合DDL 与序列需另行处理
Patroni基于 DCS 的选主、自动故障切换、配置管理生产高可用标准DCS 可用性与隔离（fencing）设计是关键
PgBouncer轻量连接池（session/transaction/statement 模式）缓解连接风暴transaction 模式下需确认会话级功能限制

Backup & PITR

备份与时间点恢复（PITR）

有全量备份与 WAL 归档，就能回到故障或误操作之前的时间点。备份只有恢复过才算备份。

基于 pgBackRest 的 PITR 示例（PostgreSQL 12+）
# 1) 停止服务并恢复到目标时间
sudo systemctl stop postgresql
sudo -u postgres pgbackrest --stanza=main --delta \
     --type=time "--target=2026-10-20 13:55:00+09" \
     --target-action=promote restore
# 2) 启动并确认恢复完成
sudo systemctl start postgresql
psql -c "select pg_is_in_recovery();"   -- f 表示已提升
查看 pg_stat_statements 前几名查询
-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT queryid, calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       rows, left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
示例基于标准选项。实际执行时，会按 stanza 名称、仓库位置、时区与 PostgreSQL 版本使用经过验证的操作手册。

Tuning

性能调优要点

实例与配置

  • 内存：shared_buffers、work_mem、maintenance_work_mem、effective_cache_size
  • WAL 与检查点：max_wal_size、checkpoint_timeout、wal_compression
  • autovacuum 阈值与 worker 数，大表单独设置
  • 限制连接数 + PgBouncer 连接池
  • OS：大页、I/O 调度器、文件系统挂载选项

查询与结构

  • 用 EXPLAIN (ANALYZE, BUFFERS) 验证执行计划
  • 复合、部分、覆盖索引；清理未使用索引
  • 声明式分区与分区裁剪
  • 统计信息管理（ANALYZE、扩展统计）
  • 改进 N+1、过多 ORM 查询等应用模式

Migration

Oracle · Tibero → PostgreSQL 迁移

拥有商用数据库运维经验的 DBA 会提前指出兼容性差异。

  1. 评估

    用 ora2pg 报告评估对象与 PL/SQL 规模及转换难度

  2. 结构转换

    映射类型（NUMBER、DATE 等）、序列、分区、约束

  3. 代码转换

    将包、存储过程、触发器转为 PL/pgSQL，修改应用 SQL

  4. 数据迁移

    全量加载后确定增量同步方式，校验行数与校验和

  5. 性能验证

    比较并调优业务代表查询的执行计划

  6. 切换

    演练 → 切换 → 稳定期监控

ora2pg 面向 Oracle。Tibero 虽兼容 Oracle，但工具能否直接连接需按环境确认；必要时将结构抽取与转换作为单独流程进行。

Lifecycle

版本生命周期检查（截至 2026 年 10 月）

  • 社区对每个大版本自首次发布起支持 5 年。
  • 2026 年 10 月支持版本：18 · 17 · 16 · 15 · 14 —— PostgreSQL 14 预计于 2026 年 11 月 12 日停止支持
  • PostgreSQL 19 在 2026 年 10 月处于 RC 阶段（尚未正式发布），建议正式发布并验证后再用于生产。
  • 建议始终使用所在大版本的最新小版本。
来源：postgresql.org Versioning Policy。日期可能变动，实施前请确认官方页面。

FAQ

常见问题

没有 Patroni，只用流复制不够吗？

复制本身是 PostgreSQL 内置功能，但故障检测、提升、隔离旧主库与连接切换需要额外工具。Patroni 是自动化这些工作的代表性开源工具。

一定需要 PgBouncer 吗？

连接数多或短连接频繁的环境效果明显。但在 transaction 池模式下，需要确认会话级功能（部分预处理语句用法、会话变量等）的限制。

可以不停机做大版本升级吗？

常见做法是用逻辑复制同步新版本集群，再在很短的切换时间内完成迁移。如果可以接受停机，pg_upgrade 更简单。

从 Oracle 迁移后性能会下降吗？

优化器、锁与事务行为不同，部分查询需要改写或调整索引。我们会在迁移前后比较代表性查询，并把调优纳入迁移范围。

从现状开始，一起检查您的云与数据库？

告诉我们您使用的云、数据库版本、架构与困扰，我们将提出评估范围与实施方式。

咨询khy061473@gmail.com