Postgres exporter

Installation of Prometheus postgres_exporter in production environment consists from following high level steps:

Install exporter;
Configure authentication;
Configure firewall;
Configure service for autostart and restart;
Check access to exporter from I&B monitoring platform.

1. Install Prometheus Postgres exporter.

Check latest version, available platforms on postgres_exporter GitHub page and make necessary changes in variables on next step.
Prepare environment variables to simplify installation and configuration process.

Bash

export EXPORTER_VERSION=0.17.1
export EXPORTER_PLATFORM=linux-amd64

Download exporter

Bash

wget https://github.com/prometheus-community/postgres_exporter/releases/download/v$EXPORTER_VERSION/postgres_exporter-$EXPORTER_VERSION.$EXPORTER_PLATFORM.tar.gz

Create directory /usr/local/bin/postgres_exporter and untar exporter into it.

Bash

sudo mkdir -p /usr/local/bin/postgres_exporter
sudo tar xvfz postgres_exporter-$EXPORTER_VERSION.$EXPORTER_PLATFORM.tar.gz -C /usr/local/bin/postgres_exporter --strip-components=1

Create prometheus user. If it already exist - go to step 6.

Fedora (RHEL, CentOS)

Bash

sudo useradd -M -s /sbin/nologin prometheus

-M (or --no-create-home): This option prevents the creation of a home directory for the new user;
-s /sbin/nologin: This option sets the user's login shell to /sbin/nologin. This effectively prevents the user from interactively logging into the system. While the user won't have a password set, this further ensures they cannot log in.

Debian (Ubuntu)

Bash

sudo useradd -s /sbin/nologin prometheus --no-create-home

Make prometheus user owner of postgres_exporter directory

Bash

sudo chown -hR prometheus:prometheus /usr/local/bin/postgres_exporter

2. Configure firewall

Configure firewall on resource

Postgres exporter uses default port 9187, so allow it in firewall on resource.

Fedora (RHEL, CentOS)

Bash

sudo firewall-cmd --zone=public --add-port=9187/tcp --permanent
sudo systemctl reload firewalld

Check firewall configuration

Bash

sudo firewall-cmd --list-all

You should see in output:

Bash

ports: 9187/tcp

Debian (Ubuntu)

Bash

sudo ufw allow 9187

You should see in output:

Bash

Rules updated
Rules updated (v6)

Check firewall configuration

Bash

sudo ufw status

You should see in output:

Bash

To                             Action          From
--                             ------          ----
9187                           ALLOW           Anywhere

3. Configure authentication for exporter

Do the steps described in Exporters configuration → Authentication section.

4. Check configuration

Run postgres_exporter

Bash

/usr/local/bin/postgres_exporter/postgres_exporter --web.config.file=/usr/local/bin/postgres_exporter/web-config.yml

In output you should see:

Bash

... msg="TLS is enabled." ...

Check with browser if it is accessible at:

https://resource-hostname-or-ip:9187/metrics

After entering exporter’s user name and password you should see page like this:

Bash

# HELP go_gc_duration_seconds A summary of the wall-time pause (stop-the-world) duration in garbage collection cycles.
# TYPE go_gc_duration_seconds summary
go_gc_duration_seconds{quantile="0"} 2.2622e-05
...

Stop postgres exporter with Ctrl-c in terminal where you run it.

To monitor your databases need user with rights to connect and read metrics of database. You need to create it or use existing user.

Postgres exporter looks connection string from environment variable variable DATA_SOURCE_NAME.

For example run command with connecting parameters:

Bash

DATA_SOURCE_NAME="postgresql://db_user:db_password@127.0.01:5432/control?sslmode=require" /usr/local/bin/postgres_exporter/postgres_exporter

You need to replace db_user with your database user, db_password with that user's password, 127.0.0.1 with the IP address or network name of the database server, 5432 with the port number on which this server accepts connections, and set the sslmode=disable parameter if you are not using ssl for database connections, or set this parameter to sslmode=require if you are using it.

Run postgres_exporter start command:

Bash

DATA_SOURCE_NAME="postgresql://db_user:db_password@127.0.01:5432/control?sslmode=require" /usr/local/bin/postgres_exporter/postgres_exporter

if you NOT see

Bash

level=WARN source=main.go:141 msg="Failed to create PostgresCollector" err="empty dsn"

its good, you properly setup connect to database.

Make postgres_exporter service for autostart and restart.

Create file with your preferred text editor, for example nano …

Bash

sudo nano /etc/systemd/system/postgres_exporter.service

… and paste following text into it.

in string Environment="DATA_SOURCE_NAME=postgresql://db_user:db_password@127.0.01:5432/control?sslmode=disable"

You need to replace db_user with your database user, db_password with that user's password, 127.0.0.1 with the IP address or network name of the database server, 5432 with the port number on which this server accepts connections, and leave the sslmode=disable parameter unchanged if you are not using ssl for database connections, or change this parameter to sslmode=require if you are using it.

Bash

[Unit]
Description=postgres Exporter

[Service]
Environment="DATA_SOURCE_NAME=postgresql://db_user:db_password@127.0.01:5432/control?sslmode=require"
User=prometheus
ExecStart=/usr/local/bin/postgres_exporter/postgres_exporter  --web.config.file=/usr/local/bin/postgres_exporter/web-config.yml

[Install]
WantedBy=multi-user.target

Save file and exit editor.

Start service …

Bash

sudo systemctl daemon-reload
sudo systemctl start postgres_exporter
sudo systemctl enable postgres_exporter

… and check

sudo systemctl status postgres_exporter

You should see following output similar to:

In case of any problem, you should view log entries for service:

Bash

journalctl -u postgres_exporter.service -e

Now postgres exporter is ready to accept data and then expose it to Prometheus.