PolarDB:wal2json (decoding to JSON)

1. Setelah membuat logical replication slot dengan plugin wal2json, jalankan perintah berikut untuk mengambil objek JSON dari WAL.

   -- Create tables with and without a primary key
   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);
   
   -- Create a logical replication slot of type wal2json
   SELECT 'init' FROM pg_create_logical_replication_slot('test_slot', 'wal2json');
   
   -- Commit the transaction to write to 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;
   
   -- Retrieve JSON objects from WAL
   SELECT data FROM pg_logical_slot_get_changes('test_slot', NULL, NULL, 'pretty-print', '1');

   Output-nya adalah sebagai berikut:

   {
       "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"]
           }
       ]
   }

2. Jalankan perintah berikut untuk menghapus slot replikasi test_slot. Perintah ini mengembalikan string 'stop'.

   SELECT 'stop' FROM pg_drop_replication_slot('test_slot');

Tabel berikut menjelaskan parameter-parameter wal2json.