新建一個(gè)庫(kù)，做為訂閱端
[pg@localhost data]$ pg_ctl init -D /data/db3

發(fā)布與訂閱端都建相關(guān)同步結(jié)構(gòu)的表t1,t2
postgres=# create table t1(id int,name varchar(20));
CREATE TABLE
postgres=# create table t2(id int,name varchar(20));
CREATE TABLE

修改發(fā)布端wal_level = logical，修改后要restart訂閱端，reload不生效。
postgres=# show wal_level;
 wal_level
-----------
 logical
(1 row)


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

注：訂閱端有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

在發(fā)布端分別向 t1 t2表插入數(shù)據(jù)
postgres=# insert into t1(id,name) values (1,'name1');
INSERT 0 1
postgres=# insert into t2(id,name) values (1,'name1');
INSERT 0 1

查看訂閱端，只有已新建訂閱的表數(shù)據(jù)同步
postgres=# select * from t1;
 id | name
----+-------
  1 | name1
(1 row)

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

發(fā)布端，將t2加入到發(fā)布
postgres=# alter publication pub_test add table t2;
ALTER PUBLICATION

查看發(fā)布表
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

發(fā)布端，分別向t1 t2插入數(shù)據(jù)
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的數(shù)據(jù)
postgres=# select * from t1;
 id | name
----+-------
  1 | name1
  2 | name2
(2 rows)

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

T1沒(méi)有主建，直接刪除，提示需要設(shè)置 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