decoder_raw_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

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

注意事项

PolarDB PostgreSQL版（兼容Oracle）默认开启GUC参数polar_create_table_with_full_replica_identity，该参数设置表级REPLICA IDENTITY为FULL。如果需要修改，请使用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 of Non-selective column
UPDATE aa SET b = 'cc' WHERE a = 1;

-- Update of only selective column
UPDATE aa SET a = 3 WHERE a = 1;

-- Update of both columns
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)