All Products
Search

This Product

    PolarDB:decoder_raw

    Document Center

    PolarDB:decoder_raw

    Last Updated:Mar 28, 2026

    decoder_raw is a logical replication output plugin that converts table changes into raw SQL statements. These statements can be applied directly to a remote database to replicate changes. For UPDATE and DELETE operations, the plugin uses the table's REPLICA IDENTITY setting to identify affected rows. INSERT statements are produced by decoding write-ahead logging (WAL) records.

    Prerequisites

    Before you begin, ensure that:

    • Your cluster runs PolarDB for PostgreSQL (Compatible with Oracle) 2.0, revision version 2.0.14.16.0 or later

    To check the revision version, run:

    SHOW polar_version;

    Usage notes

    PolarDB for PostgreSQL (Compatible with Oracle) clusters set the Grand Unified Configuration (GUC) parameter polar_create_table_with_full_replica_identity to ON by default. This means all tables use FULL REPLICA IDENTITY, so every column value is recorded for UPDATE and DELETE operations.

    How REPLICA IDENTITY affects decoded output: The REPLICA IDENTITY setting determines what appears in the WHERE clause of decoded UPDATE and DELETE statements.

    REPLICA IDENTITYWHERE clause behaviorExample
    FULL (default in PolarDB)Includes all column values — rows are uniquely identifiable even without a primary keyWHERE a = 1 AND b = 'aa'
    DEFAULTUses only primary key columnsWHERE a = 1

    To change the REPLICA IDENTITY for a specific table, use ALTER TABLE. For details on all available modes, see the decoder_raw README.

    Warning

    Replication slots persist across crashes and accumulate WAL until consumed. An inactive slot prevents VACUUM from reclaiming storage and can cause the database to shut down to prevent transaction ID wraparound. Drop any slot you no longer need.

    Use the decoder_raw plugin

    The following example walks through creating a replication slot, making table changes, and retrieving the decoded SQL output.

    Create a replication slot

    Create a logical replication slot using the decoder_raw output plugin:

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

    To verify the slot was created successfully:

    SELECT slot_name, plugin, slot_type, database, active
FROM pg_replication_slots;

    Create a table and make changes

    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 the primary key column
UPDATE aa SET a = 3 WHERE a = 1;

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

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

    Peek at changes

    pg_logical_slot_peek_changes returns decoded changes without consuming them — repeated calls return the same results.

    Use the include_transaction parameter to control whether BEGIN/COMMIT markers appear in the output.

    Without transaction markers (include_transaction = 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 transaction markers (include_transaction = on):

    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)
    When include_transaction is on, the WHERE clause includes all column values (WHERE a = 1 AND b = 'aa'). This reflects the FULL REPLICA IDENTITY setting, which is the default in PolarDB clusters.

    Consume changes

    pg_logical_slot_get_changes retrieves and removes changes from the slot. Each change is returned only once.

    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)

    Clean up

    Drop the replication slot when you no longer need it:

    SELECT pg_drop_replication_slot('custom_slot');
    Warning

    If you do not drop unused slots, WAL accumulates indefinitely, VACUUM cannot reclaim storage, and the database may shut down to prevent transaction ID wraparound.

    References