企业官网建设流程全解析

直接明说吧：PostgreSQL 12的读写分离，是我在生产环境里用了一年多的架构方案。当年那套系统还在用单机PostgreSQL 10，一到业务高峰就吃紧——订单写入和报表查询挤在同一个实例上，CPU动不动飙到90%以上，慢查询一拖就是几十秒。后来拆成读写分离架构，主库专心处理写入，从库扛查询压力，问题直接消掉一大半。这篇博文就把整个方案从选型、搭主从、接中间件到踩坑排查完整走一遍，适合正在被单库性能卡脖子、想上PG12读写分离但没摸清门道的开发者。

1. 为什么PostgreSQL 12适合读写分离：方案选型与整体思路

先说结论：你在生产环境里做的任何“读写分离”，本质上都是把“读”和“写”这两类特征完全不同的SQL请求，分流到不同角色的数据库节点上处理。这个思路跟用Redis做缓存不一样，缓存是拿空间换速度的旁路方案，读写分离则是直接增加数据库层面的并发处理能力。PostgreSQL 12做这件事有一个极其扎实的基础——内置的流复制（Streaming Replication）已经非常成熟，配合一个读写分离中间件，就能把应用层的读压力平滑地分摊到多个只读从库上。

1.1 核心需求解析：单库瓶颈到底出在哪

大概率你遇到的场景跟我一样：系统里写操作占比不高，但读操作极其密集。比如订单系统，用户下单只是一条INSERT，但查订单列表、查详情、跑统计报表，可能是几十条复杂的SELECT。单实例部署时，这些读操作会和写操作争抢同一份CPU、内存、磁盘IO和锁资源。

PostgreSQL单实例能承载多少读？经验数据是，如果读操作都是几十毫秒以内的点查询，单机撑2000到5000 QPS问题不大；可一旦有批量报表、多表JOIN、大量排序，几个查询就能把CPU打满。这时候你调参数、加索引都没用，瓶颈变成了硬件单点资源。读写分离的价值就是：让写操作独占主库的资源和WAL日志能力，让读操作分散到从库上，每加一个从库，读能力几乎线性扩展。

1.2 方案选型对比：应用层拆分、中间件分流还是物理复制直连

市面上做PostgreSQL读写分离的路子大致有三条，我逐一对比过：

• 应用层双数据源：在代码里配置两个数据源，写操作走主库连接，读操作走从库连接。优点是没有额外组件，性能损失接近零；缺点是侵入性强，你得在每个事务边界手写路由逻辑，还得自己管理从库列表，一旦从库扩容或故障，应用要跟着改，维护成本高。
• 中间件层分流：在应用和数据库之间架一层代理（Pgpool-II、读写分离代理、Citus的coordinator等等），由代理解析SQL类型，自动把SELECT路由到从库，把INSERT/UPDATE/DELETE路由到主库。优点是对应用透明，改造代价小；缺点是多一跳网络，代理本身有性能损耗。
• 基于流复制的只读从库直连：主库开启WAL归档和流复制，从库以热备模式开放只读访问，应用通过负载均衡器直接连接多个从库。本质是第二种方案的简化版，没有中间件，路由逻辑交给应用层或负载均衡器。

我在生产里最终选的是“PG12流复制 + 独立中间件”的组合。原因很简单：应用层双数据源在几十个微服务规模下根本维护不动，而纯从库直连又需要一个聪明的路由组件。中间件虽然有一层网络代理的开销，但换来的是运维透明、故障切换方便、连接池统一管理，这笔账很划算。

1.3 PG12的技术红利：新版复制机制让读写分离更省心

选PG12而不是旧版本，有几个具体的技术原因值得展开：

第一，PG12支持pg_basebackup在线热备加standby.signal文件启动只读从库，整个过程不再依赖recovery.conf，配置集中到postgresql.conf，清爽很多。第二，PG12在pg_stat_replication视图里暴露了更细致的复制状态信息，包括write_lag、flush_lag、replay_lag三列，直接能看到主备之间的实时延迟，不用再手工算时间差。第三，PG12对synchronous_commit参数级别做了更多组合，为同步复制方案下了良好基础。

