pgmqtt

Overview

manually upgraded PGRX from 0.16.1 to 0.17.0 by Vonng; requires wal_level = logical for CDC.

Version

Build

You can build the RPM / DEB packages for pgmqtt using pig build:

pig build pkg pgmqtt         # build RPM / DEB packages

Install

You can install pgmqtt directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

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

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf
• apt

pig install pgmqtt;          # Install for current active PG version

pig ext install -y pgmqtt -v 18  # PG 18
pig ext install -y pgmqtt -v 17  # PG 17
pig ext install -y pgmqtt -v 16  # PG 16
pig ext install -y pgmqtt -v 15  # PG 15
pig ext install -y pgmqtt -v 14  # PG 14

dnf install -y pgmqtt_18       # PG 18
dnf install -y pgmqtt_17       # PG 17
dnf install -y pgmqtt_16       # PG 16
dnf install -y pgmqtt_15       # PG 15
dnf install -y pgmqtt_14       # PG 14

apt install -y postgresql-18-pgmqtt   # PG 18
apt install -y postgresql-17-pgmqtt   # PG 17
apt install -y postgresql-16-pgmqtt   # PG 16
apt install -y postgresql-15-pgmqtt   # PG 15
apt install -y postgresql-14-pgmqtt   # PG 14

Create Extension:

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.