Use decoder_raw to generate raw queries for logical replication - PolarDB

decoder_raw is a logical decoding output plugin for PolarDB for PostgreSQL. It decodes WAL records into raw SQL statements — INSERT, UPDATE, and DELETE — that a remote database can consume directly.

UPDATE and DELETE statements are generated using REPLICA IDENTITY to uniquely identify each row.
INSERT statements are generated from tuples parsed from WAL records.

Unlike wal2json (which outputs JSON) or pgoutput (the default PostgreSQL replication format), decoder_raw is useful when your downstream system expects plain SQL rather than a binary or JSON format.

Important

A replication slot that is not consumed causes WAL records to accumulate on disk. Monitor and consume your replication slot regularly to avoid disk exhaustion.

Prerequisites

Before you begin, ensure that you have:

A PolarDB for PostgreSQL cluster running one of the following engine versions:
- PostgreSQL 14 (revision version 14.10.16.0 or later)
- PostgreSQL 11 (revision version 1.1.36 or later)

To check your cluster's revision version, run one of the following:

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, which means UPDATE and DELETE statements include all column values in the WHERE clause.

To change the REPLICA IDENTITY setting for a specific table, use the ALTER TABLE statement. For details, see the decoder_raw README.

Use decoder_raw

Step 1: Create a logical replication slot

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

Step 2: Create a table and write data

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

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

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

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

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

Step 3: Read changes from the slot

Two functions are available:

Function	Behavior
`pg_logical_slot_peek_changes`	Returns changes without advancing the slot's position. Call again to see the same changes.
`pg_logical_slot_get_changes`	Returns changes and advances the slot's position. Each change is returned only once.

View changes without consuming them

Use pg_logical_slot_peek_changes to inspect changes while keeping them in the slot.

Set the include_transaction parameter to control whether transaction boundaries appear in the output.

With `include_transaction` set to `'off'`:

SELECT data FROM pg_logical_slot_peek_changes('custom_slot', NULL, NULL, 'include_transaction', 'off');

Output:

                       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)

With `include_transaction` set to `'on'` (output includes BEGIN/COMMIT wrappers):

SELECT data FROM pg_logical_slot_peek_changes('custom_slot', NULL, NULL, 'include_transaction', 'on');

Output:

                              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)

Consume changes from the slot

Use pg_logical_slot_get_changes to retrieve changes and advance the slot. Consumed changes are not returned on subsequent calls.

SELECT data FROM pg_logical_slot_get_changes('custom_slot', NULL, NULL);

Output:

                              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 other logical decoding output plugins, see Logical Decoding Plugins.