Nowadays JSON is used pretty much everywhere. Not just web developers 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 replicaton slot can be created:


test=# SELECT       *

FROM pg_create_logical_replication_slot('hans_slot', 'wal2json');

slot_name | xlog_position

-----------+---------------

hans_slot | 0/18DD268

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

<pre>
test=# CREATE TABLE t_data (id int, name text, payload int[]);

CREATE TABLE

test=# INSERT INTO t_data VALUES (1, 'hans', '{10, 20, 30}');

INSERT 0 1

test=# INSERT INTO t_data VALUES (2, 'paul', '{23, 49, 87}');

INSERT 0 1

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.