pgmqtt

pgmqtt : CDC-to-MQTT broker for PostgreSQL

Overview

Packages

Source

pig build pkg pgmqtt;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

pig repo add pgsql -u   # add both repo and update cache

Install this extension with pig:

pig install pgmqtt;		# install via package name, for the active PG version

pig install pgmqtt -v 18;   # install for PG 18
pig install pgmqtt -v 17;   # install for PG 17
pig install pgmqtt -v 16;   # install for PG 16
pig install pgmqtt -v 15;   # install for PG 15
pig install pgmqtt -v 14;   # install for PG 14

Create this extension with:

CREATE EXTENSION pgmqtt;

Usage

Sources: official README, official repo

pgmqtt is a pgrx extension that embeds an MQTT broker into PostgreSQL and uses change data capture to turn table changes into MQTT messages. It also supports inbound topic mappings so MQTT publishes can insert rows into PostgreSQL tables.

CREATE EXTENSION pgmqtt;

Outbound Mapping

Publish table changes to topics:

SELECT pgmqtt_add_outbound_mapping(
  'public',
  'my_table',
  'topics/{{ op | lower }}',
  '{{ columns | tojson }}'
);

With that mapping, INSERT, UPDATE, and DELETE publish JSON payloads to topics such as topics/insert.

Inbound Mapping

Insert rows from MQTT publishes:

SELECT pgmqtt_add_inbound_mapping(
  'sensor/{site_id}/temperature',
  'sensor_readings',
  '{"site_id": "{site_id}", "value": "$.temperature"}'::jsonb
);

Publishing {"temperature": 22.5} to sensor/site-1/temperature inserts a row into sensor_readings.

MQTT Client Examples

mosquitto_sub -h localhost -t 'topics/#'
mosquitto_pub -h localhost -t 'sensor/site-1/temperature' -m '{"temperature": 22.5}'

Caveats

• The README requires wal_level = logical; without logical decoding the CDC side will not work.
• Upstream documentation is currently README-level only, so the documented SQL surface is limited to the inbound and outbound mapping workflow.