Integrate TimescaleDB with Telegraf for metrics collection and time-series monitoring

Intermediate 25 min Apr 22, 2026 120 views
Ubuntu 24.04 Debian 12 AlmaLinux 9 Rocky Linux 9

Set up TimescaleDB with PostgreSQL and configure Telegraf to collect system and application metrics. Create continuous aggregates and monitoring dashboards for comprehensive time-series analysis and alerting.

Prerequisites

  • Root or sudo access
  • 2GB RAM minimum
  • 10GB available disk space
  • Network access for package installation

What this solves

TimescaleDB extends PostgreSQL with time-series capabilities, making it ideal for storing metrics data from Telegraf. This combination gives you high-performance metric storage with SQL queries, continuous aggregates for real-time analytics, and automatic data retention policies. You'll get a complete monitoring stack that handles everything from system metrics to custom application data with enterprise-grade reliability.

Step-by-step installation

Update system packages

Start by updating your package manager to ensure you get the latest versions of all components.

sudo apt update && sudo apt upgrade -y
sudo apt install -y wget gnupg2 software-properties-common
sudo dnf update -y
sudo dnf install -y wget gnupg2

Install PostgreSQL

TimescaleDB requires PostgreSQL as its foundation. Install the latest version with the official repository for better performance and security updates.

wget -qO- https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /usr/share/keyrings/postgresql.gpg
echo "deb [signed-by=/usr/share/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/postgresql.list
sudo apt update
sudo apt install -y postgresql-16 postgresql-client-16 postgresql-contrib-16
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf install -y postgresql16-server postgresql16 postgresql16-contrib
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb

Start and enable PostgreSQL

Enable PostgreSQL to start on boot and verify it's running properly before adding TimescaleDB.

sudo systemctl enable --now postgresql
sudo systemctl status postgresql
sudo systemctl enable --now postgresql-16
sudo systemctl status postgresql-16

Install TimescaleDB extension

Add the TimescaleDB repository and install the extension that provides time-series functionality to PostgreSQL.

echo "deb https://packagecloud.io/timescale/timescaledb/ubuntu/ $(lsb_release -c -s) main" | sudo tee /etc/apt/sources.list.d/timescaledb.list
wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/timescaledb.gpg
sudo apt update
sudo apt install -y timescaledb-2-postgresql-16
sudo tee /etc/yum.repos.d/timescale_timescaledb.repo <

Configure PostgreSQL for TimescaleDB

Run the TimescaleDB tuning script to optimize PostgreSQL settings for time-series workloads and enable the extension.

sudo timescaledb-tune --quiet --yes
sudo systemctl restart postgresql

Create TimescaleDB database and user

Create a dedicated database for metrics and a user for Telegraf to connect with appropriate permissions.

sudo -u postgres psql -c "CREATE DATABASE telegraf;"
sudo -u postgres psql -c "CREATE USER telegraf WITH PASSWORD 'StrongTelegrafPassword123!';"
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE telegraf TO telegraf;"
sudo -u postgres psql -d telegraf -c "CREATE EXTENSION IF NOT EXISTS timescaledb;"
sudo -u postgres psql -d telegraf -c "GRANT ALL ON SCHEMA public TO telegraf;"

Install Telegraf agent

Add the InfluxData repository and install Telegraf for collecting system and application metrics.

wget -q https://repos.influxdata.com/influxdata-archive_compat.key
echo '393e8779c89ac8d958f81f942f9ad7fb82a25e133faddaf92e15b16e6ac9ce4c influxdata-archive_compat.key' | sha256sum -c && cat influxdata-archive_compat.key | gpg --dearmor | sudo tee /etc/apt/trusted.gpg.d/influxdata-archive_compat.gpg > /dev/null
echo 'deb [signed-by=/etc/apt/trusted.gpg.d/influxdata-archive_compat.gpg] https://repos.influxdata.com/debian stable main' | sudo tee /etc/apt/sources.list.d/influxdata.list
sudo apt update && sudo apt install -y telegraf
cat <

Configure Telegraf for TimescaleDB output

Create a custom Telegraf configuration that sends metrics to your TimescaleDB instance with optimal batching and error handling.

# Global Agent Configuration
[agent]
  interval = "10s"
  round_interval = true
  metric_batch_size = 1000
  metric_buffer_limit = 10000
  collection_jitter = "0s"
  flush_interval = "10s"
  flush_jitter = "0s"
  precision = "0s"
  debug = false
  quiet = false
  logfile = "/var/log/telegraf/telegraf.log"
  hostname = ""
  omit_hostname = false


PostgreSQL/TimescaleDB Output Plugin

