decoder_raw是一个逻辑复制的输出插件(output plugin),能够根据对表的修改生成可以被远程数据库消费的原始修改SQL:UPDATE
和DELETE
语句通过能够确保元组选择性的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 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 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。
文档内容是否对您有帮助?