PostgreSQL10 物理流复制实战：构建高可用数据库架构！

背景

PostgreSQL 10 在高可用架构中提供了物理复制，也称为流复制（Streaming Replication），用于实现实例级别的数据同步。PostgreSQL 复制机制主要包括物理复制和逻辑复制：物理复制依赖 WAL 日志进行物理块级别的同步，能够保证备库与主库的数据完全一致，适用于高可用、读写分离、灾备切换等场景。相比之下，逻辑复制则更灵活，支持表级数据同步。在高并发业务中，物理复制因其高效、低延迟的特性，广泛用于保障数据库的高可用性。

流复制介绍

什么是流复制？

基于流复制协议的WAL日志从主节点到备节点实时复制传输与复用；

为了实现数据库的容灾备份，我们需要搭建主库和备库；

流复制是搭建主备库的一种有效方式；

两套数据库之间的数据，通过WAL日志，后台自动同步；

对外部的应用程序而言，可以看作是两套数据库，需要根据业务需要，显示分别连接不同的数据库；

获取配合其他中间件使用，例如PGPOOL实现负载均很和故障自动切换。

流复制起源

PostgreSQL自从2010年推出的9.0版本开始，支持流式物理复制，用户可以通过流式复制，构建只读备库(主备物理复制，块级别一致)。流式物理复制可以做到极低的延迟（通常在毫秒以内）。

同步流复制

2011年推出的9.1版本，支持同步复制，当时只支持一个同步流复制备节点（例如配置了3个备，只有一个是同步模式的，其他都是异步模式）。

在同步复制模式下，当用户提交事务时，需要等待这笔事务的WAL日志复制到同步流复制备节点，才会返回提交成功的ACK给客户端。

同步模式下，可以确保数据的0丢失。（只要客户端收到了事务提交成功的ACK，这笔事务的WAL就有两份。）

级联流复制

2012年推出的9.2版本，支持级联流复制。意思是备库还可以再连备库。

级联复制特别适合跨机房的使用，例如主库在A机房，备库在B机房，但是B机房需要建立多个备库时，那么B机房只需要建立一个直连主库的备库，其他的备库可以通过B机房的这个备库级联产生。从而减少网络开销。

流式虚拟备库

2012年推出的9.2版本，除了支持级联复制，还支持虚拟备库，什么是虚拟备库呢？就是只有WAL，没有数据文件的备库。

通过虚拟备库，可以流式的接收WAL，进行实时的流式WAL归档。提高备份或归档的实时性。

逻辑复制的基础

2014年推出的9.4版本，在WAL中增加了逻辑复制需要的基础信息，通过插件，可以实现逻辑复制。

逻辑复制可以做到对主库的部分复制，例如表级复制，而不是整个集群的块级一致复制。逻辑复制的备库不仅仅是只读的，也可以执行写操作。

WAL日志

WAL（Write-Ahead Logging，预写式日志），保存了对数据库的操作记录，保证了PG的事务持久性和数据完整性，同时避免了频繁的I/O对数据库性能的影响。

WAL机制的理念是，对数据文件的操作，例如表和索引，都应该先将操作日志写入磁盘中的WAL日志文件，而Data Buffer中的脏页延迟至checkpoint发生的时候才刷新到磁盘中的数据文件。

1. 刷新数据页涉及大量的随机I/O，即刷新脏页需对硬盘中的多个分散的数据块进行写操作，这里会涉及硬盘磁头的寻道操作，非常耗时；相比之下刷新WAL是把记录追加到WAL文件上，属于连续写，效率要高得多。

2. 日志先被持久化，即使数据库发生宕机，即使Data Buffer存在未刷新到数据文件的数据页，当数据库重新启动后，那些未刷新的数据页上的变动可以根据WAL日志重做，保证数据的完整性。

流复制执行过程

1. 发生DML（INSERT/UPDATE/DELETE）、DDL（表结果变更）等变更操作提交时，数据库进程将WAL记录先写入内存的WAL Buffer中。
2. WAL Buffer中的WAL记录被刷新到硬盘的WAL日志文件中。
3. 通知walsender进程。
4. walsender进程读取刚刚刷新到硬盘的WAL记录。
5. walsender进程负责WAL记录发送给备库的walreceiver进程。
6. 备库walreceiver接收到WAL记录后会通知walwriter进程，walwriter负责把接收到的WAL记录写入硬盘的WAL日志文件。
7. 通知数据库startup进程。
8. 备库startup进程读取刚刚刷新到硬盘的WAL记录。
9. 备库startup进程重播WAL日志，并把数据写入硬盘的数据文件。

