docker.recipes

TimescaleDB IoT Analytics

intermediate

TimescaleDB for time-series with MQTT and Grafana.

Overview

TimescaleDB is a PostgreSQL extension specifically designed for time-series data that combines the reliability and SQL capabilities of PostgreSQL with specialized time-series functionality including automatic partitioning, continuous aggregates, and data compression. Originally developed to handle the massive scale of time-series data in IoT and monitoring applications, TimescaleDB provides up to 90% data compression while maintaining full PostgreSQL compatibility. This IoT analytics stack combines TimescaleDB with Eclipse Mosquitto MQTT broker and Grafana to create a complete pipeline for collecting, storing, and visualizing IoT sensor data in real-time. The architecture allows MQTT devices to publish sensor readings to Mosquitto, which can then be processed and stored in TimescaleDB's hypertables for efficient time-series queries, while Grafana provides rich dashboards and alerting capabilities. This combination is particularly valuable for IoT deployments because it handles the entire data lifecycle from ingestion through visualization while providing enterprise-grade reliability and SQL query capabilities that make it easy to correlate time-series data with relational business data.

Key Features

  • Automatic time-based partitioning through TimescaleDB hypertables for efficient IoT data storage
  • Up to 90% data compression with TimescaleDB's native compression algorithms
  • Full PostgreSQL compatibility allowing complex SQL joins between time-series and relational data
  • MQTT broker with Eclipse Mosquitto supporting IoT device connectivity and pub/sub messaging
  • Continuous aggregates for real-time computation of averages, sums, and other metrics
  • Native PostgreSQL extensions work with TimescaleDB including PostGIS for location data
  • Rich time-series visualizations in Grafana with built-in TimescaleDB data source support
  • Data retention policies for automatic cleanup of old IoT sensor data

Common Use Cases

  • 1Smart building management systems collecting temperature, humidity, and energy consumption data
  • 2Industrial IoT monitoring for equipment performance, vibration analysis, and predictive maintenance
  • 3Agricultural sensor networks tracking soil moisture, weather conditions, and crop health metrics
  • 4Vehicle fleet tracking with GPS coordinates, fuel consumption, and engine diagnostics
  • 5Environmental monitoring stations collecting air quality, noise levels, and weather data
  • 6Smart city infrastructure monitoring traffic patterns, parking availability, and utility usage
  • 7Home automation systems integrating multiple IoT devices with long-term trend analysis

Prerequisites

  • Minimum 3GB RAM for all services (TimescaleDB 2GB, Grafana 512MB, Mosquitto 256MB)
  • Docker and Docker Compose installed with support for multi-container networking
  • Available ports 5432 (TimescaleDB), 1883 (MQTT), and 3000 (Grafana) or custom port configuration
  • Basic understanding of SQL and PostgreSQL for creating hypertables and queries
  • MQTT protocol knowledge for configuring IoT device connections
  • Understanding of time-series data concepts like retention policies and continuous aggregates

For development & testing. Review security settings, change default credentials, and test thoroughly before production use. See Terms

docker-compose.yml

docker-compose.yml
1services: 
2  timescaledb: 
3    image: timescale/timescaledb:latest-pg15
4    container_name: timescaledb
5    restart: unless-stopped
6    ports: 
7      - "${TSDB_PORT:-5432}:5432"
8    environment: 
9      - POSTGRES_USER=${TSDB_USER}
10      - POSTGRES_PASSWORD=${TSDB_PASSWORD}
11      - POSTGRES_DB=iot_data
12    volumes: 
13      - timescale_data:/var/lib/postgresql/data
14
15  mosquitto: 
16    image: eclipse-mosquitto:2
17    container_name: iot-mqtt
18    restart: unless-stopped
19    ports: 
20      - "${MQTT_PORT:-1883}:1883"
21    volumes: 
22      - ./mosquitto.conf:/mosquitto/config/mosquitto.conf:ro
23
24  grafana: 
25    image: grafana/grafana:latest
26    container_name: iot-grafana
27    restart: unless-stopped
28    ports: 
29      - "${GRAFANA_PORT:-3000}:3000"
30    environment: 
31      - GF_SECURITY_ADMIN_PASSWORD=${GRAFANA_PASSWORD}
32    volumes: 
33      - grafana_data:/var/lib/grafana
34
35volumes: 
36  timescale_data: 
37  grafana_data:

.env Template

.env
1# TimescaleDB IoT
2TSDB_PORT=5432
3TSDB_USER=iot
4TSDB_PASSWORD=iot_password
5MQTT_PORT=1883
6GRAFANA_PORT=3000
7GRAFANA_PASSWORD=admin

