PostgreSQL 11 preview - Logical replication support for TRUNCATE
背景
PostgreSQL 11 逻辑订阅支持insert, update, delete, truncate。
后期可能还会增加对DDL的支持，目前的DDL(包括对已有订阅表的结构修改，新增，删除表等操作)通过如下方法来支持：
《PostgreSQL 逻辑订阅 - DDL 订阅 实现方法》
支持TRUNCATE的PATCH如下
Logical replication support for TRUNCATE
author Peter Eisentraut <peter_e@gmx.net>
Sat, 7 Apr 2018 23:24:53 +0800 (11:24 -0400)
committer Peter Eisentraut <peter_e@gmx.net>
Sat, 7 Apr 2018 23:34:11 +0800 (11:34 -0400)
commit 039eb6e92f20499ac36cc74f8a5cef7430b706f6
tree 2cf52aeafb59917d5c7ed396acb6d86325b4a8b0 tree | snapshot
parent 5dfd1e5a6696b271a2cdee54143fbc209c88c02f commit | diff
Logical replication support for TRUNCATE
Update the built-in logical replication system to make use of the
previously added logical decoding for TRUNCATE support. Add the
required truncate callback to pgoutput and a new logical replication
protocol message.
Publications get a new attribute to determine whether to replicate
truncate actions. When updating a publication via pg_dump from an older
version, this is not set, thus preserving the previous behavior.
Author: Simon Riggs <simon@2ndquadrant.com>
Author: Marco Nenciarini <marco.nenciarini@2ndquadrant.it>
Author: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Reviewed-by: Petr Jelinek <petr.jelinek@2ndquadrant.com>
Reviewed-by: Andres Freund <andres@anarazel.de>
Reviewed-by: Alvaro Herrera <alvherre@alvh.no-ip.org>
WITH ( publication_parameter [= value] [, ... ] )
This clause specifies optional parameters for a publication. The following parameters are supported:
publish (string)
This parameter determines which DML operations will be published by the new publication to the subscribers. The value is comma-separated list of operations. The allowed operations are insert, update, delete, and truncate. The default is to publish all actions, and so the default value for this option is 'insert, update, delete, truncate'.
CREATE PUBLICATION insert_only FOR TABLE mydata
WITH (publish = 'insert');
+ List of publications
+ Name | Owner | All tables | Inserts | Updates | Deletes | Truncates
+--------------------+--------------------------+------------+---------+---------+---------+-----------
+ testpib_ins_trunct | regress_publication_user | f | t | f | f | f
+ testpub_default | regress_publication_user | f | f | t | f | f
(2 rows)
+ List of publications
+ Name | Owner | All tables | Inserts | Updates | Deletes | Truncates
+--------------------+--------------------------+------------+---------+---------+---------+-----------
+ testpib_ins_trunct | regress_publication_user | f | t | f | f | f
+ testpub_default | regress_publication_user | f | t | t | t | f
(2 rows)
+ Publication testpub_foralltables
+ Owner | All tables | Inserts | Updates | Deletes | Truncates
+--------------------------+------------+---------+---------+---------+-----------
+ regress_publication_user | t | t | t | f | f
(1 row)
+ Publication testpub3
+ Owner | All tables | Inserts | Updates | Deletes | Truncates
+--------------------------+------------+---------+---------+---------+-----------
+ regress_publication_user | f | t | t | t | t
+ Publication testpub4
+ Owner | All tables | Inserts | Updates | Deletes | Truncates
+--------------------------+------------+---------+---------+---------+-----------
+ regress_publication_user | f | t | t | t | t
参考
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=039eb6e92f20499ac36cc74f8a5cef7430b706f6
https://www.postgresql.org/docs/devel/static/sql-createpublication.html
https://www.postgresql.org/docs/devel/static/sql-alterpublication.html