decoder_raw - PolarDB - Alibaba Cloud Documentation Center

decoder_raw is an output plug-in for logical replication. It can generate raw queries based on the logical changes it finds. Such queries can be consumed by remote databases. UPDATE and DELETE statements are generated by REPLICA IDENTITY that ensures tuple selectivity. INSERT statements are generated from tuples parsed from WAL logs.

Prerequisites

The feature is supported on PolarDB for PostgreSQL clusters that run the following engine:

PostgreSQL 14 (revision version 14.10.16.0 or later)
PostgreSQL 11 (revision version 1.1.36 or later)

Note

You can execute one of the following statements to query the revision version of your PolarDB for PostgreSQL cluster:

PostgreSQL 14
```
SELECT version();
```
PostgreSQL 11
```
SHOW polar_version;
```

Usage notes

PolarDB for PostgreSQL enables the polar_create_table_with_full_replica_identity Grand Unified Configuration (GUC) parameter by default. This parameter sets the table-level REPLICA IDENTITY to FULL. You can execute the ALTER TABLE statement to modify the REPLICA IDENTITY property of the 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 output plug-ins for logical replication in other decoding formats, see Logical Decoding Plugins.