create-table.sql

CREATE TABLE mqtt (
  ts       timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  topic    text[] NOT NULL,
  retain   boolean NOT NULL,
  value    text,
  PRIMARY KEY (ts, topic)
)

mqtt2psql.py

import paho.mqtt.client, psycopg2

def on_message(client, obj, msg):
  try:
    cur.execute("INSERT INTO mqtt (topic, retain, value) VALUES (%s, %s, %s)", (msg.topic.split("/"), bool(msg.retain), msg.payload.decode()))
    sql.commit()
  except e:
    print(e)

sql = psycopg2.connect("user=mqtt")
cur = sql.cursor()

mqttc = paho.mqtt.client.Client()
mqttc.connect("mqtt.peetz0r.nl")

mqttc.message_callback_add("#", on_message)
mqttc.subscribe("#")


try:
  while True:
    mqttc.loop()

except KeyboardInterrupt:
  print("Bye!")