PostgreSQL Metrics via Telegraf

This integration allows you to configure a Telegraf agent to send your metrics, in multiple formats, to Logit.io.

Telegraf is a flexible server agent equipped with plug-in support, useful for sending metrics and events from data sources like web servers, APIs, application logs, and cloud services.

To ship your metrics to Logit.io, we will integrate the relevant input and outputs.http plug-in into your Telegraf configuration file.

Choose the install for your operating system below to get started:

Windows

wget https://dl.influxdata.com/telegraf/releases/telegraf-1.19.2_windows_amd64.zip

Download and extract to: C:\Program Files\Logitio\telegraf\

Configuration file: C:\Program Files\Logitio\telegraf\

MacOS

brew install telegraf

Configuration file x86_64 Intel: /usr/local/etc/telegraf.conf Configuration file ARM (Apple Silicon): /opt/homebrew/etc/telegraf.conf

Ubuntu/Debian

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-get update
sudo apt-get install telegraf

Configuration file: /etc/telegraf/telegraf.conf

RedHat and CentOS

cat <<EOF | sudo tee /etc/yum.repos.d/influxdata.repo
[influxdata]
name = InfluxData Repository - Stable
baseurl = https://repos.influxdata.com/stable/\$basearch/main
enabled = 1
gpgcheck = 1
gpgkey = https://repos.influxdata.com/influxdata-archive_compat.key
EOF

sudo yum install telegraf

Configuration file: /etc/telegraf/telegraf.conf

SLES & openSUSE

zypper ar -f obs://devel:languages:go/ go
zypper in telegraf

Configuration file: /etc/telegraf/telegraf.conf

FreeBSD/PC-BSD

sudo pkg install telegraf

Configuration file: /etc/telegraf/telegraf.conf

Read more about how to configure data scraping and configuration options for Telegraf

The configuration file below is pre-configured to scrape the system metrics from your hosts, add the following code to the configuration file /etc/telegraf/telegraf.conf from the previous step.

# Read metrics from one or many postgresql servers
[[inputs.postgresql]]
  ## Specify address via a url matching:
  ##   postgres://[pqgotest[:password]]@localhost[/dbname]?sslmode=[disable|verify-ca|verify-full]&statement_timeout=...
  ## or a simple string:
  ##   host=localhost user=pqgotest password=... sslmode=... dbname=app_production
  ## Users can pass the path to the socket as the host value to use a socket
  ## connection (e.g. `/var/run/postgresql`).
  ##
  ## All connection parameters are optional.
  ##
  ## Without the dbname parameter, the driver will default to a database
  ## with the same name as the user. This dbname is just for instantiating a
  ## connection with the server and doesn't restrict the databases we are trying
  ## to grab metrics for.
  ##
  address = "host=localhost user=postgres sslmode=disable"

  ## A custom name for the database that will be used as the "server" tag in the
  ## measurement output. If not specified, a default one generated from
  ## the connection address is used.
  # outputaddress = "db01"

  ## connection configuration.
  ## maxlifetime - specify the maximum lifetime of a connection.
  ## default is forever (0s)
  ##
  ## Note that this does not interrupt queries, the lifetime will not be enforced
  ## whilst a query is running
  # max_lifetime = "0s"

  ## A  list of databases to explicitly ignore.  If not specified, metrics for all
  ## databases are gathered.  Do NOT use with the 'databases' option.
  # ignored_databases = ["postgres", "template0", "template1"]

  ## A list of databases to pull metrics about. If not specified, metrics for all
  ## databases are gathered.  Do NOT use with the 'ignored_databases' option.
  # databases = ["app_production", "testing"]

  ## Whether to use prepared statements when connecting to the database.
  ## This should be set to false when connecting through a PgBouncer instance
  ## with pool_mode set to transaction.
  prepared_statements = true

Read more about how to configure data scraping and configuration options for PostgreSQL

Once you have generated the configuration file, you need to set up the output plug-in to allow Telegraf to transmit your data to Logit.io in Prometheus format. This can be accomplished by incorporating the following code into your configuration file:

[[outputs.http]]
  
  url = "https://<your-metrics-username>:<your-metrics-password>@<your-metrics-stack-id>-vm.logit.io:0/api/v1/write"
  data_format = "prometheusremotewrite"

  [outputs.http.headers]
    Content-Type = "application/x-protobuf"
    Content-Encoding = "snappy"

Windows

telegraf.exe --service start

MacOS

telegraf --config telegraf.conf

Linux

sudo service telegraf start

for systemd installations

systemctl start telegraf

Data should now have been sent to your Stack.

View my data

If you don't see metrics take a look at How to diagnose no data in Stack below for how to diagnose common issues.

If you don't see data appearing in your Stack after following the steps, visit the Help Centre guide for steps to diagnose no data appearing in your Stack or Chat to support now.

To efficiently monitor and analyze PostgreSQL metrics across a distributed environment, a reliable and efficient metrics management solution is essential. Telegraf is an open-source server agent well-suited for this role, capable of gathering PostgreSQL metrics from numerous sources, including operational PostgreSQL instances, databases, and other relevant applications.

Telegraf offers a wide range of input plugins, enabling the collection of various metrics such as CPU usage, memory utilization, network traffic, and more. These are key to understanding PostgreSQL performance. To store and analyze these metrics, organizations can use Prometheus, an open-source monitoring and alerting toolkit known for its flexible querying language and impressive data visualization capabilities.

The process of transmitting PostgreSQL metrics from Telegraf to Prometheus involves configuring Telegraf to output metrics in the Prometheus format, and then setting up Prometheus to scrape these metrics from the Telegraf server. This involves setting up Telegraf to collect PostgreSQL metrics, outputting them in the Prometheus format, configuring Prometheus to retrieve these metrics from the Telegraf server, and then visually interpreting the data using Prometheus's dynamic querying and graphical visualization tools.

After the successful integration of the metrics into Prometheus, further analysis and visualization can be carried out using Grafana. Grafana is a leading open-source platform known for its monitoring and observability features, and is fully compatible with Prometheus. It allows users to create dynamic, interactive dashboards for a deeper dive into the metrics data, providing a comprehensive understanding of performance trends and potential issues within the PostgreSQL system.

If you need any further assistance with shipping your log data to Logit.io we're here to help you get started. Feel free to get in contact with our support team by sending us a message via live chat & we'll be happy to assist.