PostgreSQL 整体架构原理和高可用

一、整体架构原理

PostgreSQL 整体架构原理和高可用5.png

整体架构图

Client 客户端
↓ 连接
postmaster (主进程)
↓ 派一个后端进程
postgres (你的专属服务员)
↓ 读写数据
Shared Buffer (共享缓冲区)
               ├─ 读：没数据 → 从 Data Files (磁盘数据文件) 加载进来
└─ 写：先记到 WAL Buffer (日志缓存) → 再改 Shared Buffer 里的页 → 变成「脏页」
↓
WAL Writer (日志快递员)
            ↓ 把日志刷盘
WAL Files (磁盘WAL日志文件)
↓
Data Writer / Checkpointer (搬运工)
↓ 把「脏页」慢慢搬回磁盘
Data Files (磁盘数据文件)
            ↓ (后台定时)
Vacuum (清洁工)
↓ 清理没用的旧数据、更新统计
Data Files (保持整洁高效)

1. 内存结构

共享内存段是内存中的缓冲区缓存，用于事务和维护后台操作；不同的共享内存段用于执行不同的操作。

1.1. 共享缓冲区 (Shared Buffers)

组件作用：缓存磁盘的热点数据页 / 索引页，所有进程共用，优先从内存命中数据，减少磁盘随机 IO，是内存层最核心的性能组件。
优化参数：shared_buffers （单位：KB/MB/GB，核心必调参数）。
参数默认值：一般为物理内存的 1/16（比如 16G 内存默认 1G），这里严重偏小，生产环境必须调整。
推荐值：物理内存的 1/2 （PG 的核心最优值）。
调整原理 + 性能影响：
- shared_buffers 越大，能缓存的热点数据越多，数据「内存命中率」越高，减少磁盘随机读的次数，SQL 查询速度直接提升；
- 注意：该参数修改后，必须重启数据库 才能生效。

设置 shared_buffers 大小：

[postgres@pg1 pgsql]$ grep shared_buffers data/postgresql.conf
shared_buffers = 128MB                        # min 128kB

[postgres@pg1 pgsql]$ psql
postgres=# alter system set shared_buffers='256MB';
ALTER SYSTEM
postgres=#
\q
[postgres@pg1 pgsql]$ tail data/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
...
shared_buffers = '256MB'

1.2. WAL 缓冲区 (WAL Buffers)

WAL 的全称是 Write-Ahead Logging，中文常译为“预写式日志”。它是 PostgreSQL 中用于保证事务持久性和数据一致性的核心机制：在数据写入磁盘前，先记录日志，确保系统崩溃后可以通过日志恢复数据。

对应组件：共享内存 → WAL 缓冲区（WAL Buffers）
组件作用：缓存事务的 WAL 日志，所有数据修改操作先写入这里，再由 WAL Writer 异步刷盘，减少事务的刷盘次数，提升写入性能。
优化参数：wal_buffers （单位：KB/MB，核心必调参数）
参数默认值：一般为 16MB ，也可配置为 shared_buffers 的 1/32（自动计算）。
推荐值：32MB ~ 64MB （绝大多数业务的最优值，写入密集型业务可到 128MB）。
调整原理 + 性能影响：
- 适当调大wal_buffers，可以缓存更多的 WAL 日志，减少强制刷盘的频率，大幅提升批量写入（insert/update/delete）的性能，对读多写少业务影响较小。
- 注意：该参数修改后，reload 即可生效，无需重启。

设置 WAL buffer 大小：

[postgres@pg1 pgsql]$ grep wal_buffers data/postgresql.conf
wal_buffers = -1                        # min 32kB, -1 sets based on shared_buffers

postgres=# alter system set wal_buffers=-1;
ALTER SYSTEM
postgres=#
\q
[postgres@pg1 pgsql]$ tail data/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
...
wal_buffers = '-1'

wal_buffers = -1 的意思是让 PostgreSQL 自动管理 WAL 缓冲区的大小。简单来说，就是数据库会根据系统配置（主要是 shared_buffers 的大小）自动选择一个合适的值，不需要手动指定。

如果未来你想手动固定一个值，可以直接设置成具体大小，比如：

ALTER SYSTEM SET wal_buffers = '16MB';

1.3. CLOG Buffer

PostgreSQL 的 CLOG buffer 是提交日志，用于存储所有事务的状态，比如事务是否已完成。此缓冲区由数据库引擎自动管理，它没有特定的参数。它由 PostgreSQL 数据库中的所有后台服务器和用户共享。

1.4. 工作内存 (Work Mem)

对应组件：本地内存 → 工作内存（Work Mem）
组件作用：单进程独占，用于 SQL 的排序、分组、哈希连接、去重等计算，核心作用是避免生成磁盘临时文件。
优化参数：work_mem （单位：KB/MB，核心高频调优参数）
参数默认值：4MB （严重偏小，生产环境必调）。
推荐值：16MB ~ 64MB （常规业务），复杂报表 / 排序分组多的业务可到 128MB ~ 256MB。
调整原理 + 性能影响：
- 当work_mem足够大时，SQL 的计算逻辑能在内存中完成，彻底避免磁盘临时文件的生成，排序 / 分组的速度提升 10 倍 +；如果内存不足，生成临时文件后，SQL 耗时会急剧增加；
- 注意：不要盲目调太大！比如并发连接数是 100，work_mem 设为 1GB，会直接耗尽服务器内存，导致 OOM。OOM（Out of Memory，内存溢出）是指系统或应用程序请求的内存超出了可用资源限制，导致无法继续分配内存，进而可能触发进程终止、系统崩溃或自动回收机制（如 Linux 的 OOM Killer）。最优值是「业务单次计算的最大数据量」匹配即可。
- 生效方式：reload 即可。

设置工作内存大小：

[postgres@pg1 pgsql]$ grep work_mem data/postgresql.conf
#work_mem = 4MB                                # min 64kB

postgres=# alter system set work_mem='6MB';
ALTER SYSTEM
postgres=#
\q
[postgres@pg1 pgsql]$ tail data/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
...
work_mem = '6MB'

1.5. 维护内存 (Maintenance Work Mem)

对应组件：本地内存 → 维护内存（Maintenance Work Mem）
组件作用：单进程独占，专门用于建索引、Vacuum、Analyze 等维护操作，这类操作对内存敏感，内存越大，维护速度越快。
优化参数：maintenance_work_mem （单位：KB/MB/GB）
参数默认值：64MB （偏小）。
推荐值：512MB ~ 2GB （常规业务），大表多的库可到 4GB。
调整原理 + 性能影响：
- 建索引时，内存越大，排序越快，索引创建时间缩短 50%+；Vacuum 时，内存越大，垃圾清理的效率越高，能更快回收表空间，减少表膨胀；
- 该参数是「单进程最大值」，PG 的维护操作一般是单进程执行，所以可以适当调大，不会造成内存争用；
- 生效方式：reload 即可。

设置维护内存大小：

[postgres@pg1 pgsql]$ grep maintenance_work_mem data/postgresql.conf
#maintenance_work_mem = 64MB                # min 64kB

postgres=# alter system set maintenance_work_mem='6MB';
ALTER SYSTEM
postgres=#
\q
[postgres@pg1 pgsql]$ tail data/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
...
maintenance_work_mem = '6MB'

1.6. 临时缓冲区

PostgreSQL 的临时缓冲区区域用于在大型排序和哈希操作期间访问临时表，这些缓冲区是用户会话专用的。

2. 文件结构

2.1. 数据文件

对应组件：文件结构 → 基础数据文件（表 / 索引数据文件）
组件作用：存储业务数据，所有查询的最终数据来源，这类文件的读写是「随机 IO」，磁盘随机 IO 是数据库性能的最大瓶颈。
优化参数：effective_io_concurrency （单位：数值，核心磁盘优化参数）
参数默认值：1 （适配机械硬盘）。
推荐值：固态硬盘 (SSD)：100 ~ 200 ，机械硬盘 (HDD)：保持默认 1 即可。
调整原理 + 性能影响：
- 该参数告诉 PG：当前磁盘设备「能并行处理的 IO 请求数」是多少；
- 生效方式：reload 即可。

具体文件形式

[postgres@pg1 pgsql]$ ll data/global/
total 592
-rw------- 1 postgres postgres  8192 Jan  8 10:34 1213
-rw------- 1 postgres postgres 24576 Jan  7 12:07 1213_fsm
-rw------- 1 postgres postgres  8192 Jan  7 16:53 1213_vm
-rw------- 1 postgres postgres  8192 Jan  7 16:38 1214
-rw------- 1 postgres postgres 16384 Jan  7 16:38 1232
-rw------- 1 postgres postgres 16384 Jan  7 16:38 1233
-rw------- 1 postgres postgres  8192 Jan  7 16:27 1260
-rw------- 1 postgres postgres 24576 Jan  7 12:07 1260_fsm
-rw------- 1 postgres postgres  8192 Jan  7 12:07 1260_vm
-rw------- 1 postgres postgres  8192 Jan  7 16:17 1261
-rw------- 1 postgres postgres 24576 Jan  7 12:07 1261_fsm
-rw------- 1 postgres postgres  8192 Jan  7 16:07 1261_vm
-rw------- 1 postgres postgres  8192 Jan  8 10:35 1262
-rw------- 1 postgres postgres 24576 Jan  7 12:07 1262_fsm
-rw------- 1 postgres postgres  8192 Jan  7 12:07 1262_vm
-rw------- 1 postgres postgres  8192 Jan  7 15:02 2396
-rw------- 1 postgres postgres 24576 Jan  7 12:07 2396_fsm
-rw------- 1 postgres postgres  8192 Jan  7 12:07 2396_vm
-rw------- 1 postgres postgres 16384 Jan  7 12:07 2397
-rw------- 1 postgres postgres 16384 Jan  8 10:35 2671
-rw------- 1 postgres postgres 16384 Jan  8 10:36 2672
-rw------- 1 postgres postgres 16384 Jan  7 16:27 2676
-rw------- 1 postgres postgres 16384 Jan  7 16:07 2677
...
-rw------- 1 postgres postgres     0 Jan  7 12:07 6243
-rw------- 1 postgres postgres     0 Jan  7 12:07 6244
-rw------- 1 postgres postgres  8192 Jan  7 12:07 6245
-rw------- 1 postgres postgres  8192 Jan  7 12:07 6246
-rw------- 1 postgres postgres  8192 Jan  7 12:07 6247
-rw------- 1 postgres postgres 16384 Jan  7 16:07 6302
-rw------- 1 postgres postgres 16384 Jan  7 16:07 6303
-rw------- 1 postgres postgres  8192 Jan  8 14:12 pg_control
-rw------- 1 postgres postgres   524 Jan  7 12:07 pg_filenode.map
-rw------- 1 postgres postgres 28536 Jan  8 14:07 pg_internal.init

2.2. WAL 文件

对应组件：文件结构 → WAL 日志文件

