3

I have a PostgreSQL DB that updates periodically, and want to publish the latest changes to a mosquitto broker. What is the most elegant way to do this approach ?

Sadik Hasan
  • 173
  • 3
  • 18

3 Answers3

1

Ok this idea is interesting i am using PostgreSQL 9.5 and in the console if i type CREATE LANGUAGE plpythonu; the python language is available. But how i can receive the whole database and the periodically changes of the database into my Broker.

CREATE FUNCTION publishChanges ()
  RETURNS integer
AS $$
  import paho.mqtt.client as mqtt
  import paho.mqtt.publish as publish
  #client = mqtt.Client()
  #client.connect("localhost", 1883, 60)

  publish.single("test/data", SELECT * from table ,hostname="127.0.0.1")

  #client.loop_forever()
$$ LANGUAGE plpythonu;
Sadik Hasan
  • 173
  • 3
  • 18
  • You can't publish a query like that. You should only publish the changed record, not the whole table. But the concept is roughly what I was suggesting – hardillb Apr 06 '16 at 13:30
  • What is the return value of the function and how can can be the trigger statements implemented ? – Sadik Hasan Apr 06 '16 at 17:15
1

Postgress has its own publish/subscribe system: listen/notify. Set up the proper (listen)notify channels in postgress for the information you want published. The notify payload can be formatted as a mqtt message string. Create a client that listens to them and publishes to the mqtt-broker.

ingo
  • 117
  • 10
0

Postgress appears to support python stored procedures which you should be able to call from a create/update trigger.

This combined with the paho python client would do what you want

hardillb
  • 54,545
  • 11
  • 67
  • 105
  • Maybe so ? CREATE FUNCTION publishChanges () RETURNS integer AS $$ import paho.mqtt.client as mqtt import paho.mqtt.publish as publish #client = mqtt.Client() #client.connect("localhost", 1883, 60) publish.single("test/data", SELECT * from table ,hostname="127.0.0.1") #client.loop_forever() $$ LANGUAGE plpythonu; – Sadik Hasan Apr 06 '16 at 13:26