decoder_raw - PolarDB - Alibaba Cloud Documentation Center

decoder_raw is a logical replication output plug-in that can convert changes made to tables into raw SQL statements. The statements can be applied to remote databases to replicate the changes. For UPDATE and DELETE statements, the plug-in uses the REPLICA IDENTITY setting of the table to ensure that the corresponding tuples (rows) are accurately identified. The plug-in produces INSERT statements by decoding the relevant write-ahead logging (WAL) records.

Prerequisites

This extension is supported on the PolarDB for PostgreSQL (Compatible with Oracle) clusters that run the following engines:

PolarDB for PostgreSQL (Compatible with Oracle) 2.0 (revision version 2.0.14.16.0 or later)

Note

You can execute the following statements to view the revision version of a PolarDB for PostgreSQL (Compatible with Oracle) cluster:

SHOW polar_version;

Usage notes

By default, the Grand Unified Configuration (GUC) parameter polar_create_table_with_full_replica_identity is set to ON in PolarDB for PostgreSQL (Compatible with Oracle) clusters. This parameter specifies FULL REPLICA IDENTITY for tables. You can use the ALTER TABLE statement to modify the REPLICA IDENTITY setting of a table. For more information, see README.

Usage

Create a logical replication slot

Create a replication slot by using the decoder_raw output plug-in.

SELECT pg_create_logical_replication_slot('custom_slot', 'decoder_raw');

Create a table and perform insert, update, and delete operations on the table

CREATE TABLE aa (a INT PRIMARY KEY, b TEXT NOT NULL);
INSERT INTO aa VALUES (1, 'aa'), (2, 'bb');

-- Update a non-selective column
UPDATE aa SET b = 'cc' WHERE a = 1;

-- Update a specific column
UPDATE aa SET a = 3 WHERE a = 1;

-- Update two columns of a specific table
UPDATE aa SET a = 4, b = 'dd' WHERE a = 2;

-- Delete a column
DELETE FROM aa WHERE a = 4;

View changes to the table

Configure the include_transaction parameter to specify whether to include transactional information in the output.

SELECT data FROM pg_logical_slot_peek_changes('custom_slot', NULL, NULL, 'include_transaction', 'off');
                       data
---------------------------------------------------
 INSERT INTO public.aa (a, b) VALUES (1, 'aa');
 INSERT INTO public.aa (a, b) VALUES (2, 'bb');
 UPDATE public.aa SET a = 1, b = 'cc' WHERE a = 1;
 UPDATE public.aa SET a = 3, b = 'cc' WHERE a = 1;
 UPDATE public.aa SET a = 4, b = 'dd' WHERE a = 2;
 DELETE FROM public.aa WHERE a = 4;
(6 rows)

SELECT data FROM pg_logical_slot_peek_changes('custom_slot', NULL, NULL, 'include_transaction', 'on');
                              data
----------------------------------------------------------------
 BEGIN;
 COMMIT;
 BEGIN;
 INSERT INTO public.aa (a, b) VALUES (1, 'aa');
 INSERT INTO public.aa (a, b) VALUES (2, 'bb');
 COMMIT;
 BEGIN;
 UPDATE public.aa SET a = 1, b = 'cc' WHERE a = 1 AND b = 'aa';
 COMMIT;
 BEGIN;
 UPDATE public.aa SET a = 3, b = 'cc' WHERE a = 1 AND b = 'cc';
 COMMIT;
 BEGIN;
 UPDATE public.aa SET a = 4, b = 'dd' WHERE a = 2 AND b = 'bb';
 COMMIT;
 BEGIN;
 DELETE FROM public.aa WHERE a = 4 AND b = 'dd';
 COMMIT;
(18 rows)

Obtain changes to the table

SELECT data FROM pg_logical_slot_get_changes('custom_slot', NULL, NULL);
                              data
----------------------------------------------------------------
 INSERT INTO public.aa (a, b) VALUES (1, 'aa');
 INSERT INTO public.aa (a, b) VALUES (2, 'bb');
 UPDATE public.aa SET a = 1, b = 'cc' WHERE a = 1 AND b = 'aa';
 UPDATE public.aa SET a = 3, b = 'cc' WHERE a = 1 AND b = 'cc';
 UPDATE public.aa SET a = 4, b = 'dd' WHERE a = 2 AND b = 'bb';
 DELETE FROM public.aa WHERE a = 4 AND b = 'dd';
(6 rows)

References

For more information about logical replication output plug-ins, see Logical Decoding Plugins.