PG 逻辑复制DDL同步

最新推荐文章于 2024-05-13 08:00:00 发布
最新推荐文章于 2024-05-13 08:00:00 发布
阅读量1.6k 收藏 7
点赞数 1
文章标签： postgresql 逻辑复制
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_37692493/article/details/120998605
版权

文章目录


PG数据库逻辑复制比较大的一个弊端就是它自身无法满足同步对象DDL操作的同步，如果单纯依赖原生的逻辑复制架构，逻辑复制表对象表结构变更操作的整套维护操作是及其复杂、而且容易遗漏的。目前比较常见的解决方案是利用触发器抓取主库相关表对象的DDL变更，然后连接逻辑复制订阅端进行同步应用，本文主要讲解一个可直接部署使用的逻辑复制DDL同步插件，简化我们的运维成本。

一、逻辑复制+DDL同步部署

1.1 必要环境准备

  • 数据库环境 ： PostgreSQL 13
  • 逻辑复制数据库账号：logical_repl
  • 业务执行DDL操作数据库账号：app_user

1.2 源端逻辑复制发布

1、手动在逻辑复制发布端创建两张基础表，模拟t1、t2表进行逻辑复制

test=# \d
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 schem1 | t1   | table | postgres
 schem1 | t2   | table | postgres
(2 rows)

test=# \d t1
                 Table "schem1.t1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           | not null | 
Indexes:
    "t1_pkey" PRIMARY KEY, btree (id)
Publications:
    "pub1"

test=# \d t2
                 Table "schem1.t2"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           | not null | 
Indexes:
    "t2_pkey" PRIMARY KEY, btree (id)
Publications:
    "pub1"

2、创建逻辑复制用户并授权

1）用户创建

postgres=# create user logical_repl with password 'xxx';
postgres=# alter user logical_repl replication;

2）配置文件

-- 修改配置文件
$ vim pg_hba.conf
host    all             logical_repl    0.0.0.0/0               md5
host    replication     logical_repl    0.0.0.0/0               md5

-- 重新加载配置文件
postgres=# select pg_reload_conf();

3）授权对应逻辑复制表对象的权限

\c test
set search_path='schem1';
GRANT USAGE ON SCHEMA schem1 to logical_repl;
GRANT SELECT ON ALL TABLES IN SCHEMA schem1 TO logical_repl;
ALTER DEFAULT PRIVILEGES IN SCHEMA schem1 grant select on tables to logical_repl;

3、配置发布通道

test=# create publication pub1;
CREATE PUBLICATION

test=# alter publication pub1 add table t1;
ALTER PUBLICATION

test=# alter publication pub1 add table t2;
ALTER PUBLICATION

test=# select * from pg_publication_tables ;
 pubname | schemaname | tablename 
---------+------------+-----------
 pub1    | schem1     | t1
 pub1    | schem1     | t2
(2 rows)

1.2 目标端逻辑复制订阅

1、在创建订阅任务之前提前将需要进行逻辑复制的表对象进行创建（可以利用pg_dum进行初始化表结构的同步）

test1=# \d
Did not find any relations.
test1=# create table t1(id int primary key);
CREATE TABLE
test1=# create table t2(id int primary key);
CREATE TABLE
test1=# \d
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 schem1 | t1   | table | postgres
 schem1 | t2   | table | postgres
(2 rows)

2、创建逻辑复制的订阅任务

test1=# create subscription sub1 connection 'host=172.16.104.55 port=5433 dbname=test user=logical_repl password=123' publication pub1;
NOTICE:  created replication slot "sub1" on publisher
CREATE SUBSCRIPTION

1.3 pgl_ddl_deploy 插件部署使用

1、pgl_ddl_deploy安装部署（发布/订阅端都需要安装）

-- 软件下载地址
https://github.com/enova/pgl_ddl_deploy

-- 安装部署
# tar xf pgl_ddl_deploy-2.1.0.tar.gz -C /usr/local/postgresql-13.4/contrib/
# cd /usr/local/postgresql-13.4/contrib/pgl_ddl_deploy-2.1.0/
# make && make install

2、发布端配置相关DDL同步设置

1）加载插件

test=# CREATE EXTENSION pgl_ddl_deploy;
CREATE EXTENSION

2）编写配置文件，该配置文件表示会复制schem1库中所有的DDL操作

本案例中是配置了将发布端schem1下所有对象的变更均同步至逻辑复制的订阅端，数据库的DML同步依赖PG原生的逻辑复制。

-- 编辑DDL同步配置设置
test=# INSERT INTO pgl_ddl_deploy.set_configs (set_name,include_schema_regex,driver) VALUES('pub1','schem1','native'::pgl_ddl_deploy.driver);
INSERT 0 1

