物理复制和逻辑复制

物理复制
async

wal_level=replica
archive_mode=on
archive_command=’/bin/date’按需调整
max_wal_senders=10
wal_keep_segments=512
hot_standby=on

wal_level参数控制wal日志信息的输出级别，有minimal，replicate，logical三种。
minimal记录的日志最少，除了记录数据库异常关闭需要恢复时的wal信息外，其他操作信息都不记录；
replicate记录的wal信息比minimal信息多，会记录支持wal归档，复制和备库中启用只读查询等操作所需的wal信息；
logical记录的日志最多，包含了支持逻辑解析所需的wal。

max_wal_senders控制控制主库上的wal最大发送进程数，此参数不能比max_connections参数值高，默认为10.

wal_keep_segments参数设置主库pa_wal目录保留的最小wal日志数，通常一个wal日志为16M，这里占用空间为8G，实际可以按需调大些。

hot_standby设置流复制备库支持只读。

pg_basebackup -D /abc/pg10 -Fp -Xs -v -P -h 192.168.6.1 -p 5432 -U repuser

-D 指定还原目录，-Fp备份数据格式与主库一致，-Xs 追加备份过程中生成的wal日志，并行发送wal备份日志，-v打印日志，-P显示传输百分比

查看流复制同步方式
select username,application_name,client_addr,sync_state from pg_stat_replication;

sync
异步流复制指主库上提交的事务不需要等待备库接收写入wal日志时便返回成功，如果主库宕机，主库上已提交的事务可能还没来得及发给备库，就会造成数据丢失，备库丢失数量与wal复制延迟有关，延迟越大，丢失越多。

同步流复制需要在主库提交事务时等待备库接受wal日志，主库收到备库发回的确认信息保证了数据的完整性，但增加了事务的响应时间，因此同步流复制吞吐量比异步流复制低。

synchronous_commit参数控制数据库提交事务时是否等待日志写入硬盘后才向客户端返回成功
单实例环境：
on 提交事务时，wal写入wal buffer在写入wal日志文件，但对数据库性能有损耗
off 与on相反
local 与on类似

流复制环境：
remote_write 主库提交事务等待备库接收主库发送的日志并写入操作系统缓存中，之后向客户端返回成功。主库wal已落盘，备库在缓存中没落盘。
on 主库wal落盘，备库wal落盘
remote_apply 主库wal落盘，备库wal落盘，并且完成重做操作，响应时间最高。

主库配置多了这两条
synchronous_commit=on
synchronous_standby_names=‘node2’

recovery.conf多了这条：application_name=node2

一主多从，一个同步从一个异步从不会出现同步从宕机主库不能写的情况，此时异步从可以提升为另一个同步从

延迟备库
只需在recovery.conf中配置参数
recovery_min_apply_delay=30min
如果配置了synchronous_commit=on同时配置了延迟备库会怎样？无影响
如果配置了synchronous_commit=remote_apply会怎样？主库插入一条语句需要延迟才能写。。。。。

流复制监控语句
select * from pg_stat_replication;
state字段显示wal发送进程的状态，startup表示wal进程在启动，catchup表示备库在追赶主库，streaming表示备库已经追上主库
write_lag备库写操作系统延迟
flush_lag传输延迟
replay_lag应用延迟
sync_state同步状态

select pid,username,client_addr,state,write_lag,flush_lag,replay_lag from pg_stat_replication;

10版本以前监控主备延迟
select extract(second from now() - pg_last_xact_replay_timestamp());

通过日志应用数量判断同步状态
select pid,username,client_addr,state
pg_wal_lsn_diff(pg_current_wal_lsn(),write_lsn) write_delay,
pg_wal_lsn_diff(pg_current_wal_lsn(),flush_lsn) flush_delay,
pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn) replay_dely
from pg_stat_replication;
返回单位是字节，主库宕机此方法不行。

select * from pg_stat_wal_receiver;
pid为日志进程号

相关函数
恢复模式：select pg_is_in_recovery();
接收日志位置：select pg_last_wal_receive_lsn();
应用日志位置：select pg_last_wal_replay_lsn();
备库最后应用日志时间：select pg_last_xact_replay_timestamp();
主库当前日志位置：select pg_current_wal_lsn();
主备日志差值计算：select pg_wal_lsn_diff(‘3/940001b0’,‘3/940001a0’);

逻辑复制
逻辑复制基于逻辑解析，把wal日志解析成一定格式的数据流到订阅者节点进行应用，是表级别的复制，类似于ogg
逻辑解析前提是wal_level=logical,max_replication_slots=8(允许最大的复制槽数)物理复制槽是避免wal被覆盖，而逻辑复制槽是解析数据变换

select * from pg_logical_slot_get_changes(‘logical_slot1’,null,null);如果想重复查询解析的数据查pg_logical_slot_peek_changes

删除逻辑复制槽：select pg_drop_replication_slot(‘logical_slot1’);

publication发布者节点，subscription订阅者节点

复制的表最好有主键或者唯一标识，如果没有会影响复制效率，不复制ddl操作

逻辑复制搭建思路;

发布者的postgresql.conf要设置如下：
wal_level=logical
max_replication_slots=8
max_wal_senders=10(需大于max_replication_slots参数值加物理备库数量)

订阅者节点的postgresql.conf要设置如下：
max_replication_slots=8
max_logical_replication_workers=8(复制进程数量，另外需要设大max_worker_processes)

首先发布者节点创建发送用户：
create user logical_user replication login connection limit 8 encrypted password “logical_user”;
之后给logical_user需要发送的表的读权限
grant select on xxxx.xxxx tological_user;
grant usage on schema postgres to logical_user;

create table t (id int,name text); insert into t values (1,‘a’);

创建发布语法：create publication pub1 for table t;
select * from pg_publication;

订阅者节点只创建表结构：create table t (id int,name text);

创建订阅语法：create subscription sub1 connection ‘conninfo’ publication pub1

在订阅者节点创建~/.pgpass
192.168.6.1:mydb:logical_user:logical_user chmod 600 .pgpass
同时发布者节点的pg_hba需要相应策略允许订阅者连接

之后在订阅者节点使用超级用户创建订阅
create subscription sub1 connection ‘host=192.168.6.1 port=1921 dbname=mydb user=logical_user’ publication pub1;

select slot_name,plugin,slot_type,database,active,restart_lsn from pg_replication_slots where slot_name=‘sub1’;

订阅者节点查看订阅信息：select * from pg_subscription;
验证复制：select * from t;