wal2json Extention for MogDB&openGauss

Logical Decoding (wal2json)

With the wal2json plug-in of MogDB and openGauss, you can export logical log files in the JSON format.

Prerequisites

You have set wal_level to logical.

Background Information

wal2json is a logical decoding plug-in. It can be used for accessing tuples generated by INSERT and UPDATE and parsing WAL logs.

wal2json generates a JSON object in each transaction. JSON provides all new and old tuples as well as other optional properties, such as transaction timestamp, qualified schema, data type, and transaction ID.

Obtaining JSON Objects Using SQL

1. Log in to the MogDB database.

2. Run the following commands to create tables and initialize the plug-in.

   --Open a new session and run the following:
   pg_recvlogical -d mogdb --slot test_slot --create-slot -P wal2json
   pg_recvlogical -d mogdb --slot test_slot --start -o pretty-print=1 -f -
   --Perform the following basic DML operations:
   CREATE TABLE test_table (
       id char(10) NOT NULL,
       code        char(10),
       PRIMARY KEY (id)
   );
   mogdb=# INSERT INTO test_table (id, code) VALUES('id1', 'code1');
   INSERT 0 1
   mogdb=# update test_table set code='code2' where id='id1';
   UPDATE 1
   mogdb=# delete from test_table where id='id1';
   DELETE 1

   DML output:

   INSERT

   {
     "change": [
       {
         "kind": "insert",
         "schema": "mdmv2",
         "table": "test_table",
         "columnnames": ["id", "code"],
         "columntypes": ["character(10)", "character(10)"],
         "columnvalues": ["id1       ", "code1     "]
       }
     ]
   }

   UPDATE

   {
     "change": [
       {
         "kind": "update",
         "schema": "mdmv2",
         "table": "test_table",
         "columnnames": ["id", "code"],
         "columntypes": ["character(10)", "character(10)"],
         "columnvalues": ["id1       ", "code2     "],
         "oldkeys": {
           "keynames": ["id"],
           "keytypes": ["character(10)"],
           "keyvalues": ["id1       "]
         }
       }
     ]
   }

   DELETE

   {
     "change": [
       {
         "kind": "delete",
         "schema": "mdmv2",
         "table": "test_table",
         "oldkeys": {
           "keynames": ["id"],
           "keytypes": ["character(10)"],
           "keyvalues": ["id1       "]
         }
       }
     ]
   }

   REPLICA IDENTITY can determine the details of the exported logical logs when the UPDATE and DELETE operations are performed on a table.

   • DEFAULT: A logical log includes the original values of the primary key columns when the value is updated or deleted.
   • NOTHING: A logical log does not include any update or delete information.
   • FULL: A logical log include original information of the whole row to which the value belongs when the value in a table is updated or deleted.
   • USING INDEX: includes only original values of all columns in specified indexes.