Prometheus远端数据存储PostgreSQL/TimescaleDB

概述

Prometheus的数据存储一般都是存放本地的 TSDB (时序数据库)中，使得Prometheus部署方便快捷，然而原生的 TSDB 对于大数据量的保存及查询支持不太友好，该数据库不能保证可靠性，且无法支持Prometheus集群架构。

对于这方面的完善，Prometheus提供了remote_write和remote_read的特性，支持将数据存储到远端和从远端读取数据的功能。当配置remote_write特性后，Prometheus会将采集到的指标数据通过HTTP的形式发送给适配器(Adaptor)，由适配器进行数据的写入。而remote_read特性则会向适配器发起查询请求，适配器根据请求条件从第三方存储服务中获取响应的数据。

Prometheus的存储方案有好几种选择，例如influxDB，Cortex，CrateDB，PostgreSQL/TimescaleDB。这些方案都可读可写，下面展示PostgreSQL/TimescaleDB方案的操作步骤。

实验环境

Centos7.6
Prometheus 2.33.3
PostgreSQL 12.1 （要求12以上）
Cmake 3.19.2 （要求3.11以上）
TimescaleDB 2.6.0
pg_prometheus 0.2.2
prometheus-postgresql-adapter 0.6.0

环境准备

部署PG

#下载安装包
wget https://ftp.postgresql.org/pub/source/v12.1/postgresql-12.1.tar.gz
#永久关闭selinux，防火墙
setenforce 0
systemctl stop firewalld.service
systemctl disable firewalld.service 
#创建用户
useradd postgres
passwd postgres
#安装依赖
yum install -y gcc gcc-c++  epel-release \
 clang libicu-devel perl-ExtUtils-Embed zlib-devel openssl \
 openssl-devel pam-devel libxml2-devel libxslt-devel openldap-devel \
 systemd-devel tcl-devel python-devel
#解压编译安装
tar -zxvf postgresql-12.1.tar.gz
cd postgresql-12.1
./configure --prefix=/usr/local/pgsql12 --without-readline --with-openssl
make world
make install
复制

环境变量配置

vi /home/postgres/.bashrc
export PGPORT=5432
export PGHOME=/usr/local/pgsql12
export PGDATA=/home/postgres/data
export PATH=$PGHOME/bin:$PATH
export LANG=en_US.UTF-8
export DATE='date +"%Y%m%d%H%M"'
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
source ~/.bashrc
复制

初始化数据库

su - postgres
initdb -D $PGDATA -U postgres --locale=en_US.UTF8 -E UTF8
复制

部署TimescaleDB

检查Cmake

如果Cmake版本超过3.11则跳过此步

cmake --version
复制

替换Cmake

#卸载cmake
yum -y remove cmake
#下载解压
wget https://cmake.org/files/v3.19/cmake-3.19.2.tar.gz
tar -zxvf cmake-3.19.2.tar.gz && cd cmake-3.19.2
#编译安装Cmake
./bootstrap
gmake
gmake install
#检查版本
/usr/local/bin/cmake --version
ln -s /usr/local/bin/cmake /bin/
cmake --version
复制

获取编译TimescaleDB

最好使用postgres用户操作，方便读取环境变量

wget https://github.com/timescale/timescaledb/archive/refs/tags/2.6.0.tar.gz
tar -zxvf 2.6.0.tar.gz && cd timescaledb-2.6.0
./bootstrap
cd ./build && make
make install
复制

出现如下信息则编译安装成功。

