Postgresql Replication (逻辑复制)

Postgresql Replication 步骤

1.前期准备

需要两台虚拟机 安装数据库 版本：11.3
发布端：IP: 10.200.1.70  Port:5411	
订阅端：IP: 10.203.5.235  Port:5432

1.1 订阅端基础库创建

导出发布端数据的表结构
发布端执行命令：pg_dump -U adempiere -s kc_test > kc_test_XXXXXXXX.dmp  
将   kc_test_XXXXXXXX.dmp 文件上传到订阅端
订阅端开始创建基础库,执行如下命令 
psql -U postgres
create database kc_rep_bi;
psql -U adempiere -d kc_rep_bi < kc_test_XXXXXXXX.dmp  

1.2.发布端文件配置

通过putty登录到订阅端服务器   
Postgresql.conf文件内容修改

root 用户执行下列操作  
[root@Kobayashi_test ~]# cd /var/lib/pgsql/11/data     
[root@Kobayashi_test data]# vi postgresql.conf   
修改内容如下  
Wal_level =logical  
Listen_addresses ='*'
max_replication_slots = 8  
max_wal_senders = 10

pg_hba.conf文件内容修改
[root@Kobayashi_test ~]# cd /var/lib/pgsql/11/data  
[root@Kobayashi_test data]# vi pg_hba.conf  
添加内容如下  
host      all      all     10.203.5.235/32    trust   

修改改后重启数据库 
systemctl restart postgresql-11

1.3.订阅端文件配置

通过putty登录到订阅端服务器  
postgresql.conf文件内容修改  
root 用户执行下列操作  
[root@BI-DEV ~]# cd /var/lib/pgsql/11/data  
[root@BI-DEV data]# vi postgresql.conf   
修改内容如下  
max_replication_slots =8  
max_logical_replication_workers =8  
max_worker_processes =10 

1.4.发布节点创建复制用户

通过putty登录到发布端服务器
root 用户执行下列命令  
[root@Kobayashi_test home]# psql -U postgres 
postgres=# create user repuser replication login connection limit 5 encrypted password 'repuser';   
CREATE ROLE  
postgres=# \q  

1.5.发布节点创建需要发布的表

创建发布表，根据情况执行下面两个命令中的其中一个即可  
1 创建发布数据库中的所有表执行下列命令
[root@Kobayashi_test home]# psql -U adempiere -d kc_test  
kc_test=# Create publication kcpub for all tables;  （所有表）
kc_test=# Create publication mypub for table tablename;  （单张表）
2 创建发布数据库中的指定表执行下列命令
[root@Kobayashi_test home]# psql -U adempiere -d kc_test  
kc_test=# Create publication kcpub for table tablename(指定表名); 
执行下列命令出现如下信息则创建成功  
kc_test=# Select * from pg_publication;
pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate  
---------+----------+--------------+-----------+-----------+-----------+-------------   
kcpub      10            t               t         t             t          t    

1.6.发布用户设置权限

执行如下命令,将adempiere模式的使用权限赋给repuser  
[root@Kobayashi_test home]# psql -U adempiere -d kc_test  
kc_test=# grant usage on schema adempiere to repuser;  
GRANT   
执行如下命令,将部分表的SELECT权限赋给repuser  
kc_test=# grant select on all tables in schema adempiere to repuser; （所有表发布时执行）  
kc_test=# grant select on tablename to repuser;      （单表发布时执行）
GRANT

1.7.订阅节点创建订阅者

执行如下命令（kcsub订阅者名称、kcpub发布者名称）  
[root@Kobayashi_test home]# psql -U adempiere -d kc_test    
kc_test=# Create subscription kcsub connection 'host=10.200.1.70 port=5411 dbname=kc_test user=repuser password=repuser' publication kcpub;  
CREATE SUBSCRIPTION  
kc_test=#  
查看创建的订阅信息  
kc_test=# Select * from pg_subscription; 

2.逻辑复制注意事项