如果你还在用PG10或更早版本，虽然也能做读写分离，但配置起来需要额外处理recovery.conf，且流复制延迟监控手段偏弱。从维护性考虑，建议新项目直接上PG12及以上，这也是我最终锁定的版本线。

2. 主从复制搭建实录：从空库到PG12一主一从全流程

读写分离的地基是可靠的复制链路。主从复制没配好，后面中间件接得再漂亮也是空中楼阁。我先带你完整走一遍PG12的一主一从搭建过程，用一台主库和一台从库做最小可用环境，后续加从库只是重复动作。

2.1 环境规划与基础参数设计

生产环境里我习惯用专门的数据库服务器，资源上主库和从库尽量同规格，避免出现“主库强、从库弱”导致的读延迟放大。以下是我实际使用的规划，IP和目录名称做了脱敏处理：

系统都是CentOS 7.9，数据库版本统一选用PostgreSQL 12.x最新小版本（我用的是12.18）。在搭建之前，两边都先做基础优化，写入/etc/sysctl.conf：

kernel.semmsl = 32000 kernel.semmns = 128000 fs.file-max = 6815744 fs.aio-max-nr = 1048576 net.core.somaxconn = 4096 net.ipv4.tcp_max_syn_backlog = 4096 vm.swappiness = 10

somaxconn和tcp_max_syn_backlog调大是因为中间件会频繁建连，保留小值容易在瞬时连接高峰丢包。vm.swappiness设成10，减少swap交换，保证数据库进程的响应稳定性。

2.2 主库配置：复制账号、连接授权与关键参数

PostgreSQL安装完成后，第一步是以postgres系统用户登录创建复制专用账号。注意这里我坚持不给超级用户复制权限，遵循最小权限原则：

sudo -u postgres psql -p 5432

在SQL命令行里执行：

CREATE ROLE replica LOGIN REPLICATION PASSWORD '你的强密码';

接着配置pg_hba.conf，允许从库IP以复制协议连接。在文件末尾追加：

# 允许从库通过流复制协议连接 host replication replica 10.0.1.11/32 md5 # 允许从库以普通连接方式连主库查询状态（可选） host all replica 10.0.1.11/32 md5

第二条不是必需的，但我建议加上，因为后续监控脚本要用replica账号去查pg_stat_replication和pg_is_in_recovery()，否则还得另开普通账号。

然后是postgresql.conf里跟复制强相关的参数。我贴上生产环境的配置片段：

listen_addresses = '10.0.1.10' wal_level = replica max_wal_senders = 10 wal_keep_size = 1024 max_replication_slots = 10 hot_standby = on hot_standby_feedback = on wal_log_hints = on max_connections = 300

逐个说为什么这么设：

• wal_level = replica：必须项，这是流复制的最低要求级别，低于它主库不产生足够细节的WAL。
• max_wal_senders = 10：决定最多能同时连几个从库或备份工具。10个覆盖一主三从外加备份，绰绰有余。
• wal_keep_size = 1024：单位是MB（PG12里从wal_keep_segments改成这个语义），意思是主库保留1024MB的WAL，防止从库断连过久后拿不到日志起点。这是给从库兜底的，配合复制槽用双保险。
• max_replication_slots = 10：允许创建10个物理复制槽，用来保证主库不会清理掉从库尚未接收的WAL。
• hot_standby = on：让从库在恢复WAL时也能提供只读查询，这是“读写分离”的命门。
• hot_standby_feedback = on：从库执行长查询时，会告诉主库不要清理它需要的旧版本数据，避免查询冲突被取消。
• wal_log_hints = on：开这个是为了以后用pg_rewind做快速重新同步，避免从库因为致命错误而被迫从头重建。

改完参数，重启主库或者SELECT pg_reload_conf()。注意wal_level和max_wal_senders需要重启才能生效，pg_reload_conf()只对部分参数有效。

2.3 从库拉取基础备份：pg_basebackup的正确打开方式

在从库上，不需要手动初始化数据目录，直接用pg_basebackup把主库整个数据目录拉过来。我踩过坑的教训是：一定要在主库空闲时段做这个操作，或者用--checkpoint=fast强制快速检查点，否则备份会拖很久。

