Nowadays JSON is used pretty much everywhere. It’s not only web developers who like JSON. It is also used for configuration, data transfer, and a lot more. Luckily PostgreSQL is pretty good at JSON. Recently I have discovered a module called wal2json, which even allows the transformation of xlog to JSON. The module can be found here: https://github.com/eulerto/wal2json

The module can be compiled just like any other contrib module. Once it has been installed, postgresql.conf can be adapted to allow replication slots:

wal_level = logical
max_replication_slots = 10

After a database restart a replication slot can be created:

test=# SELECT   *
       FROM     pg_create_logical_replication_slot('hans_slot', 'wal2json');

 slot_name | xlog_position
-----------+---------------
 hans_slot | 0/18DD268

[/code]

Some data can be inserted to demonstrate how the replication slot works:



When the data is dequeued a perfect stream of JSON documents can be seen:



test=# SELECT    *
       FROM      pg_logical_slot_get_changes('hans_slot', NULL, NULL);
 location  | xid |                             data
-----------+-----+-------------------------------------------------------------------
 0/18DD2F0 | 993 | {                                                                +
           |     |         "xid": 993,                                              +
           |     |         "change": [
 0/18F9678 | 993 |         ]                                                        +
           |     | } 
 0/18F96B0 | 994 | {                                                                +
           |     |         "xid": 994,                                              +
           |     |         "change": [
 0/18F96B0 | 994 |                 {                                                +
           |     |                         "kind": "insert",                        +
           |     |                         "schema": "public",                      +
           |     |                         "table": "t_data",                       +
           |     |                         "columnnames": ["id", "name", "payload"],+
           |     |                         "columntypes": ["int4", "text", "_int4"],+
           |     |                         "columnvalues": [1, "hans", "{10,20,30}"]+
           |     |                 }
 0/18F9748 | 994 |         ]                                                        +
           |     | }
 0/18F9780 | 995 | {                                                                +
           |     |         "xid": 995,                                              +
           |     |         "change": [
 0/18F9780 | 995 |                 {                                                +
           |     |                         "kind": "insert",                        +
           |     |                         "schema": "public",                      +
           |     |                         "table": "t_data",                       +
           |     |                         "columnnames": ["id", "name", "payload"],+
           |     |                         "columntypes": ["int4", "text", "_int4"],+
           |     |                         "columnvalues": [2, "paul", "{23,49,87}"]+
           |     |                 }
0/18F9818 | 995 |         ]                                                        +
          |     | }
(8 rows)

The real beauty here is that all PostgreSQL JSON functions can be used to process the JSON stream. It is pretty easy to do ex-post analysis on the changes fetched from the xlog.

Find out more about JSON and PostgreSQL in our tag blog spot just for JSON blogs.