[[outputs.postgresql]]
  connection = "host=localhost user=telegraf password=StrongTelegrafPassword123! dbname=telegraf sslmode=disable"
  create_templates = [
    '''CREATE TABLE IF NOT EXISTS {{.table}} ({{.columns}})''',
    '''SELECT create_hypertable('''{{.table}}''','''time''',if_not_exists=>true)'''
  ]
  schema = "public"
  tags_as_foreign_keys = false
  tag_table_suffix = "_tag"
  foreign_tag_constraint = false
  

System Metrics Input Plugin

[[inputs.cpu]]
  percpu = true
  totalcpu = true
  collect_cpu_time = false
  report_active = false

[[inputs.disk]]
  ignore_fs = ["tmpfs", "devtmpfs", "devfs", "iso9660", "overlay", "aufs", "squashfs"]

[[inputs.diskio]]

[[inputs.kernel]]

[[inputs.mem]]

[[inputs.processes]]

[[inputs.swap]]

[[inputs.system]]

[[inputs.net]]

[[inputs.netstat]]


Docker Metrics (optional)

[[inputs.docker]]
  endpoint = "unix:///var/run/docker.sock"
  gather_services = false
  container_names = []
  source_tag = false
  container_name_include = []
  container_name_exclude = []
  timeout = "5s"
  perdevice = true
  total = false
  docker_label_include = []
  docker_label_exclude = []


NGINX Metrics (optional)

[[inputs.nginx]]
  urls = ["http://localhost/nginx_status"]
  response_timeout = "5s"

Set proper permissions and ownership

Configure file ownership and permissions for Telegraf to read the configuration and write logs securely.

sudo chown telegraf:telegraf /etc/telegraf/telegraf.conf
sudo chmod 640 /etc/telegraf/telegraf.conf
sudo mkdir -p /var/log/telegraf
sudo chown telegraf:telegraf /var/log/telegraf
sudo chmod 755 /var/log/telegraf
Never use chmod 777. It gives every user on the system full access to your files. Instead, fix ownership with chown and use minimal permissions like 640 for config files and 755 for directories.

Start and enable Telegraf

Enable Telegraf to start on boot and begin collecting metrics immediately.

sudo systemctl enable --now telegraf
sudo systemctl status telegraf

Create continuous aggregates for monitoring

Set up continuous aggregates in TimescaleDB to automatically compute hourly and daily statistics for faster dashboard queries.

sudo -u postgres psql -d telegraf << 'EOF'
-- Create continuous aggregate for hourly CPU usage
CREATE MATERIALIZED VIEW cpu_usage_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', "time") AS bucket,
       host,
       cpu,
       avg(usage_idle) as avg_idle,
       avg(usage_user) as avg_user,
       avg(usage_system) as avg_system
FROM cpu
GROUP BY bucket, host, cpu;

-- Create continuous aggregate for hourly memory usage
CREATE MATERIALIZED VIEW mem_usage_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', "time") AS bucket,
       host,
       avg(used_percent) as avg_used_percent,
       avg(available_percent) as avg_available_percent
FROM mem
GROUP BY bucket, host;

-- Create continuous aggregate for disk I/O statistics
CREATE MATERIALIZED VIEW disk_io_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', "time") AS bucket,
       host,
       name,
       avg(read_bytes) as avg_read_bytes,
       avg(write_bytes) as avg_write_bytes,
       avg(iops_in_progress) as avg_iops
FROM diskio
GROUP BY bucket, host, name;

EOF

Set up refresh policies

Configure automatic refresh policies for continuous aggregates to keep them updated with fresh data.

