本文为 【TDSQL-PG笔记】主从复制原理的系列文章,实验基础环境参考栏目第一篇blog
如果要将不同库的某关键表备份到指定库里,可以用逻辑复制完成相关功能。
PG逻辑复制基于发布和订阅模型,一个发布可以有多个订阅者。
发布publication可以创建在任意单机上,也可以在物理复制的主库上,发布端要求数据库参数WAL_LEVEL为logical。一个发布可以被多个数据库订阅。一个发布只能包含一个数据库中的表,但是这些表可以分布在不同的schema下。
订阅处于逻辑复制的下游端,一个订阅只能对应一个发布。订阅端的数据库名可以跟发布端不同,但是订阅发布的表名和schema必须相同。
当订阅创建成功后,就会自动在发布端创建多个逻辑复制槽,其中一个用于后续的增量数据同步,永久保留,另外还会创建一个或者多个的临时复制槽,用于复制表的全量快照数据。
是当发布端是物理复制架构时,如果发生主从切换,发布是不会跟随数据库failover的。但是可以通过手工拷贝文件或者利用插件实现。
(1)数据库的表结构不会被复制,所以需要在订阅端先创建对应的schema和表
(2)TRUNCATE和DDL不会复制,如果要进行DDL变更,需要先在订阅端执行DDL,然后在发布端执行。
(3)Sequence不会不复制,当发布端使用了sequence数据,其写入了表的值会同步到订阅端,但是目标库的sequence并不会发生变化。
(4)不支持大对象。
(5)复制只能是基表到基表,不支持视图、物化视图、外部表等。如果表是分区表,需要基于分区进行复制。
新建一个库,做为订阅端
[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,复制标识,共有4种配置模式,分别为,
表改复制标识可以通过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