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

• GitHub Repo: RayElg/pgmqtt
• README: RayElg/pgmqtt/blob/main/README.md

pgmqtt is a pgrx-based PostgreSQL extension that turns CDC into an MQTT broker. Database changes can be published to MQTT topics through SQL-defined mappings, and MQTT publishes can be written back into PostgreSQL tables.

The README’s quickstart requires wal_level = logical so CDC events can be captured correctly.

Outbound Mapping

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

With this mapping, INSERT, UPDATE, and DELETE on the table are published as MQTT messages. Subscribers to topics/insert, topics/update, or topics/delete receive JSON payloads.

Inbound Mapping

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

When a client publishes to sensor/site-1/temperature with payload {"temperature": 22.5}, the README says a row is inserted into sensor_readings with the extracted values.

Client Examples

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

Scope

The upstream README covers the broker model, the outbound/inbound mapping examples, and basic MQTT client usage. It does not document a separate project homepage, so this stub stays at README scope.