经过以上步骤，实现主库与备库的数据一致性。

流复制同步级别

2016年推出的9.6版本，PG的流式复制，通过复制WAL达到同步的目的，因此同步级别也和WAL有关。通过synchronous_commit参数，可以配置事务的同步级别。

1、remote_apply，表示本地WAL fsync（fsync：内存持久化存储），同步standby WAL 已恢复。这个带来的RT最高。

2、on，表示本地WAL fsync，同步standby WAL fsync。即两份持久化的WAL。

3、remote_write， 表示本地WAL fsync，同步standby WAL 异步write完成。一份持久化，备库的WAL可能还在OS CACHE中。

4、local，表示本地WAL fsync。

5、off，表示本地WAL写到WAL buffer中即返回客户端事务提交成功的ACK，为异步提交（数据库CRASH可能导致事务丢失，但不会导致数据库不一致）。

RT影响，从低到高如下：off, local, remote_write, on, remote_apply。

同步等级	设定值	说明	保证范围（流复制过程步骤保证）
同步	remote_apply	再备库上应用WAL（更新数据）后，它将返回COMMIT响应，并且可以在备库上进行引用。由于完全保证数据同步，因此它适用于需要备库始终保持最新数据的负载分配场景。	1-9
同步	on（默认）	在备库上写入WAL之后，返回COMMIT响应。性能和可靠性之间平衡。	1-6
准同步	remote_write	WAL已传输到备库后，返回COMMIT响应。	1-5
异步	local	写入主库WAL之后，返回COMMIT响应。	1-2
异步	off	WAL写到WAL buffer中即返回COMMIT响应，无需等待主库WAL完成写入。该设置对现有业务效率无影响，相当于异步。	1

异步流复制和同步流复制的区别

流复制传递日志的两种方式

• 异步流复制
• 同步流复制

两者的主要区别

1. 在异步流复制的情况下，事务被提交到master之后数据才会被复制。

slave就写操作而言，通常滞后于master一些，此延迟（delay）被称为滞后性。

2. 同步复制较高的数据一致性规则

在同步流复制的情况下，系统必须确保通过事务写入的数据至少事务同时在两台服务器上提交。

这意味着：slave不滞后于master，而且终端用户在两台服务器上看到的数据是一致的。

考虑数据丢失

假设我们正在以异步复制方式同步数据：

1. 事务发送到master
2. 事务提交到master
3. 在事务发送到slave之前，master宕机
4. slave永远都不会收到这个事务

在异步复制的情况下，有一个窗口（滞后），在滞后窗口期间数据会存在丢失

滞后窗口的大小因设置类型的不同而不同，它的大小非常短（几毫秒）或非常长（几分钟、几小时、几天）。

一个重要的事实是：数据可能丢失，一个小的滞后指挥是数据丢失的可能性较小，但任务大于零的滞后都可能导致数据丢失。

考虑性能问题

通过网络发送不必要的消息的开销是相对昂贵和耗时的。

如果一个事务采用同步的方式复制，PostgreSQL必须确保数据到达第二个节点，这样就会导致额外的延迟问题，业务的感知会有延迟。

在许多方面，同步复制比异步复制昂贵很多，因此如果这种消耗确实需要，应该三思而后行。（只在有特别需要的时候使用同步复制）

流式备份压缩

2017年推出的10版本，pg_basebackup, pg_receivewal命令支持流式压缩备份WAL。

quorum based 同步流复制

2017年推出的10版本，支持quorum based的同步流复制，例如有3个备节点，你可以告诉主库，这个事务需要3份WAL副本，那么主库需要等待至少2个备节点已将WAL同步过去的反馈，才会将事务提交成功的ACK返回给客户端。

quorum based同步流复制，结合raft协议，可以实现零数据丢失的高可用、高可靠架构。

内置逻辑订阅、复制

2017年推出的10版本，内置了逻辑订阅的功能。

多master

2017年推出的10版本，通过逻辑订阅的功能，可以实现多Master架构。

物理流复制

主库执行操作

1. 备份postgresql.conf

cp /data/xxx/pgsql/data/postgresql.conf /data/xxx/pgsql/data/postgresql.conf.backup
chown postgresql:postgresql postgresql.conf.backup