从库上执行：

sudo -u postgres pg_basebackup -h 10.0.1.10 -p 5432 -U replica -D /data/pgsql/12/data -R -X stream -P -v

各参数解释：

• -D：从库的数据目录，如果目录非空会报错，得先清空。
• -R：自动生成standby.signal文件并在postgresql.conf里写入主库连接信息，这是PG12的便捷特性。
• -X stream：在备份过程中通过流复制实时接收WAL，而不是依赖WAL归档，避免漏日志。
• -P -v：显示进度和详细输出。

备份完成后，检查/data/pgsql/12/data/standby.signal是否生成。然后编辑postgresql.conf，确认或补充以下内容：

primary_conninfo = 'host=10.0.1.10 port=5432 user=replica password=你的强密码 application_name=pg-standby'

application_name建议设置，后面监控复制延迟和中间件节点识别都会用到它。

2.4 主库创建复制槽并启动从库：让断连自愈成为可能

回到主库，为这台从库创建固定的物理复制槽：

SELECT * FROM pg_create_physical_replication_slot('pg-standby');

创建复制槽后，即使wal_keep_size不够大，主库也绝不会清理掉从库尚未接收的WAL，断连过长时从库能自动续上。这是生产环境的标准动作。

然后在从库上启动PostgreSQL：

sudo systemctl start postgresql-12 sudo systemctl enable postgresql-12

启动后立刻验证复制状态。从库上执行：

sudo -u postgres psql -c "SELECT pg_is_in_recovery();"

返回true代表从库正处在恢复模式，符合预期。主库上查看复制视图：

SELECT client_addr, application_name, state, sync_state, write_lag, flush_lag, replay_lag FROM pg_stat_replication;

正常情况下能看到一行为streaming状态，sync_state是async（默认异步复制）。write_lag、flush_lag、replay_lag应该都是很小的间隔，秒级以内。

到这里，一主一从的物理复制链路已经建立。你可以分别在主库和从库建一张测试表验证：主库插入数据，从库查询能看到数据，但反向插入会被拒绝（从库只读）。

3. 读写分离中间层部署与配置：把SQL请求智能分流

底层复制就绪后，真正落地的关键是让应用层平滑“读主库、写从库”。我选用的方案是类似Pgpool-II的独立读写分离中间件，它既提供连接池，又做SQL路由。下面以我实际使用的配置为蓝本，讲清楚每个关键环节。

3.1 中间件角色分解：连接池和SQL路由是两件独立的事

很多人第一次接触中间件，误以为“读写分离”就是把SQL简单拆成SELECT和非SELECT，这远远不够。我拆解一下中间件真正做的事：

• 后端连接管理：中间件与主库、从库之间维护着一组真实连接，应用连接中间件时，拿到的是从连接池借出的会话，数据库连接数被大幅收敛。实测中，200个应用连接会收敛成30个后端连接，数据库压力小很多。
• SQL语句解析：识别每条SQL的类型。SELECT、SELECT ... FOR UPDATE、WITH开头的CTE、函数调用（如nextval()）都有不同路由规则。
• 事务状态追踪：如果一个事务里既有读又有写，那这个事务内的所有后续查询都必须留在主库。这是读写分离最容易出错的地方，后面我会重点讲。
• 健康检查：周期性探测各节点的存活和复制延迟，自动把故障节点踢出读池。

所以我建议你把中间件当作一个“数据库路由器”而非“连接转发器”来理解。只有想明白这一点，后续排错时你才能很快定位问题出在解析层还是连接层。

3.2 后端节点与负载均衡策略配置

中间件的节点配置里，我定义了primary（主库）和standby（从库）两个角色，并设定读写分离比例为读流量全部分配到从库。关键配置如下（具体参数以实际中间件版本为准）：

backend_hostname0 = '10.0.1.10' backend_port0 = 5432 backend_weight0 = 0 backend_flag0 = 'ALWAYS_PRIMARY' backend_hostname1 = '10.0.1.11' backend_port1 = 5432 backend_weight1 = 1 backend_flag1 = 'ALLOW_TO_FAILOVER'

