Use PG as Prometheus Backend

Persist prometheus metrics with PostgreSQL + TimescaleDB through Promscale

It is not recommended to use PostgreSQL as a backend for Prometheus, but it is a good opportunity to understand the Pigsty deployment system.

Postgres Preparation

vi pigsty.yml # dbuser_prometheus  prometheus

pg_databases:                           # define business users/roles on this cluster, array of user definition
  - { name: prometheus, owner: dbuser_prometheus , revokeconn: true, comment: prometheus primary database }
pg_users:                           # define business users/roles on this cluster, array of user definition
  - {name: dbuser_prometheus , password: DBUser.Prometheus ,pgbouncer: true , createrole: true,  roles: [dbrole_admin], comment: admin user for prometheus database }

Create the database and user:

bin/pgsql-user  pg-meta  dbuser_prometheus
bin/pgsql-db    pg-meta  prometheus

Check the connection string:

psql postgres://dbuser_prometheus:[email protected]:5432/prometheus -c 'CREATE EXTENSION timescaledb;'

Promscale Configuration

Install promscale package:

yum install -y promscale 

If the package is not available in the default repository, you can download it directly:

sudo rpm -ivh promscale_0.6.1_Linux_x86_64.rpm

Edit the promscale configuration file /etc/sysconfig/promscale.conf


Launch promscale service, it will create schema in prometheus database.

# launch 
cat /usr/lib/systemd/system/promscale.service
systemctl start promscale && systemctl status promscale

Prometheus Configuration

Prometheus can use Remote Write/ Remote Read to store metrics in Postgres via Promscale.

Edit the Prometheus configuration file:

vi /etc/prometheus/prometheus.yml

Add the following configuration to the remote_write and remote_read sections:

  - url: ""
  - url: ""

Metrics are loaded into Postgres after Prometheus is restarted.

systemctl restart prometheus

Last modified 2024-06-25: refactor software & applet (405c5ea)