组件作用：顺序写入的事务日志，保障事务安全 + 主从同步。

优化参数：wal_writer_delay （单位：毫秒，核心 IO 优化参数）

参数默认值：200 毫秒 。

推荐值：200 ~ 500 毫秒 （读多写少业务），500 ~ 1000 毫秒 （写入密集型业务）。

调整原理 + 性能影响：

wal_writer_delay 是 WAL 写入进程的「刷盘间隔」，即每隔多久，把 WAL 缓冲区的日志刷写到磁盘一次；
调大该参数，能「攒更多的日志批量刷盘」，减少磁盘的 IO 次数（磁盘的 IOPS 是有限的），大幅提升批量写入的吞吐量，比如批量 insert、批量 update 的速度会显著提升；
权衡点：刷盘间隔越大，数据库崩溃时可能丢失的「未刷盘日志」越多，但丢失的数据量是「最多该间隔内的事务」，对绝大多数业务完全可接受；如果是金融级强一致性业务，保持默认 200ms 即可。
生效方式：reload 即可。

[postgres@pg1 pgsql]$ ll data/pg_wal/
total 49168
-rw-r----- 1 postgres postgres       42 Jan  8 14:07 00000002.history
-rw------- 1 postgres postgres 16777216 Jan  8 14:12 000000030000000000000078
-rw-r----- 1 postgres postgres 16777216 Jan  8 14:07 000000030000000000000079
-rw-r----- 1 postgres postgres 16777216 Jan  8 14:07 00000003000000000000007A
-rw------- 1 postgres postgres       85 Jan  8 14:07 00000003.history
drwx------ 2 postgres postgres     4096 Jan  8 14:12 archive_status
drwx------ 2 postgres postgres     4096 Jan  8 14:12 summaries

2.3. 日志文件

PostgreSQL 日志文件存储所有与服务器相关的日志，它可以帮助数据库管理员详细调试任何问题。

[root@pgsql-server1 ~]# find / -name postgresql.log
/opt/pgsql/data/log/postgresql.log
[root@pgsql-server1 ~]# tail /opt/pgsql/data/log/postgresql.log
sh:行1: pgbackrest：未找到命令
2026-04-30 16:55:38.109 CST [6869] 致命错误:  归档命令执行失败，退出代码为 127
2026-04-30 16:55:38.109 CST [6869] 详细信息:  执行失败的归档命令是: pgbackrest --stanza=demo archive-push pg_wal/000000010000000000000010
2026-04-30 16:55:47.582 CST [5283] 日志:  接收到快速 (fast) 停止请求
2026-04-30 16:55:47.583 CST [5283] 日志:  中断任何激活事务
2026-04-30 16:55:47.585 CST [5283] 日志:  后台工作进程 "logical replication launcher" (PID 5296) 已退出, 退出代码 1
2026-04-30 16:55:47.586 CST [5289] 日志:  正在关闭
2026-04-30 16:55:47.648 CST [5289] 日志:  checkpoint starting: shutdown immediate
2026-04-30 16:55:47.677 CST [5289] 日志:  checkpoint complete: wrote 0 buffers (0.0%), wrote 0 SLRU buffers; 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.001 s, total=0.030 s; sync files=0, longest=0.000 s, average=0.000 s; distance=16215 kB, estimate=16215 kB; lsn=0/14000028, redo lsn=0/14000028
2026-04-30 16:55:47.687 CST [5283] 日志:  数据库系统已关闭
[root@pgsql-server1 ~]#

或者
ls /opt/pgsql/data/log/
tail /opt/pgsql/data/log/postgresql-Thu.log

3. 进程

3.1. 后端工作进程（Backend Process）

对应组件：工作进程 → 后端工作进程
组件作用：一个客户端连接对应一个进程，执行业务 SQL，进程数 = 并发连接数，进程数的多少直接决定并发能力。
优化参数：max_connections （单位：数值，核心并发参数）
参数默认值：100 （常规业务够用，高并发业务必调）。
推荐值：200 ~ 500 （常规业务），高并发业务（如电商、秒杀）建议 500 ~ 1000 ，同时搭配 PG 连接池（pgbouncer）使用。
调整原理 + 性能影响：
- max_connections 是 PG 支持的最大并发连接数，超过该值的客户端会被拒绝连接，报「too many connections」错误；
- 调大该参数能提升并发能力，但是进程数越多，CPU 和内存的争用越严重：每个进程都会占用本地内存（work_mem）、CPU 时间片，进程数过多会导致 CPU 上下文切换频繁，内存使用率飙升，反而整体性能下降；
- 最优实践：高并发场景下，不要单纯调大max_connections，搭配「pgbouncer 连接池」使用，连接池把「客户端连接」和「后端工作进程」解耦，用少量进程支撑大量客户端连接，避免进程争用。
- 注意：该参数修改后，必须重启数据库 生效。

3.2. 数据写入进程（Data Writer）

对应组件：工作进程 → Data Writer
组件作用：异步将共享缓冲区中的脏数据页刷写到磁盘数据文件，避免共享缓冲区被脏页占满而阻塞业务写入。
优化参数：bgwriter_delay（刷写间隔） + bgwriter_lru_maxpages（每次最大刷页数）
默认值：
- bgwriter_delay = 200ms
- bgwriter_lru_maxpages = 100
推荐值：
- bgwriter_delay = 100~200ms
- bgwriter_lru_maxpages = 200~300
调整原理 + 性能影响：
- bgwriter_delay 越小，刷写越频繁，脏页堆积越少，写入更稳定；但是太小会增加磁盘 IO 压力。
- bgwriter_lru_maxpages 越大，每次刷写的脏页数越多，适合写入密集场景，可快速释放共享缓冲区空间。
- 两者配合，可实现「少量多次、按需刷盘」，既避免写入阻塞，又不造成 IO 突刺。

3.3. 检查点进程（Checkpointer）

对应组件：工作进程 → 检查点进程
组件作用：定期批量刷写共享缓冲区的脏数据到磁盘，更新检查点，核心作用是控制崩溃恢复时间，同时避免磁盘 IO 突刺。
优化参数：checkpoint_completion_target （单位：0~1 的小数，核心 IO 平滑参数）
参数默认值：0.5 。
推荐值：0.7 ~ 0.9 （生产环境最优值，无例外）。
调整原理 + 性能影响：
- 该参数的含义：在两次检查点的间隔时间内，用多少比例的时间完成脏数据刷盘；比如间隔是 10 分钟，设为 0.9，就是用 9 分钟慢慢刷完脏数据；
- 默认值 0.5 是「快速刷盘」，会导致短时间内磁盘 IO 飙升，挤占业务 SQL 的 IO 资源，导致业务查询变慢；
- 调大到 0.7~0.9 后，检查点进程会「匀速、平滑」地刷写脏数据，磁盘 IO 利用率稳定，不会出现突刺，业务 SQL 的 IO 资源得到保障，查询 / 写入性能更稳定，同时不影响崩溃恢复时间。
- 生效方式：reload 即可。

3.4. 自动清理进程（AutoVacuum）

对应组件：工作进程 → 自动清理进程（AutoVacuum）
组件作用：自动清理表的垃圾数据、更新统计信息，PG 的生命线，垃圾不清理会导致表膨胀、查询变慢、索引失效，最终数据库卡死。
优化参数：autovacuum_work_mem （单位：MB/GB，专属优化参数）
参数默认值：继承 maintenance_work_mem 的值。
推荐值：1GB ~ 2GB （独立配置，比 maintenance_work_mem 略大）。
调整原理 + 性能影响：
- AutoVacuum 清理垃圾时，需要扫描表的所有数据页，内存越大，扫描的效率越高，清理速度越快，能更快回收表空间，减少表膨胀；
- 独立配置autovacuum_work_mem，可以避免 Vacuum 操作抢占业务的维护内存，同时让清理操作更高效；
- 生效方式：reload 即可。

3.5. WAL 写入进程（WAL Writer）

对应组件：工作进程 → WAL Writer
组件作用：异步将 WAL 缓冲区中的日志刷写到 WAL 文件，是写入性能的关键后台进程。
优化参数：wal_writer_delay
默认值：200ms
推荐值：
- 普通业务：200ms
- 写入密集 / 批量导入：500~1000ms
调整原理 + 性能影响：
- wal_writer_delay 控制 WAL Writer 多久刷一次盘。
- 调大该值，可攒更多 WAL 日志后批量刷盘，显著提升批量写入性能（顺序写吞吐更高）。
- 代价是：数据库崩溃时最多可能丢失该间隔内的已提交事务，需要在性能与数据安全之间权衡。

检查当前的 WAL 文件。

postgres=# SELECT pg_walfile_name(pg_current_wal_lsn());
     pg_walfile_name
--------------------------
 000000030000000000000078
(1 row)

3.6. Statistics Collector

Statistics Collector 是 PostgreSQL 的后台工作进程，负责收集数据库活动统计信息，如表的读写次数、索引使用情况、SQL 执行频率等；这些统计信息是查询优化器生成高效执行计划的重要依据。

对应组件：工作进程 → Statistics Collector
组件作用：收集表、索引、SQL 执行等统计信息，为查询优化器提供依据，决定执行计划是否合理。
优化参数：track_io_timing + autovacuum_analyze_scale_factor
默认值：
- track_io_timing = off
- autovacuum_analyze_scale_factor = 0.1
推荐值：
- track_io_timing = on
- autovacuum_analyze_scale_factor = 0.05（或更小）
调整原理 + 性能影响：
- track_io_timing = on：收集 SQL 的 IO 耗时统计，便于定位慢查询是 CPU 瓶颈还是 IO 瓶颈，几乎无性能损耗。
- autovacuum_analyze_scale_factor 调小：表数据变化比例较低时就自动执行 ANALYZE，使统计信息更及时、准确，优化器更容易生成最优执行计划，减少因统计过时导致的慢 SQL。

3.7. 日志收集器

对应组件：工作进程 → Log Collector
组件作用：收集数据库运行日志、错误日志、慢查询日志等，用于问题排查与审计。
优化参数：logging_collector + log_statement
默认值：
- logging_collector = off
- log_statement = none
推荐值：
- logging_collector = on
- log_statement = 'ddl'
调整原理 + 性能影响：
- logging_collector = on：启用独立日志进程，将日志写入文件，便于集中管理和排查问题，异步写日志，对性能影响极小。
- log_statement = 'ddl'：只记录 DDL 语句（建表、建索引等），既能满足审计需求，又不会产生大量日志；避免设置为all，否则高并发场景下日志 IO 会严重影响性能。

4. 面试题

4.1. PostgreSQL 中 Shared Buffers 和 Work Mem 的核心区别是什么？分别对应内存结构的哪一层？

答案：

Shared Buffers：属于共享内存，所有进程共享，用来缓存数据页和索引页，减少磁盘随机 IO，是数据库级别的缓存。
Work Mem：属于本地内存，每个后端进程私有，用于排序、分组、哈希连接等操作，是 SQL 执行级别的计算内存。
区别总结：Shared Buffers 管 “数据缓存”，Work Mem 管 “SQL 计算内存”。

