decoder_raw（解码为SQL）

前提条件

注意事项

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 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。