概述
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 [] 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