[ 12%] Built target sqlupdatescripts
[ 12%] Built target sqlfile
[ 55%] Built target timescaledb
[ 59%] Built target timescaledb-loader
[100%] Built target timescaledb-tsl
Install the project...
-- Install configuration: "Release"
-- Installing: /usr/local/pgsql12/share/extension/timescaledb.control
-- Installing: /usr/local/pgsql12/share/extension/timescaledb--2.6.0.sql
-- Installing: /usr/local/pgsql12/share/extension/timescaledb--2.5.2--2.6.0.sql
-- Installing: /usr/local/pgsql12/share/extension/timescaledb--2.5.1--2.6.0.sql
-- Installing: /usr/local/pgsql12/share/extension/timescaledb--2.5.0--2.6.0.sql
-- Installing: /usr/local/pgsql12/share/extension/timescaledb--2.4.2--2.6.0.sql
-- Installing: /usr/local/pgsql12/share/extension/timescaledb--2.4.1--2.6.0.sql
-- Installing: /usr/local/pgsql12/share/extension/timescaledb--2.4.0--2.6.0.sql
-- Installing: /usr/local/pgsql12/share/extension/timescaledb--2.3.1--2.6.0.sql
-- Installing: /usr/local/pgsql12/share/extension/timescaledb--2.3.0--2.6.0.sql
-- Installing: /usr/local/pgsql12/share/extension/timescaledb--2.2.1--2.6.0.sql
-- Installing: /usr/local/pgsql12/share/extension/timescaledb--2.2.0--2.6.0.sql
-- Installing: /usr/local/pgsql12/share/extension/timescaledb--2.1.1--2.6.0.sql
-- Installing: /usr/local/pgsql12/share/extension/timescaledb--2.1.0--2.6.0.sql
-- Installing: /usr/local/pgsql12/share/extension/timescaledb--2.0.2--2.6.0.sql
-- Installing: /usr/local/pgsql12/share/extension/timescaledb--2.0.1--2.6.0.sql
-- Installing: /usr/local/pgsql12/share/extension/timescaledb--2.0.0--2.6.0.sql
-- Installing: /usr/local/pgsql12/share/extension/timescaledb--2.0.0-rc4--2.6.0.sql
-- Installing: /usr/local/pgsql12/share/extension/timescaledb--2.0.0-rc3--2.6.0.sql
-- Installing: /usr/local/pgsql12/share/extension/timescaledb--2.0.0-rc2--2.6.0.sql
-- Installing: /usr/local/pgsql12/share/extension/timescaledb--2.0.0-rc1--2.6.0.sql
-- Installing: /usr/local/pgsql12/share/extension/timescaledb--1.7.5--2.6.0.sql
-- Installing: /usr/local/pgsql12/share/extension/timescaledb--1.7.4--2.6.0.sql
-- Installing: /usr/local/pgsql12/share/extension/timescaledb--1.7.3--2.6.0.sql
-- Installing: /usr/local/pgsql12/share/extension/timescaledb--1.7.2--2.6.0.sql
-- Installing: /usr/local/pgsql12/share/extension/timescaledb--1.7.1--2.6.0.sql
-- Installing: /usr/local/pgsql12/share/extension/timescaledb--1.7.0--2.6.0.sql
-- Installing: /usr/local/pgsql12/lib/timescaledb-2.6.0.so
-- Installing: /usr/local/pgsql12/lib/timescaledb.so
-- Installing: /usr/local/pgsql12/lib/timescaledb-tsl-2.6.0.so
复制

修改PG配置文件，保存退出，重启PG数据库进程加载插件

shared_preload_libraries = 'timescaledb'
复制

创建插件

postgres=# create extension timescaledb ;
复制

创建表

postgres=# CREATE TABLE conditions (time TIMESTAMP WITH TIME ZONE NOT NULL,device_id TEXT,temperature NUMERIC,humidity NUMERIC);
postgres=# SELECT create_hypertable('conditions', 'time');
复制

插入数据

postgres=# INSERT INTO conditions(time, device_id, temperature, humidity) VALUES (NOW(), 'weather-pro-000000', 84.1, 84.1);
INSERT INTO conditions VALUES (NOW(), 'weather-pro-000002', 71.0, 51.0),(NOW(), 'weather-pro-000003', 70.5, 50.5),(NOW(), 'weather-pro-000004', 70.0, 50.2);
复制

查询数据

postgres=# SELECT * FROM conditions LIMIT 10;
复制

到了此步骤，Centos7 postgresql v12安装时序数据库 TimescaleDB 操作完毕！