1. 发布节点的WAL_LEVEL参数需要设置成LOGICAL
2. 发布节点上逻辑复制用户至少需要REPLICATION角色权限
3. 支持一次发布一个数据库中的所有表
4. 发布节点上需要发布的表如果需要将UPDATE/DELETE操作同步到订阅节点，需要给发布配置复制标识（复制标识默认为主键，如果没有主键也可以是唯一索引）
5. 发布表上的DDL操作不会自动同步到订阅节点，如果发布节点上发布的表执行了DDL操作，需手工给订阅节点的相应表执行DDL，之前如果有没有同步的数据会自动同步。
6. 当发布者添加新表时，订阅者不能自动的获知，我们需要发布端和订阅端创建表后做以下操作：
   ○1 将表的SELECT权限赋给repuser （发布端执行 ）
   a. grant select on all tables in schema adempiere to repuser; （所有表发布时执行）
   b. grant select on tablename to repuser; （单表发布时执行）
   如果只对一张表创建了发布者，新增表后，想要把此表也添加到发布中的时候执行以下命令：
   Alter publication mypub add table newtablename;
   如果需要从发布mypub中移除一张表时（此表不需要逻辑同步时）
   Alter publication mypub drop table tablename; （如果创建发布者是所有表时，不能将表添加或从中删除。）
   更新订阅者（订阅端执行）
   alter subscription mysub refresh publication; （mysub：订阅名称，需要在超级用户下执行）
7. 订阅者列多于发布者（订阅者的列包含发布者的列）时，发布者插入的数据会同步到订阅者，多的字段内容为 NULL。
8. 建立逻辑复制后，由于冲突或者表结构变更，导致逻辑复制关系挂起后，通过解决冲突和问题后，逻辑复制关系会恢复，并会同步此时发布者的数据。
9. 发布者表结构变更后，插入数据（1、字段为之前数据类型，2、字段为变更后的数据类型），逻辑复制挂起，均不能进行数据同步，当解决冲突后，逻辑复制恢复，同步数据。

3.遇到问题的解决办法

1. 服务器断网导致REPLICATION无法同步数据
   网络连接正常后，会自动同步断网没有同步的数据

2. 通过脚本能够准确的判断REPLICATION的连接状态和连接数量
   查看发布者：Select * from pg_publication;
   Pubname:指发布的名称
   Pubowner：指发布的属主，和pg_user视图的usesysid字段关联
   Puballtables：是发布数据库中的所有表，t表示发布数据库中所有已存在的表和以后新建的表
   Pubinsert：t表示仅发布表上的INSERT操作
   Pubupdate：t表示仅发布表上的UPDATE操作
   Pubdelete：t表示仅发布表上的DELETE操作
   删除发布者：drop publication mypub; （发布名称）
   查看订阅者：Select * from pg_subscription;
   Subdbid：数据库的OID，和pg_database.iod关联
   Subname：订阅的名称
   Subowner：订阅的属主
   Subenabled：是否启用订阅（t为启用，f为停止）
   Subconninfo：订阅的连接串信息，显示发布节点连接串信息
   Subslotname：复制槽名称
   Subpublications：订阅节点订阅的发布列表
   删除订阅者：drop subscription mysub; （订阅名称）

3. 数据库表结构变化主库和副库如何同步
   01 订阅节点上停止mysub订阅
   alter subscription mysub disable;
   02 主库和副库备份数据后，执行数据库结构变更脚本
   03 订阅节点上开启mysub订阅
   alter subscription mysub enable;
   04 涉及到添加新表时需要执行以下两个命令（根据情况确认是否执行）
   grant select on all tables in schema adempiere to repuser;（发布端执行）
   alter subscription mysub refresh publication; （订阅端执行）

4. 当画面上直接配置字段或者修改字段值的情况，主库和副库如何同步
   a.如果涉及配置字段（增加、减少、修改字段类型等）DDL操作，按照“数据库表结构变化主库和副库如何同步”的方法执行
   b.如果只是修改字段的数据内容（只是表的数据发生变化），修改的数据可以自动同步到订阅对应的表中，无需手动处理。

4.发布成功及报错

1. 表发布成功的LOG日志
   LOG: logical replication table synchronization worker for subscription “mysub”, table “table” has started
   LOG: logical replication table synchronization worker for subscription “mysub”, table “table” has finished
2. 报错日志解决办法：（LOG路径 DATA/LOG）
   错误日志一：duplicate key value violates unique constraint “table”
   订阅表中的Key值与发布者表中的Key值冲突，删除订阅表中对应key的整条数据即可。
   错误日志二：logical replication target relation “table” is missing some replicated columns
   订阅表与发布表的表结构不同，修改表的结构保持一致即可。（订阅表的字段比发布表多也可以）
   错误日志三：could not start initial contents copy for table “table”: ERROR: permission denied for relation table
   没有发布成功，需要将表的SELECT权限赋给发布用户