- 参数说明：
    - pub1 : 发布端发布通道名称
    - schema1 : 可指定schema级别的DDL同步，本案例只对schema1进行同步
    - 'native'::pgl_ddl_deploy.driver ： 表示DML同步使用PG原生的逻辑复制

-- 配置生效
test=# SELECT pgl_ddl_deploy.deploy('pub1') from pgl_ddl_deploy.set_configs;
NOTICE:  table "tmp_objs" does not exist, skipping
NOTICE:  event trigger "auto_rep_ddl_create_1_pub1" does not exist, skipping
NOTICE:  event trigger "auto_rep_ddl_drop_1_pub1" does not exist, skipping
NOTICE:  event trigger "auto_rep_ddl_unsupp_1_pub1" does not exist, skipping
NOTICE:  function pgl_ddl_deploy.auto_rep_ddl_create_1_pub1() does not exist, skipping
NOTICE:  function pgl_ddl_deploy.auto_rep_ddl_drop_1_pub1() does not exist, skipping
NOTICE:  function pgl_ddl_deploy.auto_rep_ddl_unsupp_1_pub1() does not exist, skipping
 deploy 
--------
 t
(1 row)

3）权限加载
若需要使用指定账号执行该schema下的DDL操作，需要将该数据库账号对pgl_ddl_deploy进行授权。

test=# SELECT pgl_ddl_deploy.add_role(oid) from pg_roles where rolname='logical_repl';
 add_role 
----------
 t
(1 row)

test=# SELECT pgl_ddl_deploy.add_role(oid) from pg_roles where rolname='app_user';
 add_role 
----------
 t
(1 row)

3、订阅端加载pgl_ddl_deploy插件并授权

1）若为全新的环境，可以先安装插件后创建逻辑复制订阅任务

test1=# create extension pgl_ddl_deploy ;
CREATE EXTENSION
test1=# create subscription sub1 connection 'host=172.16.104.55 port=5433 dbname=test user=logical_repl password=123' publication pub1;
NOTICE:  created replication slot "sub1" on publisher
CREATE SUBSCRIPTION

2）若逻辑复制已经创建逻辑复制订阅任务已经创建，在安装插件后重新加载逻辑复制任务

test1=# create extension pgl_ddl_deploy ;
CREATE EXTENSION
test1=# alter subscription sub2 refresh publication ;
ALTER SUBSCRIPTION

3）若需要使用指定账号执行该schema下的DDL操作，需要将该数据库账号对pgl_ddl_deploy进行授权。

test=# SELECT pgl_ddl_deploy.add_role(oid) from pg_roles where rolname='logical_repl';
 add_role 
----------
 t
(1 row)

test=# SELECT pgl_ddl_deploy.add_role(oid) from pg_roles where rolname='app_user';
 add_role 
----------
 t
(1 row)

二、功能验证

1、表对象DDL同步验证

源																														目标 
--------------------------------------------------------------------------------------------------------------------------------------
																								             test1=# select * from t1;
																								              id                      
																								             ----                     
																								             (0 rows)                 
test=# insert into t1 values(1);
INSERT 0 1            
                                                              test1=# select * from t1;					    // 正常的数据复制
                                                               id                      
                                                              ----                     
                                                                1                      
                                                              (1 row) 
                                                                               
test=# alter table t1 add id1 int;																																	// 逻辑复制相关表的DDL复制
ALTER TABLE      
                                                              test1=# \d t1                                      
                                                                               Table "schem1.t1"                 
                                                               Column |  Type   | Collation | Nullable | Default 
                                                              --------+---------+-----------+----------+---------
                                                               id     | integer |           | not null |         
                                                               id1    | integer |           |          |         
                                                              Indexes:                                           
                                                                  "t1_pkey" PRIMARY KEY, btree (id)              



test=# create  table t3 (id int);																																		  //  非逻辑复制表，在当前schema下,也会同步自目标库，但是不影响原有的逻辑复制表清单
CREATE TABLE
                                                                                                       
test=# insert into t3 values(1);
INSERT 0 1                                                                                                       
                                                                                                       
                                                              test1=# \d                                                                                                           
test=# select * from pg_publication_tables ;                          List of relations         
 pubname |   schemaname   | tablename                          Schema | Name | Type  |  Owner   
---------+----------------+-----------                        --------+------+-------+----------
 pub1    | schem1         | t1                                 schem1 | t1   | table | postgres 
 pub1    | schem1         | t2                                 schem1 | t2   | table | postgres 
 pub1    | pgl_ddl_deploy | queue                              schem1 | t3   | table | postgres 
