wal2json（解碼為JSON） - PolarDB

PolarDB PostgreSQL版（相容Oracle）提供wal2json外掛程式，可以將邏輯記錄檔輸出為JSON格式。

前提條件

支援的PolarDB PostgreSQL版（相容Oracle）的版本如下：

Oracle文法相容 2.0（核心小版本2.0.14.1.0及以上）

說明

您可通過如下語句查看PolarDB PostgreSQL版（相容Oracle）的核心小版本號碼：

SHOW polar_version;

背景資訊

wal2json是邏輯解碼外掛程式，具體功能如下：

可以訪問由INSERT和UPDATE產生的元組。
根據配置的副本身份，可以訪問UPDATE和DELETE舊行版本。
可以使用流協議（邏輯複製插槽）或特殊的SQL API來使用更改。

wal2json外掛程式會在每個事務中產生一個JSON對象。JSON對象中提供了所有新/舊元組，額外選項還可以包括事務時間戳記、限定架構、資料類型、事務ID等屬性。詳情請參見通過SQL擷取JSON對象。

注意事項

由於採用的複製方式為REPLICA_IDENTITY_FULL，因此在更新和刪除時，所顯示的資料為整行資料，而非預設的僅涉及更新和刪除前後變化的列。如需修改為僅涉及更新前後變化的列，需要關閉 polar_create_table_with_full_replica_identity 參數，該參數無法通過控制台進行修改，請聯絡我們處理。
wal2json外掛程式依賴於邏輯編解碼功能，wal_level參數的值需設定為logical。
說明
您可以通過控制台設定wal_level參數，詳細操作請參考設定叢集參數。修改該參數後叢集將會重啟，請在修改參數前做好業務安排，謹慎操作。

通過SQL擷取JSON對象

wal2json外掛程式不需要通過CREATE EXTENSION來建立，而是通過邏輯複製槽來裝載wal2json外掛程式進行使用。

建立含有wal2json外掛程式的邏輯複製槽後，通過如下命令擷取WAL中的JSON對象。

-- 建立帶主鍵和不帶主鍵的表
CREATE TABLE table2_with_pk (a SERIAL, b VARCHAR(30), c TIMESTAMP NOT NULL, PRIMARY KEY(a, c));
CREATE TABLE table2_without_pk (a SERIAL, b NUMERIC(5,2), c TEXT);

-- 建立wal2json類型的邏輯複製槽
SELECT 'init' FROM pg_create_logical_replication_slot('test_slot', 'wal2json');

-- 提交事務，寫入WAL
BEGIN;
INSERT INTO table2_with_pk (b, c) VALUES('Backup and Restore', now());
INSERT INTO table2_with_pk (b, c) VALUES('Tuning', now());
INSERT INTO table2_with_pk (b, c) VALUES('Replication', now());
DELETE FROM table2_with_pk WHERE a < 3;

INSERT INTO table2_without_pk (b, c) VALUES(2.34, 'Tapir');
UPDATE table2_without_pk SET c = 'Anta' WHERE c = 'Tapir';
COMMIT;

-- 擷取WAL中的JSON對象
SELECT data FROM pg_logical_slot_get_changes('test_slot', NULL, NULL, 'pretty-print', '1');

返回結果如下：

{
    "change": [
        {
            "kind": "insert",
            "schema": "public",
            "table": "table2_with_pk",
            "columnnames": ["a", "b", "c"],
            "columntypes": ["integer", "character varying(30)", "timestamp without time zone"],
            "columnvalues": [1, "Backup and Restore", "2018-03-27 12:05:29.914496"]
        }
        ,{
            "kind": "insert",
            "schema": "public",
            "table": "table2_with_pk",
            "columnnames": ["a", "b", "c"],
            "columntypes": ["integer", "character varying(30)", "timestamp without time zone"],
            "columnvalues": [2, "Tuning", "2018-03-27 12:05:29.914496"]
        }
        ,{
            "kind": "insert",
            "schema": "public",
            "table": "table2_with_pk",
            "columnnames": ["a", "b", "c"],
            "columntypes": ["integer", "character varying(30)", "timestamp without time zone"],
            "columnvalues": [3, "Replication", "2018-03-27 12:05:29.914496"]
        }
        ,{
            "kind": "delete",
            "schema": "public",
            "table": "table2_with_pk",
            "oldkeys": {
                "keynames": ["a", "c"],
                "keytypes": ["integer", "timestamp without time zone"],
                "keyvalues": [1, "2018-03-27 12:05:29.914496"]
            }
        }
        ,{
            "kind": "delete",
            "schema": "public",
            "table": "table2_with_pk",
            "oldkeys": {
                "keynames": ["a", "c"],
                "keytypes": ["integer", "timestamp without time zone"],
                "keyvalues": [2, "2018-03-27 12:05:29.914496"]
            }
        }
        ,{
            "kind": "insert",
            "schema": "public",
            "table": "table2_without_pk",
            "columnnames": ["a", "b", "c"],
            "columntypes": ["integer", "numeric(5,2)", "text"],
            "columnvalues": [1, 2.34, "Tapir"]
        }
    ]
}