ALWAYS_PRIMARY标识主节点，写流量只能走它；ALLOW_TO_FAILOVER表示该节点可以切换角色。backend_weight决定读流量的分配权重，我这里只有一台从库所以设1，后加从库就再增加节点。

负载均衡策略我选择“连接级均衡”，意思是每个新数据库会话建立时，中间件挑一个延迟最小的从库分配。相比“语句级均衡”（每条SQL都重新选节点），连接级均衡的会话内体验更稳定，不会出现同一个页面上两次查询走到不同从库、读到不一致数据的情况。

3.3 读写分离的陷进：事务内一致性是这样保住的

中间件最核心的规则不在配置文件的某个开关里，而在于它对“事务内读”的处理。以Pgpool-II为例，它的核心规则是：

• 使用LOAD BALANCE模式下，纯只读事务（未开启显式事务且只含SELECT）路由到从库。
• 在显式事务中执行了INSERT/UPDATE/DELETE后，事务切换到主库，后续所有语句都留在主库，直到事务提交或回滚。
• SELECT ... FOR UPDATE被视为写操作，强制路由到主库，因为行锁和MVCC快照的语义不能跨节点。

这套规则保证了强一致性事务不被打散。举一个典型例子：用户下单后立刻查订单列表，如果查订单列表的SELECT被路由到从库，而主库的事务尚未提交（或复制延迟），用户就看不到刚下的订单。中间件通过在事务内锁定主库路由解决这个场景。

但有一个绕不开的限制：跨请求的会话级一致性。如果应用先写主库（HTTP请求A），下一个请求（HTTP请求B）查询从库，而A和B之间没有显式事务关联，复制延迟可能导致B读不到新数据。要解决这个问题，我用的办法是在关键业务查询上加“主库强制路由”提示，例如对刚下单的订单详情查询强制走主库，牺牲一点查询性能换回绝对的业务正确性。

3.4 健康检查与延迟容忍阈值设定

从库宕机或复制延迟拉长时，中间件必须能够快速感知并自动降级。我这里配置了两类健康检查：

• 连接级检查：每30秒探测一次后端节点端口，失败3次标记节点不可用。
• 复制延迟检查：通过查询后端节点的pg_wal_lsn_diff()对比主从WAL位置，计算出延迟字节数，超过阈值就把该从库踢出读池。

延迟阈值我设为16MB，换算成时间大约在1到2秒内。如果业务对数据新鲜度要求极其严格，可以把这个阈值降到1MB以下，但代价是从库会频繁被摘除，读压力反而堆回主库。这个平衡要依据业务验收标准来定，不能拍脑袋。

部署完成后，我习惯做一次路由正确性验证：用psql通过中间件连接数据库，分别执行SHOW pool_nodes（或对应中间件的节点状态命令）查看节点状态，再执行一条SELECT和一条INSERT，通过观察后端节点收到的SQL来确认路由正确。这里我给你一个验证SQL：

-- 查看当前会话实际落在哪个后端节点 SELECT inet_server_addr(), inet_server_port();

分别开两个会话执行SELECT和INSERT，看返回的IP是主库还是从库，能直观确认分流是否生效。

4. 复制延迟与一致性难题：读写分离场景下的性能调优

读写分离上线后，表面看查询快了，但真正考验功底的是复制延迟、主从数据不一致、长事务把从库拖垮这类深水区问题。这一章节是我踩坑最多的地方，也是我认为一篇文章里最有价值的部分。

4.1 量化复制延迟：从视图指标到真实业务影响

复制延迟可以从两个维度看：技术延迟和业务延迟。技术延迟就是pg_stat_replication里的write_lag、flush_lag、replay_lag，单位是时间，反映的是WAL从生成到在主库写出、刷盘、在从库重放的耗时。业务延迟则是“用户在从库上看到的数据和主库实际数据的差”。

我建议监控脚本直接采集replay_lag作为核心指标，因为它代表从库重放WAL到当前位置的实际时间差。下面是一段我常用的采集SQL：