(3 rows)                                                      (3 rows)                          
                                  
                                                              test1=# select * from t3;         
                                                              id                               
                                                              ----                              
                                                              (0 rows)                          

	
																															// 模拟将一些无主键表删除复制的情况
test=# create table t4(id int);																														
CREATE TABLE
test=# insert into t4 values(1);
INSERT 0 1     
test=# alter publication pub1 add table t4;
ALTER PUBLICATION
                                                              test1=# alter subscription sub1 refresh publication ;
                                                              ALTER SUBSCRIPTION                                   
                                                              test1=# select * from t4;                            
                                                               id                                                  
                                                              ----                                                 
                                                                1                                                  
                                                              (1 row)

2、表对象移除逻辑复制后，数据不再进行同步，但表结构同步依旧同步

test=# delete from t4 where id=1;																													// 由于无主键或者唯一键，无法执行delete、update
ERROR:  cannot delete from table "t4" because it does not have a replica identity and publishes deletes
HINT:  To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.


test=# alter publication pub1 drop table t4;																						 //  将无主键表清除在逻辑复制之外
ALTER PUBLICATION



test=# delete from t4 where id=1;
DELETE 1
                                                                 test1=# select * from t4;
                                                                  id                      
                                                                 ----                     
                                                                   1                      
                                                                 (1 row)                  

test=# insert into t4 values(1);
INSERT 0 1
                                                                 test1=# select * from t4;
                                                                  id                      
                                                                 ----                     
                                                                   1                      
                                                                 (1 row)                  
test=# alter table t4 add column id1 int;
ALTER TABLE
test=# \d t4                                                     test1=# \d t4                                      
                 Table "schem1.t4"                                                Table "schem1.t4"                 
 Column |  Type   | Collation | Nullable | Default                Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------              --------+---------+-----------+----------+---------
 id     | integer |           |          |                        id     | integer |           |          |         
 id1    | integer |           |          |                        id1    | integer |           |          |

3、删除逻辑复制表对象验证

test=# select * from pg_publication_tables ;         
 pubname |   schemaname   | tablename       
---------+----------------+-----------      
 pub1    | schem1         | t1              
 pub1    | schem1         | t2              
 pub1    | pgl_ddl_deploy | queue           
(3 rows)                                    
                                            
test=# drop table t2;                       
DROP TABLE                                  
test=# select * from pg_publication_tables ;
 pubname |   schemaname   | tablename       
---------+----------------+-----------      
 pub1    | schem1         | t1              
 pub1    | pgl_ddl_deploy | queue           
(2 rows) 

test=# \d                                                        test1=# \d                            
        List of relations                                                List of relations         
 Schema | Name | Type  |  Owner                                   Schema | Name | Type  |  Owner   
--------+------+-------+----------                               --------+------+-------+----------
 schem1 | t1   | table | postgres                                 schem1 | t1   | table | postgres 
 schem1 | t3   | table | postgres                                 schem1 | t3   | table | postgres 
 schem1 | t4   | table | postgres                                 schem1 | t4   | table | postgres 
(3 rows)                                                         (3 rows)

总结：

  • pgl_ddl_deploy基本可满足大部分场景的DDL同步，同步粒度可从配置文件着手进行优化，目前测试来看create index是无法进行同步的，需要手动在逻辑订阅端执行；
  • 若同步整个schema级别的表对象DDL
    • 发布端新增表架构自动同步至逻辑复制订阅端，且会将该表加入至逻辑复制对象，需要订阅端重新加载订阅通道开始进行数据同步
    • 发布端删除逻辑复制对象，数据DML不再继续同步，但是表结构DDL操作依旧会进行同步
    • 发布端删除表后，会自动删除其逻辑复制对象

参考文档：

https://github.com/enova/pgl_ddl_deploy
https://developer.aliyun.com/article/277983

