PostgreSQL11preview-LogicalreplicationsupportforTRUNCATE
標籤
PostgreSQL , 邏輯訂閱 , 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://www.postgresql.org/docs/devel/static/sql-createpublication.html
https://www.postgresql.org/docs/devel/static/sql-alterpublication.html