SELECT application_name, now() - replay_lag AS replay_time, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_bytes FROM pg_stat_replication;

当replay_bytes持续增大且超过max_wal_senders允许的WAL保留范围时，从库会开始追赶主库，但追不上时就会触发复制中断。我实践中处理延迟的主线是：先排查慢查询，再排查网络，最后考虑架构改造。

从库上的慢查询会阻塞WAL重放，这是最常见的丢延迟原因。典型例子是从库上跑了一个大报表JOIN，占用大量CPU和IO，WAL重放线程被挤到一边。怎么发现？在从库执行：

SELECT pid, state, wait_event_type, wait_event, query_start, now() - query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' ORDER BY duration DESC;

找到执行时间异常长的查询，分析执行计划，加索引或改写SQL。从库上的慢查询不解决，调网络参数都是治标不治本。

4.2 热备冲突处理：当主库清理VACUUM撞上从库长查询

PostgreSQL从库运行hot_standby时，主库的VACUUM清理旧版本数据，如果从库上恰好有一个长查询正在使用对应数据行，就会触发“hot standby conflict”，从库查询被强制取消，报错信息形如：

ERROR: canceling statement due to conflict with recovery Detail: User query might have needed to see row versions that must be removed.

这是读写分离架构里最磨人的问题。我来回折腾后总结出两个有效手段。

第一个手段是开hot_standby_feedback = on，我搭建主从时已经开启了。它会让从库把自己的最小活跃事务ID反馈给主库，主库的VACUUM就不敢清理从库还在用的行版本。代价是主库表膨胀风险略增，因为旧版本数据可能被保留更久。为对冲这个代价，我在主库上配置了更积极的autovacuum_vacuum_scale_factor，把默认的0.2调低到0.05，让VACUUM更频繁地跑小批量。

第二个手段是设置max_standby_streaming_delay = 30000（默认也是30秒）。这个参数控制在从库上WAL重放被允许延迟多久来避开冲突。如果从库查询运行时间超过这个阈值，WAL重放会跳过被阻塞的清理操作，宁可短时间内在从库上保留旧数据，也要保住正在执行的查询不被取消。

实战中的取舍是：报表类长查询放到专门的报表从库，并把hot_standby_feedback = on开到这个从库上；而面向用户交互的从库，则适当调低max_standby_streaming_delay，优先保证数据新度，宁可个别慢查询被取消也不让用户体验到明显的数据延迟。

4.3 从库查询性能加成的落地细节：并行查询与只读参数

读写分离不只是把查询挪到从库，还要让从库的查询性能真正跑起来。PG12支持并行查询，但默认参数往往偏保守。我在从库上专门调了这几个参数：

max_parallel_workers_per_gather = 4 max_parallel_workers = 8 min_parallel_table_scan_size = 8MB effective_cache_size = 8GB shared_buffers = 4GB work_mem = 32MB

max_parallel_workers_per_gather决定单个查询最多用几个并行worker。注意它不能超过CPU核心数，否则并行调度反而增加开销。work_mem设成32MB，同时给排序和哈希操作足够的私有内存，但不宜再大，否则并发50个排序操作轻松吃掉1.6GB内存。

另外，从库查询如果是报表类，我习惯把jit = on保持开启（PG12默认开启），让复杂表达式走JIT编译。如果从库CPU资源紧张，可以关掉JIT减少编译开销，这个看服务器负载再定。

4.4 主库写入侧的配合调优：别让WAL拖慢同步

读写分离后主库承载全部写流量，synchronous_commit策略直接关系到数据安全级别和延迟。默认的on模式每一笔事务都要等WAL刷盘后才返回提交成功，延迟低但安全。如果你想更高安全级别，用remote_apply把提交等扩展到从库重放完毕，但每次写入都增加一个网络RTT的延迟。

我实践里的建议是：核心业务表所在库用synchronous_commit = remote_write或remote_apply（取决于RPO要求），普通业务保持默认。这需要配合synchronous_standby_names指定同步备机，否则设置了也没用：

synchronous_standby_names = 'pg-standby'