4.2. WAL Writer 进程的核心作用是什么？调整 wal_writer_delay 参数时，需要权衡的性能与安全点是什么？

答案：

WAL Writer 作用：异步把 WAL 缓冲区的日志刷到磁盘，保证事务持久性，同时减少业务进程的同步刷盘等待。
权衡点：
- 增大 wal_writer_delay：WAL 日志攒批刷盘，写入吞吐量提升，但数据库崩溃时可能丢失最多该间隔内的已提交事务。
- 减小 wal_writer_delay：数据更安全，但刷盘更频繁，写入性能下降。

4.3. 为什么生产环境不建议将 log_statement 参数设为 all？

答案：

会记录所有 SQL 语句，在高并发场景下日志量巨大，导致：
- 磁盘 IO 暴涨，挤占业务 IO；
- 磁盘空间迅速耗尽；
- 数据库性能明显下降。
生产环境一般只记录 DDL（log_statement = ddl），需要排查时再临时调整。

4.4. 总结

PG 核心架构逻辑

PG = 共享内存（缓存核心） + 磁盘文件（持久化核心） + 工作进程（执行核心）

内存：优先缓存，减少 IO，是性能第一优先级；

文件：顺序写（WAL）快、随机写（数据）慢，优化核心是减少 IO 次数、平滑 IO 压力；

进程：进程式架构，并发靠进程数，优化核心是「匹配并发 + 避免资源争用」。

PG 核心优化逻辑（所有参数都围绕这个逻辑）

内存调大 → 减少磁盘 IO → 进程匹配负载 → 平滑 IO 压力 → 避免垃圾堆积

所有优化都是「对症下药」，每个参数都对应架构中的一个组件，调整参数的本质是「让组件的能力匹配业务需求」，没有万能参数，只有最优匹配。

必调核心参数清单（生产环境直接用，优先级排序）

内存：shared_buffers（重启）、work_mem、maintenance_work_mem

磁盘：wal_writer_delay、effective_io_concurrency

进程：max_connections（重启）、checkpoint_completion_target、autovacuum_work_mem

二、高可用

1. 流式复制

PostgreSQL 的流式复制(streaming replication)允许从库持续复制主库的数据，其工作原理是将 WAL 从主库流式传输到一个或多个从库。WAL 包含对数据库进行的所有更改记录，包括数据修改和库表结构更改（它是物理层面的修改，是页面的字节层面的修改）。

1.1. 从库安装 PostgreSQL

阿里源（可选）

cat >/etc/yum.repos.d/aliyun.repo<<EOF
[baseos]
name=CentOS Stream \$releasever - BaseOS
baseurl=https://mirrors.aliyun.com/centos-stream/\$stream/BaseOS/\$basearch/os/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
countme=1
enabled=1

[baseos-debug]
name=CentOS Stream \$releasever - BaseOS - Debug
baseurl=https://mirrors.aliyun.com/centos-stream/\$stream/BaseOS/\$basearch/debug/tree/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
enabled=0

[baseos-source]
name=CentOS Stream \$releasever - BaseOS - Source
baseurl=https://mirrors.aliyun.com/centos-stream/\$stream/BaseOS/source/tree/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
enabled=0

[appstream]
name=CentOS Stream \$releasever - AppStream
baseurl=https://mirrors.aliyun.com/centos-stream/\$stream/AppStream/\$basearch/os/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
countme=1
enabled=1

[appstream-debug]
name=CentOS Stream \$releasever - AppStream - Debug
baseurl=https://mirrors.aliyun.com/centos-stream/\$stream/AppStream/\$basearch/debug/tree/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
enabled=0

[appstream-source]
name=CentOS Stream \$releasever - AppStream - Source
baseurl=https://mirrors.aliyun.com/centos-stream/\$stream/AppStream/\$basearch/debug/tree/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
enabled=0

[crb]
name=CentOS Stream \$releasever - CRB
baseurl=https://mirrors.aliyun.com/centos-stream/\$stream/CRB/\$basearch/os/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
countme=1
enabled=0

[crb-debug]
name=CentOS Stream \$releasever - CRB - Debug
baseurl=https://mirrors.aliyun.com/centos-stream/\$stream/CRB/\$basearch/debug/tree/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
enabled=0

[crb-source]
name=CentOS Stream \$releasever - CRB - Source
baseurl=https://mirrors.aliyun.com/centos-stream/\$stream/CRB/source/tree/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
enabled=0

[highavailability]
name=CentOS Stream \$releasever - HighAvailability
baseurl=http://mirrors.aliyun.com/centos-stream/\$stream/HighAvailability/\$basearch/os/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
countme=1
enabled=0

[highavailability-debug]
name=CentOS Stream \$releasever - HighAvailability - Debug
baseurl=http://mirrors.aliyun.com/centos-stream/\$stream/HighAvailability/\$basearch/debug/tree/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
enabled=0

[highavailability-source]
name=CentOS Stream \$releasever - HighAvailability - Source
baseurl=http://mirrors.aliyun.com/centos-stream/\$stream/HighAvailability/source/tree/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
enabled=0

[nfv]
name=CentOS Stream \$releasever - NFV
baseurl=http://mirrors.aliyun.com/centos-stream/\$stream/NFV/\$basearch/os/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
countme=1
enabled=0

[nfv-debug]
name=CentOS Stream \$releasever - NFV - Debug
baseurl=http://mirrors.aliyun.com/centos-stream/\$stream/NFV/\$basearch/debug/tree/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
enabled=0

[nfv-source]
name=CentOS Stream \$releasever - NFV - Source
baseurl=http://mirrors.aliyun.com/centos-stream/\$stream/NFV/source/tree/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
enabled=0

[rt]
name=CentOS Stream \$releasever - RT
baseurl=http://mirrors.aliyun.com/centos-stream/\$stream/RT/\$basearch/os/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
countme=1
enabled=0

[rt-debug]
name=CentOS Stream \$releasever - RT - Debug
baseurl=http://mirrors.aliyun.com/centos-stream/\$stream/RT/\$basearch/debug/tree/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
enabled=0

[rt-source]
name=CentOS Stream \$releasever - RT - Source
baseurl=http://mirrors.aliyun.com/centos-stream/\$stream/RT/source/tree/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
enabled=0

[resilientstorage]
name=CentOS Stream \$releasever - ResilientStorage
baseurl=http://mirrors.aliyun.com/centos-stream/\$stream/ResilientStorage/\$basearch/os/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
countme=1
enabled=0

[resilientstorage-debug]
name=CentOS Stream \$releasever - ResilientStorage - Debug
baseurl=http://mirrors.aliyun.com/centos-stream/\$stream/ResilientStorage/\$basearch/debug/tree/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
enabled=0

[resilientstorage-source]
name=CentOS Stream \$releasever - ResilientStorage - Source
baseurl=http://mirrors.aliyun.com/centos-stream/\$stream/ResilientStorage/source/tree/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
enabled=0

[extras-common]
name=CentOS Stream \$releasever - Extras packages
baseurl=http://mirrors.aliyun.com/centos-stream/SIGs/\$stream/extras/\$basearch/extras-common/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-SIG-Extras-SHA512
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
countme=1
enabled=1

[extras-common-source]
name=CentOS Stream \$releasever - Extras packages - Source
baseurl=http://mirrors.aliyun.com/centos-stream/SIGs/\$stream/extras/source/extras-common/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-SIG-Extras-SHA512
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
enabled=0
EOF
dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# 替换为清华大学镜像源（了解）
# sed -i 's|download.postgresql.org/pub|mirrors.tuna.tsinghua.edu.cn/postgresql|g' /etc/yum.repos.d/pgdg-redhat-all.repo

# 安装
dnf install -y postgresql18-server

# 离线安装（了解）
dnf localinstall postgresql18-server-18.3-1PGDG.rhel9.7.x86_64.rpm -y

# 初始化
/usr/pgsql-18/bin/postgresql-18-setup initdb

# 启动
systemctl start postgresql-18
systemctl status postgresql-18
systemctl enable postgresql-18

# 配置 PATH 环境变量
echo 'export PATH=/usr/pgsql-18/bin:$PATH' >> /etc/profile
source /etc/profile

1.2. 配置主库

配置复制参数

# 查看当前值
grep -Ei 'wal_level|wal_keep_size|max_wal_senders|hot_standby|listen_addresses' /opt/pgsql/data/postgresql.conf # 注意：如果没改过数据目录，postgresql.conf 配置文件则是默认的 /var/lib/pgsql/18/data/postgresql.conf

-E：启用扩展正则表达式（Extended Regex），允许使用 |、+、?、() 等特殊字符而不需要反斜杠转义。例如，grep -E 'pattern1|pattern2' 表示匹配 pattern1 或 pattern2。
-i：忽略大小写（Ignore case），匹配时不区分字母的大小写。例如，grep -i 'hello' 可以匹配 hello、Hello、HELLO 等。
组合 -Ei 表示使用扩展正则表达式并忽略大小写。

# 修改
vim /opt/pgsql/data/postgresql.conf
# 监听所有网卡
listen_addresses = '*'     

# 最大 WAL 发送进程数             
max_wal_senders = 10    

# 启用复制所需级别                 
wal_level = replica    

# 保留的 WAL 大小                  
wal_keep_size = 1GB     

# 从库允许只读查询                 
hot_standby = on                          

[root@pg1 ~]# cat /opt/pgsql/data/postgresql.conf
max_wal_size = 1GB
min_wal_size = 80MB

log_timezone = 'Asia/Shanghai'

datestyle = 'iso, mdy'
timezone = 'Asia/Shanghai'

default_text_search_config = 'pg_catalog.english'

listen_addresses = '*'

archive_command = 'pgbackrest --stanza=demo archive-push %p'
archive_mode = on
log_filename = 'postgresql.log'
max_wal_senders = 10
wal_level = replica
wal_keep_size = 1GB
hot_standby = on
[root@pg1 ~]#

# 重启生效
systemctl restart postgresql-18
systemctl status postgresql-18

