如何在 CentOS 香港服务器上搭建主从 PostgreSQL，把 ERP 的“跨境延迟”打回原形

_ 2025-09-19 09:55
返回列表
如何在 CentOS 香港服务器上搭建主从 PostgreSQL，把 ERP 的“跨境延迟”打回原形
技术教程 2025-09-19 09:55 184


那天凌晨 01:13，我正准备收工，深圳仓库值班的小马打来电话：“哥，ERP 查库存要等 6 秒，打单都卡住了。”
我脑子里蹦出的第一反应不是应用层，而是跨境链路：我们的 PostgreSQL 主库在香港机房（合规和结算原因），内地的仓库和门店都要穿过“那条线”。读多写少的 ERP 场景，读延迟被放大了。
于是我决定：把读流量留在内地，把写流量留在香港。方案是——在香港部署主库，在深圳（或你任一点内地边缘节点）部署只读从库（物理流复制），应用做读写分离。下面是我这一夜到天明的完整实操与复盘。

1）目标与拓扑

目标

  • 让内地用户的读请求在本地就地命中（走从库），跨境的只有写请求和 WAL 流，体感延迟降到 < 100 ms。
  • 可随时在灾备演练中把从库升主，RPO 在可接受范围（异步复制），RTO < 3 分钟。
  • 整套在 CentOS 7 上跑，贴近“老业务、不想大升级”的现实。

拓扑（文字示意）

[ERP 应用 - 深圳/内地] --读--> [从库 PostgreSQL - 内地] <=WAL复制=> [主库 PostgreSQL - 香港]
                                   ^  本地连接池(pgbouncer)                 ^  主库连接池/HA
                                   |                                         |
                                 监控告警 <----------- VPN/专线 -----------> 监控告警

基础设施与参数（生产可参考）

角色 机房/线路 CPU/RAM 存储 网络 OS/DB
主库 香港（HK）/双上联，含 CN2/CMI 优化 AMD EPYC 7302P（16C/32T）/128GB 2× U.2 NVMe 3.84TB（RAID1） 1GbE（可上 10GbE），专线或 VPN 出口 CentOS 7 + PostgreSQL 14
从库 深圳边缘节点（或广州/东莞等） Intel Xeon Gold 5220（18C/36T）/96GB 2× NVMe 1.92TB（RAID1） 1GbE 内网 + 跨境隧道 CentOS 7 + PostgreSQL 14
隧道 WireGuard（推荐）或 OpenVPN — — MTU 1380（跨境更稳） —

为避免踩坑：从库的 NVMe 顺序读性能要够；ERP 场景读多，别省。

2）系统基线（CentOS 7）——“先把地基夯实”

# 基础仓库
yum -y install epel-release
yum -y update

# 时间同步（跨机房复制关键）
yum -y install chrony
systemctl enable --now chronyd

# 关闭透明大页 & 调整内核参数（重启保持）
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag
cat >/etc/rc.d/rc.local <<'EOF'
#!/bin/bash
for f in /sys/kernel/mm/transparent_hugepage/enabled /sys/kernel/mm/transparent_hugepage/defrag; do
  [ -f "$f" ] && echo never > "$f"
done
EOF
chmod +x /etc/rc.d/rc.local

# I/O & TCP（CentOS7 默认 3.10，无 BBR，用 cubic）
cat >/etc/sysctl.d/99-pg.conf <<'EOF'
vm.swappiness=1
vm.dirty_background_ratio=5
vm.dirty_ratio=15
vm.overcommit_memory=1
fs.aio-max-nr=1048576

net.core.rmem_max=134217728
net.core.wmem_max=134217728
net.ipv4.tcp_rmem=4096 87380 134217728
net.ipv4.tcp_wmem=4096 65536 134217728
net.ipv4.tcp_congestion_control=cubic
net.ipv4.tcp_fin_timeout=15
net.ipv4.tcp_keepalive_time=300
net.ipv4.tcp_keepalive_intvl=30
net.ipv4.tcp_keepalive_probes=9
EOF
sysctl --system

# XFS 分区建议：noatime,nodiratime,discard（NVMe）
# /etc/fstab 示例（注意按你的盘符修改）
# UUID=xxxx /var/lib/pgsql xfs defaults,noatime,nodiratime,discard 0 0

有 NVMe 的机器，把队列调度器设为 none（CentOS7 可通过 udev 规则固化）。

3）安装 PostgreSQL 14（主库与从库都执行）

# 官方 PGDG 源
yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum -y install postgresql14 postgresql14-server postgresql14-contrib

# 初始化与启动
/usr/pgsql-14/bin/postgresql-14-setup initdb
systemctl enable --now postgresql-14

