【PostageSQL】【数据订阅】常用操作

1  数据订阅的使用场景

　　我们的项目大多是多个微服务构建起来的，比如库存、订单、支付、商品，各个中心数据库相对独立，服务和服务之间又相互依赖。当需要支付和订单进行关联查询的时候，我们是通过数据中心来做的，数据中心订阅了支付、订单等中心的表，这样来实现我们的关联查询，比如一些报表查询、数据收集等。

2  数据订阅相关概念

　　数据订阅，那就有从哪订阅到哪，就引申出发布端和订阅端。

　　发布端：可以理解为数据源头，负责发布哪些表；

　　订阅端：订阅哪个发布端的表数据；

3  数据订阅注意事项

　　发布端和订阅端的表数据结构要一模一样，包括索引都要保持一致。

　　一般的发布端执行过DDL语句，订阅端也要执行，否则订阅会断开。

4  常用操作

4.1  发布端 查询当前数据库有哪些发布

SELECT * FROM pg_publication;

4.2  发布端 查询每个复制槽的状态、wal日志大小

SELECT * FROM pg_replication_slots;

SELECT
　　pg_replication_slots.*,
　　pg_current_wal_lsn ( ),
　　pg_size_pretty ( pg_wal_lsn_diff ( pg_current_wal_insert_lsn ( ), restart_lsn ) ) AS delay_size
FROM
　　pg_replication_slots
WHERE
　　active = FALSE;

4.3  发布端 创建发布、发布DDL相关

postgres=# \h create publication
命令：       CREATE PUBLICATION
描述：       建立新的出版
语法：
CREATE PUBLICATION 名称
    [ FOR TABLE [ ONLY ] 表名 [ * ] [, ...]
      | FOR ALL TABLES ]
    [ WITH ( publication_parameter [= 值] [, ... ] ) ]
比如：CREATE publication 名字 FOR TABLE 表名1,表名2;

postgres-# \h alter publication
命令： ALTER PUBLICATION
描述： 更改出版的定义
语法：
ALTER PUBLICATION 名称 ADD TABLE [ ONLY ] 表名 [ * ] [, ...]
ALTER PUBLICATION 名称 SET TABLE [ ONLY ] 表名 [ * ] [, ...]
ALTER PUBLICATION 名称 DROP TABLE [ ONLY ] 表名 [ * ] [, ...]
ALTER PUBLICATION 名称 SET ( 出版参数 [= 值] [, ... ] )
ALTER PUBLICATION 名称 OWNER TO { 新的属主 | CURRENT_USER | SESSION_USER }
ALTER PUBLICATION 名称 RENAME TO 新的名称

4.4  订阅端 创建订阅、订阅DDL

postgres-# \h create subscription
命令：       CREATE SUBSCRIPTION
描述：       建立新的订阅
语法：
CREATE SUBSCRIPTION 订阅_名称
    CONNECTION '连接信息'
    PUBLICATION publication_name(出版名) [, ...]
    [ WITH ( 订阅参数 [= 值] [, ... ] ) ]

postgres-# \h alter subscription
命令：       ALTER SUBSCRIPTION
描述：       更改订阅的定义
语法：
ALTER SUBSCRIPTION 名称 CONNECTION '连接信息'
ALTER SUBSCRIPTION 名称 SET PUBLICATION publication_name(出版名) [, ...] [ WITH ( 设置出版选项 [= 值] [, ... ] ) ]
ALTER SUBSCRIPTION 名称 REFRESH PUBLICATION [ WITH ( refresh选项 [= 值] [, ... ] ) ]
ALTER SUBSCRIPTION 名称 ENABLE
ALTER SUBSCRIPTION 名称 DISABLE
ALTER SUBSCRIPTION 名称 SET ( 订阅参数 [= 值] [, ... ] )
ALTER SUBSCRIPTION 名称 OWNER TO { 新的属主 | CURRENT_USER | SESSION_USER }
ALTER SUBSCRIPTION 名称 RENAME TO 新的名称

4.5  删除某个订阅

ALTER subscription sub名称;  --订阅端执行
ALTER subscription sub名称 SET ( slot_name = NONE ); --订阅端执行
DROP subscription sub名称;  --订阅端执行

SELECT pg_terminate_backend ( slot进程 ID );  --发布端执行
SELECT pg_drop_replication_slot ( 'bop_sub sub名称' );  --发布端执行