刪除名為test_slot的複製槽，同時返回字串 'stop'。
```
SELECT 'stop' FROM pg_drop_replication_slot('test_slot');
```

參數說明

wal2json相關參數說明如下：

參數	說明
change	每次DML的WAL條目。例如，Insert、Update、Delete、Truncate的WAL記錄。
changeset	若干個change的集合。
include-xids	用於控制是否添加xid到每一個changeset，預設值為false，取值如下： true：添加xid到每一個changeset。 false（預設）：不添加xid到每一個changeset。
include-timestamp	用於控制是否添加timestamp到每一個changeset，預設值為false，取值如下： true：添加timestamp到每一個changeset。 false（預設）：不添加timestamp到每一個changeset。
include-schemas	用於控制是否添加schema到每一個change，預設值為true，取值如下： true（預設）：添加schema到每一個change。 false：不添加schema到每一個change。
include-types	用於控制是否添加type到每一個change，預設值為true，取值如下： true（預設）：添加type到每一個change。 false：不添加type到每一個change。
include-typmod	將修飾符添加到具有修飾符的類型（例如，varchar（20）而不是varchar），預設值為true，取值如下： true（預設）：將修飾符添加到具有修飾符的類型。 false：不將修飾符添加到具有修飾符的類型。
include-type-oids	用於控制是否添加類型oids，預設值為false，取值如下： true：添加類型oids。 false（預設）：不添加類型oids。
include-not-null	用於控制是否添加`not null`資訊作為columnoptionals，預設值為false，取值如下： true：添加`not null`資訊作為columnoptionals。 false（預設）：不添加`not null`資訊作為columnoptionals。
pretty-print	用於控制是否向JSON結構添加空格和縮排，進行格式化，預設值為false，取值如下： true：向JSON結構添加空格和縮排，進行格式化。 false（預設）：不向JSON結構添加空格和縮排，不進行格式化。
write-in-chunks	用於控制是否是每次change後都寫，而不是每個changeset，預設值為false，取值如下： true：每次change後都寫，而不是每個changeset。 false（預設）：每個changeset後都寫，而不是每次change。
include-lsn	用於控制是否添加nextlsn到每一個changeset，預設值為false，取值如下： true：添加nextlsn到每一個changeset。 false（預設）：不添加nextlsn到每一個changeset。
filter-tables	排除指定表。預設為空白，表示不過濾任何錶。說明通過逗號分隔不同的表，每張表需要指定schema。 `.foo`表示所有schema中的表foo，而`bar.`表示schema中的所有表。特殊字元（空格，單引號，逗號，句號，星號）必須以\轉義。 Schema和表區分大小寫。 Schema public下的表Foo bar應指定為`public.Foo\bar`。
add-tables	指定解析特定表，預設解析所有schema下的所有表。用法和filter-tables相同。
filter-msg-prefixes	排除指定prefix的行，通常用於`pg_logical_slot_peek_changes()`函數中。預設為空白，保證沒有資訊被過濾，通過逗號分隔。
add-msg-prefixes	增加指定prefix的行，通常用於`pg_logical_slot_peek_changes()`函數中。預設為所有prefix，通過逗號分隔，需要在該參數之前使用`filter-msg-prefixes`。
format-version	定義使用哪種輸出格式，預設值為1。取值如下： 1：使用1的輸出格式。 2：使用2的輸出格式。
actions	定義輸出哪種操作。預設為所有（Insert，Update，Delete和Truncate）。如果您使用`format-version 1`，truncate將不會被啟用。

樣本

以include-xids為例說明參數如何使用。

建立表和邏輯複製槽，並插入一行資料。

DROP TABLE IF EXISTS tbl;
CREATE TABLE tbl (id int);
SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot', 'wal2json');
INSERT INTO tbl VALUES (1);

將參數名和參數內容依次填入函數。

SELECT    
count(*) = 1,    
count(distinct ((data::json)->'xid')::text) = 1
FROM pg_logical_slot_get_changes(    
'regression_slot', NULL, NULL,    
'format-version', '1',    
'include-xids', '1');

原理設計

更多資訊和原理設計請參見官方使用文檔。