sudo -u postgres psql -d telegraf << 'EOF'
-- Add refresh policies for continuous aggregates
SELECT add_continuous_aggregate_policy('cpu_usage_hourly',
    start_offset => INTERVAL '3 hours',
    end_offset => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour');

SELECT add_continuous_aggregate_policy('mem_usage_hourly',
    start_offset => INTERVAL '3 hours',
    end_offset => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour');

SELECT add_continuous_aggregate_policy('disk_io_hourly',
    start_offset => INTERVAL '3 hours',
    end_offset => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour');
EOF

Configure data retention policies

Set up automatic data retention to manage storage space by removing old raw metrics while keeping aggregated data longer.

sudo -u postgres psql -d telegraf << 'EOF'
-- Set retention policy for raw CPU data (30 days)
SELECT add_retention_policy('cpu', INTERVAL '30 days');

-- Set retention policy for raw memory data (30 days)
SELECT add_retention_policy('mem', INTERVAL '30 days');

-- Set retention policy for raw disk I/O data (30 days)
SELECT add_retention_policy('diskio', INTERVAL '30 days');

-- Set retention policy for raw system data (30 days)
SELECT add_retention_policy('system', INTERVAL '30 days');

-- Set retention policy for raw network data (30 days)
SELECT add_retention_policy('net', INTERVAL '30 days');
EOF

Verify your setup

Check that both TimescaleDB and Telegraf are running correctly and collecting metrics.

# Check Telegraf status
sudo systemctl status telegraf


Check PostgreSQL status

sudo systemctl status postgresql


Verify metrics are being collected

sudo -u postgres psql -d telegraf -c "SELECT COUNT(*) FROM cpu;"
sudo -u postgres psql -d telegraf -c "SELECT COUNT(*) FROM mem;"


Check continuous aggregates

sudo -u postgres psql -d telegraf -c "SELECT * FROM cpu_usage_hourly ORDER BY bucket DESC LIMIT 5;"


View available hypertables

sudo -u postgres psql -d telegraf -c "SELECT hypertable_name, num_chunks FROM timescaledb_information.hypertables;"


Check Telegraf logs

sudo tail -f /var/log/telegraf/telegraf.log

Create basic monitoring queries

Use these SQL queries to analyze your metrics data and create custom monitoring dashboards.

CPU utilization analysis

Query average CPU usage over time with breakdown by core and host.

-- Average CPU usage by host over last 24 hours
SELECT 
    time_bucket('1 hour', "time") AS hour,
    host,
    ROUND(100 - AVG(usage_idle), 2) as cpu_usage_percent
FROM cpu 
WHERE "time" > NOW() - INTERVAL '24 hours'
    AND cpu = 'cpu-total'
GROUP BY hour, host
ORDER BY hour DESC;

Memory usage trends

Monitor memory consumption patterns and identify potential issues.

-- Memory usage trends over last week
SELECT 
    time_bucket('6 hours', "time") AS period,
    host,
    ROUND(AVG(used_percent), 2) as avg_memory_used,
    ROUND(MAX(used_percent), 2) as max_memory_used
FROM mem 
WHERE "time" > NOW() - INTERVAL '7 days'
GROUP BY period, host
ORDER BY period DESC;

Disk I/O performance

Analyze disk performance and identify I/O bottlenecks across different storage devices.

-- Top disk I/O by device over last 4 hours
SELECT 
    time_bucket('30 minutes', "time") AS period,
    host,
    name as device,
    ROUND(AVG(read_bytes)/1024/1024, 2) as avg_read_mb,
    ROUND(AVG(write_bytes)/1024/1024, 2) as avg_write_mb
FROM diskio 
WHERE "time" > NOW() - INTERVAL '4 hours'
GROUP BY period, host, name
ORDER BY period DESC, (AVG(read_bytes) + AVG(write_bytes)) DESC;

Common issues

SymptomCauseFix
Telegraf connection refusedPostgreSQL not accepting connectionsCheck sudo systemctl status postgresql and /etc/postgresql/16/main/pg_hba.conf
Permission denied for databaseIncorrect user privilegesGrant privileges: sudo -u postgres psql -c "GRANT ALL ON SCHEMA public TO telegraf;"
TimescaleDB extension not foundExtension not enabledRun: sudo -u postgres psql -d telegraf -c "CREATE EXTENSION timescaledb;"
No metrics in databaseTelegraf configuration errorCheck logs: sudo journalctl -u telegraf -f and verify config syntax
High memory usageToo frequent metric collectionIncrease interval in /etc/telegraf/telegraf.conf agent section
Continuous aggregates not updatingRefresh policy not workingManual refresh: CALL refresh_continuous_aggregate('cpu_usage_hourly', NULL, NULL);

Next steps

Running this in production?

Want this handled for you? Setting this up once is straightforward. Keeping it patched, monitored, backed up and performant across environments is the harder part. See how we run infrastructure like this for European SaaS and e-commerce teams.

Automated install script

Run this to automate the entire setup

#timescaledb #telegraf #postgresql #metrics #monitoring

Related tutorials

monitoring Intermediate

Configure advanced network monitoring with SmokePing for detailed latency analysis

Set up SmokePing for detailed network latency monitoring with visual graphs, multi-target analysis, and advanced alerting. Perfect for tracking network performance trends and identifying connectivity issues across multiple hosts.

45 min 4 distros 18 views
monitoring Intermediate

Set up PHP application performance monitoring with APM tools and real-time metrics collection

Monitor PHP application performance with New Relic APM and Elastic APM, implement custom metrics collection, and set up comprehensive Grafana dashboards for real-time application observability and performance optimization.

35 min 4 distros 13 views
monitoring Intermediate

Monitor cron jobs and systemd timers with Prometheus and Grafana alerting

Set up comprehensive monitoring for scheduled tasks using Prometheus node_exporter and custom metrics collection. Configure Grafana dashboards and alerting rules to track job success, failures, and missed executions across your infrastructure.

45 min 4 distros 62 views
Browse all tutorials in our complete directory →

Need help?

Don't want to manage this yourself?

We handle managed devops services for businesses that depend on uptime. From initial setup to ongoing operations.

Learn about our managed devops services Talk to an engineer