decoder_raw(解码为SQL)

decoder_raw是一个逻辑复制的输出插件(output plugin),能够根据对表的修改生成可以被远程数据库消费的原始修改SQL:UPDATEDELETE语句通过能够确保元组选择性的REPLICA IDENTITY等级生成,INSERT语句通过WAL日志中解析出的元组生成。

前提条件

支持的PolarDB PostgreSQL版(兼容Oracle)的版本如下:

Oracle语法兼容 2.0(内核小版本2.0.14.16.0及以上)

说明

您可通过如下语句查看PolarDB PostgreSQL版(兼容Oracle)的内核小版本号:

SHOW polar_version;

注意事项

PolarDB PostgreSQL版(兼容Oracle)默认开启GUC参数polar_create_table_with_full_replica_identity,该参数设置表级REPLICA IDENTITYFULL。如果需要修改,请使用ALTER TABLE语句修改表的REPLICA IDENTITY属性。更多选项说明请参考插件的README

使用方法

创建逻辑复制槽

使用decoder_raw作为输出插件创建复制槽。

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

创建表并执行增删改

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

-- 更新非选择性列
UPDATE aa SET b = 'cc' WHERE a = 1;

-- 指定更新特定列
UPDATE aa SET a = 3 WHERE a = 1;

-- 同时更新指定表中的两列
UPDATE aa SET a = 4, b = 'dd' WHERE a = 2;

-- 删除指定列
DELETE FROM aa WHERE a = 4;

查看对表的修改

通过include_transaction参数可以控制输出中是否包含事务信息。

  • 不输出事务信息。

    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)

获取对表的修改

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)

相关参考

更多解码格式的逻辑复制输出插件请参考Logical Decoding Plugins