PostgreSQL数据库管理第七章流复制
PostgreSQL数据库管理第七章流复制
概述
日志复制和流复制式。
日志复制介绍：主服务器将预写日志主动拷贝到一个安全的位置(可以直接到备用服务器，也可以是第三台服务器)，同时备用服务器定期扫描这个位置，并将预写日志拷贝到备用服务器端然后再回放。这样即使主服务器崩溃了，备用服务器也可以从这个安全的位置获取到一份完整的记录，以确保任何数据不会丢失。会丢失一个日志文件16M的数据。
流式复制介绍：主服务器直接通过TCP协议向备用服务器传输日志，避免了两次复制的开销，有利于减小备用服务器和主服务器直接的数据延时。但当主服务器崩溃时，未被传输到备用服务器的日志则会丢失，造成数据损失。
流复制，就是从服务器通过tcp流从主服务器中同步相应的数据。这样当主服务器数据丢失时从服务器中仍有备份。
与基于文件日志传送相比，流复制允许保持从服务器更新。 从服务器连接主服务器，其产生的流WAL记录到从服务器， 而不需要等待主服务器写完WAL文件。
PostgreSQL流复制默认是异步的。在主服务器上提交事务和从服务器上变化可见之间有一个小的延迟，这个延迟远小于基于文件日志传送，通常1秒能完成。如果主服务器突然崩溃，可能会有少量数据丢失。
同步复制必须等主服务器和从服务器都写完WAL后才能提交事务。这样在一定程度上会增加事务的响应时间。
TCP是个"流"协议，所谓流，就是没有界限的一串数据，大家可以想想河里的流水，是连成一片的，其间是没有分界线的。
TCP协议确保了数据到达的顺序与文本流顺序相符。当计算机从TCP协议的接口读取数据时，这些数据已经是排列好顺序的“流”了。比如我们有一个大文件要从本地主机发送到远程主机，如果是按照“流”接收到的话，我们可以一边接收，一边将文本流存入文件系统。这样，等到“流”接收完了，硬盘写入操作也已经完成。
7.1 复制功能涉及的术语
复制是实例级别的，在一个实例上面所有的数据库改变，都会复制。
7.1.1 主服务器(Master)
主服务器是作为要复制数据的源头的数据库服务器，所有更新都在其上发生。使用PostgreSQL的内置复制功能时，仅允许使用一一个主服务器。
7.1.2 从属服务器(Slave)
从属服务器使用复制的数据并提供主服务器的副本。PostgreSQL 内置复制目前仅支持只读从属服务器。
7.1.3 预写日志(Write Ahead Log, WAL)
WAL就是记录所有已完成事务信息的日志文件，在其他数据库产品中一般称为事务日志。为了支持复制功能，PostgreSQL 将主服务器的WAL日志向从属服务器开放，然后从属服务器持续地将这些日志取到本地，然后将其中记载的事务重演一遍，这样就实现了数据同步。
7.1.4 同步复制(Synchronous)
在事务提交阶段，PostgreSQL 需保证已经将此事务中所做的修改成功同步到至少一个从属服务器，然后才能向用户反馈事务提交成功。这种工作模式保证了主服务器和从属服务器的数据在同一个事务内被同步修改，因此称为同步复制。如果配置了多个从属服务器，只要写入一个成功就算提交成功。
7.1.5 异步复制(Asynchronous)
在事务提交阶段，主服务器上提交成功就算成功，不需要等待从属服务器的数据更新成功。当从属服务器位于远端时该模式就比较有用了，因为可以避免网络延迟的影响。但有利必有弊，该模式下从属服务器的数据更新不够及时，与主服务器之间会有一些延迟。当发生传输失败时，从属服务器可能会丢失一些事务数据。
7.1.6流式复制(Streaming)
从PostgreSQL 9.0 版开始支持流式复制。在此前的版本中，WAL日志是通过直接复制文件的方式从主服务器传递到从属服务器，但在流式复制模式下是通过消息来传递的。
117.1.7 级联复制 (Cascading replication)
从9.2版开始，一个从属服务器可以把WAL日志传递给另-一个从属服务器，而不需要所有的从属服务器都从主服务器取WAL日志，这进--步减轻了主服务器的负担。这种模式下，有的从属服务器可以作为同步的数据源从而继续向别的从属服务器传播WAL数据，从这个角度看，其作用类似于主服务器。注意，这种扮演着“WAL日志二传手”角色的从属服务器是只读的，它们也被称为级联从属服务器。
7.2 设置归档
建立归档目录
[[email protected] ~]$ mkdir /pgdb/pgarchivedir/
--pgdata中的postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /pg_archivedir/%f && cp %p /pg_archivedir/%f'
--重启pg systemctl restart postgresql-10.service
postgres=# show wal_level;
wal_level
replica
postgres=# show archive_mode;
archive_mode
on
postgres=# show archive_command;
archive_command
--------------------------------------------------------
test ! -f /pg_archivedir/%f && cp %p /pg_archivedir/%f
1 手动触发归档
select pg_switch_wal ();
7.3 11G同异步流复制部署
PostgreSQL主备数据库的同步设置主要涉及如下文件：
pg_hba.conf postgresql 主库访问规则文件
postgresql.conf postgresql 主库配置文件
recovery.conf postgresql 备库访问主库配置文件
.pgpass postgresql 备库访问主库的密码文件
正常主备流复制情况下：
主库需要pg_hba.conf、postgresql.conf
备库需要recovery.conf、.pgpass
7.3.1 异主库部署
1 在主库上面建立用户
在主库上面用户备库进行连接的流复制用户。
CREATE USER repuser replication LOGIN CONNECTION LIMIT 3 ENCRYPTED PASSWORD 'repuser';
postgres=# CREATE USER repuser replication LOGIN ENCRYPTED PASSWORD 'repuser';
CREATE ROLE
2 postgresql.conf 设置参数
postgresql.conf (master)
# 需要流复制物理备库、归档、时间点恢复时，设置为replica，需要逻辑订阅或逻辑备库则设置为logical
wal_level = logical # minimal, replica, or logical
# 如果底层存储能保证IO的原子写，也可以设置为OFF。
#full_page_writes = on
# 同时允许几个流复制协议的连接，根据实际需求设定 ，可以设置一个默认值例如64.如果有2台从机10也可以。它表示主库最多可以有多少个并发的standby数据库，
max_wal_senders = 64
# 根据实际情况设置保留WAL的数量
wal_keep_segments = 5000
# 根据实际情况设置需要创建多少replication slot
# 使用slot，可以保证流复制下游没有接收的WAL会在当前节点永久保留。所以必须留意下游的接收情况，否则可能导致WAL爆仓
# 建议大于等于max_wal_senders
max_replication_slots = 64
＃ 说明这台机器不仅仅是用于数据归档，也用于数据查询
#hot_standby = on
# 建议关闭，以免备库长事务导致 主库无法回收垃圾而膨胀。
hot_standby_feedback = on
# 开启归档
archive_mode = on
#归档命令
archive_command = 'test ! -f /pgdb/pgarchivedir/%f && cp %p /pgdb/pgarchivedir/%f'
wal_level = logical # minimal, replica, or logical
max_wal_senders = 64
wal_keep_segments = 5000
hot_standby = on
archive_mode = on
archive_command = 'test ! -f /pgdb/pgarchivedir/%f && cp %p /pgdb/pgarchivedir/%f'
3 pg_hba.conf
host replication repuser 192.168.27.0/24 md5
4 流复制几个复制级别
通过参数synchronous_commit (enum)配置事务的同步级别。也就是说，用户可以根据实际的业务需求，对不同的事务，设置不同的同步级别。
目前支持的同步级别如下，事务提交或回滚时，会产生一笔事务结束的commit/rollback redo record，在REDO的地址系统中，用LSN表示。
#synchronous_commit = on # synchronization level;
# off, local, remote_write, remote_apply, or on
Off 异步 on同步最高级别
off, local, remote_write, remote_apply, or on 顺序从高到低
remote_apply,
事务commit或rollback时，等待其redo在primary、以及同步standby(s)已持久化（>=其lsn）。 并且其redo在同步standby(s)已apply（>=其lsn）。
on
事务commit或rollback时，等待其redo在primary、以及同步standby(s)已持久化（>=其lsn）。
remote_write,
事务commit或rollback时，等待其redo在primary已持久化; 其redo在同步standby(s)已调用write接口(写到OS, 但是还没有调用持久化接口如fsync)（>=其lsn）。
local,
事务commit或rollback时，等待其redo在primary已持久化;
Off
事务commit或rollback时，等待其redo在primary已写入wal buffer，不需要等待其持久化;
7.3.2 异从库部署
1 部署
[[email protected] ~]# systemctl stop postgresql-11.service
[[email protected] ~]# rm -rf /pgdb/*
[[email protected] ~]$ pg_basebackup -h 192.168.27.140 -U repuser -D /pgdb -X stream -P
Password:
1776874/1776874 kB (100%), 1/1 tablespace
2 创建恢复文件recovery.conf
cp -p /opt/pgsql11.4/share/recovery.conf.sample /pgdb/recovery.conf
standby_mode = on
# 说明该节点是从服务器
primary_conninfo = 'host=192.168.27.140 port=5432 application_name=141 user= repuser password= repuser ' # 主服务器的信息以及连接的用户与从机信息
3 启动
[[email protected] ~]$ pg_ctl start -D /pgdb
waiting for server to start....2019-10-11 23:30:21.030 CST [6538] FATAL: data directory "/pgdb" has invalid permissions
2019-10-11 23:30:21.030 CST [6538] DETAIL: Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).
stopped waiting
pg_ctl: could not start server
Examine the log output.
[[email protected] ~]$ chmod 0750 /pgdb
[[email protected] ~]$ pg_ctl start -D /pgdb
waiting for server to start....2019-10-11 23:31:26.779 CST [6550] FATAL: could not access file "pg_stat_statements": 没有那个文件或目录
安装pg_stat_statements
[[email protected] pg_stat_statements]# make
[[email protected] pg_stat_statements]# make install
[[email protected] ~]$ pg_ctl start -D /pgdb
server started
postgres=# SELECT client_addr,application_name,sync_state FROM pg_stat_replication;
client_addr | application_name | sync_state
----------------+------------------+------------
192.168.27.141 | 141 | async
7.3.3 同从库部署
首先全部部署从库，在主节点上面修改参数
synchronous_standby_names = '141'
synchronous_commit = on
# 设置那个是同步流复制
[[email protected] ~]$ psql
psql (11.4)
Type "help" for help.
postgres=# SELECT client_addr,application_name,sync_state FROM pg_stat_replication;
client_addr | application_name | sync_state
----------------+------------------+------------
192.168.27.142 | 142 | async
192.168.27.141 | 141 | sync
7.4 12G同异步流复制
7.4.1 12G异从
1 主库
创建用户
create role repuser login encrypted password 'repuser123' replication;
配置参数
listen_addresses = '*'
port = 5432
max_connections=1000
superuser_reserved_connections = 10
shared_buffers = 1536MB
effective_cache_size = 3GB
temp_buffers = 128MB
work_mem = 256MB
max_worker_processes = 12
archive_mode = on
archive_command = 'cp %p /postgresql/archive/%f'
wal_level = logical
max_wal_senders = 10
wal_keep_segments=256
wal_sender_timeout=60s
host all all all md5
host replication repuser all md5
2 从库
rm -rf /postgresql/pgdata/*
pg_basebackup -h 192.168.198.131 -U repuser -F p -P -R -D /postgresql/pgdata/ -l replbackup20200511
#参数说明：
# -h:指定连接的数据库的主机名或IP地址，这里就是主库的ip
# -U:指定连接的用户名，此处是我们刚才创建的专门负责流复制的repl用户
# -F:指定了输出的格式，支持p（原样输出）或者t（tar格式输出）
# -P:表示允许在备份过程中实时的打印备份的进度
# -R:表示会在备份结束后自动生成recovery.conf文件，这样也就避免了手动创建
# -D:指定把备份写到哪个目录
# -l:表示指定一个备份的标识，运行命令后看到如下进度提示就说明生成基础备份成功
[[email protected]:/postgresql/pgdata]$pg_basebackup -h 192.168.198.131 -U repuser -F p -P -R -D /postgresql/pgdata/ -l replbackup20200511
Password:
32568/32568 kB (100%), 1/1 tablespace
克隆完成，如果有standby.signal 说明是备库。
修改vi postgresql.conf文件
primary_conninfo ='host=192.168.198.131 port=5432 user=repuser passowrd=repuser123'
### 在postgre.auto.conf 添加 application_name =132,配置如***意要是postgresql.conf上面加了application_name下面就不用加了）
7.4.2 主从切换
P12之前: pg_ctl promote shell、 触发器方式,recovery.conf: triggre_file
P12 : pg_ promote ()函数( true, 60)
模拟主库故障，
Pg_ctl stop -m fast
**从库，提升从库为主库
Select pg_promote (true,60)
1 原主库以新备库存在
主库宕机
pg_ctl stop -m f
备库提升主库
postgres=# select pg_promote(true,60);
pg_promote
------------
t
(1 row)
把这条注释
#primary_conninfo ='host=192.168.198.131 port=5432 user=repuser passowrd=repuser123'
主库主机重启以后，
首先要删除目录
[[email protected]:/postgresql/pgdata]$rm -rf /postgresql/pgdata/*
[[email protected]:/postgresql/pgdata]$pg_basebackup -h 192.168.198.132 -U repuser -F p -P -R -D /postgresql/pgdata/ -l 132replbackup20200511
Password:
32584/32584 kB (100%), 1/1 tablespace
vi /postgresql/pgdata/postgresql.conf
primary_conninfo ='host=192.168.198.132 port=5432 user=repuser passowrd=repuser123'
2 原主库恢复后，以主库运行。
主库停机
pg_ctl stop -D /postgresql/pgdata/ -m f
备库停机-变主库
rm standby.signal
注释 primary_conninfo
主库变备库
[[email protected]:/postgresql/pgdata]$vi standby.signal
[[email protected]:/postgresql/pgdata]$cat standby.signal
standby_mode=on
增加以下内容
primary_conninfo ='host=192.168.198.131 port=5432 user=repuser passowrd=repuser123'
[[email protected]:/home/pgsql]$pg_basebackup -h 192.168.198.131 -U repuser -F p -P -R -D /postgresql/pgdata/ -l replbackup20200525
Password:
32590/32590 kB (100%), 1/1 tablespace
7.4.3 从节点扩容（特别简单-2）
rm -rf /postgresql/pgdata/*
pg_basebackup -h 192.168.198.131 -U repuser -F p -P -R -D /postgresql/pgdata/ -l replbackup20200511
Vim /postgresql/pgdata/postgresql.conf
primary_conninfo =' host=192.168.198.131 application_name=132 port=5432 user=repuser passowrd=repuser123'
7.4.4 12G同步数据库
#synchronous_commit = on # synchronization level;
# off, local, remote_write, remote_apply, or on
off:异步 on:不同级别最高
#synchronous_standby_names = '' # standby servers that provide sync rep
# method to choose sync standbys, number of sync standbys,
# and comma-separated list of application_name
# from standby(s); '*' = all
多台从机的时候，选择*会，随机指定一台。如果3台从机，指定1是同步，2，3就是异步，1故障，2就会变成同步。指定*只有1台是同步。
synchronous_commit = on
synchronous_standby_names = '*'
7.5 测试
[[email protected] ~]$ psql
psql (11.4)
Type "help" for help.
postgres=# SELECT client_addr,application_name,sync_state FROM pg_stat_replication;
client_addr | application_name | sync_state
----------------+------------------+------------
192.168.27.142 | 142 | async
192.168.27.141 | 141 | sync
1 异步流复制验证
主服务器
postgres=# insert into test1 values (1,'syj',25)；
从服务器
postgres=# select * from test1;
id | name | age
----+------+-----
1 | syj | 25
从服务器
postgres=# insert into test1 values (2,'syj',21);
2019-10-11 23:53:01.642 CST [7760] ERROR: cannot execute INSERT in a read-only transaction
2019-10-11 23:53:01.642 CST [7760] STATEMENT: insert into test1 values (2,'syj',21);
ERROR: cannot execute INSERT in a read-only transaction
2 同步流复制验证
关闭同步流复制机器，剩下异步流复制async
postgres=# SELECT client_addr,application_name,sync_state FROM pg_stat_replication;
client_addr | application_name | sync_state
----------------+------------------+------------
192.168.27.142 | 142 | async
(1 row)
insert into test1 values (5,'syj',18);
主数据库死锁
启动同步流复制服务器
[[email protected] ~]$ pg_ctl start -D /pgdb
2019-10-12 22:31:54.455 CST [4346] LOG: duration: 46909.826 ms statement: insert into test1 values (5,'syj',18);
INSERT 0 1
插入成功
7.6 流复制监控
7.6.1 pg_stat_replication 视图详解
pg_stat_replication是一个视图，主要用于监控一个基于流的视图。
7.6.2 检查主备库情况pg_is_in_recovery
当为t就是ture备库，f就是fase不是备考，是主库。
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row)
7.6.3 检查流复制情况
Select pid,state,client_addr,application_name,sync_priority, sync_state from Pg_stat_replication;
postgres=# \x
Expanded display is on.
postgres=#
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 5766
usesysid | 16389
usename | repuser
application_name | walreceiver
client_addr | 192.168.198.132
client_hostname |
client_port | 16204
backend_start | 2020-05-11 21:46:36.670227+08
backend_xmin |
state | streaming
sent_lsn | 0/A000148
write_lsn | 0/A000148
flush_lsn | 0/A000148
replay_lsn | 0/A000148
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
reply_time | 2020-05-11 21:51:51.772669+08
7.6.4 监控主备延迟
WAL的延迟分为write延时、flush延时、replay延时，分别对应pg_stat_replication的write_lag、flush_lag、replay_lag字段。
SELECT pid, client_addr, state, sync_state ,write_lag , flush_lag , replay_lag FROM pg_stat_replication;