# 查看配置是否生效
su - postgres
SHOW listen_addresses; 
SHOW wal_level; 
SHOW wal_keep_size; 
SHOW max_wal_senders; 
SHOW hot_standby;
[root@pgsql-server1 ~]# vim /opt/pgsql/data/postgresql.conf
[root@pgsql-server1 ~]# cat /opt/pgsql/data/postgresql.conf
max_connections = 100                   # (change requires restart)
shared_buffers = 128MB                  # min 128kB
dynamic_shared_memory_type = posix      # the default is usually the first option
max_wal_size = 1GB
min_wal_size = 80MB
log_destination = 'stderr'              # Valid values are combinations of
logging_collector = on                  # Enable capturing of stderr, jsonlog,
log_directory = 'log'                   # directory where log files are written,
log_filename = 'postgresql-%a.log'      # log file name pattern,
log_rotation_age = 1d                   # Automatic rotation of logfiles will
log_rotation_size = 0                   # Automatic rotation of logfiles will
log_truncate_on_rotation = on           # If on, an existing log file with the
log_line_prefix = '%m [%p] '            # special values:
log_timezone = 'Asia/Shanghai'
autovacuum_worker_slots = 16            # autovacuum worker slots to allocate
datestyle = 'iso, ymd'
timezone = 'Asia/Shanghai'
lc_messages = 'zh_CN.UTF-8'             # locale for system error message
lc_monetary = 'zh_CN.UTF-8'             # locale for monetary formatting
lc_numeric = 'zh_CN.UTF-8'              # locale for number formatting
lc_time = 'zh_CN.UTF-8'                 # locale for time formatting
default_text_search_config = 'pg_catalog.simple'

