IDC网络质量监控之（十）PostgreSQL+TimescaleDB打造Prometheus持久化存储

环境介绍

    TimescaleDB是基于PostgreSQL数据库打造的一款时序数据库，插件化的形式，随着PostgreSQL的版本升级而升级
    系统环境：CentOS7+PostgreSQL12+timescaledb_12

第一：安装PostgreSQL的repo源
    yum install -y https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

第二：安装PostgreSQL12
    yum install centos-release-scl-rh gcc epel* -y      #这是安装llvm-toolset-7-clang等的依赖源
    yum install postgresql12 postgresql12-server postgresql12-contrib postgresql12-test     #最后一个包含了后面pg_prometheus需要的依赖
        #要不要考虑安装PostgreSQL的WebUI的图形管理工具
        #yum install -y pgadmin4

第三：安装timescaledb12
    #TimescaleDB的YUM包已经被集成到PostgreSQL社区源里面，直接装就好
    yum install timescaledb_12 -y

第四：安装timescaledb-postgresql-12的二进制版本，不然可能因为./timescale-prometheus 使用license \"ApacheOnly\" 导致无法使用 
    #https://docs.timescale.com/latest/getting-started/installation/rhel-centos/installation-yum    这是介绍的文章

tee /etc/yum.repos.d/timescale_timescaledb.repo <<EOL
[timescale_timescaledb]
name=timescale_timescaledb
baseurl=https://packagecloud.io/timescale/timescaledb/el/7/\$basearch
repo_gpgcheck=1
gpgcheck=0
enabled=1
gpgkey=https://packagecloud.io/timescale/timescaledb/gpgkey
sslverify=1
sslcacert=/etc/pki/tls/certs/ca-bundle.crt
metadata_expire=300
EOL

    yum update -y && yum install -y timescaledb-postgresql-12

第五：初始化数据库（初始化完成后会在/var/lib/pgsql/12/data 生成一系列的文件包括其配置文件 postgresql.conf ）
    #使用默认数据目录，默认数据目录在/var/lib/pgsql/12/data
    /usr/pgsql-12/bin/postgresql-12-setup initdb

第六：让PostgreSQL加载timescaledb插件
    #echo "shared_preload_libraries = 'timescaledb'" >> /var/lib/pgsql/12/data/postgresql.conf
    echo "shared_preload_libraries = 'timescaledb, pg_prometheus'" >> /var/lib/pgsql/12/data/postgresql.conf

第七：启动数据库（会开放5432端口）
    systemctl start postgresql-12.service
    systemctl enable postgresql-12.service
    systemctl restart postgresql-12.service

第八：修改postgres账号密码
PostgreSQL安装成功之后，会默认创建一个名为postgres的Linux用户，初始化数据库后，会有名为postgres的数据库，来存储数据库的基础信息，例如用户信息等等，相当于MySQL中默认的名为mysql数据库
postgres数据库中会初始化一名超级用户postgres，为了方便我们使用postgres账号进行管理，我们可以修改该账号的密码
1、进入PostgreSQL命令行（通过su命令切换linux用户为postgres会自动进入命令行）
    su - postgres
2、修改密码   
    psql -c "alter user postgres with password '123456'"

下载编译pg

    wget https://github.com/timescale/pg_prometheus/archive/0.2.2.tar.gz
    tar xf 0.2.2.tar.gz
    mv pg_prometheus-0.2.2/ pg_prometheus
    cd pg_prometheus/
    export PATH=/usr/pgsql-12/bin:$PATH
    make
    make install

创建Prometheus数据库

    su - postgres
    psql -U postgres    #登录数据库
    #CREATE ROLE prometheus WITH LOGIN PASSWORD '123456';   #创建用户
    CREATE USER prometheus WITH LOGIN PASSWORD '123456';    #创建用户
    CREATE DATABASE prometheus OWNER prometheus;        #创建数据库
    \connect prometheus;            #切换数据库
    CREATE EXTENSION timescaledb;   #添加Extension
    CREATE EXTENSION pg_prometheus; #添加Extension
    GRANT ALL PRIVILEGES ON DATABASE prometheus TO prometheus;      #授权
    #SELECT create_prometheus_table('metrics');         #创建表，只使用pg_prometheus
    SELECT create_prometheus_table('metrics',use_timescaledb=>true);    #让pg_prometheus使用TimescaleDB扩展。

    SELECT set_default_retention_period(180 * INTERVAL '1 day')     #配置数据保留，默认情况下，数据存储90天，然后删除