三思呐三思
关注
  • 1
    点赞
  • 踩
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
pgl_ddl_deploy:透明逻辑DDL复制
05-09
透明逻辑DDL复制（pgl_ddl_deploy） 适用于pg逻辑复制和本机逻辑复制的Postgres 9.5+的透明DDL复制。 本机逻辑支持的配置 解决DDL复制问题 解决订户上失败的DDL 解决未处理的DDL 在订阅服务器上禁用DDL复制 对于开发人员 帮助所需功能 回归测试 概述 自此扩展的原始版本发布以来，版本2.0引入了对本机逻辑复制的支持的重大更改。 在此处阅读原始发行摘要： https : //innovation.enova.com/pursuing-postgres-ddl-replication/ 发行说明 2.0版 变更摘要： 支持使用本机逻辑复制进行DDL复制 支持Postgres 13 1.7版 变更摘要： 支持Postgres 12 支持pgologic 2.3.0 版本1.6 变更摘要： 因不稳定的debug_query_strin
Postgresql逻辑复制DDL插件pgl_ddl_deploy
魂醉的一亩二分地
07-05 701
pgl_ddl_deploy可以支持DDL复制，不过目前版本只支pg13持原生的逻辑复制，13之前的版本，需要安装pglogical,也就是需要部署两个插件。 所以对于使用pg13版本的同学，可以单独使用该插件，以后就再也不用担心字段变更的时候，两边表字段不一致，导致复制槽false了。 部署安装实例可参考如下： https://github.com/enova/pgl_ddl_deploy ...
PG16 逻辑复制
最新发布
cqsztech的博客
05-13 833
PG逻辑复制，和我想的逻辑复制，有点不一样
postgresql常见的DDL和DML操作
归来仍少年
07-15 1517
postgresql基本操作 ## 创建数据库,默认模板是template1 create database db_name; ## 重命名数据库 alter database db_name to new_db_name; ## 删除数据库 drop database db_name; (如果还有用户连接在这个数据库上，将无法删除该数据库) ##[为某个用户]创建模式 create schema sm_name [authorization user_name]; ##修改模式名称和属主 alte
postgresql 获取 table ddl 之一 pgddl
一名数据库爱好者的专栏
09-24 2616
os: centos 7.6.1810 db: postgresql 10 postgresql 没有 mysql 数据库 show create table 命令,所以查看表的定义就稍微麻烦一些。 本次先试下 pgddl 版本 # cat /etc/centos-release CentOS Linux release 7.6.1810 (Core) # # yum list installed|grep -i postgre postgresql10.x86_64 10
PostgreSQL 逻辑订阅 - DDL 订阅 实现方法
weixin_34401479的博客
12-04 872
标签 PostgreSQL , 逻辑订阅 , 逻辑复制 , DDL 复制 , udf , 触发器 , 事件触发器 背景 逻辑订阅是PostgreSQL 10内置的功能，通过逻辑订阅，可以实现部分数据的同步要求。例如可以做到行级、表级、库级的订阅。 《使用PostgreSQL逻辑订阅实现multi-master》 《PostgreSQL 逻辑订阅 - ...
京东数科-PG逻辑复制实战
05-14
【京东数科-PG逻辑复制实战】探讨的是PostgreSQL数据库中的逻辑复制功能，这一特性自PostgreSQL 10版本开始引入。逻辑复制不同于传统的物理复制，后者基于WAL（Write-Ahead Log）日志进行，确保主备节点的块级完全...
OGG配置DDL数据同步
04-11
通过ogg DDL 配置，实现源端到目标端数据，表结构，序列等对象的同步。也可以配置指定的对象，如只监控指定的表或其他对象。
mysql实时同步pg
01-17
Flink CDC是一种基于Flink的Change Data Capture（CDC）工具，用于实现数据库...此外，Flink CDC还支持DDL变更，这意味着当MySQL中的表结构发生变化时，Flink CDC能够自动适应这些变化，并将其同步到PostgreSQL中。 另
配置GoldenGate同步DDL语句
03-24
配置GoldenGate同步DDL语句 GoldenGate 是一种数据 replication 和集成工具，能够在异构...GoldenGate同步DDL语句能够帮助用户实现数据的一致性和实时性，提高数据的可用性和可靠性，是数据同步和复制的重要工具。
深入学习PostgreSQL数据库复制优势、原理及设置等
WPHunter的编程技术资料库！
01-03 1307
在两个数据库之间设置复制提供了针对意外事故的容错能力。它被认为是在灾难期间实现高可用性的最佳策略。在本文中，我们将深入探讨后端开发人员可以为无缝PostgreSQL复制实施的不同策略。
PostgreSQL DDL操作解析
帅的数说
02-25 2046
本文介绍PG DDL操作上层接口。1、DDL操作的入口函数是ExecModifyTable，insert、delete、update都会进入这个函数，如果为insert，执行ExecPr...
PostgreSQL数据库序列的DDL获取和细节处理
SUOYUEのBLOG
04-28 1069
PostgreSQL数据库序列的DDL获取和细节处理
常用SQL语句常见建表建索引等DDL语句（PGSQL）
w8y56f的专栏
02-16 3321
背景 pgsql常见DDL语句，怎么建主键，怎么建索引，建表时自动建sequence，查询某个表是否存在，某个表是否存在某个字段，某个索引是否存在，查主键的名字 查 “是否存在” 获得当前数据库(catalog)和schema --使用这两个函数 select current_database(); select current_schema(); 查schema下有什么表 --查询当前schema下有什么表（方法一） select * from pg_tables where schemanam
PostgreSQL获取某个表的DDL语句
丘处机的专栏
12-10 7125
pg中有获取函数，索引，约束的方法，但是没有获取表结构的方法，这个挺奇怪。最近有需求，根据网上的找的一个语法，进行了修改，代码如下： CREATE OR REPLACE FUNCTION tabledef(text,text) RETURNS text LANGUAGE sql STRICT AS $$ WITH attrdef AS ( SELECT n.nspname, c.relname, c.oid, pg_catalog.array_to_string(c.reloption
【postgresql】schema(namespace) ddl代码解析
兰陵鸡王的博客
03-03 1196
非v
GBase 8c 发布（三）
qq_41614765的博客
10-18 74
表可以使用ALTER PUBLICATION动态地增加或者移除。ADD TABLE以及DROP TABLE操作都是事务性的，因此一旦该事务提交，该表将以正确的快照开始或者停止复制。Publication通过使用CREATE PUBLICATION命令创建并且可以在之后使用相应的命令进行修改或者删除。每一个发布都可以有多个订阅者。
DDL审计 , DDL逻辑复制 , 打造DDL统一管理入口 - PostgreSQL DDL 事件触发器应用
weixin_33809981的博客
12-07 391
标签 PostgreSQL , event trigger , 事件触发器 , 用法详解 背景 DDL是非常重的操作，（锁大，或者会导致TABLE REWRITE导致消耗大量资源，影响大，例如DROP,TRUNCATE）也是数据库的使用过程中需要关注的。 通常企业会对DDL做一定的限制，不允许某些用户执行，或者不允许从网络登录的用户执行DDL。 同时...
oracle ddl 同步,详解DDL同步配置.doc
weixin_36130547的博客
04-13 554
详解DDL同步配置详解DDL同步配置在配置DDL同步之前,建议先动手配置DML的同步环境,如果你还不会配置GoldenGate–DML环境,可以参考Oracle GoldenGate for Oracle 11g(Solaris 10 x86-64) to Oracle 10g(CentOS 4.8) configuration这篇博文。DDL复制与DML复制的机制原理是完全不同的,DDL基于tr...
mysql ddl同步表相差字段
04-04
MySQL DDL同步表相差字段可以使用以下步骤： 1. 使用SHOW CREATE TABLE语句获取源表和目标表的DDL语句。 2. 将两个DDL语句分别解析为字段列表。 3. 比较两个字段列表，找出相差的字段。 4. 使用ALTER TABLE语句将目标表添加缺失字段或删除多余字段。 下面是一个示例脚本，可以自动比较并同步两个表的DDL： ``` #!/bin/bash # source table src_table="source_table" src_db="source_database" # target table tgt_table="target_table" tgt_db="target_database" # get source table ddl src_ddl=$(echo "SHOW CREATE TABLE ${src_db}.${src_table}" | mysql | tail -n +2 | awk '{print $2}') # get target table ddl tgt_ddl=$(echo "SHOW CREATE TABLE ${tgt_db}.${tgt_table}" | mysql | tail -n +2 | awk '{print $2}') # parse source and target ddl into field lists src_fields=$(echo ${src_ddl} | awk -F"(" '{print $2}' | awk -F")" '{print $1}' | sed 's/ /\n/g' | sed '/^$/d') tgt_fields=$(echo ${tgt_ddl} | awk -F"(" '{print $2}' | awk -F")" '{print $1}' | sed 's/ /\n/g' | sed '/^$/d') # find missing fields in target table for field in ${src_fields} do if [[ ! ${tgt_fields} =~ ${field} ]] then echo "Adding field ${field} to ${tgt_db}.${tgt_table}..." alter_stmt="ALTER TABLE ${tgt_db}.${tgt_table} ADD COLUMN ${field} $(echo ${src_ddl} | grep ${field} | awk -F"${field} " '{print $2}' | sed 's/,//g')" echo ${alter_stmt} | mysql fi done # find extra fields in target table for field in ${tgt_fields} do if [[ ! ${src_fields} =~ ${field} ]] then echo "Dropping field ${field} from ${tgt_db}.${tgt_table}..." alter_stmt="ALTER TABLE ${tgt_db}.${tgt_table} DROP COLUMN ${field}" echo ${alter_stmt} | mysql fi done ``` 注意：这个脚本只能同步字段的添加和删除，不能处理字段顺序、类型、长度等的变化。如果需要完整的DDL同步功能，可以考虑使用开源工具如Liquibase或Flyway。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值