2. 配置postgresql.conf

# - iuser数据库主备配置 - 
wal_level = replica
fsync = on
synchronous_commit = remote_write
synchronous_standby_names = 'ANY 1 (*)'
max_wal_senders = 10
wal_keep_segments = 1024
hot_standby = on
# - iuser数据库主备配置 -

配置说明

max_wal_senders 设置为一个大于0的数，表示主库最多可以有多少个并发的standby
wal_keep_segments 设置为一个尽量大的值，以防止主库生成WAL日志太快，日志还没有来得及传送到standby就被覆盖，但是需要考虑磁盘空间允许，一个WAL日志文件的大小通常是16M

postgresql.conf、pg_hba.conf配置调优

https://github.com/digoal/blog/blob/master/201707/20170711_01.md

3. 备份pg_hba.conf

cp /data/xxx/pgsql/data/pg_hba.conf /data/xxx/pgsql/data/pg_hba.conf.backup
chown postgresql:postgresql pg_hba.conf.backup

4. 配置pg_hba.conf

# - iuser数据库主备配置 - 
local   replication     all                                     md5  
host    replication     all             127.0.0.1/32            md5  
host    replication     all             ::1/128                 md5  
host    replication     all             0.0.0.0/0               md5
# - iuser数据库主备配置 -

5. 重启主库

service restart postgresql

6. 创建流复制角色
   创建replication角色的用户来专门负责standby连接去获取WAL日志

psql -h 127.0.0.1 -p 5432 -U postgres
PGPASSWORD=xxx psql -h 127.0.0.1 -p 5432 -U postgres
postgres=# set synchronous_commit = off;
postgres=# create role rep login replication encrypted password '123456';

备库执行操作

1. 备份备库目录
   做基础备份之前从库的数据目录需要手动清空

