postgresql 逻辑复制

发布时间:2024年01月15日

本文为 【TDSQL-PG笔记】主从复制原理的系列文章,实验基础环境参考栏目第一篇blog


如果要将不同库的某关键表备份到指定库里,可以用逻辑复制完成相关功能。

逻辑复制基本概念

PG逻辑复制基于发布和订阅模型,一个发布可以有多个订阅者
发布publication可以创建在任意单机上,也可以在物理复制的主库上,发布端要求数据库参数WAL_LEVEL为logical。一个发布可以被多个数据库订阅。一个发布只能包含一个数据库中的表,但是这些表可以分布在不同的schema下。

订阅处于逻辑复制的下游端,一个订阅只能对应一个发布。订阅端的数据库名可以跟发布端不同,但是订阅发布的表名和schema必须相同

当订阅创建成功后,就会自动在发布端创建多个逻辑复制槽,其中一个用于后续的增量数据同步,永久保留,另外还会创建一个或者多个的临时复制槽,用于复制表的全量快照数据。
是当发布端是物理复制架构时,如果发生主从切换,发布是不会跟随数据库failover的。但是可以通过手工拷贝文件或者利用插件实现。

逻辑复制的限制

(1)数据库的表结构不会被复制,所以需要在订阅端先创建对应的schema和表
(2)TRUNCATE和DDL不会复制,如果要进行DDL变更,需要先在订阅端执行DDL,然后在发布端执行。
(3)Sequence不会不复制,当发布端使用了sequence数据,其写入了表的值会同步到订阅端,但是目标库的sequence并不会发生变化。
(4)不支持大对象。
(5)复制只能是基表到基表,不支持视图、物化视图、外部表等。如果表是分区表,需要基于分区进行复制。

测试

  • 发布和订阅端都新建相同的表t1,t2
  • 发布端为t1创建发布
  • 订阅端为t1创建订阅
新建一个库,做为订阅端
[pg@localhost data]$ pg_ctl init -D /data/db3

发布与订阅端都建相关同步结构的表t1,t2
postgres=# create table t1(id int,name varchar(20));
CREATE TABLE
postgres=# create table t2(id int,name varchar(20));
CREATE TABLE

在发布端为t1创建发布

修改发布端wal_level = logical,修改后要restart订阅端,reload不生效。
postgres=# show wal_level;
 wal_level
-----------
 logical
(1 row)


postgres=# create publication pub_test for table t1;
CREATE PUBLICATION

在订阅端为t1创建订阅

注:订阅端有wal_level也必须是logical

postgres=# create subscription sub_test connection 'host=127.0.0.1 port=15431 dbname=postgres user=pg' publication pub_test;
NOTICE:  created replication slot "sub_test" on publisher
CREATE SUBSCRIPTION

观察同步效果

在发布端分别向 t1 t2表插入数据
postgres=# insert into t1(id,name) values (1,'name1');
INSERT 0 1
postgres=# insert into t2(id,name) values (1,'name1');
INSERT 0 1

查看订阅端,只有已新建订阅的表数据同步
postgres=# select * from t1;
 id | name
----+-------
  1 | name1
(1 row)

postgres=# select * from t2;
 id | name
----+------
(0 rows)

为新表添加同步

发布端,将t2加入到发布
postgres=# alter publication pub_test add table t2;
ALTER PUBLICATION

查看发布表
postgres=# select * from pg_publication_tables;
 pubname  | schemaname | tablename
----------+------------+-----------
 pub_test | public     | t1
 pub_test | public     | t2
(2 rows)

订阅端,刷新订阅配置
postgres=# alter subscription sub_test refresh publication;
ALTER SUBSCRIPTION

发布端,分别向t1 t2插入数据
postgres=# insert into t1(id,name) values (2,'name2');
INSERT 0 1
postgres=# insert into t2(id,name) values (2,'name2');
INSERT 0 1

订阅端,查看t1 t2的数据
postgres=# select * from t1;
 id | name
----+-------
  1 | name1
  2 | name2
(2 rows)

postgres=# select * from t2;
 id | name
----+-------
  1 | name1
  2 | name2
(2 rows)

复制标识 REPLICA IDENTITY

REPLICA IDENTITY,复制标识,共有4种配置模式,分别为,

  1. 默认模式(default):
    非系统表采用的默认模式,如果有主键,则用主键列作为身份标识,否则用完整模式。
  2. 索引模式(index):将某一个符合条件的索引中的列,用作身份标识。
  3. 完整模式(full):将整行记录中的所有列作为复制标识(类似于整个表上每一列共同组成主键)。
  4. 无身份模式(nothing):不记录任何复制标识,这意味着UPDATE|DELETE操作无法复制到订阅者上。

表改复制标识可以通过ALTER TABLE进行修改。
ALTER TABLE T1 REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING};

T1没有主建,直接删除,提示需要设置 REPLICA IDENTITY
postgres=# delete from t1 where id=1;
ERROR:  cannot delete from table "t1" because it does not have a replica identity and publishes deletes
HINT:  To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.

修改为full,删除成功
postgres=# alter table t1 replica identity full;
ALTER TABLE
postgres=# delete from t1 where id=1;
DELETE 1

文章来源:https://blog.csdn.net/usoa/article/details/135598096
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。