安装pg_prometheus扩展

wget https://github.com/timescale/pg_prometheus/archive/refs/tags/0.2.2.tar.gz
tar -zxvf 0.2.2.tar.gz && cd pg_prometheus-0.2.2/
make
make install
复制

修改PG配置文件，保存退出，重启PG数据库进程加载插件

shared_preload_libraries = 'timescaledb,pg_prometheus'
复制

创建插件

postgres=# create extension pg_prometheus ;
postgres=# SELECT create_prometheus_table('metrics');   #或者使用 SELECT create_prometheus_table('metrics',use_timescaledb=>true);
postgres=# INSERT INTO metrics VALUES ('cpu_usage{service="nginx",host="machine1"} 34.6 1494595898000');
postgres=# \d
                  List of relations
 Schema |         Name          |   Type   |  Owner   
--------+-----------------------+----------+----------
 public | metrics               | view     | postgres
 public | metrics_copy          | table    | postgres
 public | metrics_labels        | table    | postgres
 public | metrics_labels_id_seq | sequence | postgres
 public | metrics_values        | table    | postgres
(5 rows)
复制

部署prometheus-postgresql-adapter

下载解压即可使用

wget https://github.com/timescale/prometheus-postgresql-adapter/releases/download/v0.6.0/prometheus-postgresql-adapter-0.6.0-linux-amd64.tar.gz
tar -zxvf prometheus-postgresql-adapter-0.6.0-linux-amd64.tar.gz
复制

prometheus-postgresql-adapter使用说明

./prometheus-postgresql-adapter --help
Usage of ./prometheus-postgresql-adapter:
  -adapter-send-timeout duration
        The timeout to use when sending samples to the remote storage. [TS_PROM_ADAPTER_SEND_TIMEOUT] (default 30s)
  -leader-election-pg-advisory-lock-id int
        Unique advisory lock id per adapter high-availability group. Set it if you want to use leader election implementation based on PostgreSQL advisory lock. [TS_PROM_LEADER_ELECTION_PG_ADVISORY_LOCK_ID]
  -leader-election-pg-advisory-lock-prometheus-timeout slack
        Adapter will resign if there are no requests from Prometheus within a given timeout (0 means no timeout). Note: make sure that only one Prometheus instance talks to the adapter. Timeout value should be co-related with Prometheus scrape interval but add enough slack to prevent random flips. [TS_PROM_LEADER_ELECTION_PG_ADVISORY_LOCK_PROMETHEUS_TIMEOUT] (default -1ns)
  -leader-election-rest
        Enable REST interface for the leader election [TS_PROM_LEADER_ELECTION_REST]
  -log-level string
        The log level to use [ "error", "warn", "info", "debug" ]. [TS_PROM_LOG_LEVEL] (default "debug")
  -pg-copy-table string
        Override default table to COPY data to [TS_PROM_PG_COPY_TABLE]
  -pg-database string
        The PostgreSQL database [TS_PROM_PG_DATABASE] (default "postgres")
  -pg-db-connect-retries int
        How many times to retry connecting to the database [TS_PROM_PG_DB_CONNECT_RETRIES]
  -pg-host string
        The PostgreSQL host [TS_PROM_PG_HOST] (default "localhost")
  -pg-max-idle-conns int
        The max number of idle connections to the database [TS_PROM_PG_MAX_IDLE_CONNS] (default 10)
  -pg-max-open-conns int
        The max number of open connections to the database [TS_PROM_PG_MAX_OPEN_CONNS] (default 50)
  -pg-password string
        The PostgreSQL password [TS_PROM_PG_PASSWORD]
  -pg-port int
        The PostgreSQL port [TS_PROM_PG_PORT] (default 5432)
  -pg-prometheus-chunk-interval duration
        The size of a time-partition chunk in TimescaleDB [TS_PROM_PG_PROMETHEUS_CHUNK_INTERVAL] (default 12h0m0s)
  -pg-prometheus-log-samples
        Log raw samples to stdout [TS_PROM_PG_PROMETHEUS_LOG_SAMPLES]
  -pg-prometheus-normalized-schema
        Insert metric samples into normalized schema [TS_PROM_PG_PROMETHEUS_NORMALIZED_SCHEMA] (default true)
  -pg-read-only
        Read-only mode. Don't write to database. Useful when pointing adapter to read replica [TS_PROM_PG_READ_ONLY]
  -pg-schema string
        The PostgreSQL schema [TS_PROM_PG_SCHEMA]
  -pg-ssl-mode string
        The PostgreSQL connection ssl mode [TS_PROM_PG_SSL_MODE] (default "disable")
  -pg-table string
        Override prefix for internal tables. It is also a view name used for querying [TS_PROM_PG_TABLE] (default "metrics")
  -pg-use-timescaledb
        Use timescaleDB [TS_PROM_PG_USE_TIMESCALEDB] (default true)
  -pg-user string
        The PostgreSQL user [TS_PROM_PG_USER] (default "postgres")
  -scheduled-election-interval duration
        Interval at which scheduled election runs. This is used to select a leader and confirm that we still holding the advisory lock. [TS_PROM_SCHEDULED_ELECTION_INTERVAL] (default 5s)
  -web-listen-address string
        Address to listen on for web endpoints. [TS_PROM_WEB_LISTEN_ADDRESS] (default ":9201")
  -web-telemetry-path string
        Address to listen on for web endpoints. [TS_PROM_WEB_TELEMETRY_PATH] (default "/metrics")