cd /data/xxx/pgsql
tar -cvf pg_data_backup.tar.gz /data/xxx/pgsql/data
rm -rf /data/xxx/pgsql/data/*

2. 停止备库

service stop postgresql

3. 恢复备库（备份主库数据）

# 获取整库备份文件（支持自定义表空间）
# 注意：密码也会被覆盖成主机密码、即主备机密码需一致
./bin/pg_basebackup -D /data/xxx/pgsql/data -Ft -Pv -U rep -h 192.168.121.23-D 表示指定数据备份的位置
-F 表示备份文件格式，这里t表示是tar压缩文件格式。
-Pv 表示显示备份过程。
-U、-h、-p 表示数据库连接相关设置------
# 生成基础备份（如果自定义表空间没有和默认表空间data目录一起存储可使用该命令）
./bin/pg_basebackup -h 192.168.121.23 -U rep -F p -P -R -D /data/xxx/pgsql/data -l repbackup20240304-X fetch:
当使用 -X fetch 选项时，pg_basebackup 将通过获取和复制 WAL 日志文件的方式来进行备份。它会在备份完成后将 WAL 日志文件复制到备份目录中，以确保备份的完整性。
优点是备份的完整性更高，因为它会将 WAL 日志文件复制到备份目录中，以便在恢复时能够恢复到特定的时间点。
缺点是备份过程可能会稍慢，特别是在网络连接较慢或延迟较高的情况下。
-X stream:
当使用 -X stream 选项时，pg_basebackup 将通过流复制的方式来进行备份。它会在备份过程中直接从主服务器获取 WAL 日志文件，并将它们应用到备份数据中。
优点是备份过程可能会更快，因为它直接从主服务器获取 WAL 日志文件，并且不需要等待 WAL 日志文件复制完成。
缺点是由于备份数据中包含 WAL 日志的一部分，因此在恢复时可能无法恢复到特定的时间点，而是恢复到备份结束时的状态。
综上所述，-X fetch 和 -X stream 的区别在于备份时获取 WAL 日志文件的方式不同，前者将 WAL 日志文件复制到备份目录中以确保备份的完整性，而后者则直接从主服务器获取 WAL 日志文件以提高备份速度。您可以根据实际需求选择适合的方式进行备份。./bin/pg_basebackup -D /data/xxx/pgsql/data -F p -X stream -h 192.168.121.23 -p 5432 -U rep
密码：123456
./bin/pg_basebackup -D /data/xxx/pgsql/data -F p -X fetch -h 192.168.121.23 -p 5432 -U rep

自定义表空间恢复注意事项

由于使用了自定义表空间恢复备份过程失败参考：
https://cloud.tencent.com/developer/ask/sof/111519647
https://zhuanlan.zhihu.com/p/677427319
https://baijiahao.baidu.com/s?id=1708319850223764755&wfr=spider&for=pc
http://www.manongjc.com/detail/40-xpencvjhrnhytdn.html

4. 恢复备库（备库执行恢复）
   a.目录解压
   数据目录/data/xxx/pgsql/data下存在以下三个备份文件

# 第一个基础信息包base.tar解压（直接数据目录下执行）
tar -xvf base.tar
# 第二个数据目录包16385.tar解压（解压到data目录下的pg_iuser目录下）
tar -xvf 16385.tar -C pg_iuser/
# 第三个WAL日志包pg_wal.tar解压（解压到data目录下的pg_iuser目录下）
tar -xvf pg_wal.tar -C pg_wal/

b.配置备库恢复配置文件recovery.conf

cp /data/xxx/postgresql/share/recovery.conf.sample recovery.conf
chown postgresql:postgresql recovery.conf
# 设置recovery.conf文件内容
recovery_target_timeline = 'latest'  
standby_mode = on  
primary_conninfo = 'host=192.168.121.23 port=5432 user=rep password=123456'

注意：recovery_target_timeline当设置为 ‘latest’ 时，PostgreSQL 将使用最新的时间线来进行恢复。这意味着在恢复数据库时，将使用 WAL 日志中最新的时间线。这对于在流复制环境中进行故障切换时非常有用，因为您希望备库能够尽快地切换到主库的最新时间线，以确保数据的一致性和完整性。这在进行 PITR（Point-In-Time Recovery）时特别有用，因为您可以确保将数据库恢复到最新的状态，而不是恢复到之前的某个时间点。‘immediate’ 该选择只能恢复到备份的时间点，无法使用备份后生成的wal。

若recovery.conf文件存在，则进行PITR操作，根据recovery_target应用日志。

c.数据目录赋权

# 备份过程中可能由于root用户操作导致目录权限被覆盖
sudo chown -R postgresql:postgresql /data/xxx/pgsql/data

5. 备份完成启动备库

service start postgresql

主从节点部署完成。

节点状态监控

主库查询

# 扩展展示查询
\x
Expanded display is on.  # 查询状态
select pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)) as sent_delay,   pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), write_lsn)) as write_delay,   pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn)) as flush_delay,   pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) as replay_delay,   *  
from pg_stat_replication;# 结果查询
-[ RECORD 1 ]----+------------------------------
sent_delay       | 0 bytes
write_delay      | 0 bytes
flush_delay      | 0 bytes
replay_delay     | 0 bytes
pid              | 56584
usesysid         | 24576
usename          | rep
application_name | walreceiver
client_addr      | 192.168.121.24
client_hostname  | 
client_port      | 45564
backend_start    | 2024-03-05 00:29:50.486627+08
backend_xmin     | 
state            | streaming
sent_lsn         | 0/F152118
write_lsn        | 0/F152118
flush_lsn        | 0/F152118
replay_lsn       | 0/F152118
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 1
sync_state       | quorum

备库查询

-- 查看当前WAL应用是否暂停了 （navicat可执行）
iuser=# select pg_is_wal_replay_paused();pg_is_wal_replay_paused   
-------------------------  f  -- 查看WAL接收到的位点  
iuser=# select pg_last_wal_receive_lsn();pg_last_wal_receive_lsn   
-------------------------  0/F152A88 -- 查看WAL的应用位点  
iuser=# select pg_last_wal_replay_lsn();pg_last_wal_replay_lsn   
------------------------  
0/F152B30-- 查看wal receiver的统计信息  
iuser=# \x  
Expanded display is on.  
iuser=# select * from pg_stat_get_wal_receiver();
-[ RECORD 1 ]---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------  
pid                   | 65235  
status                | streaming  
receive_start_lsn     | 0/F000000 
receive_start_tli     | 1  
received_lsn          | 0/F152B68 
received_tli          | 1  
last_msg_send_time    | 2024-03-05 11:41:32.810624+08  
last_msg_receipt_time | 2024-03-05 11:41:33.591844+08  
latest_end_lsn        | 0/F152B68 
latest_end_time       | 2024-03-05 11:41:02.770438+08  
slot_name             |   
conninfo              | user=rep password=******** dbname=replication host=192.168.121.23 port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any-- 注意！！！执行暂停WAL的应用（例如要做一些排错时）
iuser=# select pg_wal_replay_pause();  
-[ RECORD 1 ]-------+-  
pg_wal_replay_pause |   -- 查询是否暂停WAL - 结果t
postgres=# select pg_is_wal_replay_paused();
-[ RECORD 1 ]-----------+--  
pg_is_wal_replay_paused | t  -- 注意！！！执行继续
postgres=# select pg_wal_replay_resume();
-[ RECORD 1 ]--------+-  
pg_wal_replay_resume |   -- 查询是否暂停WAL - 结果f
postgres=# select pg_is_wal_replay_paused();
-[ RECORD 1 ]-----------+--  
pg_is_wal_replay_paused | f  

注：

– 删除所有发布

SELECT pg_drop_replication_slot(slot_name) FROM pg_replication_slots;

– 删除所有订阅

SELECT pg_drop_subscription(subname) FROM pg_subscription;

注意事项

1、如果要防止日志主库删除，备库还没有接收的WAL文件。

使用slot，或者配置足够大的wal keep。

但是这两种方法都有一定的风险或问题，例如当备库挂了，或者备库不再使用了，而用户忘记删除对应的SLOT时，可能导致主库WAL无限膨胀。

而wal keep则会导致主库的WAL预留足够的个数，默认一个wal文件大小16M，占用一定空间，例如配置wal_keep_segments=1024，那么磁盘最大存储是16G。

相关参数

主 postgresql.conf  
# max_replication_slots = 10  
# wal_keep_segments = 1024  #保持至少 1024 个 WAL 文件可用，以确保数据库的持续运行备 recovery.conf  
# primary_slot_name = ''  

2、如果不想通过以上方法预防备库需要的WAL已被删除，那么可以配置主库的归档，同时备库需要能获取到已归档的WAL文件。

相关参数

主 postgresql.conf  
#archive_mode = off             # enables archiving; off, on, or always  # (change requires restart)  
#archive_command = ''           # command to use to archive a logfile segment  # placeholders: %p = path of file to archive  #               %f = file name only  # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'  备 recovery.conf  
# restore_command = ''           # e.g. 'cp /mnt/server/archivedir/%f %p'  

3、保护好recovery.conf文件中的密码，因为配置的是明文。–源码中是否可以配置密文？

在 recovery.conf 配置文件中配置的密码通常是以明文形式存储的，而不是加密存储的。这是因为 recovery.conf 文件通常只能由数据库管理员或具有足够权限的用户访问，因此存储密码为明文通常被视为足够安全。
然而，对于安全性要求较高的环境，您可能希望采取额外的措施来保护密码的安全性。这可能包括：
1.使用操作系统权限限制访问：确保只有具有足够权限的用户才能访问 recovery.conf 文件。
2.定期更改密码：定期更改密码是一种通用的安全实践，可以帮助减少密码泄露的风险。
3.使用密码管理工具：某些密码管理工具允许您安全地存储和管理密码，并提供额外的安全功能，如加密存储、访问控制和审计日志等。
4.加密密码：虽然 recovery.conf 文件本身不支持加密存储密码，但您可以使用加密技术来保护密码。例如，您可以将密码存储在加密的配置文件中，并在数据库启动时使用脚本来解密密码。

4、主备之间的带宽请足够大，否则可能导致主备延迟。

5、大数据量下磁盘空间占满问题，预估一下一个大数据量项目的数据库大小占存储空间多少。查看数据库的大小 :

postgres=# select round(sum(pg_database_size(oid))/1024/1024.0,2)||'MB' from pg_database;?column? 
----------288.21MB
(1 row)

查看表空间和使用的数据库

CREATE ROLE
iuser=# SHOW data_directory;data_directory       
---------------------------/data/xxx/pgsql/data
(1 row)iuser=# SELECT * FROM pg_tablespace;spcname   | spcowner | spcacl | spcoptions 
------------+----------+--------+------------pg_default |       10 |        | pg_global  |       10 |        | iuser        |       10 |        | 
(3 rows)iuser=# \db+List of tablespacesName    |   Owner    |             Location             | Access privileges | Options |  Size  | Description 
------------+------------+----------------------------------+-------------------+---------+--------+-------------iuser        | postgresql | /data/xxx/pgsql/data/pg_iuser |                   |         | 11 MB  | pg_default | postgresql |                                  |                   |         | 23 MB  | pg_global  | postgresql |                                  |                   |         | 574 kB |

同步压测

连接主库进行TPC-B的压测

TPC-B 测试的结果通常以每秒完成的事务数（Transaction-per-Second, TPS）来衡量，即系统在单位时间内能够完成的事务数量。这个指标越高，表示数据库系统在处理事务型工作负载时的性能越好。

可通过正常部署的机器对比主从部署的机器性能测试。

1. 创建测试数据库

iuser=# create database test_db; 

2. 初始化数据库

pgbench -i -U <user> -d <dbname> -h <host>

主库执行

./bin/pgbench -i -s 10 -d test_db -h 127.0.0.1 -p 5432 -U iuser密码：xxx-i：表示初始化数据库。执行此命令后，pgbench 将创建必要的表和索引，并向数据库中插入初始数据。-s：指定了插入的规模因子（Scale Factor）。Scale Factor 是一个用于控制测试数据规模的参数，它决定了插入的数据量。在这个例子中，规模因子为 10，表示将会插入约为预设规模的 10 倍的数据。这意味着将会插入较大规模的测试数据，以便进行性能测试。比例因子，将生成的行数乘以给定的数，例如，在默认情况下，比例因子为1，pgbench_accounts表会创建100,000行，当-s 10 即会创建1,000,000行。

./bin/pgbench -n -r -P 1 -d test_db -h 127.0.0.1 -p 5432 -U iuser -c 32 -j 32 -T 120这个命令使用 pgbench 工具对 PostgreSQL 数据库执行性能测试，并输出了一些关于测试结果的统计信息。具体的参数含义如下：
-n：表示不执行事务初始化阶段。如果数据库已经初始化过，可以通过这个选项跳过初始化步骤，直接执行测试。
-r：表示按照事务顺序执行，而不是随机顺序。在这个例子中，使用的是 TPC-B 测试，它涉及到一系列按顺序执行的事务。
-P 1：指定测试过程中的预热时间。在执行正式的测试之前，通常需要一段时间来预热数据库缓存。这个选项表示预热时间为 1 秒。
-c 32：指定测试过程中的并发客户端数量为 32。即同时有 32 个客户端连接到数据库执行测试。
-j 32：指定测试过程中的线程数量为 32。即同时有 32 个线程执行测试。
-T 120：指定测试的持续时间为 120 秒。

测试结果

transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
query mode: simple
number of clients: 32
number of threads: 32
duration: 120 s
number of transactions actually processed: 109736
latency average = 34.958 ms
latency stddev = 172.667 ms
tps = 913.754775 (including connections establishing)
tps = 914.944815 (excluding connections establishing)
script statistics:- statement latencies in milliseconds:0.027  \set aid random(1, 100000 * :scale)0.013  \set bid random(1, 1 * :scale)0.011  \set tid random(1, 10 * :scale)0.008  \set delta random(-5000, 5000)1.122  BEGIN;0.741  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;0.916  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;6.643  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;17.218  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;0.837  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);7.421  END;

结果分析

根据提供的测试结果，我们可以进行性能分析：平均延迟（Latency Average）: 平均延迟约为 34.958 毫秒，这表示每个事务的平均执行时间。较低的延迟通常表示更好的性能。延迟标准偏差（Latency Stddev）: 延迟标准偏差为 172.667 毫秒，表示延迟值的离散程度。较小的标准偏差通常表示延迟值分布较为集中。每秒事务数（TPS）：在连接建立的情况下，TPS约为 913.75；不包括连接建立时，TPS约为 914.94。TPS 表示系统每秒钟可以处理的事务数量。较高的 TPS 值通常表示更好的性能。各种语句的执行时间：在脚本统计中，每个语句的执行时间分别列出。可以看到，UPDATE 语句的执行时间较长，特别是 pgbench_branches 表的更新操作和 pgbench_tellers 表的更新操作。基于以上数据，性能评估如下：平均延迟较高：平均延迟较高，可能表示数据库服务器的负载较重或者系统资源不足。需要进一步分析系统资源使用情况，优化数据库配置或增加硬件资源以提高性能。TPS 较低：每秒处理的事务数量较低，可能会影响系统的并发处理能力。可以尝试优化 SQL 查询、调整连接池配置或者进行硬件升级等方式来提高 TPS。部分语句执行时间较长：特别是 UPDATE 语句的执行时间较长，可能会影响整体性能。可以通过优化表结构、添加索引或调整查询计划等方式来减少执行时间。综上所述，需要进一步分析系统的资源使用情况，并针对性地进行优化，以提高系统的性能和稳定性。

观察主备的延迟

iuser=# \x
iuser=# select pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)) as sent_delay,pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), write_lsn)) as write_delay,pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn)) as flush_delay,pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) as replay_delay, * from pg_stat_replication;iuser=# \watch 1 

运行过程中测试结果

-[ RECORD 1 ]----+------------------------------
sent_delay       | 0 bytes
write_delay      | 0 bytes
flush_delay      | 0 bytes
replay_delay     | 1592 bytes
pid              | 56584
usesysid         | 24576
usename          | rep
application_name | walreceiver
client_addr      | 192.168.121.24
client_hostname  | 
client_port      | 45564
backend_start    | 2024-03-05 00:29:50.486627+08
backend_xmin     | 
state            | streaming
sent_lsn         | 0/2B1D7998
write_lsn        | 0/2B1D7998
flush_lsn        | 0/2B1D7998
replay_lsn       | 0/2B1D7360
write_lag        | 00:00:00.005352
flush_lag        | 00:00:00.005363
replay_lag       | 00:00:00.007701
sync_priority    | 1
sync_state       | quorum

运行过程中测试结果分析

根据提供的 pg_stat_replication 查询结果，我们可以分析主从数据库同步延迟情况如下：streaming 状态：从结果中可以看到，状态为 "streaming"，这表示该从节点正在通过流复制（streaming replication）的方式与主节点同步数据。延迟信息：从结果中的 sent_delay、write_delay、flush_delay 和 replay_delay 等字段可以看出不同阶段的延迟情况。这些字段表示从接收 WAL 日志到将其写入磁盘、刷新到持久存储、以及重放到从节点数据库的延迟情况。延迟时间：从 write_lag、flush_lag 和 replay_lag 等字段可以看到相应的延迟时间。这些字段显示了在不同阶段的延迟时间，例如从接收到 WAL 日志到写入磁盘的延迟、刷新到持久存储的延迟以及重放到从节点数据库的延迟。同步状态：sync_state 字段显示同步状态，这里是 "quorum"，表示同步状态良好。综合分析这些信息，可以得出以下结论：延迟情况分析：从结果中可以看出，从节点的 replay 延迟较大，为 7.701 毫秒。这表示从节点接收到 WAL 日志后，需要经过约 7.7 毫秒的时间才能将其重放到从节点数据库中。其他阶段的延迟时间较小，都在毫秒级别。同步状态正常：从结果中的 sync_state 字段可以看出，同步状态为 "quorum"，表示同步状态良好，从节点与主节点之间的数据同步工作正常。综上所述，根据 pg_stat_replication 查询结果分析，从节点的重放延迟较大，可能是由于从节点的资源负载较重或者网络延迟等原因导致。建议进一步分析系统资源使用情况，并针对性地进行优化，以减少同步延迟，提高主从数据库之间的同步性能。

空载运行时结果对比

-[ RECORD 1 ]----+------------------------------
sent_delay       | 0 bytes
write_delay      | 0 bytes
flush_delay      | 0 bytes
replay_delay     | 0 bytes
pid              | 56584
usesysid         | 24576
usename          | rep
application_name | walreceiver
client_addr      | 192.168.121.24
client_hostname  | 
client_port      | 45564
backend_start    | 2024-03-05 00:29:50.486627+08
backend_xmin     | 
state            | streaming
sent_lsn         | 0/2F103930
write_lsn        | 0/2F103930
flush_lsn        | 0/2F103930
replay_lsn       | 0/2F103930
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 1
sync_state       | quorum

总结

物理流复制是 PostgreSQL 10 构建高可用架构的重要方案之一，适用于读写分离、数据容灾、快速故障切换等场景。与逻辑复制相比，物理复制能保证主备库的数据严格一致，但灵活性较低。通过合理配置同步复制或异步复制，可以在性能与一致性之间找到最佳平衡，确保业务的稳定运行。🚀 想让你的 PostgreSQL 更高可用？物理流复制是必不可少的技术！💡