wal2json

wal2json Overview

wal2json is a logical decoding extension. With this extension, you can access the tuples generated by INSERT and UPDATE and parse the content in WAL.

The wal2json extension will generate a JSON object in each transaction. All new/old tuples are provided in the JSON object, and additional options can also include such attributes as transaction timestamp, restricted structure, data type, and transaction ID.

Install wal2json

For details, please refer to gs_install_plugin or gs_install_plugin_local.

Configure Database Parameters

1. Modify the postgresql.conf file in the data directory of the database (both the primary and standby databases need to be modified).

   wal_level = logical
   max_replication_slots = 10
   max_wal_senders = 10 

2. Modify pg_hba.conf (both the primary and standby databases need to be modified).

   host   replication   all     127.0.0.1/32       trust

3. Restart the database.

Use wal2json

1. Open a new terminal and use wal2json to connect to the database.

   pg_recvlogical -d postgres --slot test2_slot --create -P wal2json -h 127.0.0.1 -p 5001 -U test
   pg_recvlogical -d postgres -h 127.0.0.1 -p 5001 -U test --slot test2_slot --start  -f - 

2. Operate with the database.

   create table ff(id int, name int);
   insert into ff values(1,2);
   insert into ff values(2,4);
   select * from ff;

3. Output the operation process on wal2json.

   {"change":[]}
   {"change":[]}
   {"change":[{"kind":"insert","schema":"test","table":"ff","columnnames":["id","name"],"columntypes":["integer","integer"],"columnvalues":[1,10]}]}
   {"change":[{"kind":"insert","schema":"test","table":"ff","columnnames":["id","name"],"columntypes":["integer","integer"],"columnvalues":[2,2]}]}

   
   

Wal2json Parameters

Run the following command in the data directory of the database:

pg_recvlogical --help
 -f, --file=FILE     receive log into this file. - for stdout
 -n, --no-loop      do not loop on connection lost
 -v, --verbose      output verbose messages
 -V, --version      output version information, then exit
 -?, --help       show this help, then exit

Connection options:
 -d, --dbname=DBNAME   database to connect to
 -h, --host=HOSTNAME   database server host or socket directory
 -p, --port=PORT     database server port number
 -U, --username=NAME   connect as specified database user
 -w, --no-password    never prompt for password
 -W, --password     force password prompt (should happen automatically)

Replication options:
 -F  --fsync-interval=INTERVAL
            frequency of syncs to the output file (in seconds, defaults to 10)
 -o, --option=NAME[=VALUE]
            Specify option NAME with optional value VAL, to be passed
            to the output plugin
 -P, --plugin=PLUGIN   use output plugin PLUGIN (defaults to mppdb_decoding)
 -s, --status-interval=INTERVAL
            time between status packets sent to server (in seconds, defaults to 10)
 -S, --slot=SLOT     use existing replication slot SLOT instead of starting a new one
 -I, --startpos=PTR   Where in an existing slot should the streaming start

Action to be performed:
   --create      create a new replication slot (for the slotname see --slot)
   --start       start streaming in a replication slot (for the slotname see --slot)
   --drop       drop the replication slot (for the slotname see --slot)