postgresql logical replication

PUBLISHED ON SEP 23, 2017 — POSTGRESQL

I found I needed to stream changes from postgresql into a go app, and while some prototyping with triggers and listen/notify seemed promising logical decoding might be a better way.

There are two pieces of code needed to use logical decoding. One is a postgresql extension that receives changesets in the WAL internal representation and converts them to something a client can use more easily. The other is a client that connects to a postgresql replication connection and streams data from the extension.

Writing a logical decoding plugin doesn’t look terribly complex, but if I’m asking customers to install extensions in their databases I’d prefer something that’s battle-tested (and that I don’t need to maintain). Some searching found a dozen implementations generating various formats.

Of the protobuf based plugins postgres-decoderbufs looks the most mature. It’s part of debezium’s change data capture platform and is being maintained by them. It has a few broken links in the documentation, and the docs don’t seem to match the code, but it’s less than 800 lines of code, how bad can it be?

I develop on OS X using Postgres.app so the first thing is to build the plugin for that. It’s surprisingly simple. Two of the prerequisites, PostgreSQL and PostGIS development files, are included in Postgres.app and handled by pg_config. The remaining one, protocol buffers for C is just a brew install protobuf-c away. make && make install and it’s built and installed.

After adding the needed config to postgresql.conf and pg_hba.conf I can create a logical replication slot and interact with it from SQL.

Next step, accessing it over a replication connection. The included binary pg_recvlogical will do that, but I’m going to write some client code.

My favorite Go postgresql driver, jackc/pgx, will handle logical replication connections, so with luck I should just have to parse the messages.