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:
|
1 2 |
wal_level = logical max_replication_slots = 10 |
After a database restart a replication slot can be created:
|
1 2 3 4 5 6 |
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:
|
1 2 3 4 5 6 7 8 9 |
<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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
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.
+43 (0) 2622 93022-0
office@cybertec.at