postgresql主备同步复制

同步复制前提条件

replica增加wal归档信息，包括只读服务器需要的信息。（9.6中新增，将之前版本的 archive 和 hot_standby 合并）

主库开启参数

synchronous_standby_names= 'ANY 1 (standby1,standby2)'
wal_level = replica
synchronous_commit = on

参数说明

synchronous_standby_names

synchronous_standby_names
s1,s2,s3,s4 --优先级确定同步复制主机
2(s1,s2,s3,s4 ) --优先级确定2个同步复制主机
ANY 2 (s1,s2,s3,s4 ) --不存在优先级，2个同步复制主机

synchronous_commit

OFF ⽆论什么状态，WAL只需要刷⼊主机的wal buffer即可。
LOCAL ⽆论什么状态，WAL⽇志需刷⼊主机磁盘。
REMOTE_WRITE 备机需要写⼊磁盘
REMOTE_APPLY 备机需要回放完成。`

wal_level

字符型
默认： wal_level = replica ，minimal、replica、logical三选一
重启数据库生效
预写日志模式
minimal是仅写入崩溃或者突发关机时所需要的信息
replica增加wal归档信息，包括只读服务器需要的信息。（9.6中新增，将之前版本的 archive 和 hot_standby 合并）
logical主要用于logical decoding场景
在写入频繁的场景中，会产生大量的WAL日志，而且WAL日志量会远远超过实际更新的数据量。叫做“WAL写放大”。
造成WAL写放大的主要原因有2点。
    1、在checkpoint之后第一次修改页面，需要在WAL中输出整个page，即全页写(full page writes)。全页写的目的是防止在意外宕机时出现的数据块部分写导致数据库无法恢复。
    2、更新记录时如果新记录位置(ctid)发生变更，索引记录也要相应变更，这个变更也要记入WAL。更严重的是索引记录的变更又有可能导致索引页的全页写，进一步加剧了WAL写放大。
过量的WAL输出会对系统资源造成很大的消耗，因此需要进行适当的优化。
    1. 磁盘IO：WAL写入是顺序写,通常再差的硬盘对付WAL的写入速度也是绰绰有余。所以一般可以忽略。
    2. 网络IO：对局域网内的复制估计还不算问题，远程复制就难说了。
    3. 磁盘空间：如果做WAL归档，需要的磁盘空间也是巨大的。 
版本小于或等于9.6的配置为：
    minimal是仅写入崩溃或者突发关机时所需要的信息（不建议使用）。
    archive是增加wal归档所需的日志（最常用）。
    hot_standby是在备用服务器上增加了运行只读查询所需的信息，一般实在流复制的时候使用到。

验证：

在同步过程中，如果把备库给关闭，然后在主库进行数据操作，会发现无法操作，该事务会挂起，处于等待状态。此时对主库会造成很大的影响，跟oracle的最大保护模式一样。 如果我们配置了多个备库，而且进行实时同步，假如只要保证前面的备库能够实时就可以

主库

su - postgres -c "/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile stop"
su - postgres -c "/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start"

vim /usr/local/pgsql/data/postgresql.conf

synchronous_standby_names= 'ANY 1 (standby1,standby2)'
wal_level = replica
synchronous_commit = on

备库1

旧

[postgres@replicate1 data]$ cat  postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=replication passfile=''/home/postgres/.pgpass'' channel_binding=disable host=10.0.2.15 port=5432 sslmode=disable sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any'
[postgres@replicate1 data]$

新application_name=standby1

primary_conninfo = 'user=replication application_name=standby1 passfile=''/home/postgres/.pgpass'' channel_binding=disable host=10.0.2.15 port=5432 sslmode=disable sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any'

备库2

旧

vi /usr/local/pgsql/data/postgresql.auto.conf

# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=replication passfile=''/home/postgres/.pgpass'' channel_binding=disable host=10.0.2.15 port=5432 sslmode=disable sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any'

新application_name=standby2

# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=replication application_name=standby2 passfile=''/home/postgres/.pgpass'' channel_binding=disable host=10.0.2.15 port=5432 sslmode=disable sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any'

相关重启操作

/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile stop

/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start

查看sync_state由async变成quorum

查看集群状态

postgres=# select * from pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
pid              | 1552
usesysid         | 49176
usename          | replication
application_name | standby1
client_addr      | 10.0.2.6
client_hostname  |
client_port      | 38062
backend_start    | 2024-05-11 02:16:58.88811+00
backend_xmin     |
state            | streaming
sent_lsn         | 0/C010BB0
write_lsn        | 0/C010BB0
flush_lsn        | 0/C010BB0
replay_lsn       | 0/C010BB0
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 1
sync_state       | quorum
reply_time       | 2024-05-11 02:19:28.427076+00
-[ RECORD 2 ]----+------------------------------
pid              | 1553
usesysid         | 49176
usename          | replication
application_name | standby2
client_addr      | 10.0.2.7
client_hostname  |
client_port      | 33868
backend_start    | 2024-05-11 02:16:59.700664+00
backend_xmin     |
state            | streaming
sent_lsn         | 0/C010BB0
write_lsn        | 0/C010BB0
flush_lsn        | 0/C010BB0
replay_lsn       | 0/C010BB0
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 1
sync_state       | quorum
reply_time       | 2024-05-11 02:19:30.702888+00

postgres=#

查看集群复制流

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

postgres=# SELECT application_name AS host,
postgres-#          sync_priority, sync_state FROM pg_stat_replication;
   host   | sync_priority | sync_state
----------+---------------+------------
 standby1 |             1 | quorum
 standby2 |             1 | quorum
(2 rows)

查看备库复制流

postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_wal_receiver ;
-[ RECORD 1 ]---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid                   | 1804
status                | streaming
receive_start_lsn     | 0/C000000
receive_start_tli     | 1
written_lsn           | 0/C026678
flushed_lsn           | 0/C026678
received_tli          | 1
last_msg_send_time    | 2024-05-11 02:45:57.791328+00
last_msg_receipt_time | 2024-05-11 02:45:46.425947+00
latest_end_lsn        | 0/C026678
latest_end_time       | 2024-05-11 02:44:27.105021+00
slot_name             |
sender_host           | 10.0.2.15
sender_port           | 5432
conninfo              | user=replication passfile=/home/postgres/.pgpass channel_binding=disable dbname=replication host=10.0.2.15 port=5432 application_name=standby1 fallback_application_name=walreceiver sslmode=disable sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any

postgres=#

验证同步复制

备库

备库1

[postgres@replicate1 ~]$ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile stop
waiting for server to shut down.... done
server stopped

备库2

[postgres@replicate2 ~]$ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile stop
waiting for server to shut down.... done
server stopped

主库（无法提交）

postgres=# \q
[postgres@primary ~]$ /usr/local/pgsql/bin/psql
psql (13.14)
Type "help" for help.

postgres=# create table test01(id int primary key)
postgres-# ;
CREATE TABLE
postgres=# begin;
BEGIN
postgres=*# insert into test01 values(1);
INSERT 0 1
postgres=*# insert into test01 values(2);
INSERT 0 1
postgres=*# insert into test01 values(3);
INSERT 0 1
postgres=*# commit;     -------慢长的等待，直到replicate2启动就可以提交
COMMIT
postgres=#

备库启动

[root@replicate2 ~]# su - postgres -c "/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start"
waiting for server to start.... done
server started

借来一个流程图

standby1和standby2的sync_state分别为“_sync”_和“_potential”。_  
（1）主服务器的后端进程继续等待来自同步备用服务器的ACK响应，即使它已收到来自潜在备用服务器的ACK响应。  
（2）在收到来自同步备用服务器的ACK响应后，主的后端进程释放闩锁并完成当前事务处理。

参考 https://www.postgresql.org/docs/13/runtime-config-wal.html