4）主库（香港）配置

4.1 创建复制用户与访问规则

sudo -u postgres psql -c "CREATE ROLE repl WITH REPLICATION LOGIN PASSWORD 'Str0ng_Repl_P@ss';"
# 允许隧道网段连复制
echo "host replication repl 10.66.0.0/24 scram-sha-256" >> /var/lib/pgsql/14/data/pg_hba.conf

4.2 postgresql.conf（关键片段）

以 128GB RAM 为例，给出一套稳健参数（不是极限）。

# /var/lib/pgsql/14/data/postgresql.conf
listen_addresses = '0.0.0.0'
port = 5432

# 内存与检查点
shared_buffers = 32GB                 # ~25% RAM
effective_cache_size = 96GB           # 约 75%
work_mem = 64MB                       # 按连接数谨慎估算
maintenance_work_mem = 2GB
checkpoint_timeout = 15min
max_wal_size = 64GB
min_wal_size = 8GB
checkpoint_completion_target = 0.9
wal_compression = on

# 复制
wal_level = replica
max_wal_senders = 20
max_replication_slots = 20
wal_keep_size = 2GB

# 延迟与超时（跨境链路防抖）
wal_sender_timeout = 60s
# 业务一致性：异步（更快）。如强一致可用 synchronous_standby_names，但跨境会拉高写延迟。
synchronous_commit = on

# 监控 & 扩展
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

应用用户密码统一使用 SCRAM-SHA-256，在 postgresql.conf 中设置 password_encryption = scram-sha-256。

4.3 SSL 加密（建议）

# 仅示例，自签名；生产可上证书
cd /var/lib/pgsql/14/data
openssl req -new -x509 -nodes -text -out server.crt -keyout server.key -subj "/CN=pg-primary.hk"
chown postgres:postgres server.*
chmod 600 server.key

# postgresql.conf
ssl = on
ssl_ciphers = 'HIGH:!aNULL:!MD5'

4.4 复制槽

sudo -u postgres psql -c "SELECT * FROM pg_create_physical_replication_slot('sz_slot');"
systemctl reload postgresql-14

5）从库（内地）初始化与回放

我用 pg_basebackup 一把梭：并行拉取、顺手生成 standby.signal，省事又稳。

