decoder_raw是一个逻辑复制的输出插件(output plugin),能够根据对表的修改生成可以被远程数据库消费的原始修改SQL:UPDATE
和DELETE
语句通过能够确保元组选择性的REPLICA IDENTITY
等级生成,INSERT
语句通过WAL日志中解析出的元组生成。
前提条件
支持的PolarDB PostgreSQL版的版本如下:
PostgreSQL 14(内核小版本14.10.16.0及以上)
PostgreSQL 11(内核小版本1.1.36及以上)
说明
您可通过如下语句查看PolarDB PostgreSQL版的内核小版本的版本号:
PostgreSQL 14
select version();
PostgreSQL 11
show polar_version;
注意事项
PolarDB PostgreSQL版默认开启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)
相关参考
更多解码格式的逻辑复制输出插件请参考Logical Decoding Plugins。
文档内容是否对您有帮助?