Postgresql Replication (逻辑复制)

最新推荐文章于 2024-04-08 17:19:56 发布

渡人不如度己

最新推荐文章于 2024-04-08 17:19:56 发布

阅读量4.8k

点赞数

分类专栏：数据库文章标签：数据库 postgresql

本文链接：https://blog.csdn.net/qq_38626589/article/details/108217681

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

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.逻辑复制注意事项

发布节点的WAL_LEVEL参数需要设置成LOGICAL
发布节点上逻辑复制用户至少需要REPLICATION角色权限
支持一次发布一个数据库中的所有表
发布节点上需要发布的表如果需要将UPDATE/DELETE操作同步到订阅节点，需要给发布配置复制标识（复制标识默认为主键，如果没有主键也可以是唯一索引）
发布表上的DDL操作不会自动同步到订阅节点，如果发布节点上发布的表执行了DDL操作，需手工给订阅节点的相应表执行DDL，之前如果有没有同步的数据会自动同步。
当发布者添加新表时，订阅者不能自动的获知，我们需要发布端和订阅端创建表后做以下操作：
○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：订阅名称，需要在超级用户下执行）
订阅者列多于发布者（订阅者的列包含发布者的列）时，发布者插入的数据会同步到订阅者，多的字段内容为 NULL。
建立逻辑复制后，由于冲突或者表结构变更，导致逻辑复制关系挂起后，通过解决冲突和问题后，逻辑复制关系会恢复，并会同步此时发布者的数据。
发布者表结构变更后，插入数据（1、字段为之前数据类型，2、字段为变更后的数据类型），逻辑复制挂起，均不能进行数据同步，当解决冲突后，逻辑复制恢复，同步数据。

3.遇到问题的解决办法

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

通过脚本能够准确的判断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; （订阅名称）

数据库表结构变化主库和副库如何同步
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; （订阅端执行）

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

4.发布成功及报错

表发布成功的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
报错日志解决办法：（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
评论
Postgresql Replication (逻辑复制)

Postgresql Replication1.前期准备需要两台虚拟机安装数据库版本：11.3发布端：IP: 10.200.1.70 Port:5411 订阅端：IP: 10.203.5.235 Port:54321.1 订阅端基础库创建导出发布端数据的表结构发布端执行命令：pg_dump -U adempiere -s kc_test > kc_test_XXXXXXXX.dmp 将 kc_test_XXXXXXXX.dmp 文件上传到订阅端订阅端开始创建基础
复制链接

扫一扫