# 停库并清空从库数据目录（谨慎）
systemctl stop postgresql-14
rm -rf /var/lib/pgsql/14/data/*

# 通过跨境隧道地址（例：10.66.0.1）做全量基线
sudo -u postgres /usr/pgsql-14/bin/pg_basebackup \
  -h 10.66.0.1 -p 5432 -U repl \
  -D /var/lib/pgsql/14/data -v -P \
  -R -C -S sz_slot -X stream -j 4
# -R 自动写入 primary_conninfo 并创建 standby.signal

从库 postgresql.conf 补充

hot_standby = on
max_standby_streaming_delay = 60s
hot_standby_feedback = on        # 减少因 VACUUM 造成的冲突；长期开启会增大膨胀，监控配套

启动并检查

systemctl start postgresql-14

# 主库查看复制状态
sudo -u postgres psql -c "
SELECT application_name, client_addr, state, sync_state,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;"

6）跨境隧道（两种做法）

6.1 WireGuard（我在生产首选）

CentOS 7 原生内核缺少内置 WG，建议临时维护窗口用 ELRepo 安装新内核或用 wireguard-dkms。下面是 tools 快速法（已在多套生产稳定）。

# 两端都执行（略去内核升级细节）
yum -y install epel-release wireguard-tools

# 香港主库 /etc/wireguard/wg0.conf
[Interface]
Address = 10.66.0.1/24
PrivateKey = <HK_PRIVATE_KEY>
ListenPort = 51820
MTU = 1380
[Peer]
PublicKey = <SZ_PUBLIC_KEY>
AllowedIPs = 10.66.0.2/32

# 内地从库 /etc/wireguard/wg0.conf
[Interface]
Address = 10.66.0.2/24
PrivateKey = <SZ_PRIVATE_KEY>
ListenPort = 51820
MTU = 1380
[Peer]
PublicKey = <HK_PUBLIC_KEY>
Endpoint = <HK_Public_IP>:51820
PersistentKeepalive = 15
AllowedIPs = 10.66.0.0/24

systemctl enable --now wg-quick@wg0

6.2 OpenVPN（如果你不想动内核）

服务端/客户端一套标准配置，注意 tun-mtu 1400 起步，视丢包调到 1380 左右。这里不赘述完整服务器配置，核心点是稳和低抖动。

7）连接池与读写分离（pgbouncer）

ERP 通常连接比较多，且事务短小，我选择 transaction 模式。两地各布一个 pgbouncer：

  • 内地：监听 erp_ro，连从库（读）
  • 香港：监听 erp_rw，连主库（写）
yum -y install pgbouncer

# /etc/pgbouncer/pgbouncer.ini（内地 RO 池）
[databases]
erp_ro = host=127.0.0.1 port=5432 dbname=erp user=app_ro password=******
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 200
reserve_pool_size = 50
ignore_startup_parameters = extra_float_digits
server_reset_query = DISCARD ALL

应用侧：两条 DSN。读请求走 ro，写请求走 rw。如果你用 Java，可在 DAO 层或读写模板、MyBatis 插件做路由；Python/Go 同理。

（示例）Python 读写分离片段

import psycopg2
import os

rw_dsn = os.getenv("ERP_RW_DSN","host=hk-pg.lb port=6432 dbname=erp user=app password=***")
ro_dsn = os.getenv("ERP_RO_DSN","host=sz-pg.lb port=6432 dbname=erp user=app_ro password=***")

def query(sql, params=None, write=False):
    dsn = rw_dsn if write else ro_dsn
    with psycopg2.connect(dsn) as conn:
        with conn.cursor() as cur:
            cur.execute(sql, params)
            return cur.fetchall() if cur.description else None

# 示例：读
rows = query("SELECT * FROM inventory WHERE sku=%s", ("ABC-001",))
# 示例：写
query("UPDATE orders SET status='PAID' WHERE id=%s", (123,), write=True)

8）性能与可靠性优化（这部分最花时间）

8.1 Autovacuum（真实场景要调）

参数 建议 说明
autovacuum = on ✅ 别关
autovacuum_naptime 20s 忙库适当缩短
autovacuum_vacuum_scale_factor 0.05 大表用阈值+因子结合
autovacuum_vacuum_threshold 500 触发基线
autovacuum_analyze_scale_factor 0.02 统计信息更新更积极
maintenance_work_mem 2GB 与上节一致

对热点表（如订单、库存明细）用 ALTER TABLE ... SET (autovacuum_vacuum_scale_factor=0.02, autovacuum_vacuum_threshold=200);

8.2 WAL 与检查点

  • checkpoint_timeout=15min、checkpoint_completion_target=0.9：平滑写入。
  • wal_compression=on：跨境流量立减，CPU 换带宽（我这边实测 WAL 体积降 30% 左右）。
  • wal_keep_size=2GB：短时网络抖动不致于立即断流。
  • 复制槽要配监控，从库长时间宕机会导致主库 WAL 无法回收。

8.3 复制相关

  • hot_standby_feedback=on 可减少冲突，但要盯表膨胀；配合 pg_stat_all_tables 和 pg_class.relpages 观测。
  • max_standby_streaming_delay=60s 控制从库最多落后多长时间就取消冲突查询（别把报表无限拖长）。
  • wal_sender_timeout / wal_receiver_timeout = 60s：跨境躲抖动。

8.4 文件系统与 I/O

NVMe 建议 XFS，挂载 noatime,nodiratime,discard。

监控 pg_xlog/pg_wal 的 IOPS 峰值，必要时单独分区放 WAL。

9）安全：只让“隧道”访问数据库

pg_hba.conf 只开放 10.66.0.0/24（隧道网段）与必要的内网段。

firewalld 仅放行 5432（内网）、隧道端口：

firewall-cmd --permanent --add-rich-rule='rule family=ipv4 source address=10.66.0.0/24 port protocol=tcp port=5432 accept'
firewall-cmd --permanent --add-port=51820/udp
firewall-cmd --reload

强制 ssl=on + scram-sha-256。

机房侧再加一层 安全组 白名单。

10）监控与告警（复制延迟是命）

主库看复制进度

SELECT application_name, client_addr, state, sync_state,
       pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS lag_bytes,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

从库秒级延迟

SELECT now() - pg_last_xact_replay_timestamp() AS repl_delay;

简易告警脚本（>5 秒报警）

#!/bin/bash
delay=$(psql -At -U postgres -h 127.0.0.1 -c "SELECT EXTRACT(EPOCH FROM (now()-pg_last_xact_replay_timestamp()))::int" 2>/dev/null)
[ -z "$delay" ] && exit 0
if [ "$delay" -gt 5 ]; then
  echo "Replication delay ${delay}s on $(hostname)" | mail -s "PG Delay" dba@corp.local
fi

11）演练：从库升主与回切（必须定期做）

从库升主

# 从库
sudo -u postgres psql -c "SELECT pg_promote(wait=>true, wait_seconds=>30);"
# 或：sudo -u postgres pg_ctl -D /var/lib/pgsql/14/data promote

应用临时把写流量指向新的主库（调整 erp_rw 的后端即可）。

原主库清理 recovery.signal/standby.signal，按需要作为新从库重拉（我倾向于重做一次基线，最干净）。

12）效果对比（我这次现场的真实量级）

指标 调整前（跨境直连主库） 调整后（读本地从库）
查询 P95（库存查询） 2.8 s 180 ms
查询 P99（报表典型） 6.1 s 420 ms
写入 P95（下单） 350 ms 380 ms（写仍跨境，略升可接受）
复制延迟（稳态） — < 200 ms
跨境带宽占用（WAL） — 下降 ~35%（wal 压缩 + MTU 调优）

用户体感：读操作“秒开”。写入因为仍跨境，延迟略升，但不敏感（下单提交一次）。总体反馈很好。

13）这一路的坑 & 我当时怎么填

pg_basebackup 拉基线龟速

现象：3.8TB NVMe 拉了 3 小时。

解决：-j 4 并行、-X stream，同时把隧道 MTU 调到 1380，丢包明显减少；非忙时段进行。

复制槽导致 WAL 堆积

现象：从库宕机一夜，主库 pg_wal 涨到 50GB+。

解决：对每个槽做监控，超过阈值（如 20GB）告警；长宕机先暂停主库产生新槽并评估是否重做基线。

hot_standby_feedback=on 带来膨胀

现象：热点表膨胀，查询计划变差。

解决：对具体大表启更积极的 autovacuum；报表查询尽量走物化视图或逻辑层缓存；必要时短时关闭 hot_standby_feedback 做维护窗口。

SSL 与 SCRAM 混搭问题

现象：历史用户还是 md5，连不上。

解决：统一升级密码到 SCRAM：ALTER ROLE app WITH PASSWORD '***';，pg_hba.conf 的 hostssl 明确 scram-sha-256。

跨境抖动导致 replay_lag 抽风

现象：晚高峰偶发延迟飙升。

解决：WireGuard PersistentKeepalive=15，wal_keep_size=2GB，并把 tcp_keepalive_* 下沉到 sysctl；同时在隧道侧做双上联（CN2/CMI）。

SELinux 与文件上下文

现象：改了数据目录权限，库起不来。

解决：restorecon -R /var/lib/pgsql/14/data，别偷懒直接关 SELinux，合规环境能不关就不关。

14）从“救火”到“常态化工程”

天亮前，深圳仓库的报表“嗖”地一下出来了，小马微信发来一个“抱拳”。我看着监控面板上复制延迟 120ms 的绿线，长出一口气。
这次改造，其实并不花哨：把读就地化、把写跨境化，用最稳的物理流复制，配上连接池与参数打磨，就把 ERP 的跨境延迟从“致命缺陷”变成“可忽略的事实”。
真正的价值，是把这套东西流程化、模板化：新增城，开一台边缘只读，从模板化脚本起步、pg_basebackup 一键拉基线、接上告警，晚上可以安心睡觉——直到下一个凌晨电话响起，但那时你会更淡定。

15）附：我常用的最小可用清单

  1.  HK 主库：wal_level=replica、max_wal_senders/slots、wal_compression=on、checkpoint_* 调好
  2.  复制用户 repl + pg_hba.conf 隧道网段白名单
  3.  复制槽：pg_create_physical_replication_slot('sz_slot')
  4.  从库：pg_basebackup -R -C -S sz_slot -X stream -j 4
  5.  从库：hot_standby=on、hot_standby_feedback=on（配监控）
  6.  隧道：WireGuard/OpenVPN，MTU 1380，Keepalive 开
  7.  连接池：pgbouncer 两套 DSN（erp_ro/erp_rw），应用层路由
  8.  监控：pg_stat_replication、pg_last_xact_replay_timestamp()、复制槽 WAL 大小
  9.  灾备演练：pg_promote() 流程月度演练 + 回切 SOP
  10.  安全：ssl=on、scram-sha-256、防火墙只放隧道网段

如果你的场景不止读多写少（例如门店高频小写），可以继续往前走：

  • 在内地加一层写前缓冲/异步任务（下单→本地排队→异步写主库），或按业务拆分为本地写子集 + 主库全量。
  • 甚至考虑逻辑复制把热点表拆家，但这就进入业务一致性设计了，这一篇先不展开。

以上，就是我那次“凌晨救火”的全部脉络与手工活。愿你用得上，也愿你晚上睡得更踏实。

上一篇： 香港服务器如何在Ubuntu系统中配置Samba+ACL解决跨国团队文件冲突？ 下一篇： 香港服务器如何用多线 BGP 优化海外用户访问 CRM 平台 API 的时延