pg逻辑复制创建发布语法使用说明

 

语法如下：

CREATE PUBLICATION name
    [ FOR TABLE [ ONLY ] table_name [ * ] [, ...]
      | FOR ALL TABLES ]
    [ WITH ( publication_parameter [= value] [, ... ] ) ]

 

1.*说明
适用于父子表关系的同步
create publication pub_test for table tb_01*;
tb_01为父表,tb_02为子表

那么父表和子表也会发布了.

--父表
CREATE TABLE tb_01 (
name varchar(32),
population int,
altitude int
);


--子表
CREATE TABLE tb_02 (
state varchar(32)
) inherits (tb_01);

 

2.for all tables

create publication pub_test for all tables;

可以同步一个库下的所有的表，主库新增表后，需要在从库手工创建，同时需要刷新从库的订阅，语句如下:

db_rep=# alter subscription sub_test refresh publication;

 

说明:
1.发布和订阅都是位于特定的库下的,订阅不能垮库订阅
2.重新部署发版和订阅,需要把从库订阅端的数据清空,否则会导致数据重复

 3.发布端删除数据

db_rep=# delete from tb_ee;
ERROR:  cannot delete from table "tb_ee" because it does not have a replica identity and publishes deletes
HINT:  To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.
解决办法:
ALTER TABLE tb_ee REPLICA IDENTITY FULL;