Prometheus遠端數據存儲方案之PostgreSQL

今天看到德哥分享的基于postgresql的prometheus數據遠端存儲方案，于是有了本文的實驗。

通常，prometheus的外置存儲方案有好幾種選擇，例如influxdb，m3db， es，postgresql。

m3db一般在云原生環境下，用的更趁手些。

這里我們演示下postgresql的遠端存儲方案（可讀可寫），具體看下面的實驗。

實驗環境：

? ? CentOS7.4

? ? PG11-git-1921 編譯安裝版

參考文檔：?

? ? https://blog.csdn.net/qq_43303221/article/details/88574693#cmake_34_50? ?編譯安裝 TimescaleDB 擴展

? ? https://studygolang.com/articles/13522?fr=sidebar? ?遠端存儲的介紹

? ? https://github.com/timescale/prometheus-postgresql-adapter? ?prometheus-postgresql-adapter的官方文檔介紹

? ? https://prometheus.io/docs/prometheus/latest/configuration/configuration/#remote_write? 官方文檔

? ? https://github.com/digoal/blog/blob/master/201910/20191027_04.md? ? 德哥blog

? ? https://github.com/timescale/pg_prometheus? ? ?pg_prometheus擴展的官方文檔

1、安裝llvm

yum?install?centos-release-scl-rh
yum?install?llvm-toolset-7-clang
yum?install?llvm5.0?
yum?install?llvm5.0-libs
yum?install?llvm5.0-devel
yum?install?clang

2、安裝cmake-3.4以上的版本

cd?/usr/local/
wget?https://github.com/Kitware/CMake/releases/download/v3.16.0-rc2/cmake-3.16.0-rc2-Linux-x86_64.sh
sh?cmake-3.16.0-rc2-Linux-x86_64.sh
即可在當前路徑下安裝cmake-3.16

載入cmake-3.16到環境變量：
export?PATH=/usr/local/cmake-3.16.0-rc2-Linux-x86_64/bin:$PATH

3、編譯安裝PG11帶jit模塊

su?-?postgres

cd?/home/postgres/

wget??

yum?localinstall?epel-release-latest-7.noarch.rpm

yum?install?llvm5.0?llvm5.0-devel?clang

cd?/home/postgres/pg_sources/postgresql-11
./configure?--prefix=/usr/local/pgsql-11.5-jit-1921?\
--with-python?--with-perl?--with-tcl?--with-pam?\
--with-openssl?--with-libxml?--with-libxslt?\
--with-llvm?LLVM_CONFIG='/usr/lib64/llvm5.0/bin/llvm-config'

make?
make?install?
然后，還有些數據庫的基礎配置，這里就不細說了。

4、安裝TimescaleDB擴展

su?-?postgres
cd?/home/postgres/
cd?timescaledb-1.4.2/

export?PATH=/usr/local/pgsql-11.5-jit-1921/bin:$PATH

./bootstrap?輸入y

cd?./build?&&?make

make?install??日志如下：

[??0%]?Built?target?sqlfile
[?25%]?Built?target?sqlupdatescripts
[?81%]?Built?target?timescaledb
[?85%]?Built?target?timescaledb-loader
[100%]?Built?target?timescaledb-tsl
Install?the?project...
--?Install?configuration:?"Release"
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb.control
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.4.1--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.4.1--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.4.0--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.3.2--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.3.1--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.3.0--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.2.2--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.2.1--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.2.0--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.1.1--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.1.0--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.0.1--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.0.0--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.0.0-rc3--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.0.0-rc2--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.0.0-rc1--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.12.1--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.12.0--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.11.0--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.10.1--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.10.0--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.9.2--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.9.1--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.9.0--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.8.0--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.7.1--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.7.0--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.6.1--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.6.0--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.5.0--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.4.2--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.4.1--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.4.0--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.3.0--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.2.0--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.1.0--1.4.2.sql
--?Installing:?/usr/local/pgsql-11.5-jit-1921/lib/timescaledb-1.4.2.so
--?Installing:?/usr/local/pgsql-11.5-jit-1921/lib/timescaledb.so
--?Installing:?/usr/local/pgsql-11.5-jit-1921/lib/timescaledb-tsl-1.4.2.so

即可完成timescaledb擴展的安裝

編輯pg的配置文件，修改如下：

shared_preload_libraries?=?'timescaledb'

完成后重啟PG進程

然后，登錄到PG中，創建擴展：

postgres=#?CREATE?EXTENSION?timescaledb;

5、安裝pg_prometheus擴展

su?-?postgres
cd?/home/postgres/
export?PATH=/usr/local/pgsql-11.5-jit-1921/bin:$PATH
USE_PGXS=1?make??
USE_PGXS=1?make?install

編輯pg的配置文件，修改如下：

shared_preload_libraries?=?'timescaledb,pg_prometheus'

完成后重啟PG進程

然后，登錄到PG中，創建擴展：

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)

6、啟動prometheus-postgresql-adapter

直接到https://github.com/timescale/prometheus-postgresql-adapter?下載解壓prometheus-postgresql-adapter

./prometheus-postgresql-adapter?-pg-port?1921?--pg-user?postgres??即可在前臺啟動adapter進程

7、使用prometheus連接adapter端口：

vim /usr/local/prometheus/prometheus.yml? 在最后加2個配置；

remote_write:
?-?url:?"http://192.168.2.4:9201/write"
???write_relabel_configs:
??????-?source_labels:?[__name__]
????????regex:?'go_.*'
????????action:?drop
remote_read:
??-?url:?"http://192.168.2.4:9201/read"
????read_recent:?false

說明： 我這里設置的read_recent為false，表示查詢prometheus近期數據不要到PG庫里來撈數據，而是直接查prometheus本地緩存的數據，這樣提高查詢的速度并且能降低PG庫的負載。

不足：

? ? 目前的配置來看，我們同事運行的prometheus和PG庫， 存放同樣歷史周期的的metrics，PG庫的體積遠遠超過了prometheus的體積。

? ? PG庫的metrics_values表已經 40 MB，此時的prometheus的data目錄才 16MB 。 磁盤空間占用上，相差接近3倍。

優化方案待定：

? ? 調整remote_write 寫入策略，通過drop操作來降低存儲到PG的metrics的體積？