MQTT and PostgreSQL Integration
Powerful performance with an easy integration, powered by Telegraf, the open source data connector built by InfluxData.
5B+
Telegraf downloads
#1
Time series database
Source: DB Engines
1B+
Downloads of InfluxDB
2,800+
Contributors
Table of Contents
Powerful Performance, Limitless Scale
Collect, organize, and act on massive volumes of high-velocity data. Any data is more valuable when you think of it as time series data. with InfluxDB, the #1 time series platform built to scale with Telegraf.
See Ways to Get Started
Input and output integration overview
The MQTT plugin reads from the specified topics and creates metrics using the supported input data formats.
The Telegraf PostgreSQL plugin allows you to efficiently write metrics to a PostgreSQL database while automatically managing the database schema.
Integration details
MQTT
This plugin allows Telegraf to consume metrics from specified MQTT topics. It supports a variety of configuration options to connect to MQTT brokers and manage message subscriptions, including features for handling startup errors and using TLS for secure connections.
PostgreSQL
This plugin writes metrics to PostgreSQL (or acompatible database) and manages the schema, automatically updating missing columns.
Configuration
MQTT
[[inputs.mqtt_consumer]]
servers = ["tcp://127.0.0.1:1883"]
topics = [
"telegraf/host01/cpu",
"telegraf/+/mem",
"sensors/#",
]
# topic_tag = "topic"
# qos = 0
# connection_timeout = "30s"
# keepalive = "60s"
# ping_timeout = "10s"
# max_undelivered_messages = 1000
# persistent_session = false
# client_id = ""
# username = "telegraf"
# password = "metricsmetricsmetricsmetrics"
# tls_ca = "/etc/telegraf/ca.pem"
# tls_cert = "/etc/telegraf/cert.pem"
# tls_key = "/etc/telegraf/key.pem"
# insecure_skip_verify = false
# client_trace = false
data_format = "influx"
# [[inputs.mqtt_consumer.topic_parsing]]
# topic = ""
# measurement = ""
# tags = ""
# fields = ""
# [inputs.mqtt_consumer.topic_parsing.types]
# key = type
PostgreSQL
# Publishes metrics to a postgresql database
[[outputs.postgresql]]
## Specify connection address via the standard libpq connection string:
## host=... user=... password=... sslmode=... dbname=...
## Or a URL:
## postgres://[user[:password]]@localhost[/dbname]?sslmode=[disable|verify-ca|verify-full]
## See https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING
##
## All connection parameters are optional. Environment vars are also supported.
## e.g. PGPASSWORD, PGHOST, PGUSER, PGDATABASE
## All supported vars can be found here:
## https://www.postgresql.org/docs/current/libpq-envars.html
##
## Non-standard parameters:
## pool_max_conns (default: 1) - Maximum size of connection pool for parallel (per-batch per-table) inserts.
## pool_min_conns (default: 0) - Minimum size of connection pool.
## pool_max_conn_lifetime (default: 0s) - Maximum age of a connection before closing.
## pool_max_conn_idle_time (default: 0s) - Maximum idle time of a connection before closing.
## pool_health_check_period (default: 0s) - Duration between health checks on idle connections.
# connection = ""
## Postgres schema to use.
# schema = "public"
## Store tags as foreign keys in the metrics table. Default is false.
# tags_as_foreign_keys = false
## Suffix to append to table name (measurement name) for the foreign tag table.
# tag_table_suffix = "_tag"
## Deny inserting metrics if the foreign tag can't be inserted.
# foreign_tag_constraint = false
## Store all tags as a JSONB object in a single 'tags' column.
# tags_as_jsonb = false
## Store all fields as a JSONB object in a single 'fields' column.
# fields_as_jsonb = false
## Name of the timestamp column
## NOTE: Some tools (e.g. Grafana) require the default name so be careful!
# timestamp_column_name = "time"
## Type of the timestamp column
## Currently, "timestamp without time zone" and "timestamp with time zone"
## are supported
# timestamp_column_type = "timestamp without time zone"
## Templated statements to execute when creating a new table.
# create_templates = [
# '''CREATE TABLE {{ .table }} ({{ .columns }})''',
# ]
## Templated statements to execute when adding columns to a table.
## Set to an empty list to disable. Points containing tags for which there is no column will be skipped. Points
## containing fields for which there is no column will have the field omitted.
# add_column_templates = [
# '''ALTER TABLE {{ .table }} ADD COLUMN IF NOT EXISTS {{ .columns|join ", ADD COLUMN IF NOT EXISTS " }}''',
# ]
## Templated statements to execute when creating a new tag table.
# tag_table_create_templates = [
# '''CREATE TABLE {{ .table }} ({{ .columns }}, PRIMARY KEY (tag_id))''',
# ]
## Templated statements to execute when adding columns to a tag table.
## Set to an empty list to disable. Points containing tags for which there is no column will be skipped.
# tag_table_add_column_templates = [
# '''ALTER TABLE {{ .table }} ADD COLUMN IF NOT EXISTS {{ .columns|join ", ADD COLUMN IF NOT EXISTS " }}''',
# ]
## The postgres data type to use for storing unsigned 64-bit integer values (Postgres does not have a native
## unsigned 64-bit integer type).
## The value can be one of:
## numeric - Uses the PostgreSQL "numeric" data type.
## uint8 - Requires pguint extension (https://github.com/petere/pguint)
# uint64_type = "numeric"
## When using pool_max_conns>1, and a temporary error occurs, the query is retried with an incremental backoff. This
## controls the maximum backoff duration.
# retry_max_backoff = "15s"
## Approximate number of tag IDs to store in in-memory cache (when using tags_as_foreign_keys).
## This is an optimization to skip inserting known tag IDs.
## Each entry consumes approximately 34 bytes of memory.
# tag_cache_size = 100000
## Enable & set the log level for the Postgres driver.
# log_level = "warn" # trace, debug, info, warn, error, none
Input and output integration examples
MQTT
-
Basic Configuration: This example connects to a local MQTT broker, subscribes to specific topics for CPU and memory metrics, and outputs using the Influx data format.
-
Topic Parsing: Extracts tag values from MQTT topics for better data organization and analysis, allowing metrics to be categorized based on their topics.
-
Field Pivoting: Demonstrates how to pivot single-valued metrics into a multi-field metric. This is useful for consolidating data from multiple sensors into a single metric.
PostgreSQL
-
Monitoring Database Performance: You can use this plugin to regularly send metrics on PostgreSQL performance such as active connections, query performance, and resource usage, allowing for better monitoring and optimization of your database.
-
Integrating with TimescaleDB: If you’re using TimescaleDB for time-series data storage, this plugin can help you write metrics directly into a hypertable. This allows you to benefit from TimescaleDB’s advanced time-series capabilities while leveraging standard PostgreSQL features.
-
Data Archiving: Create a long-term data archiving solution where you can push metrics into PostgreSQL for historical analysis. The plugin’s support for JSONB allows you to store complex data structures directly into a single column, making retrieval efficient.
Feedback
Thank you for being part of our community! If you have any general feedback or found any bugs on these pages, we welcome and encourage your input. Please submit your feedback in the InfluxDB community Slack.
Powerful Performance, Limitless Scale
Collect, organize, and act on massive volumes of high-velocity data. Any data is more valuable when you think of it as time series data. with InfluxDB, the #1 time series platform built to scale with Telegraf.
See Ways to Get Started
Related Integrations
Related Integrations
HTTP and InfluxDB Integration
The HTTP plugin collects metrics from one or more HTTP(S) endpoints. It supports various authentication methods and configuration options for data formats.
View IntegrationKafka and InfluxDB Integration
This plugin reads messages from Kafka and allows the creation of metrics based on those messages. It supports various configurations including different Kafka settings and message processing options.
View IntegrationKinesis and InfluxDB Integration
The Kinesis plugin allows for reading metrics from AWS Kinesis streams. It supports multiple input data formats and offers checkpointing features with DynamoDB for reliable message processing.
View Integration