一旦把从库设为同步备机，主库写延迟会随网络质量波动，所以要谨慎启用。我目前是“异步复制+高频延迟告警”，RPO目标控制在1秒内，对绝大多数互联网业务够用，且架构简单可靠。

5. 常见问题与排查技巧实录：那些年我在读写分离上踩过的坑

最后这部分写成“问题速查+解决方案”，都是我真实遇到并解决的场景。你可以直接当运维手册用。

5.1 复制链路中断：主从不同步的定位四板斧

从一个告警说起：某天凌晨收到从库replay_lag超过5分钟未恢复的告警。我按以下顺序排查：

1. 在从库查看pg_stat_wal_receiver，确认接收进程是否存在。为空则连接断开，看日志里WAL receiver报的错。
2. 在主库查看pg_stat_replication，如果state变成catchup或startup，说明传输是通的但在追赶差异。
3. 对比pg_current_wal_lsn()和pg_last_wal_receive_lsn()，算出需要追赶的字节数。超过100MB且趋势持续增长，多半是从库重放堵住了。
4. 查看从库CPU和IO利用率。如果IO饱和，检查是否磁盘性能不足或WAL重放频繁触发full page write。

我在一次真实故障中定位到罪魁祸首是主库一条大事务产生的WAL暴增，从库磁盘IO跟不上。解决是给从库开了独立的数据盘，并把wal_recycle等重放参数调优，问题不再复发。

5.2 从库查询被无故取消：冲突与死锁的辨别

从库查询报canceling statement due to conflict with recovery时，很多人误以为是主从数据不一致，其实这是热备冲突。快速确认方法：在从库查看日志，搜索recovery conflict关键字，会看到详细的冲突类型统计：

SELECT * FROM pg_stat_database_conflicts;

confl_bufferpin、confl_deadlock、confl_snapshot是三类主要冲突。confl_snapshot占绝大多数，对应我前面说的VACUUM冲突，用hot_standby_feedback解决。confl_bufferpin比较少见，源于从库正在读的buffer页被主库WAL重放修改，这种情况只能提高max_standby_streaming_delay或优化查询时长。

5.3 中间件连接池导致的事务悬挂：会话级坑

有段时间应用偶发报“connection is busy”，查下来是中间件连接池复用了一个在事务中间断开的连接。根因是应用框架在连接归还前没有正确执行回滚。解决办法是在应用侧强制开启自动提交，或配置中间件在归还连接时执行ROLLBACK清理残留事务。

经验教训：连接池是把双刃剑，事务边界不清的代码最容易踩到池复用相关的故障。上线前务必用压测模拟连接复用场景，不能只跑单连接功能测试。

5.4 读写分离与序列的隐藏坑：并行插入竟然报主键冲突

这是个很隐蔽的坑：两个应用实例同时通过中间件写入主库，各自连接池里的会话都缓存了nextval()的本地序列快照。如果中间件配置了连接级负载均衡，某些会话被错误路由到从库导致序列无法推进，应用再插入时就产生主键冲突。

解决也很简单：明确告诉中间件nextval()和currval()相关的语句必须路由到主库。在Pgpool-II里通过write_allow_function_list参数控制，只允许主库执行序列函数。遇到这种问题，先在中间件开启SQL日志，抓一条报错的SQL，分析路由去向，很快能定位到。

结语：个人实践里的几个关键体会

读写分离这套方案，我从PG10一直用到PG12，最大的体会是“先保证一致性，再谈性能”。设计路由规则时，宁可在少数查询上牺牲性能强制走主库，也不要让业务出现读到旧数据的情况。另外监控比架构更重要，pg_stat_replication的延迟指标一定要接告警，延迟失控的第一分钟发现和十分钟发现，修复代价完全不一样。

如果你正打算上手，我建议按这个顺序推进：先在一台从库上把流复制搭稳，再接入中间件做路由，最后才考虑加从库扩读。不要一上来就搭主从加从库全家桶，每一步都要有验证手段。最后分享一个小技巧：把中间件的SQL日志在测试环境全量打开跑一天，你会有大量“原来这条SQL走了从库、那条SQL走了主库”的意外发现，这些信息比任何理论分析都值钱。