文档

decoder_raw

更新时间:

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