Turning xlog into JSON

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

1 2	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

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:

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

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)

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.

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.

Turning xlog into JSON

Leave a Reply Cancel reply

Hans-Jürgen Schönig

Blog Tags

NEWSLETTER

Articles by our PostgreSQL Experts