下载timescale-prometheus，这是Prometheus连接postgresql的中间件

    wget https://github.com/timescale/timescale-prometheus/releases/download/0.1.0-alpha.3.1/timescale-prometheus_0.1.0-alpha.3.1_Linux_x86_64.tar.gz
    tar xf timescale-prometheus_0.1.0-alpha.3.1_Linux_x86_64.tar.gz

#启动连接件
    ./timescale-prometheus -db-port=5432  -web-telemetry-path="/metrics" -db-name=prometheus -db-password=123456 -db-user=postgres

修改postgresql的配置文件

A：修改监听地址  vi /var/lib/pgsql/12/data/postgresql.conf
listen_addresses = '*'

B：允许访问的配置文件  vi /var/lib/pgsql/12/data/pg_hba.conf
    # TYPE  DATABASE        USER            ADDRESS                 METHOD
    # 默认配置数据库主机以socket、127.0.0.1、::1的方式连接数据库可以跳过认证阶段直接登录数据库
    local   all             all                                     trust
    host    all             all             127.0.0.1/32            trust
    host    all             all             ::1/128                 trust
    # 配置app_user可以基于用户名密码+MD5加密算法从0.0.0.0/0访问数据库服务，并且是能访问所有databases
    host    all             all             0.0.0.0/0               md5

C：这是额外的介绍，postgresql存储扩展插件(extension)的位置  /usr/pgsql-12/share/extension/

D：重启数据库
    systemctl restart postgresql-12.service

Prometheus之配置调整，添加后端的独写接口

    vi /root/prometheus/prometheus.yml

remote_write:
    - url: "http://timescaledb:9201/write"
remote_read:
    - url: "http://timescaledb:9201/read"

PostgreSQL常见用句

l                                          罗列现有数据库 
CREATE database test;       创建数据库
\c test                                 切换到test数据库
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;             加载TimescaleDB的extensions
CREATE USER test WITH PASSWORD 'test';          #新建用户
GRANT ALL PRIVILEGES ON DATABASE mydb TO test;          #赋予指定账户指定数据库所有权限
REVOKE ALL PRIVILEGES ON DATABASE mydb TO test          #移除指定账户指定数据库所有权限

FAQ 之 一 ：因非二进制版本license不支持，导致的异常，解决办法，请参看文中 第四步骤

[root@localhost ~]# ./timescale-prometheus -db-port=5432 -web-telemetry-path="/metrics" -db-name=prometheus -db-password=test@123 -db-user=postgres
{"caller":"log.go:46","config":"&{listenAddr::9201 telemetryPath:/metrics pgmodelCfg:{host:localhost port:5432 user:postgres password:**** database:prometheus sslMode:disable dbConnectRetries:0} logLevel:debug haGroupLockID:0 restElection:false prometheusTimeout:-1 electionInterval:5000000000 migrate:true}","level":"info","ts":"2020-05-17T05:01:16.458Z"}
{"caller":"log.go:51","level":"warn","msg":"No adapter leader election. Group lock id is not set. Possible duplicate write load if running adapter in high-availability mode","ts":"2020-05-17T05:01:16.459Z"}
{"caller":"log.go:51","cause":"ERROR: could not open extension control file \"/usr/pgsql-12/share/extension/timescale_prometheus_extra.control\": No such file or directory (SQLSTATE 58P01)","level":"warn","msg":"timescale_prometheus_extra extension not installed","ts":"2020-05-17T05:01:16.535Z"}
{"caller":"log.go:46","level":"info","msg":"host=localhost port=5432 user=postgres dbname=prometheus password='****' sslmode=disable connect_timeout=10","ts":"2020-05-17T05:01:16.556Z"}
{"caller":"log.go:46","level":"info","msg":"Starting up...","ts":"2020-05-17T05:01:16.753Z"}
{"addr":":9201","caller":"log.go:46","level":"info","msg":"Listening","ts":"2020-05-17T05:01:16.753Z"}
{"caller":"log.go:51","err":"ERROR: functionality not supported under the current license \"ApacheOnly\", license (SQLSTATE 0A000)","level":"warn","msg":"Error sending samples to remote storage","num_samples":0,"ts":"2020-05-17T05:01:16.868Z"}
{"caller":"log.go:51","err":"ERROR: functionality not supported under the current license \"ApacheOnly\", license (SQLSTATE 0A000)","level":"warn","msg":"Error sending samples to remote storage","num_samples":0,"ts":"2020-05-17T05:01:17.048Z"}