Usage Notes

  1. 1TimescaleDB at localhost:5432
  2. 2MQTT broker at localhost:1883
  3. 3Grafana at http://localhost:3000
  4. 4Create hypertables for time-series

Individual Services(3 services)

Copy individual services to mix and match with your existing compose files.

timescaledb
timescaledb:
  image: timescale/timescaledb:latest-pg15
  container_name: timescaledb
  restart: unless-stopped
  ports:
    - ${TSDB_PORT:-5432}:5432
  environment:
    - POSTGRES_USER=${TSDB_USER}
    - POSTGRES_PASSWORD=${TSDB_PASSWORD}
    - POSTGRES_DB=iot_data
  volumes:
    - timescale_data:/var/lib/postgresql/data
mosquitto
mosquitto:
  image: eclipse-mosquitto:2
  container_name: iot-mqtt
  restart: unless-stopped
  ports:
    - ${MQTT_PORT:-1883}:1883
  volumes:
    - ./mosquitto.conf:/mosquitto/config/mosquitto.conf:ro
grafana
grafana:
  image: grafana/grafana:latest
  container_name: iot-grafana
  restart: unless-stopped
  ports:
    - ${GRAFANA_PORT:-3000}:3000
  environment:
    - GF_SECURITY_ADMIN_PASSWORD=${GRAFANA_PASSWORD}
  volumes:
    - grafana_data:/var/lib/grafana

Quick Start

terminal
1# 1. Create the compose file
2cat > docker-compose.yml << 'EOF'
3services:
4  timescaledb:
5    image: timescale/timescaledb:latest-pg15
6    container_name: timescaledb
7    restart: unless-stopped
8    ports:
9      - "${TSDB_PORT:-5432}:5432"
10    environment:
11      - POSTGRES_USER=${TSDB_USER}
12      - POSTGRES_PASSWORD=${TSDB_PASSWORD}
13      - POSTGRES_DB=iot_data
14    volumes:
15      - timescale_data:/var/lib/postgresql/data
16
17  mosquitto:
18    image: eclipse-mosquitto:2
19    container_name: iot-mqtt
20    restart: unless-stopped
21    ports:
22      - "${MQTT_PORT:-1883}:1883"
23    volumes:
24      - ./mosquitto.conf:/mosquitto/config/mosquitto.conf:ro
25
26  grafana:
27    image: grafana/grafana:latest
28    container_name: iot-grafana
29    restart: unless-stopped
30    ports:
31      - "${GRAFANA_PORT:-3000}:3000"
32    environment:
33      - GF_SECURITY_ADMIN_PASSWORD=${GRAFANA_PASSWORD}
34    volumes:
35      - grafana_data:/var/lib/grafana
36
37volumes:
38  timescale_data:
39  grafana_data:
40EOF
41
42# 2. Create the .env file
43cat > .env << 'EOF'
44# TimescaleDB IoT
45TSDB_PORT=5432
46TSDB_USER=iot
47TSDB_PASSWORD=iot_password
48MQTT_PORT=1883
49GRAFANA_PORT=3000
50GRAFANA_PASSWORD=admin
51EOF
52
53# 3. Start the services
54docker compose up -d
55
56# 4. View logs
57docker compose logs -f

One-Liner

Run this command to download and set up the recipe in one step:

terminal
1curl -fsSL https://docker.recipes/api/recipes/timescaledb-iot-stack/run | bash

Troubleshooting

  • TimescaleDB connection refused: Ensure POSTGRES_USER and POSTGRES_PASSWORD environment variables are set and container has fully initialized
  • Mosquitto authentication failures: Check mosquitto.conf file permissions and verify it's mounted read-only to /mosquitto/config/mosquitto.conf
  • Grafana TimescaleDB datasource connection errors: Use service name 'timescaledb' as hostname, not localhost, due to Docker networking
  • Hypertable creation fails: Connect to TimescaleDB first and run 'CREATE EXTENSION IF NOT EXISTS timescaledb;' before creating hypertables
  • MQTT messages not persisting: Verify database connection from your MQTT consumer and check that hypertables are created with proper time column
  • High memory usage in TimescaleDB: Enable compression on older data chunks and configure appropriate retention policies for IoT data lifecycle

Community Notes

Loading...
Loading notes...

Download Recipe Kit

Get all files in a ready-to-deploy package

Includes docker-compose.yml, .env template, README, and license

Components

timescaledbgrafanamosquitto

Tags

#timescaledb#iot#mqtt#time-series

Category

Database Stacks

Related Recipes

Ad Space