IoT Bridge with TimescaleDB Setup Guide

This document provides step-by-step instructions for setting up TimescaleDB and configuring the IoT Bridge application for persistence on your IoT platform.

1. TimescaleDB Setup

1.1 Installation

TimescaleDB is an extension for PostgreSQL that optimizes time-series data storage and querying.

# Pull the official TimescaleDB Docker image
docker pull timescale/timescaledb:latest-pg15

# Run TimescaleDB container
docker run -d --name timescaledb \
  -p 5432:5432 \
  -e POSTGRES_USER=telegraf \
  -e POSTGRES_PASSWORD=telegraf_secure_password \
  -e POSTGRES_DB=iotplatform \
  -v timescale_data:/var/lib/postgresql/data \
  timescale/timescaledb:latest-pg15

Native Installation

For Ubuntu/Debian:

# Add TimescaleDB repository
sudo add-apt-repository ppa:timescale/timescaledb-ppa
sudo apt-get update

# Install TimescaleDB
sudo apt install timescaledb-2-postgresql-15

# Enable TimescaleDB
sudo timescaledb-tune --quiet --yes
sudo systemctl restart postgresql

1.2 Database Schema Creation

Connect to your TimescaleDB instance and create the required schema:

-- Create schema
CREATE SCHEMA IF NOT EXISTS iot_events;

-- Create table
CREATE TABLE IF NOT EXISTS iot_events.events (
    id TEXT,
    ts TIMESTAMPTZ NOT NULL,
    thing_id TEXT NOT NULL,
    edge_id TEXT NOT NULL,
    type TEXT NOT NULL,
    version INTEGER NOT NULL,
    context JSONB,
    payload JSONB,
    metadata JSONB,
    PRIMARY KEY (id, ts)
);

1.3 Converting to a Hypertable

Transform the table into a TimescaleDB hypertable for time-series optimization:

-- Create hypertable with time partitioning on 'ts' column
SELECT create_hypertable('iot_events.events', 'ts', if_not_exists => TRUE, 
                        chunk_time_interval => INTERVAL '1 day');

1.4 Creating Indexes for Performance

Add recommended indexes for query performance:

-- Index on thing_id
CREATE INDEX IF NOT EXISTS idx_events_thing_id ON iot_events.events (thing_id);

-- Index on edge_id
CREATE INDEX IF NOT EXISTS idx_events_edge_id ON iot_events.events (edge_id);

-- Index on type
CREATE INDEX IF NOT EXISTS idx_events_type ON iot_events.events (type);

-- Compound index for common query patterns
CREATE INDEX IF NOT EXISTS idx_events_thing_edge_type ON iot_events.events (thing_id, edge_id, type);

1.5 TimescaleDB Compression (Optional)

For long-term storage efficiency, enable TimescaleDB compression:

-- Enable compression
ALTER TABLE iot_events.events SET (timescaledb.compress, timescaledb.compress_segmentby = 'thing_id, edge_id, type');

-- Add compression policy (compress data older than 7 days)
SELECT add_compression_policy('iot_events.events', INTERVAL '7 days');

1.6 Retention Policy (Optional)

Set data retention period to automatically remove old data:

-- Add retention policy (keep data for 90 days)
SELECT add_retention_policy('iot_events.events', INTERVAL '90 days');

2. IoT Bridge Application Setup

2.1 Application Installation

Docker Deployment

Create a docker-compose.yml file:

version: '3.8'

services:
  iot-bridge:
    build:
      context: .
      dockerfile: Dockerfile
    container_name: iot-bridge
    restart: unless-stopped
    depends_on:
      - nats-server
      - timescaledb
    volumes:
      - ./config.yaml:/app/config.yaml
      - ./logs:/app/logs
    environment:
      - NATS_SERVERS=nats://nats-server:4222
      - NATS_USERNAME=${NATS_USERNAME:-telegraf}
      - NATS_PASSWORD=${NATS_PASSWORD:-your_nats_password}
      - DB_HOST=timescaledb
      - DB_PORT=5432
      - DB_USERNAME=${DB_USERNAME:-telegraf}
      - DB_PASSWORD=${DB_PASSWORD:-telegraf_secure_password}
      - DB_DATABASE=${DB_DATABASE:-iotplatform}
      - LOG_LEVEL=${LOG_LEVEL:-info}
    networks:
      - iot-network

  nats-server:
    image: nats:2.9-alpine
    container_name: nats-server
    restart: unless-stopped
    ports:
      - "4222:4222"  # Client connections
      - "8222:8222"  # HTTP monitoring
    volumes:
      - ./nats-config:/etc/nats
      - nats-data:/data
    command: ["-c", "/etc/nats/server.conf"]
    networks:
      - iot-network

  timescaledb:
    image: timescale/timescaledb:latest-pg15
    container_name: timescaledb
    restart: unless-stopped
    ports:
      - "5432:5432"
    environment:
      - POSTGRES_USER=${DB_USERNAME:-telegraf}
      - POSTGRES_PASSWORD=${DB_PASSWORD:-telegraf_secure_password}
      - POSTGRES_DB=${DB_DATABASE:-iotplatform}
    volumes:
      - timescaledb-data:/var/lib/postgresql/data
    networks:
      - iot-network

volumes:
  nats-data:
  timescaledb-data:

networks:
  iot-network:
    driver: bridge

Deploy with Docker Compose:

# Create a .env file with your credentials
echo "NATS_USERNAME=your_username" > .env
echo "NATS_PASSWORD=your_password" >> .env
echo "DB_USERNAME=your_db_user" >> .env
echo "DB_PASSWORD=your_db_password" >> .env

# Start the services
docker-compose up -d

Manual Installation

Build and install the application:

# Clone the repository
git clone https://github.com/your-org/iot-bridge
cd iot-bridge

# Build the application
go build -o iot-bridge ./cmd/iot-bridge

# Create configuration
cp config.yaml.example config.yaml
# Edit config.yaml with your settings

# Run the application
./iot-bridge -config config.yaml

2.2 Systemd Service Configuration (For persistence)

To ensure the application runs as a service and starts automatically:

# Create a systemd service file
sudo vi /etc/systemd/system/iot-bridge.service

Add the following content:

[Unit]
Description=IoT Bridge Service
After=network.target

[Service]
Type=simple
User=iotuser
WorkingDirectory=/opt/iot-bridge
ExecStart=/opt/iot-bridge/iot-bridge -config /opt/iot-bridge/config.yaml
Restart=always
RestartSec=5
Environment=NATS_USERNAME=your_username
Environment=NATS_PASSWORD=your_password
Environment=DB_USERNAME=your_db_user
Environment=DB_PASSWORD=your_db_password

[Install]
WantedBy=multi-user.target

Enable and start the service:

sudo systemctl daemon-reload
sudo systemctl enable iot-bridge
sudo systemctl start iot-bridge
sudo systemctl status iot-bridge

2.3 Configuration

Create a config.yaml file with the following settings:

# IoT Bridge Configuration

# NATS connection settings
nats:
  servers:
    - "nats://nats-server:4222"

  username: "telegraf"
  password: "your_nats_password"

  # TLS configuration (if needed)
  tls:
    enabled: false
    ca_file: "/path/to/ca.pem"
    cert_file: "/path/to/cert.pem"
    key_file: "/path/to/key.pem"
    insecure_skip_verify: false

  # NATS queue group for horizontal scaling
  queue_group: "iot_consumers"

  # Subjects to subscribe to
  subjects:
    - ">"  # Subscribe to everything
    # Alternatively, use specific subjects:
    # - "moco.bldg-na-001.controllers.ctrl-main-001.inputs.>"

  # Message handling limits
  pending_limits:
    messages: 65536
    bytes: 67108864

# Database connection settings
database:
  host: "timescaledb"  # Use container name in Docker
  port: 5432
  username: "telegraf"
  password: "telegraf_secure_password"
  database: "iotplatform"
  schema: "iot_events"
  table: "events"
  ssl_mode: "disable"
  max_open_conns: 10
  max_idle_conns: 5

# Logging configuration
logging:
  level: "info"  # debug, info, warn, error
  format: "json"  # json or text
  output_path: "/var/log/iot-bridge/iot-bridge.log"  # or "stdout"

3. Monitoring and Maintenance

3.1 Monitoring TimescaleDB

Check database status:

# Connect to TimescaleDB
psql -U telegraf -h localhost -d iotplatform

# List hypertables
SELECT * FROM timescaledb_information.hypertables;

# Check disk usage
SELECT hypertable_name, pg_size_pretty(hypertable_size) AS size
FROM timescaledb_information.hypertable_size;

# Check chunk information
SELECT * FROM timescaledb_information.chunks
WHERE hypertable_name = 'events' AND hypertable_schema = 'iot_events'
ORDER BY range_start DESC;

3.2 Backing Up TimescaleDB

Regular backups are essential:

# Using pg_dump for a logical backup
pg_dump -U telegraf -h localhost -d iotplatform -F c -f iotplatform_backup.dump

# Using Docker
docker exec -t timescaledb pg_dump -U telegraf iotplatform -F c > iotplatform_backup.dump

3.3 Application Logging

View application logs:

# If using systemd
sudo journalctl -u iot-bridge -f

# If using Docker
docker logs -f iot-bridge

3.4 Scaling Considerations

For high-throughput environments:

  1. Vertical Scaling: Increase TimescaleDB resources (CPU, RAM)
  2. Horizontal Scaling: Run multiple IoT Bridge instances with the same queue group
  3. Connection Pooling: Adjust max_open_conns and max_idle_conns based on server capacity
  4. Partitioning Strategy: Adjust chunk_time_interval based on data volume
  5. Index Optimization: Add or remove indexes based on query patterns

4. Troubleshooting

4.1 Common Issues

Database Connection Issues

error connecting to database: connection refused
  • Check if TimescaleDB is running
  • Verify host, port, username, and password
  • Ensure network connectivity between app and database

NATS Connection Issues

error connecting to NATS: no servers available for connection
  • Check if NATS server is running
  • Verify server URLs
  • Check authentication credentials

TimescaleDB Primary Key Errors

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
  • Ensure the table has the composite primary key (id, ts)
  • Verify the SQL definition matches the expected format

4.2 Performance Optimization

  1. TimescaleDB Configuration:
  2. Adjust shared_buffers to 25-30% of RAM
  3. Increase maintenance_work_mem for faster index creation

  4. Set max_parallel_workers and max_parallel_workers_per_gather based on CPU cores

  5. Message Processing Optimization:

  6. Batch inserts for high-throughput scenarios
  7. Use appropriate NATS subjects to filter messages

5. Next Steps and Enhancements

Consider these future enhancements: - Implement data aggregation for long-term storage - Add monitoring with Prometheus and Grafana - Set up alerts for system health - Implement data backup and recovery procedures - Develop data access APIs