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权限赋给发布用户
  • 0
    点赞
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值