listen_addresses = '*'
archive_command = 'pgbackrest --stanza=demo archive-push %p'
archive_mode = on
log_filename = 'postgresql.log'
max_wal_senders = 10
wal_level = replica
wal_keep_size = 1GB
hot_standby = on
[root@pgsql-server1 ~]# systemctl restart postgresql-18
[root@pgsql-server1 ~]# systemctl status postgresql-18
● postgresql-18.service - PostgreSQL 18 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-18.service; enabled; pr>
     Active: active (running) since Tue 2026-05-05 19:01:28 CST; 4s ago
       Docs: https://www.postgresql.org/docs/18/static/
    Process: 1481 ExecStartPre=/usr/pgsql-18/bin/postgresql-18-check-db-dir ${P>
   Main PID: 1486 (postgres)
      Tasks: 12 (limit: 22926)
     Memory: 30.9M
        CPU: 61ms
     CGroup: /system.slice/postgresql-18.service
             ├─1486 /usr/pgsql-18/bin/postgres -D /opt/pgsql/data/
             ├─1487 "postgres: logger "
             ├─1488 "postgres: io worker 0"
             ├─1489 "postgres: io worker 2"
             ├─1490 "postgres: io worker 1"
             ├─1491 "postgres: checkpointer "
             ├─1492 "postgres: background writer "
             ├─1494 "postgres: walwriter "
             ├─1495 "postgres: autovacuum launcher "
             ├─1497 "postgres: walsummarizer "
             ├─1498 "postgres: logical replication launcher "
             └─1500 "postgres: walsender replication 192.168.80.22(40398) strea>

5月 05 19:01:28 pgsql-server1 systemd[1]: Starting PostgreSQL 18 database serve>
5月 05 19:01:28 pgsql-server1 postgres[1486]: 2026-05-05 19:01:28.900 CST [1486>
5月 05 19:01:28 pgsql-server1 postgres[1486]: 2026-05-05 19:01:28.900 CST [1486>
[root@pgsql-server1 ~]# su - postgres
上一次登录： 四 4月 30 17:13:20 CST 2026 pts/0 上
[postgres@pgsql-server1 ~]$ psql
psql (18.3)
输入 "help" 来获取帮助信息.

postgres=# SHOW listen_addresses;
 listen_addresses
------------------
 *
(1 row)

postgres=# SHOW wal_level;
 wal_level
-----------
 replica
(1 row)

postgres=# SHOW wal_keep_size;
 wal_keep_size
---------------
 1GB
(1 row)

postgres=# SHOW max_wal_senders;
 max_wal_senders
-----------------
 10
(1 row)

postgres=# SHOW hot_standby;
 hot_standby
-------------
 on
(1 row)

postgres=#

1.3. 在主库创建用户并配置hba

[root@pgsql-server1 ~]# su - postgres
上一次登录： 二 5月  5 19:02:06 CST 2026 pts/0 上
[postgres@pgsql-server1 ~]$ psql
psql (18.3)
输入 "help" 来获取帮助信息.

postgres=#

# 创建复制用户
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'replicator';

# 配置 hba
su - root
echo 'host    replication     replicator     192.168.80.0/24     scram-sha-256' >> /opt/pgsql/data/pg_hba.conf

# 重载生效
su - postgres
pg_ctl reload -D /opt/pgsql/data

# 或
psql -c 'select pg_reload_conf();'

[postgres@pgsql-server1 ~]$ pg_ctl reload -D /opt/pgsql/data
服务器进程发出信号
[postgres@pgsql-server1 ~]$ psql -c 'select pg_reload_conf();'
 pg_reload_conf
----------------
 t
(1 行记录)

[postgres@pgsql-server1 ~]$ psql
psql (18.3)
输入 "help" 来获取帮助信息.

postgres=# SELECT * FROM pg_hba_file_rules WHERE 'replicator' = ANY(user_name);
 rule_number |          file_name          | line_number | type |   database
|  user_name   |   address    |    netmask    |  auth_method  | options | error
-------------+-----------------------------+-------------+------+---------------
+--------------+--------------+---------------+---------------+---------+-------
           9 | /opt/pgsql/data/pg_hba.conf |           9 | host | {replication}
| {replicator} | 192.168.80.0 | 255.255.255.0 | scram-sha-256 |         |
(1 行记录)

postgres=#

1.4. 从库拉取主库备份（在从库操作）

主库环境优化

# 切换到 root 用户
su - root
# 查看 PG 状态
systemctl status postgresql-18

# 进入 PostgreSQL 数据目录（根据你的实际路径调整，先确认路径）
cd /var/lib/pgsql/18/data  # 这是 PG18 默认数据目录，若你自定义过请替换
或者
cd /opt/pgsql/data

# 删除多余的 .bak 备份文件（核心！）
rm -f postgresql.conf.bak pg_hba.conf.bak  # 如有其他 .bak 文件一并删除

# 确保数据目录所有文件归 postgres 所有
chown -R postgres:postgres *
chmod -R 700 *  # PG 数据目录必须是 700 权限（仅所有者可访问）

从库环境优化

# 切换到 root 用户
su - root

# 先删除原有目录（避免残留文件）
rm -rf /opt/pgsql/* # 如果有这个目录的话就执行这一步操作

# 重建目录（层级创建，确保每一级权限正确）
mkdir -p /opt/pgsql/data

# 核心：仅授权 postgres 用户拥有该目录（禁止 root 或其他用户权限）
chown -Rf postgres:postgres /opt/pgsql
chmod -Rf 700 /opt/pgsql  # 关键！PG 要求数据目录权限为 700

在从库执行以下操作

su - root
chown -Rf postgres:postgres /opt

su - postgres
pg_basebackup -h 192.168.80.21 -D /opt/pgsql/data -U replicator -P -R # 这里的 192.168.80.21 是主库的 IP

注意：
这里要出入密码 replicator
主库要关闭防火墙和 SELinux
主库 /opt/pgsql/data 的这个目录不能有多余的 xxx.bak 文件

# 选项解释
# -h：指定主服务器的 IP 地址
# -D：备份文件的目标目录
# -U：用于复制的用户
# -P：显示进度
# -R：自动配置从库（primary_conninfo 和 standby.signal）
[root@pgsql-server2 ~]# ls /opt/pgsql/
data
[root@pgsql-server2 ~]# rm -rf /opt/pgsql/*
[root@pgsql-server2 ~]# mkdir -p /opt/pgsql/data
[root@pgsql-server2 ~]# chown -Rf postgres:postgres /opt/pgsql
[root@pgsql-server2 ~]# chmod -Rf 700 /opt/pgsql
[root@pgsql-server2 ~]# chown -Rf postgres:postgres /opt
[root@pgsql-server2 ~]# su - postgres
上一次登录： 二 5月  5 19:29:50 CST 2026 pts/0 上
[postgres@pgsql-server2 ~]$ pg_basebackup -h 192.168.80.21 -D /opt/pgsql/data -U replicator -P -R
口令:
32391/32391 kB (100%), 1/1 表空间
[postgres@pgsql-server2 ~]$ ls /opt/pgsql/
data
[postgres@pgsql-server2 ~]$ ls /opt/pgsql/data/
backup_label      pg_dynshmem    pg_snapshots  pg_xact
backup_label.old  pg_hba.conf    pg_stat       postgres-data
backup_manifest   pg_ident.conf  pg_stat_tmp   postgresql.auto.conf
base              pg_logical     pg_subtrans   postgresql.conf
current_logfiles  pg_multixact   pg_tblspc     standby.signal
global            pg_notify      pg_twophase
log               pg_replslot    PG_VERSION
pg_commit_ts      pg_serial      pg_wal
[postgres@pgsql-server2 ~]$

1.5. 修改从库数据目录并重启从库

su - root

sed -i 's|Environment=PGDATA=.*|Environment=PGDATA=/opt/pgsql/data/|g' /usr/lib/systemd/system/postgresql-18.service

chown -Rf postgres:postgres /opt/pgsql

systemctl daemon-reload
systemctl stop postgresql-18
systemctl start postgresql-18
systemctl status postgresql-18
[root@pgsql-server2 ~]# sed -i 's|Environment=PGDATA=.*|Environment=PGDATA=/opt/pgsql/data/|g' /usr/lib/systemd/system/postgresql-18.service
[root@pgsql-server2 ~]# chown -Rf postgres:postgres /opt/pgsql
[root@pgsql-server2 ~]# systemctl daemon-reload
[root@pgsql-server2 ~]# systemctl restart postgresql-18
systemctl status postgresql-18
● postgresql-18.service - PostgreSQL 18 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-18.service; enabled; pr>
     Active: active (running) since Tue 2026-05-05 19:30:20 CST; 18ms ago
       Docs: https://www.postgresql.org/docs/18/static/
    Process: 1699 ExecStartPre=/usr/pgsql-18/bin/postgresql-18-check-db-dir ${P>
   Main PID: 1705 (postgres)
      Tasks: 10 (limit: 22926)
     Memory: 22.1M
        CPU: 44ms
     CGroup: /system.slice/postgresql-18.service
             ├─1705 /usr/pgsql-18/bin/postgres -D /opt/pgsql/data/
             ├─1706 "postgres: logger "
             ├─1707 "postgres: io worker 0"
             ├─1708 "postgres: io worker 2"
             ├─1709 "postgres: io worker 1"
             ├─1710 "postgres: checkpointer "
             ├─1711 "postgres: background writer "
             ├─1713 "postgres: walwriter "
             ├─1714 "postgres: autovacuum launcher "
             └─1715 "postgres: logical replication launcher "

5月 05 19:30:20 pgsql-server2 systemd[1]: Starting PostgreSQL 18 database serve>
5月 05 19:30:20 pgsql-server2 postgres[1705]: 2026-05-05 19:30:20.454 CST [1705>
5月 05 19:30:20 pgsql-server2 postgres[1705]: 2026-05-05 19:30:20.454 CST [1705>
5月 05 19:30:20 pgsql-server2 systemd[1]: Started PostgreSQL 18 database server.
[root@pgsql-server2 ~]#

1.6. 测试流式复制

注意：在主库执行写操作，在从库执行查询操作！

-- 1. 创建 testdb_new_2026
create database testdb_new_2026;
\c testdb_new_2026;

-- 2. 创建新表（示例：员工表 employee，包含常用字段类型）
CREATE TABLE employee (
    emp_id SERIAL PRIMARY KEY,  -- 自增主键
    emp_name VARCHAR(50) NOT NULL,  -- 员工姓名
    emp_age INT CHECK (emp_age > 18),  -- 年龄（校验大于18）
    emp_dept VARCHAR(30),  -- 所属部门
    hire_date DATE DEFAULT CURRENT_DATE  -- 入职日期（默认当前日期）
);

-- 3. 插入多条测试数据
INSERT INTO employee (emp_name, emp_age, emp_dept) 
VALUES 
    ('张三', 25, '研发部'),
    ('李四', 30, '市场部'),
    ('王五', 35, '财务部'),
    ('赵六', 28, '运维部');

-- 4. 查询验证表和数据是否创建成功
SELECT * FROM employee;

主库

[postgres@pgsql-server1 ~]$ psql
psql (18.3)
输入 "help" 来获取帮助信息.

postgres=# \l
                                                         数据库列表
   名称    |  拥有者  | 字元编码 | Locale Provider |  校对规则   |    Ctype    |
 Locale | ICU Rules |       存取权限
-----------+----------+----------+-----------------+-------------+-------------+
--------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | zh_CN.UTF-8 | zh_CN.UTF-8 |
        |           |
 template0 | postgres | UTF8     | libc            | zh_CN.UTF-8 | zh_CN.UTF-8 |
        |           | =c/postgres          +
           |          |          |                 |             |             |
        |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | zh_CN.UTF-8 | zh_CN.UTF-8 |
        |           | =c/postgres          +
           |          |          |                 |             |             |
        |           | postgres=CTc/postgres
 test_db   | postgres | UTF8     | libc            | zh_CN.UTF-8 | zh_CN.UTF-8 |
        |           |
(4 行记录)

postgres=# create database testdb_new_2026;
CREATE DATABASE
postgres=# \c testdb_new_2026;
您现在已经连接到数据库 "testdb_new_2026",用户 "postgres".
testdb_new_2026=# CREATE TABLE employee (
    emp_id SERIAL PRIMARY KEY,  -- 自增主键
    emp_name VARCHAR(50) NOT NULL,  -- 员工姓名
    emp_age INT CHECK (emp_age > 18),  -- 年龄（校验大于18）
    emp_dept VARCHAR(30),  -- 所属部门
    hire_date DATE DEFAULT CURRENT_DATE  -- 入职日期（默认当前日期）
);
CREATE TABLE
testdb_new_2026=# INSERT INTO employee (emp_name, emp_age, emp_dept)
VALUES
    ('张三', 25, '研发部'),
    ('李四', 30, '市场部'),
    ('王五', 35, '财务部'),
    ('赵六', 28, '运维部');
INSERT 0 4
testdb_new_2026=# SELECT * FROM employee;
 emp_id | emp_name | emp_age | emp_dept | hire_date
--------+----------+---------+----------+------------
      1 | 张三     |      25 | 研发部   | 2026-05-05
      2 | 李四     |      30 | 市场部   | 2026-05-05
      3 | 王五     |      35 | 财务部   | 2026-05-05
      4 | 赵六     |      28 | 运维部   | 2026-05-05
(4 行记录)

testdb_new_2026=# \l
                                                            数据库列表
      名称       |  拥有者  | 字元编码 | Locale Provider |  校对规则   |    Ctyp
e    | Locale | ICU Rules |       存取权限
-----------------+----------+----------+-----------------+-------------+--------
-----+--------+-----------+-----------------------
 postgres        | postgres | UTF8     | libc            | zh_CN.UTF-8 | zh_CN.U
TF-8 |        |           |
 template0       | postgres | UTF8     | libc            | zh_CN.UTF-8 | zh_CN.U
TF-8 |        |           | =c/postgres          +
                 |          |          |                 |             |
     |        |           | postgres=CTc/postgres
 template1       | postgres | UTF8     | libc            | zh_CN.UTF-8 | zh_CN.U
TF-8 |        |           | =c/postgres          +
                 |          |          |                 |             |
     |        |           | postgres=CTc/postgres
 test_db         | postgres | UTF8     | libc            | zh_CN.UTF-8 | zh_CN.U
TF-8 |        |           |
 testdb_new_2026 | postgres | UTF8     | libc            | zh_CN.UTF-8 | zh_CN.U
TF-8 |        |           |
(5 行记录)

testdb_new_2026=# \dt
             List of tables
 架构模式 |   名称   |  类型  |  拥有者
----------+----------+--------+----------
 public   | employee | 数据表 | postgres
(1 行记录)

testdb_new_2026=#

从库

[postgres@pgsql-server2 ~]$ psql
psql (18.3)
输入 "help" 来获取帮助信息.

postgres=#
postgres=#
postgres=# \l
                                                            数据库列表
      名称       |  拥有者  | 字元编码 | Locale Provider |  校对规则   |    Ctyp
e    | Locale | ICU Rules |       存取权限
-----------------+----------+----------+-----------------+-------------+--------
-----+--------+-----------+-----------------------
 postgres        | postgres | UTF8     | libc            | zh_CN.UTF-8 | zh_CN.U
TF-8 |        |           |
 template0       | postgres | UTF8     | libc            | zh_CN.UTF-8 | zh_CN.U
TF-8 |        |           | =c/postgres          +
                 |          |          |                 |             |
     |        |           | postgres=CTc/postgres
 template1       | postgres | UTF8     | libc            | zh_CN.UTF-8 | zh_CN.U
TF-8 |        |           | =c/postgres          +
                 |          |          |                 |             |
     |        |           | postgres=CTc/postgres
 test_db         | postgres | UTF8     | libc            | zh_CN.UTF-8 | zh_CN.U
TF-8 |        |           |
 testdb_new_2026 | postgres | UTF8     | libc            | zh_CN.UTF-8 | zh_CN.U
TF-8 |        |           |
(5 行记录)

postgres=# \c testdb_new_2026
您现在已经连接到数据库 "testdb_new_2026",用户 "postgres".
testdb_new_2026=# \dt
             List of tables
 架构模式 |   名称   |  类型  |  拥有者
----------+----------+--------+----------
 public   | employee | 数据表 | postgres
(1 行记录)

testdb_new_2026=# select * from employee;
 emp_id | emp_name | emp_age | emp_dept | hire_date
--------+----------+---------+----------+------------
      1 | 张三     |      25 | 研发部   | 2026-05-05
      2 | 李四     |      30 | 市场部   | 2026-05-05
      3 | 王五     |      35 | 财务部   | 2026-05-05
      4 | 赵六     |      28 | 运维部   | 2026-05-05
(4 行记录)

testdb_new_2026=#

查看主从状态

在主库执行

# 1. 切换到 postgres 用户（必须用这个用户，普通用户权限不足）
su - postgres

# 2. 进入 PostgreSQL 命令行（直接执行SQL）
psql

# 3. 执行查询命令（推荐用精简版，只看核心字段）
SELECT 
  pid,                -- 主库wal_sender进程的PID
  client_addr,        -- 从库的IP地址（关键！确认是哪个从库）
  application_name,   -- 从库的应用名称（默认是walreceiver）
  state,              -- 通信状态（核心！streaming=正常）
  sent_lsn,           -- 主库已发送的WAL位点
  replay_lsn,         -- 从库已重放的WAL位点
  write_lag           -- 从库接收WAL到写入本地的延迟（毫秒级）
FROM pg_stat_replication;

# 也可以执行极简版，只看关键信息
SELECT client_addr, state FROM pg_stat_replication;
[postgres@pgsql-server1 ~]$ psql
psql (18.3)
输入 "help" 来获取帮助信息.

postgres=# SELECT
  pid,                -- 主库wal_sender进程的PID
  client_addr,        -- 从库的IP地址（关键！确认是哪个从库）
  application_name,   -- 从库的应用名称（默认是walreceiver）
  state,              -- 通信状态（核心！streaming=正常）
  sent_lsn,           -- 主库已发送的WAL位点
  replay_lsn,         -- 从库已重放的WAL位点
  write_lag           -- 从库接收WAL到写入本地的延迟（毫秒级）
FROM pg_stat_replication;
 pid  |  client_addr  | application_name |   state   |  sent_lsn  | replay_lsn |
 write_lag
------+---------------+------------------+-----------+------------+------------+
-----------
 1856 | 192.168.80.22 | walreceiver      | streaming | 0/1F441C00 | 0/1F441C00 |

(1 行记录)

postgres=# SELECT client_addr, state FROM pg_stat_replication;
  client_addr  |   state
---------------+-----------
 192.168.80.22 | streaming
(1 行记录)

postgres=#

在从库执行

# 1. 切换到 postgres 用户
su - postgres

# 2. 进入 PostgreSQL 命令行
psql

# 3. 执行查询命令（精简版，只看核心字段）
-- 从库执行，适配当前 PG18 版本的查询
SELECT
  pid,                -- 从库wal_receiver进程的PID
  status,             -- 接收状态（核心！streaming=正常）
  conninfo,           -- 连接的主库信息（IP、用户等）
  latest_end_lsn,     -- 从库已重放的WAL位点
  last_msg_send_time  -- 最后一次向主库反馈进度的时间
FROM pg_stat_wal_receiver;

# 极简版（快速看状态）
SELECT status, conninfo FROM pg_stat_wal_receiver;
[postgres@pgsql-server2 ~]$ psql
psql (18.3)
输入 "help" 来获取帮助信息.

postgres=# SELECT status, conninfo FROM pg_stat_wal_receiver;
  status   |

                               conninfo


-----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------------------------
 streaming | user=replicator password=******** channel_binding=prefer dbname=rep
lication host=192.168.80.21 port=5432 fallback_application_name=walreceiver sslm
ode=prefer sslnegotiation=postgres sslcompression=0 sslcertmode=allow sslsni=1 s
sl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegat
ion=0 target_session_attrs=any load_balance_hosts=disable
(1 行记录)

postgres=#
[postgres@pgsql-server2 ~]$ cat /opt/pgsql/data/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
summarize_wal = 'on'
shared_buffers = '256MB'
work_mem = '6MB'
primary_conninfo = 'user=replicator password=replicator channel_binding=prefer host=192.168.80.21 port=5432 sslmode=prefer sslnegotiation=postgres sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'
[postgres@pgsql-server2 ~]$

1.7. 总结

流式复制的核心是「主库被动开放权限 + 从库主动发起连接」，主库无需配置从库信息，从库连接信息由 pg_basebackup -R 自动生成，是实现「隐形连接」的关键；
状态验证的核心判据：主库 state=streaming + 从库 status=streaming，即可确认主从通信正常，数据同步无异常；
WAL 字节级复制保证了所有数据修改（DML）和结构修改（DDL）都能实时同步，是 PostgreSQL 高可用的基础。

2. 一主两从

2.1. 环境说明

主库：192.168.88.11（节点名 pg1）
从库1：192.168.88.12（节点名 pg2）
从库2：192.168.88.13（节点名 pg3）
PostgreSQL 版本：18
操作系统：RHEL 9 / CentOS Stream 9

hostnamectl set-hostname pg1 && bash
hostnamectl set-hostname pg2 && bash
hostnamectl set-hostname pg3 && bash

2.1.1. 阿里源（可选）

cat >/etc/yum.repos.d/aliyun.repo<<EOF
[baseos]
name=CentOS Stream \$releasever - BaseOS
baseurl=https://mirrors.aliyun.com/centos-stream/\$stream/BaseOS/\$basearch/os/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
countme=1
enabled=1

[baseos-debug]
name=CentOS Stream \$releasever - BaseOS - Debug
baseurl=https://mirrors.aliyun.com/centos-stream/\$stream/BaseOS/\$basearch/debug/tree/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
enabled=0

[baseos-source]
name=CentOS Stream \$releasever - BaseOS - Source
baseurl=https://mirrors.aliyun.com/centos-stream/\$stream/BaseOS/source/tree/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
enabled=0

[appstream]
name=CentOS Stream \$releasever - AppStream
baseurl=https://mirrors.aliyun.com/centos-stream/\$stream/AppStream/\$basearch/os/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
countme=1
enabled=1

[appstream-debug]
name=CentOS Stream \$releasever - AppStream - Debug
baseurl=https://mirrors.aliyun.com/centos-stream/\$stream/AppStream/\$basearch/debug/tree/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
enabled=0

[appstream-source]
name=CentOS Stream \$releasever - AppStream - Source
baseurl=https://mirrors.aliyun.com/centos-stream/\$stream/AppStream/\$basearch/debug/tree/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
enabled=0

[crb]
name=CentOS Stream \$releasever - CRB
baseurl=https://mirrors.aliyun.com/centos-stream/\$stream/CRB/\$basearch/os/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
countme=1
enabled=0

[crb-debug]
name=CentOS Stream \$releasever - CRB - Debug
baseurl=https://mirrors.aliyun.com/centos-stream/\$stream/CRB/\$basearch/debug/tree/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
enabled=0

[crb-source]
name=CentOS Stream \$releasever - CRB - Source
baseurl=https://mirrors.aliyun.com/centos-stream/\$stream/CRB/source/tree/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
enabled=0

[highavailability]
name=CentOS Stream \$releasever - HighAvailability
baseurl=http://mirrors.aliyun.com/centos-stream/\$stream/HighAvailability/\$basearch/os/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
countme=1
enabled=0

[highavailability-debug]
name=CentOS Stream \$releasever - HighAvailability - Debug
baseurl=http://mirrors.aliyun.com/centos-stream/\$stream/HighAvailability/\$basearch/debug/tree/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
enabled=0

[highavailability-source]
name=CentOS Stream \$releasever - HighAvailability - Source
baseurl=http://mirrors.aliyun.com/centos-stream/\$stream/HighAvailability/source/tree/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
enabled=0

[nfv]
name=CentOS Stream \$releasever - NFV
baseurl=http://mirrors.aliyun.com/centos-stream/\$stream/NFV/\$basearch/os/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
countme=1
enabled=0

[nfv-debug]
name=CentOS Stream \$releasever - NFV - Debug
baseurl=http://mirrors.aliyun.com/centos-stream/\$stream/NFV/\$basearch/debug/tree/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
enabled=0

[nfv-source]
name=CentOS Stream \$releasever - NFV - Source
baseurl=http://mirrors.aliyun.com/centos-stream/\$stream/NFV/source/tree/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
enabled=0

[rt]
name=CentOS Stream \$releasever - RT
baseurl=http://mirrors.aliyun.com/centos-stream/\$stream/RT/\$basearch/os/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
countme=1
enabled=0

[rt-debug]
name=CentOS Stream \$releasever - RT - Debug
baseurl=http://mirrors.aliyun.com/centos-stream/\$stream/RT/\$basearch/debug/tree/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
enabled=0

[rt-source]
name=CentOS Stream \$releasever - RT - Source
baseurl=http://mirrors.aliyun.com/centos-stream/\$stream/RT/source/tree/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
enabled=0

[resilientstorage]
name=CentOS Stream \$releasever - ResilientStorage
baseurl=http://mirrors.aliyun.com/centos-stream/\$stream/ResilientStorage/\$basearch/os/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
countme=1
enabled=0

[resilientstorage-debug]
name=CentOS Stream \$releasever - ResilientStorage - Debug
baseurl=http://mirrors.aliyun.com/centos-stream/\$stream/ResilientStorage/\$basearch/debug/tree/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
enabled=0

[resilientstorage-source]
name=CentOS Stream \$releasever - ResilientStorage - Source
baseurl=http://mirrors.aliyun.com/centos-stream/\$stream/ResilientStorage/source/tree/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
enabled=0

[extras-common]
name=CentOS Stream \$releasever - Extras packages
baseurl=http://mirrors.aliyun.com/centos-stream/SIGs/\$stream/extras/\$basearch/extras-common/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-SIG-Extras-SHA512
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
countme=1
enabled=1

[extras-common-source]
name=CentOS Stream \$releasever - Extras packages - Source
baseurl=http://mirrors.aliyun.com/centos-stream/SIGs/\$stream/extras/source/extras-common/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-SIG-Extras-SHA512
gpgcheck=1
repo_gpgcheck=0
metadata_expire=6h
enabled=0
EOF

安装 PostgreSQL（所有节点执行）

dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# 替换为清华大学镜像源（了解）
# sed -i 's|download.postgresql.org/pub|mirrors.tuna.tsinghua.edu.cn/postgresql|g' /etc/yum.repos.d/pgdg-redhat-all.repo

# 安装
dnf install -y postgresql18-server

# 离线安装（了解）
dnf localinstall postgresql18-server-18.3-1PGDG.rhel9.7.x86_64.rpm -y

# 初始化
/usr/pgsql-18/bin/postgresql-18-setup initdb

# 启动
systemctl start postgresql-18
systemctl enable postgresql-18
systemctl status postgresql-18 --no-pager

# 配置 PATH 环境变量
echo 'export PATH=/usr/pgsql-18/bin:$PATH' >> /etc/profile
source /etc/profile

PostgreSQL 整体架构原理和高可用2.png

PostgreSQL 整体架构原理和高可用3.png

PostgreSQL 整体架构原理和高可用4.png

2.2. PostgreSQL 18 主从复制（一主两从）

说明

基于 RHEL 9/CentOS Stream 9 系统，完成 PostgreSQL 18 一主（192.168.88.11/pg1）两从（192.168.88.12/pg2、192.168.88.13/pg3）架构的搭建，并验证数据同步效果。

前置条件

三台机器完成 PostgreSQL 18 基础安装、初始化、启动并设置开机自启；
三台机器之间网络互通（关闭防火墙/放行 5432 端口、关闭 SELinux）；
所有操作均使用 root 用户或具有 sudo 权限的用户执行。

2.3. 第一步：环境准备（所有节点执行）

关闭防火墙和 SELinux

# 关闭防火墙
iptables -F
systemctl stop firewalld
systemctl disable firewalld

# 关闭 SELinux
setenforce 0
sed -i 's/^SELINUX=.*/SELINUX=disabled/' /etc/selinux/config

切换到 postgres 用户（PostgreSQL 内置管理用户）

su - postgres

2.4. 第二步：主库（pg1/192.168.88.11）配置

修改 postgresql.conf 配置文件

该文件是 PostgreSQL 核心配置文件，默认路径：/var/lib/pgsql/18/data/postgresql.conf。

# 编辑配置文件
cp /var/lib/pgsql/18/data/postgresql.conf /var/lib/pgsql/18/data/postgresql.conf.bak
sed -i '/^\s*#/d; /^\s*$/d' /var/lib/pgsql/18/data/postgresql.conf
vim /var/lib/pgsql/18/data/postgresql.conf

修改以下关键参数：

# 监听地址（允许所有地址访问）
listen_addresses = '*'

# 开启 WAL（预写日志）归档，主从复制依赖 WAL
wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/18/archive/%f'  # 归档命令，需先创建归档目录
max_wal_senders = 10  # 允许的最大从库连接数（至少大于从库数量）
wal_keep_size = 16MB  # 保留的 WAL 文件大小，防止从库同步时 WAL 被清理
hot_standby = on  # 支持热备
[postgres@pg1 ~]$ sed -i '/^\s*#/d; /^\s*$/d' /var/lib/pgsql/18/data/postgresql.conf
[postgres@pg1 ~]$ vim /var/lib/pgsql/18/data/postgresql.conf
[postgres@pg1 ~]$ cat /var/lib/pgsql/18/data/postgresql.conf
max_connections = 100                   # (change requires restart)
shared_buffers = 128MB                  # min 128kB
dynamic_shared_memory_type = posix      # the default is usually the first option
max_wal_size = 1GB
min_wal_size = 80MB
log_destination = 'stderr'              # Valid values are combinations of
logging_collector = on                  # Enable capturing of stderr, jsonlog,
log_directory = 'log'                   # directory where log files are written,
log_filename = 'postgresql-%a.log'      # log file name pattern,
log_rotation_age = 1d                   # Automatic rotation of logfiles will
log_rotation_size = 0                   # Automatic rotation of logfiles will
log_truncate_on_rotation = on           # If on, an existing log file with the
log_line_prefix = '%m [%p] '            # special values:
log_timezone = 'Asia/Shanghai'
autovacuum_worker_slots = 16            # autovacuum worker slots to allocate
datestyle = 'iso, mdy'
timezone = 'Asia/Shanghai'
lc_messages = 'en_US.UTF-8'             # locale for system error message
lc_monetary = 'en_US.UTF-8'             # locale for monetary formatting
lc_numeric = 'en_US.UTF-8'              # locale for number formatting
lc_time = 'en_US.UTF-8'                 # locale for time formatting
default_text_search_config = 'pg_catalog.english'

listen_addresses = '*'
wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/18/archive/%f'
max_wal_senders = 10
wal_keep_size = 16MB
hot_standby = on
[postgres@pg1 ~]$

创建 WAL 归档目录并授权

# 退出 postgres 用户，用 root 创建目录
exit
mkdir -p /var/lib/pgsql/18/archive
chown -R postgres:postgres /var/lib/pgsql/18/archive
chmod 700 /var/lib/pgsql/18/archive

# 重新切换到 postgres 用户
su - postgres

修改 pg_hba.conf 配置文件

该文件用于配置 PostgreSQL 访问权限，默认路径：/var/lib/pgsql/18/data/pg_hba.conf。

cp /var/lib/pgsql/18/data/pg_hba.conf /var/lib/pgsql/18/data/pg_hba.conf.bak
sed -i '/^\s*#/d; /^\s*$/d' /var/lib/pgsql/18/data/pg_hba.conf
vim /var/lib/pgsql/18/data/pg_hba.conf

在文件末尾添加以下内容（允许从库通过复制用户访问主库）：

# 允许从库（192.168.88.12/13）通过 replication 复制用户访问
host    replication     replication     192.168.88.12/32        scram-sha-256
host    replication     replication     192.168.88.13/32        scram-sha-256

systemctl restart postgresql-18
systemctl status postgresql-18 --no-pager

注意：以下是拓展操作，跟主从没关系！
vim /var/lib/pgsql/18/data/pg_hba.conf
# 允许本机/远程客户端通过密码访问（针对普通业务用户的访问权限，而非复制用户；可选，方便测试）
host    all             all             192.168.88.0/24         scram-sha-256

systemctl restart postgresql-18
systemctl status postgresql-18 --no-pager

su - postgres
psql
create database testdb;

# 基本格式：psql -h 远程IP -p 端口 -U 用户名 -d 数据库名
psql -h 192.168.88.11 -p 5432 -U postgres -d testdb
psql -h 192.168.88.11 -p 5432 -U postgres
PGPASSWORD='PostgreSQL@666' psql -h 192.168.88.11 -p 5432 -U postgres -d testdb

# 说明：
# -h：远程数据库服务器的IP地址或域名
# -p：PG端口（默认5432，若修改过需填写）
# -U：连接使用的PG用户名（默认postgres）
# -d：要连接的数据库名（若不填，默认连接与用户名同名的数据库）

-- 修改postgres用户的密码
psql
ALTER USER postgres WITH PASSWORD 'PostgreSQL@666';

-- 退出psql
\q
[postgres@pg1 ~]$ cp /var/lib/pgsql/18/data/pg_hba.conf /var/lib/pgsql/18/data/pg_hba.conf.bak
[postgres@pg1 ~]$ sed -i '/^\s*#/d; /^\s*$/d' /var/lib/pgsql/18/data/pg_hba.conf
[postgres@pg1 ~]$ vim /var/lib/pgsql/18/data/pg_hba.conf
[postgres@pg1 ~]$ cat /var/lib/pgsql/18/data/pg_hba.conf
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

host    replication     replication     192.168.88.12/32    scram-sha-256
host    replication     replication     192.168.88.13/32    scram-sha-256
[postgres@pg1 ~]$
[root@pg2 ~]# psql -h 192.168.88.11 -p 5432 -U postgres
Password for user postgres:
psql (18.3)
Type "help" for help.

postgres-# \l
                                                     List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | Loc
ale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+-------------+-------------+----
----+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
    |           |
 template0 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
    |           | =c/postgres          +
           |          |          |                 |             |             |
    |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
    |           | =c/postgres          +
           |          |          |                 |             |             |
    |           | postgres=CTc/postgres
 testdb    | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
    |           |
(4 rows)

postgres-# 

[root@pg2 ~]# psql -h 192.168.88.11 -p 5432 -U postgres -d testdb
Password for user postgres:
psql (18.3)
Type "help" for help.

testdb=#
testdb=#
testdb=# \q
[root@pg2 ~]#
[root@pg2 ~]# PGPASSWORD='PostgreSQL@666' psql -h 192.168.88.11 -p 5432 -U postgres -d testdb
psql (18.3)
Type "help" for help.

testdb=# \q
[root@pg2 ~]#

[root@pg3 ~]# PGPASSWORD='PostgreSQL@666' psql -h 192.168.88.11 -p 5432 -U postgres -d testdb
psql (18.3)
Type "help" for help.

testdb=# \q
[root@pg3 ~]#

创建复制用户

主从复制需要一个专门的用户（这里命名为 replication），用于从库连接主库同步数据：

# 进入 PostgreSQL 命令行
su - postgres
psql

# 创建复制用户（设置密码为 PostgreSQL@666，可自定义）
CREATE ROLE replication REPLICATION LOGIN ENCRYPTED PASSWORD 'PostgreSQL@666';

# 退出 PostgreSQL 命令行
\q

重启主库使配置生效

# 退出 postgres 用户
exit

# 重启 PostgreSQL 18
systemctl restart postgresql-18
systemctl status postgresql-18 --no-pager

2.5. 第三步：从库（pg2/192.168.88.12、pg3/192.168.88.13）配置

注意：两台从库操作完全一致，以下步骤需在 pg2 和 pg3 分别执行。

停止从库 PostgreSQL 服务

systemctl stop postgresql-18

清空从库默认数据目录

# 备份并清空数据目录
mv /var/lib/pgsql/18/data /var/lib/pgsql/18/data.bak
mkdir -p /var/lib/pgsql/18/data
chown -R postgres:postgres /var/lib/pgsql/18/data

从主库同步基础数据（使用 pg_basebackup）

pg_basebackup 是 PostgreSQL 官方工具，用于从主库拉取完整数据副本：

# 切换到 postgres 用户
su - postgres

# 在从库执行同步命令（替换主库 IP 和复制用户密码）
-- 在 pg2 上执行
pg_basebackup -h 192.168.88.11 -U replication -D /var/lib/pgsql/18/data -P -X stream -C -S standby1

-- 在 pg3 上执行
pg_basebackup -h 192.168.88.11 -U replication -D /var/lib/pgsql/18/data -P -X stream -C -S standby2

# 注意：需要输入密码 PostgreSQL@666

# 参数说明：
# -h：主库 IP
# -U：复制用户
# -D：从库数据目录
# -P：显示进度
# -X stream：流式复制 WAL 日志
# -C：自动创建复制槽
# -S：复制槽名称（standby1，可自定义；不同从库建议不同名称，如 pg2 用 standby1，pg3 用 standby2）

执行后输入复制用户密码（PostgreSQL@666），等待数据同步完成。

创建从库专属配置文件（recovery.conf / standby.signal）

PostgreSQL 12+ 版本取消了 recovery.conf，改用 standby.signal 文件标识从库，并在 postgresql.conf 中配置复制参数。

# 切换到 postgres 用户
su - postgres

# 创建 standby.signal 文件，标识当前节点为从库
touch /var/lib/pgsql/18/data/standby.signal

# 编辑 postgresql.conf，添加复制参数
vim /var/lib/pgsql/18/data/postgresql.conf

添加/修改以下参数：

# 从库核心配置
primary_conninfo = 'host=192.168.88.11 port=5432 user=replication password=PostgreSQL@666 application_name=standby1'

primary_conninfo = 'host=192.168.88.11 port=5432 user=replication password=PostgreSQL@666 application_name=standby2'

# 说明：
# host：主库 IP
# user/password：复制用户和密码
# application_name：复制槽名称（需和 pg_basebackup 的 -S 参数一致）

# 其他辅助配置
hot_standby = on  # 允许从库只读访问
max_standby_streaming_delay = 30s  # 最大同步延迟
wal_receiver_status_interval = 10s  # 从库向主库报告状态的间隔
recovery_target_timeline = 'latest'  # 同步到最新的时间线
[root@pg2 ~]# su - postgres
[postgres@pg2 ~]$ pg_basebackup -h 192.168.88.11 -U replication -D /var/lib/pgsql/18/data -P -X stream -C -S standby1
Password:
23675/23675 kB (100%), 1/1 tablespace
[postgres@pg2 ~]$ ls /var/lib/pgsql/18/data
backup_label      pg_commit_ts     pg_multixact  pg_stat_tmp  pg_xact
backup_manifest   pg_dynshmem      pg_notify     pg_subtrans  postgresql.auto.conf
base              pg_hba.conf      pg_replslot   pg_tblspc    postgresql.conf
current_logfiles  pg_hba.conf.bak  pg_serial     pg_twophase
global            pg_ident.conf    pg_snapshots  PG_VERSION
log               pg_logical       pg_stat       pg_wal
[postgres@pg2 ~]$ touch /var/lib/pgsql/18/data/standby.signal
[postgres@pg2 ~]$ vim /var/lib/pgsql/18/data/postgresql.conf
[postgres@pg2 ~]$ cat /var/lib/pgsql/18/data/postgresql.conf
max_connections = 100                   # (change requires restart)
shared_buffers = 128MB                  # min 128kB
dynamic_shared_memory_type = posix      # the default is usually the first option
max_wal_size = 1GB
min_wal_size = 80MB
log_destination = 'stderr'              # Valid values are combinations of
logging_collector = on                  # Enable capturing of stderr, jsonlog,
log_directory = 'log'                   # directory where log files are written,
log_filename = 'postgresql-%a.log'      # log file name pattern,
log_rotation_age = 1d                   # Automatic rotation of logfiles will
log_rotation_size = 0                   # Automatic rotation of logfiles will
log_truncate_on_rotation = on           # If on, an existing log file with the
log_line_prefix = '%m [%p] '            # special values:
log_timezone = 'Asia/Shanghai'
autovacuum_worker_slots = 16            # autovacuum worker slots to allocate
datestyle = 'iso, mdy'
timezone = 'Asia/Shanghai'
lc_messages = 'en_US.UTF-8'             # locale for system error message
lc_monetary = 'en_US.UTF-8'             # locale for monetary formatting
lc_numeric = 'en_US.UTF-8'              # locale for number formatting
lc_time = 'en_US.UTF-8'                 # locale for time formatting
default_text_search_config = 'pg_catalog.english'

listen_addresses = '*'
wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/18/archive/%f'
max_wal_senders = 10
wal_keep_size = 16MB
hot_standby = on

primary_conninfo = 'host=192.168.88.11 port=5432 user=replication password=PostgreSQL@666 application_name=standby1'

max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
recovery_target_timeline = 'latest'
[postgres@pg2 ~]$
[root@pg3 ~]# su - postgres
[postgres@pg3 ~]$ pg_basebackup -h 192.168.88.11 -U replication -D /var/lib/pgsql/18/data -P -X stream -C -S standby2
Password:
23675/23675 kB (100%), 1/1 tablespace
[postgres@pg3 ~]$ ls /var/lib/pgsql/18/data
backup_label      pg_commit_ts     pg_multixact  pg_stat_tmp  pg_xact
backup_manifest   pg_dynshmem      pg_notify     pg_subtrans  postgresql.auto.conf
base              pg_hba.conf      pg_replslot   pg_tblspc    postgresql.conf
current_logfiles  pg_hba.conf.bak  pg_serial     pg_twophase
global            pg_ident.conf    pg_snapshots  PG_VERSION
log               pg_logical       pg_stat       pg_wal
[postgres@pg3 ~]$ touch /var/lib/pgsql/18/data/standby.signal
[postgres@pg3 ~]$ vim /var/lib/pgsql/18/data/postgresql.conf
[postgres@pg3 ~]$ cat /var/lib/pgsql/18/data/postgresql.conf
max_connections = 100                   # (change requires restart)
shared_buffers = 128MB                  # min 128kB
dynamic_shared_memory_type = posix      # the default is usually the first option
max_wal_size = 1GB
min_wal_size = 80MB
log_destination = 'stderr'              # Valid values are combinations of
logging_collector = on                  # Enable capturing of stderr, jsonlog,
log_directory = 'log'                   # directory where log files are written,
log_filename = 'postgresql-%a.log'      # log file name pattern,
log_rotation_age = 1d                   # Automatic rotation of logfiles will
log_rotation_size = 0                   # Automatic rotation of logfiles will
log_truncate_on_rotation = on           # If on, an existing log file with the
log_line_prefix = '%m [%p] '            # special values:
log_timezone = 'Asia/Shanghai'
autovacuum_worker_slots = 16            # autovacuum worker slots to allocate
datestyle = 'iso, mdy'
timezone = 'Asia/Shanghai'
lc_messages = 'en_US.UTF-8'             # locale for system error message
lc_monetary = 'en_US.UTF-8'             # locale for monetary formatting
lc_numeric = 'en_US.UTF-8'              # locale for number formatting
lc_time = 'en_US.UTF-8'                 # locale for time formatting
default_text_search_config = 'pg_catalog.english'

listen_addresses = '*'
wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/18/archive/%f'
max_wal_senders = 10
wal_keep_size = 16MB
hot_standby = on

primary_conninfo = 'host=192.168.88.11 port=5432 user=replication password=PostgreSQL@666 application_name=standby2'

max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
recovery_target_timeline = 'latest'
[postgres@pg3 ~]$

创建 archive 目录与 data 目录配置权限

# 0. 切换到 root
su - root

# 1. 创建 archive
mkdir -p /var/lib/pgsql/18/archive
chown -Rf postgres:postgres /var/lib/pgsql/18/archive

# 2. 设置目录权限为 0700（仅 postgres 用户可访问）
chmod 0700 /var/lib/pgsql/18/data

# 3. 确保目录属主和属组都是 postgres（防止之前权限设置不彻底）
chown -Rf postgres:postgres /var/lib/pgsql/18/data

# 4. 修复 SELinux 上下文（即使是 Permissive 模式也建议执行）
restorecon -Rv /var/lib/pgsql/18/data

启动从库并设置开机自启

# 切换到 root
su - root

# 启动从库
systemctl start postgresql-18
systemctl enable postgresql-18

# 查看从库状态
systemctl status postgresql-18 --no-pager

2.6. 第四步：验证主从复制架构

主库验证（pg1）

# 切换到 postgres 用户，进入 PostgreSQL 命令行
su - postgres
psql

# 查看复制状态（确认从库已连接）
SELECT client_addr, application_name, state FROM pg_stat_replication;

# 预期输出（会显示两台从库的 IP 和状态为 streaming）：
#  client_addr  | application_name |   state
# --------------+------------------+-----------
#  192.168.88.12 | standby1         | streaming
#  192.168.88.13 | standby2         | streaming

PostgreSQL 整体架构原理和高可用5.png

主库插入测试数据

-- 在主库创建测试数据库
CREATE DATABASE test_db;

-- 切换到 test_db
\c test_db;

-- 创建测试表
CREATE TABLE user_info (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

-- 插入测试数据
INSERT INTO user_info (name, age) VALUES ('张三', 25), ('李四', 30), ('王五', 35);

-- 查询数据（验证插入成功）
SELECT * FROM user_info;

-- 退出 PostgreSQL 命令行
\q

PostgreSQL 整体架构原理和高可用6.png

从库验证数据同步（pg2/pg3 分别执行）

# 切换到 postgres 用户，进入 PostgreSQL 命令行
su - postgres
psql

# 查看是否存在 test_db 数据库
\l  # 列表中应能看到 test_db

# 切换到 test_db
\c test_db;

# 查询 user_info 表数据（应和主库一致）
SELECT * FROM user_info;

# 预期输出：
#  id | name | age
# ----+------+-----
#   1 | 张三 |  25
#   2 | 李四 |  30
#   3 | 王五 |  35

PostgreSQL 整体架构原理和高可用7.png

PostgreSQL 整体架构原理和高可用8.png

PostgreSQL 整体架构原理和高可用9.png

PostgreSQL 整体架构原理和高可用10.png

2.7. 常见问题排查

从库启动失败：检查 /var/lib/pgsql/18/data/pg_log 目录下的日志文件，查看具体错误；
数据同步失败：
1. 验证主库 pg_hba.conf 是否允许从库 IP 访问；
2. 验证复制用户密码是否正确；
3. 检查主库 max_wal_senders 参数值是否大于从库数量；
从库无法访问：检查防火墙是否放行 5432 端口，SELinux 是否关闭。

经典问题

ERROR: replication slot "standby1" already exists

[postgres@pg2 ~]$ pg_basebackup -h 192.168.88.11 -U replication -D /var/lib/pgsql/18/data -P -X stream -C -S standby1
Password:PostgreSQL@666
pg_basebackup: error: directory "/var/lib/pgsql/18/data" exists but is not empty
[postgres@pg2 ~]$ rm -rf /var/lib/pgsql/18/data/*
[postgres@pg2 ~]$ pg_basebackup -h 192.168.88.11 -U replication -D /var/lib/pgsql/18/data -P -X stream -C -S standby1
Password:PostgreSQL@666
pg_basebackup: error: could not send replication command "CREATE_REPLICATION_SLOT "standby1" PHYSICAL ( RESERVE_WAL)": ERROR:  replication slot "standby1" already exists
pg_basebackup: removing contents of data directory "/var/lib/pgsql/18/data"
[postgres@pg2 ~]$

解决方法：

到主库上执行以下操作！

SELECT pg_drop_replication_slot('standby1');
SELECT slot_name, active, restart_lsn FROM pg_replication_slots;
[postgres@pg1 ~]$ psql
psql (18.3)
Type "help" for help.

postgres=# SELECT client_addr, application_name, state FROM pg_stat_replication;
  client_addr  | application_name |   state
---------------+------------------+-----------
 192.168.88.12 | standby1         | streaming
 192.168.88.13 | standby2         | streaming
(1 row)

postgres=# SELECT pg_drop_replication_slot('standby1');
 pg_drop_replication_slot
--------------------------

(1 row)

postgres=# SELECT client_addr, application_name, state FROM pg_stat_replication;
  client_addr  | application_name |   state
---------------+------------------+-----------
 192.168.88.13 | standby2         | streaming
(1 row)

postgres=# SELECT slot_name, active, restart_lsn FROM pg_replication_slots;
 slot_name | active | restart_lsn
-----------+--------+-------------
 standby2  | f      | 0/4000000
(1 row)

postgres=#

postgres=# SELECT client_addr, application_name, state FROM pg_stat_replication;
  client_addr  | application_name |   state
---------------+------------------+-----------
 192.168.88.13 | standby2         | streaming
 192.168.88.12 | standby1         | streaming
(2 rows)

postgres=#
[postgres@pg2 ~]$ pg_basebackup -h 192.168.88.11 -U replication -D /var/lib/pgsql/18/data -P -X stream -C -S standby1
Password:
39265/39265 kB (100%), 1/1 tablespace
[postgres@pg2 ~]$ ls /var/lib/pgsql/18/data
backup_label      pg_commit_ts     pg_multixact  pg_stat_tmp  pg_xact
backup_manifest   pg_dynshmem      pg_notify     pg_subtrans  postgresql.auto.conf
base              pg_hba.conf      pg_replslot   pg_tblspc    postgresql.conf
current_logfiles  pg_hba.conf.bak  pg_serial     pg_twophase  postgresql.conf.bak
global            pg_ident.conf    pg_snapshots  PG_VERSION
log               pg_logical       pg_stat       pg_wal
[postgres@pg2 ~]$

2.8. 总结

主从复制核心依赖 WAL 日志，主库需开启 wal_level = replica 并配置归档，从库通过 pg_basebackup 同步主库基础数据；
主库需创建复制用户并配置 pg_hba.conf 授权从库访问，从库通过 standby.signal 标识身份并配置 primary_conninfo 连接主库；
验证主从同步的关键：主库插入数据后，从库能查询到相同数据，且主库 pg_stat_replication 能看到从库的 streaming 状态。

分享

PostgreSQL 整体架构原理和高可用

PostgreSQL 整体架构原理和高可用

一、整体架构原理

1. 内存结构

1.1. 共享缓冲区 (Shared Buffers)

1.2. WAL 缓冲区 (WAL Buffers)

1.3. CLOG Buffer

1.4. 工作内存 (Work Mem)

1.5. 维护内存 (Maintenance Work Mem)

1.6. 临时缓冲区

2. 文件结构

2.1. 数据文件

2.2. WAL 文件

2.3. 日志文件

3. 进程

3.1. 后端工作进程（Backend Process）

3.2. 数据写入进程（Data Writer）

3.3. 检查点进程（Checkpointer）

3.4. 自动清理进程（AutoVacuum）

3.5. WAL 写入进程（WAL Writer）

3.6. Statistics Collector

3.7. 日志收集器

4. 面试题

4.1. PostgreSQL 中 Shared Buffers 和 Work Mem 的核心区别是什么？分别对应内存结构的哪一层？

4.2. WAL Writer 进程的核心作用是什么？调整 wal_writer_delay 参数时，需要权衡的性能与安全点是什么？

4.3. 为什么生产环境不建议将 log_statement 参数设为 all？

4.4. 总结

二、高可用

1. 流式复制

1.1. 从库安装 PostgreSQL

阿里源（可选）

1.2. 配置主库

1.3. 在主库创建用户并配置hba

1.4. 从库拉取主库备份（在从库操作）

1.5. 修改从库数据目录并重启从库

1.6. 测试流式复制

1.7. 总结

2. 一主两从

2.1. 环境说明

2.1.1. 阿里源（可选）

2.2. PostgreSQL 18 主从复制（一主两从）

说明

前置条件

2.3. 第一步：环境准备（所有节点执行）

关闭防火墙和 SELinux

切换到 postgres 用户（PostgreSQL 内置管理用户）

2.4. 第二步：主库（pg1/192.168.88.11）配置

修改 postgresql.conf 配置文件

创建 WAL 归档目录并授权

修改 pg_hba.conf 配置文件

创建复制用户

重启主库使配置生效

2.5. 第三步：从库（pg2/192.168.88.12、pg3/192.168.88.13）配置

停止从库 PostgreSQL 服务

清空从库默认数据目录

从主库同步基础数据（使用 pg_basebackup）

创建从库专属配置文件（recovery.conf / standby.signal）

启动从库并设置开机自启

2.6. 第四步：验证主从复制架构

主库验证（pg1）

主库插入测试数据

从库验证数据同步（pg2/pg3 分别执行）

2.7. 常见问题排查

经典问题

ERROR: replication slot "standby1" already exists

解决方法：

2.8. 总结