逻辑订阅功能测试

单表功能测试

注意点

1. 发布节点发布的表和订阅节点订阅的表必须都要有主键，否则无法同步update、delete操作。如果该表仅仅是做insert操作，那么可以不建主键。
2. 逻辑复制订阅节点订阅的表不存在一致性检测，订阅节点订阅的表只会同步发布节点的操作，可以在订阅节点修改订阅表的内容，所以，如果要保证一致性可以设置用户权限来保证订阅节点不会修改订阅的表的内容。
3. 表结构变更测试

• 添加列

  #发布节点
  postgres=# alter table test add column tid int;
  ALTER TABLE
  postgres=# select * from test;
  id | name | cid | tid 
  ----+------+-----+-----
  1 | a | 1 |
  #订阅节点
  postgres=# select * from test;
  id | name | cid 
  ----+------+-----
  1 | a | 1
  #在订阅节点手动添加tid列
  postgres=# alter table test add column tid int;
  ALTER TABLE
  postgres=# select * from test;
  id | name | cid | tid 
  ----+------+-----+-----
  1 | a | 1 |
  #DML测试
  #1. insert
  #发布节点
  postgres=# insert into test values(2,'b',2,2);
  INSERT 0 1
  #订阅节点
  postgres=# select * from test;
  id | name | cid | tid 
  ----+------+-----+-----
  1 | a | 1 |    
  2 | b | 2 | 2
  #2. delete
  #发布节点
  postgres=# delete from test where id=2;
  DELETE 1
  #订阅节点
  postgres=# select * from test;
  id | name | cid | tid 
  ----+------+-----+-----
  1 | a | 1 |
  #3.update
  #发布节点
  postgres=# update test set tid=1 where id=1;
  UPDATE 1
  #订阅节点
  postgres=# select * from test;
  id | name | cid | tid 
  ----+------+-----+-----
  1 | a | 1 | 1

• 删除列

#发布节点
postgres=# select * from test01;
 id | name | tid
----+------+-----
  1 | a | 1
postgres=# alter table test01 drop tid;
ALTER TABLE
postgres=# select * from test01;
 id | name
----+------
  1 | a
#订阅节点
postgres=# select * from test01;
 id | name | tid
----+------+-----
  1 | a | 1
(1 row)
postgres=# alter table test01 drop tid;
ALTER TABLE
postgres=# select * from test01;
 id | name 
----+------
  1 | a
#1. insert
#发布节点
postgres=# insert into test01 values(2,'b');
INSERT 0 1
#订阅节点
postgres=# select * from test01;
 id | name
----+------
  1 | a
  2 | b
(2 rows)
#2. delete
#发布节点
postgres=# delete from test01 where id=1;
DELETE 1
#订阅节点
postgres=# select * from test01;
 id | name
----+------
  2 | b
(1 row)
#3. update
#发布节点
postgres=# update test01 set name='c' where name='b';
UPDATE 1
postgres=# select * from test01;
 id | name
----+------
  2 | c
(1 row)
#订阅节点
postgres=# select * from test01;
 id | name
----+------
  2 | c
(1 row)

• 改变列的数据类型

#1. insert
#发布节点
postgres=# alter table test01 alter id type varchar;
ALTER TABLE
postgres=# select * from test01;
 id | name
----+------
 2 | c
(1 row)
postgres=# \d test01
                      Table "public.test01"
 Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 id | character varying | | not null |
 name | character varying(10) | | |
Indexes:
    "test01_pkey" PRIMARY KEY, btree (id)
Publications:
    "pub2"
postgres=# insert into test01 values('3','a');
INSERT 0 1
#订阅节点
postgres=# \d test01;
                      Table "public.test01"
 Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 id | integer | | not null |
 name | character varying(10) | | |
Indexes:
    "test01_pkey" PRIMARY KEY, btree (id)
postgres=# select * from test01;
 id | name
----+------
  2 | c
  3 | a
(2 rows)
#2. update
#发布节点
postgres=# update test01 set id='4' where id='2';
UPDATE 1
#订阅节点
postgres=# select * from test01;
 id | name
----+------
  3 | a
  4 | c
(2 rows)
#3.delete
#发布节点
postgres=# delete from test01 where id='3';
DELETE 1
#订阅节点
postgres=# select * from test01;
 id | name
----+------
  4 | c
(1 rows)

• 订阅某个库

#发布节点
postgres=# \c testdatabase
You are now connected to database "testdatabase" as user "postgres".
testdatabase=# \d
        List of relations
 Schema | Name | Type | Owner
--------+------+-------+----------
 public | t1 | table | postgres
 public | t2 | table | postgres
(2 rows)
testdatabase=# create publication pub5 for all tables;
CREATE PUBLICATION
#订阅节点
testdatabase=# create subscription sub5 connection 'host=192.168.0.109 port=5432 dbname=testdatabase user=postgres' publication pub5;
ERROR: relation "public.t1" does not exist
testdatabase=# create table t1(id int,name varchar(20));
CREATE TABLE
testdatabase=# create subscription sub5 connection 'host=192.168.0.109 port=5432 dbname=testdatabase user=postgres' publication pub5;
ERROR: relation "public.t2" does not exist
testdatabase=# create table t2(id int,name varchar(20));
CREATE TABLE
testdatabase=# create subscription sub5 connection 'host=192.168.0.109 port=5432 dbname=testdatabase user=postgres' publication pub5;
NOTICE: created replication slot "sub5" on publisher
CREATE SUBSCRIPTION
testdatabase=# \d
        List of relations
 Schema | Name | Type | Owner
--------+------+-------+----------
 public | t1 | table | postgres
 public | t2 | table | postgres
(2 rows)
#发布节点
testdatabase=# create table t3(id int,name varchar(20));
CREATE TABLE
#订阅节点
testdatabase=# \d
        List of relations
 Schema | Name | Type | Owner
--------+------+-------+----------
 public | t1 | table | postgres
 public | t2 | table | postgres
(2 rows)
#发布节点
testdatabase=# create table t4(id int,name varchar(10));
CREATE TABLE
testdatabase=# \d
        List of relations
 Schema | Name | Type | Owner
--------+------+-------+----------
 public | t1 | table | postgres
 public | t2 | table | postgres
 public | t4 | table | postgres
(3 rows)
#订阅节点
testdatabase=# \d
        List of relations
 Schema | Name | Type | Owner
--------+------+-------+----------
 public | t1 | table | postgres
 public | t2 | table | postgres
(3 rows)

总结

1. 可以通过手动对订阅的表DDL来同步发布表的DDL操作。
2. 对发布表的列的添加或删除一定要同步到订阅节点，否则，单纯在发布节点表的列增加删除操作会导致订阅节点数据无法同步（即使是增加后删除，也要同步这些动作）。
3. 可以对订阅的表增加列，这样并不会影响数据的同步，可以删除订阅表的列，但前提是删除的列不能包括发布表中的列，否则数据无法同步。
4. 当改变列的类型时（不管是发布表还是订阅表），不会影响数据的同步，但是发布表和订阅表列的类型最好还是保持一致。
5. 目前逻辑复制只能到表级别，但是可以同步一个数据库中全部的表（在创建发布时指定all tables），同样需要提前在订阅节点建好这些表，如果后面想增加同步的表，那么需要把这个表添加到发布中才能实现同步该新表。