5 Enhancement of Logical Replication

PDF page 35

The following features have been added to PostgreSQL 11 logical replication.

3.3.1 TRUNCATE statement support

The TRUNCATE statement executed on the PUBLICATION node in the Logical Replication environment now propagates to the SUBSCRIPTION node. Along with this, the specification to propagate TRUNCATE statement has been added to WITH clause of CREATE PUBLICATION and ALTER PUBLICATION statements.

postgres=> CREATE PUBLICATION pub1 FOR TABLE data1
WITH (publish='INSERT, DELETE, UPDATE, TRUNCATE') ;
CREATE PUBLICATION

DDLs other than TRUNCATE statement is not propagated.

3.3.2 pg_replication_slot_advance function

When a conflict occurs in the logical replication environment in PostgreSQL 10, the start LSN of logical replication needs to be specified by executing the pg_replication_origin_advance function on the standby instance to resolve a conflict. In PostgreSQL 11, similar processing can now be executed in the PUBLICATION instance. Execute pg_replication_slot_advance function. This function specifies the replication slot name and LSN.

postgres=# SELECT
pg_replication_slot_advance('sub1', pg_current_wal_lsn()) ;
pg_replication_slot_advance
-----------------------------
 (sub1,0/5B63E18)
(1 row)