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"}
声明:本文为原创,作者为 辣条①号,转载时请保留本声明及附带文章链接:https://boke.wsfnk.com/archives/362.html
谢谢你请我吃辣条谢谢你请我吃辣条

如果文章对你有帮助,欢迎点击上方按钮打赏作者

最后编辑于:2023/3/3作者: 辣条①号

现在在做什么? 接下来打算做什么? 你的目标什么? 期限还有多少? 进度如何? 不负遇见,不谈亏欠!

暂无评论

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

arrow grin ! ? cool roll eek evil razz mrgreen smile oops lol mad twisted wink idea cry shock neutral sad ???