复制

启动prometheus-postgresql-adapter

nohup ./prometheus-postgresql-adapter -pg-port 5432 --pg-user postgres 2>&1 &
复制

使用prometheus连接adapter端口

remote_write和remote_read的具体配置参考以下内容：

remote_write:
    url: <string>
    [ remote_timeout: <duration> | default = 30s ]
    write_relabel_configs:
    [ - <relabel_config> ... ]
    basic_auth:
    [ username: <string> ]
    [ password: <string> ]
    [ bearer_token: <string> ]
    [ bearer_token_file: /path/to/bearer/token/file ]
    tls_config:
    [ <tls_config> ]
    [ proxy_url: <string> ]
remote_read:
    url: <string>
    required_matchers:
    [ <labelname>: <labelvalue> ... ]
    [ remote_timeout: <duration> | default = 30s ]
    [ read_recent: <boolean> | default = false ]
    basic_auth:
    [ username: <string> ]
    [ password: <string> ]
    [ bearer_token: <string> ]
    [ bearer_token_file: /path/to/bearer/token/file ]
    [ <tls_config> ]
    [ proxy_url: <string> ]
复制

完成前面组件的部署后,只要在Prometheus中配置远程读写功能,并指定对应的url和端口即可。

remote_write:
  - url: "http://192.168.134.129:9201/write"
remote_read:
  - url: "http://192.168.134.129:9201/read"
复制

测试使用

PG数据库里查询

select * from metrics_labels  where metric_name = 'ALERTS' order by id desc limit 10;
复制

Prometheus界面


数据呈现一致之后，我们可以试着停止Prometheus服务，同时删除本地data目录的监控数据，模拟Promthues数据丢失的情况后重启服务。重新打开Prometheus后，如果还可以正常查询到本地存储已删除的历史数据记录，则代表配置正常。

总结

TimescaleDB的编译安装不仅会检查Cmake的版本而且会检查PG的版本，我安装过程中提示Cmake不能低于3.11，只支持PG的12，13，14版本，yum安装TimescaleDB也是可行的。
发现PG远程存储还有不足之处，存储空间来看，TSDB和PG数据库存放同样历史周期的的metrics，PG总是会比本地存储多两倍以上空间。

参考文档：
https://github.com/timescale/timescaledb/
https://github.com/timescale/pg_prometheus
https://github.com/timescale/prometheus-postgresql-adapter
https://blog.51cto.com/u_14065119/4594232?b=totalstatistic