Logical Decoding Plugins

From PostgreSQL wiki
Jump to navigationJump to search

Logical decoding provides the ability to stream modifications made via SQL to external consumers.

To be able to use logical decoding you need to install a plugin into postgresql to transform the WAL internal representation to a format the client can use.

JSON format plugins

wal2json

https://github.com/eulerto/wal2json

JSON output plugin for changeset extraction

"change": [
	{
		"kind": "insert",
		"schema": "public",
		"table": "table_with_pk",
		"columnnames": ["a", "b", "c"],
		"columntypes": ["int4", "varchar", "timestamp"],
		"columnvalues": [1, "Backup and Restore", "2015-08-27 16:46:35.818038"]
	}]

decoding-json

https://github.com/leptonix/decoding-json

This plugin receives the changes from WAL and decodes them to JSON.

{"type":"transaction.begin","xid":"2010561","committed":"2015-04-22 19:23:35.714443+00"}
{"type":"table","name":"abc","change":"INSERT","data":{"a":6,"b":7,"c":42}}
{"type":"table","name":"abc","change":"UPDATE","key":{"a":6,"b":7},"data":{"a":6,"b":7,"c":13}}

decoder_json

https://github.com/ildus/decoder_json

Uses libjansson for json generation rather than native postgresql code

jsoncdc

https://github.com/instructure/jsoncdc

Written in rust

wal2mongo

Translates changesets into a format consumable directly by MongoDB

https://github.com/HighgoSoftware/wal2mongo

Protobuf Format Plugins

decoderbufs

https://github.com/xstevens/decoderbufs

decoderbufs is a PostgreSQL logical decoder output plugin to deliver data as Protocol Buffers.

postgres-decoderbufs

https://github.com/debezium/postgres-decoderbufs

A fork of xstevens/decoderbufs for use as a debezium connector

Avro Format Plugins

Bottled Water

https://github.com/confluentinc/bottledwater-pg

Streams into Kafka. No longer supported.

SQL Format Plugins

decoder_raw

https://github.com/michaelpq/pg_plugins/tree/master/decoder_raw

This output plugin for logical replication generates raw queries based on the logical changes it finds. Those queries can be consumed as they are by any remote source.

Miscellaneous Plugins

test_decoding

contrib/test_decoding is example code for a logical decoding output plugin. It describes itself as "doesn't do anything especially useful, but can serve as a starting point for developing your own decoder".

People have written code to parse the output from this plugin, but that doesn't make it a good idea:

osm-logical

https://github.com/zerebubuth/osm-logical

Toy code for use with openstreetmaps. Looks to be based on test_decoding.

pglogical

https://www.2ndquadrant.com/en/resources/pglogical/

pglogical is a logical replication system, implemented as a postgresql extension

transicator

https://github.com/apigee-labs/transicator

transicator reads from a single logical decoding connection and replicates change out to many (thousands) of intermittently connected clients via a REST API