Prometheus使用RemoteStorage将监控数据写入TimescaleDB

说明

• prometheus-postgresql-adapter最新版本是2018-10-17发布的v0.4.1
• 不保证ctrl+c和ctrl+v能直接跑起来
• 仅用于验证功能
• 记录实验操作过程

实验环境

主机名	IP地址	说明
timescaledb	172.16.80.201	部署TimescaleDB和prometheus-postgresql-adapter
prometheus	172.16.80.202	部署prometheus和node_exporter

部署TimescaleDB

参考文档

• 【PostgreSQL10安装部署和初始化】
• 【PostgreSQL10搭建时间序列数据库TimescaleDB】

添加YUM源

• 使用清华大学的RPM文件

yum install -y https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

• 替换YUM源地址

sed -e 's,download.postgresql.org/pub,mirrors4.tuna.tsinghua.edu.cn/postgresql,g' -i /etc/yum.repos.d/pgdg-redhat-all.repo

安装PostgreSQL10

yum install -y postgresql10 \
               postgresql10-server \
               postgresql10-contrib \
               postgresql10-test

安装TimescaleDB

yum install -y timescaledb_10

安装pg_prometheus

• 安装编译环境

yum install -y postgresql10-devel make gcc make

• 下载源代码

wget -O - https://github.com/timescale/pg_prometheus/archive/0.2.1.tar.gz | tar xz

• 切换目录

cd pg_prometheus-0.2.1

• 编译安装

export PATH=/usr/pgsql-10/bin:$PATH
make && make install

初始化数据库

• 切换到postgres用户

su - postgres

• 初始化数据库

/usr/pgsql-10/bin/initdb --encoding=UTF-8  \
                         --local=en_US.UTF8 \
                         --username=postgres \
                         --pwprompt \
                         --pgdata=$PGDATA \
                         --data-checksums

配置数据库

• postgresql.conf

最重要的是添加shared_preload_libraries = 'timescaledb, pg_prometheus'

# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
#   name = value
#
# (The "=" is optional.)  Whitespace may be used.  Comments are introduced with
# "#" anywhere on a line.  The complete list of parameter names and allowed
# values can be found in the PostgreSQL documentation.
#
# The commented-out settings shown in this file represent the default values.
# Re-commenting a setting is NOT sufficient to revert it to the default value;
# you need to reload the server.
#
# This file is read on server startup and when the server receives a SIGHUP
# signal.  If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, run "pg_ctl reload", or execute
# "SELECT pg_reload_conf()".  Some parameters, which are marked below,
# require a server shutdown and restart to take effect.
#
# Any parameter can also be given as a command-line option to the server, e.g.,
# "postgres -c log_connections=on".  Some parameters can be changed at run time
# with the "SET" SQL command.
#
# Memory units:  kB = kilobytes        Time units:  ms  = milliseconds
#                MB = megabytes                     s   = seconds
#                GB = gigabytes                     min = minutes
#                TB = terabytes                     h   = hours
#                                                   d   = days


#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
#data_directory = 'ConfigDir'
#hba_file = 'ConfigDir/pg_hba.conf'
#ident_file = 'ConfigDir/pg_ident.conf'
#external_pid_file = ''

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*'
port = 5432
# max_connections默认是100
max_connections = 500
superuser_reserved_connections = 3

# - TCP settings -
# 默认值为0，即使用系统默认值
#tcp_keepalives_idle = 0
#tcp_keepalives_interval = 0
#tcp_keepalives_count = 0
#tcp_user_timeout = 0

# - Authentication -
# 认证超时时间，默认1min
authentication_timeout = 30s
# 密码加密算法，默认md5
password_encryption = md5

# GSSAPI using Kerberos
#krb_server_keyfile = ''
#krb_caseins_users = off

# - SSL -
ssl = off
#ssl_ca_file = ''
#ssl_cert_file = 'server.crt'
#ssl_crl_file = ''
#ssl_key_file = 'server.key'
#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
#ssl_prefer_server_ciphers = on
#ssl_ecdh_curve = 'prime256v1'
#ssl_min_protocol_version = 'TLSv1'
#ssl_max_protocol_version = ''
#ssl_dh_params_file = ''
#ssl_passphrase_command = ''
#ssl_passphrase_command_supports_reload = off

#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------
# - Memory -
shared_buffers = 128MB
#huge_pages = try
#temp_buffers = 8MB
#max_prepared_transactions = 0
#work_mem = 4MB
#maintenance_work_mem = 64MB
#autovacuum_work_mem = -1
#max_stack_depth = 2MB
#shared_memory_type = mmap
dynamic_shared_memory_type = posix

# - Disk -
#temp_file_limit = -1

# - Kernel Resources -
#max_files_per_process = 1000
shared_preload_libraries = 'timescaledb, pg_prometheus'

# - Cost-Based Vacuum Delay -
#vacuum_cost_delay = 0
#vacuum_cost_page_hit = 1
#vacuum_cost_page_miss = 10
#vacuum_cost_page_dirty = 20
#vacuum_cost_limit = 200

# - Background Writer -
#bgwriter_delay = 200ms
#bgwriter_lru_maxpages = 100
#bgwriter_lru_multiplier = 2.0
#bgwriter_flush_after = 0

# - Asynchronous Behavior -
#effective_io_concurrency = 1
#max_worker_processes = 8
#max_parallel_maintenance_workers = 2
#max_parallel_workers_per_gather = 2
#parallel_leader_participation = on
#max_parallel_workers = 8
#old_snapshot_threshold = -1
#backend_flush_after = 0

#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------
# - Settings -
wal_level = replica
fsync = on
synchronous_commit = on
wal_sync_method = fdatasync
#full_page_writes = on
#wal_compression = off
# 如需要用pg_rewind修复WAL的timeline, 需要打开wal_log_hints
# 但是开启它会导致写wal变多, 请斟酌
wal_log_hints = off
#wal_init_zero = on
#wal_recycle = on
#wal_buffers = -1
#wal_writer_delay = 200ms
#wal_writer_flush_after = 1MB
#commit_delay = 0
#commit_siblings = 5

# - Checkpoints -
#checkpoint_timeout = 5min
max_wal_size = 1GB
min_wal_size = 80MB
#checkpoint_completion_target = 0.5
#checkpoint_flush_after = 0
#checkpoint_warning = 30s

# - Archiving -
#archive_mode = off
#archive_command = ''
#archive_timeout = 0

#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------
# - Sending Server(s) -
max_wal_senders = 10
# wal_keep_segments可以设置大一点，每个segments大小16MB
# 这样在备库不会因为wal receiver启动太慢导致所需wal在主库被删除
wal_sender_timeout = 60s
#max_replication_slots = 10
#track_commit_timestamp = off

# - Master Server -
#synchronous_standby_names = ''
#vacuum_defer_cleanup_age = 0

# - Standby Servers -
#hot_standby = on
#max_standby_archive_delay = 30s
#max_standby_streaming_delay = 30s
#wal_receiver_status_interval = 10s
#hot_standby_feedback = off
#wal_receiver_timeout = 60ss
#wal_retrieve_retry_interval = 5s

# - Subscribers -
#max_logical_replication_workers = 4
#max_sync_workers_per_subscription = 2

#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------
# - Planner Method Configuration -
#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_indexonlyscan = on
#enable_material = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on

# - Planner Cost Constants -
#seq_page_cost = 1.0
#random_page_cost = 4.0
#cpu_tuple_cost = 0.01
#cpu_index_tuple_cost = 0.005
#cpu_operator_cost = 0.0025
#parallel_tuple_cost = 0.1
#parallel_setup_cost = 1000.0
#min_parallel_table_scan_size = 8MB
#min_parallel_index_scan_size = 512kB
#effective_cache_size = 4GB

# - Genetic Query Optimizer -
#geqo = on
#geqo_threshold = 12
#geqo_effort = 5
#geqo_pool_size = 0
#geqo_generations = 0
#geqo_selection_bias = 2.0
#geqo_seed = 0.0

# - Other Planner Options -
#default_statistics_target = 100
#constraint_exclusion = partition
#cursor_tuple_fraction = 0.1
#from_collapse_limit = 8
#join_collapse_limit = 8
#force_parallel_mode = off

#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------
# - Where to Log -
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y%m%d.log'
#log_file_mode = 0600
log_truncate_on_rotation = off
log_rotation_age = 1d
log_rotation_size = 0
# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
#syslog_sequence_numbers = on
#syslog_split_messages = on

# - When to Log -
#log_min_messages = warning 
#log_min_error_statement = error
log_min_duration_statement = 0 

# - What to Log -
#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = on
log_checkpoints = on 
log_connections = on 
log_disconnections = on 
log_duration = on 
log_error_verbosity = verbose
#log_hostname = off
log_line_prefix = '%m [%p]: user=%u,db=%d '
log_lock_waits = on
#log_statement = 'none'
#log_replication_commands = off
#log_temp_files = -1
log_timezone = 'PRC'

# - Process Title -
#cluster_name = ''
#update_process_title = on

#------------------------------------------------------------------------------
# RUNTIME STATISTICS
#------------------------------------------------------------------------------
# - Query/Index Statistics Collector -
track_activities = on
track_counts = on
track_io_timing = on
track_functions = none
track_activity_query_size = 1024
stats_temp_directory = 'pg_stat_tmp'

# - Statistics Monitoring -
log_parser_stats = on
log_planner_stats = on
log_executor_stats = on
log_statement_stats = off

#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------
#autovacuum = on
#log_autovacuum_min_duration = -1
#autovacuum_max_workers = 3
#autovacuum_naptime = 1min
#autovacuum_vacuum_threshold = 50
#autovacuum_analyze_threshold = 50
#autovacuum_vacuum_scale_factor = 0.2
#autovacuum_analyze_scale_factor = 0.1
#autovacuum_freeze_max_age = 200000000
#autovacuum_multixact_freeze_max_age = 400000000
#autovacuum_vacuum_cost_delay = 20ms
#autovacuum_vacuum_cost_limit = -1

#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------
# - Statement Behavior -
#client_min_messages = notice
#search_path = '"$user", public'
#default_tablespace = ''
#temp_tablespaces = ''
#check_function_bodies = on
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = off
#default_transaction_deferrable = off
#session_replication_role = 'origin'
#statement_timeout = 0
#lock_timeout = 0
#idle_in_transaction_session_timeout = 0
#vacuum_freeze_min_age = 50000000
#vacuum_freeze_table_age = 150000000
#vacuum_multixact_freeze_min_age = 5000000
#vacuum_multixact_freeze_table_age = 150000000
#bytea_output = 'hex'
#xmlbinary = 'base64'
#xmloption = 'content'
#gin_fuzzy_search_limit = 0
#gin_pending_list_limit = 4MB

# - Locale and Formatting -
datestyle = 'iso, ymd'
#intervalstyle = 'postgres'
timezone = 'PRC'
#timezone_abbreviations = 'Default'
#extra_float_digits = 0
#client_encoding = sql_ascii

# These settings are initialized by initdb, but they can be changed.
lc_messages = 'en_US.UTF-8'			# locale for system error message strings
lc_monetary = 'en_US.UTF-8'			# locale for monetary formatting
lc_numeric = 'en_US.UTF-8'			# locale for number formatting
lc_time = 'en_US.UTF-8'				# locale for time formatting
# default configuration for text search
default_text_search_config = 'pg_catalog.english'

# - Other Defaults -
#dynamic_library_path = '$libdir'
#local_preload_libraries = ''
#session_preload_libraries = ''

#------------------------------------------------------------------------------
# LOCK MANAGEMENT
#------------------------------------------------------------------------------
#deadlock_timeout = 1s
#max_locks_per_transaction = 64
#max_pred_locks_per_transaction = 64
#max_pred_locks_per_relation = -2
#max_pred_locks_per_page = 2

#------------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#------------------------------------------------------------------------------

# - Previous PostgreSQL Versions -
#array_nulls = on
#backslash_quote = safe_encoding
#default_with_oids = off
#escape_string_warning = on
#lo_compat_privileges = off
#operator_precedence_warning = off
#quote_all_identifiers = off
#standard_conforming_strings = on
#synchronize_seqscans = on

# - Other Platforms and Clients -
#transform_null_equals = off

#------------------------------------------------------------------------------
# ERROR HANDLING
#------------------------------------------------------------------------------
#exit_on_error = off
#restart_after_crash = on
#data_sync_retry = off


#------------------------------------------------------------------------------
# CONFIG FILE INCLUDES
#------------------------------------------------------------------------------
#include_dir = ''
#include_if_exists = ''
#include = ''

#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
# Add settings for extensions here

• 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

启动数据库

systemctl enable postgresql-10.service
systemctl start postgresql-10.service

创建Prometheus数据库

• 登录数据库

psql -U postgres

• 创建用户

CREATE ROLE prometheus WITH LOGIN PASSWORD 'prometheus_password';

• 创建数据库

create database prometheus owner prometheus;

• 切换数据库

\connect prometheus ;

• 添加Extension

CREATE EXTENSION pg_prometheus;
CREATE EXTENSION timescaledb;

• 授权

grant all on SCHEMA prometheus TO prometheus;

• 创建表

只使用pg_prometheus

SELECT create_prometheus_table('metrics');

TimescaleDB可以实现时序数据库的功能，带来更强的性能和可扩展性。

如果postgresql已经安装了TimescaleDB的Extension，则会自动启用TimescaleDB。

可以显式的让pg_prometheus使用TimescaleDB扩展。

SELECT create_prometheus_table('metrics',use_timescaledb=>true);

部署Adapter

下载二进制文件

wget -O - https://github.com/timescale/prometheus-postgresql-adapter/releases/download/0.4.1/prometheus-postgresql-adapter-0.4.1-linux-amd64.tar.gz | tar xz

拷贝到PATH目录

mv prometheus-postgresql-adapter /usr/local/bin/

创建systemd服务

• /usr/lib/systemd/system/prometheus-postgresql-adapter.service

[Unit]
Description=prometheus-postgresql-adapter
After=network.target
[Service]
Type=simple
User=nobody
ExecStart=/usr/local/bin/prometheus-postgresql-adapter \
          -adapter.send-timeout 30s \
          -log.level info \
          -pg.database prometheus \
          -pg.host timescaledb \
          -pg.port 5432 \
          -pg.user prometheus \
          -pg.password prometheus_password \
          -web.listen-address :9201 \
          -web.telemetry-path /metrics
Restart=on-failure
RestartSec=60s

[Install]
WantedBy=multi-user.target

启动Adapter

systemctl daemon-reload
systemctl enable prometheus-postgresql-adapter.service
systemctl start prometheus-postgresql-adapter.service

部署Prometheus

参考文档

• 【Prometheus搭建简单的监控告警系统】

环境准备

# 创建prometheus配置目录
mkdir -p /etc/prometheus /etc/prometheus/rules.d
# 创建数据目录
mkdir -p /var/lib/prometheus
# 创建prometheus用户
useradd prometheus
# 修改属主属组
chown -R prometheus:prometheus /etc/prometheus /var/lib/prometheus

下载软件包

wget -O - https://github.com/prometheus/prometheus/releases/download/v2.9.2/prometheus-2.9.2.linux-amd64.tar.gz | tar xz

拷贝到PATH目录

cd prometheus-2.9.2.linux-amd64
chown -R prometheus:prometheus prometheus \
                               promtool \
                               console_libraries \
                               consoles \
                               prometheus.yml
mv prometheus promtool /usr/local/bin/
mv console_libraries consoles prometheus.yml /etc/prometheus/

配置prometheus

• /etc/prometheus/prometheus.yml

global:
  scrape_interval: 15s
  scrape_timeout: 10s
  evaluation_interval: 15s
alerting: {}
rule_files: {}
remote_write:
    - url: "http://timescaledb:9201/write"
remote_read:
    - url: "http://timescaledb:9201/read"
scrape_configs:
- job_name: prometheus
  honor_timestamps: true
  scrape_interval: 5s
  scrape_timeout: 5s
  metrics_path: /metrics
  scheme: http
  static_configs:
  - targets:
    - localhost:9090
- job_name: node-exporter
  honor_timestamps: true
  scrape_interval: 5s
  scrape_timeout: 5s
  metrics_path: /metrics
  scheme: http
  static_configs:
  - targets:
    - localhost:9100

创建systemd服务

• /usr/lib/systemd/system/prometheus.service

[Unit]
Description=prometheus
After=network.target
[Service]
Type=simple
User=prometheus
ExecStart=/usr/local/bin/prometheus \
          --config.file=/etc/prometheus/prometheus.yml \
          --storage.tsdb.path=/var/lib/prometheus \
          --storage.tsdb.retention.time=15d \
          --storage.tsdb.retention.size=40GB \
          --web.console.templates=/etc/prometheus/consoles \
          --web.console.libraries=/etc/prometheus/console_libraries
ExecReload=/bin/kill -HUP $MAINPID
Restart=on-failure
RestartSec=60s

[Install]
WantedBy=multi-user.target

启动prometheus

systemctl daemon-reload
systemctl enable prometheus.service
systemctl start prometheus.service

部署node_exporter

下载软件包

wget -O - https://github.com/prometheus/node_exporter/releases/download/v0.18.0/node_exporter-0.18.0.linux-amd64.tar.gz | tar xz

拷贝到PATH目录

cd node_exporter-0.18.0.linux-amd64
chown root:root node_exporter
chmod 755 node_exporter
mv node_exporter /usr/local/bin/

创建systemd服务

• /usr/lib/systemd/system/node_exporter.service

[Unit]
Description=node_exporter
After=network.target
[Service]
Type=simple
User=nobody
ExecStart=/usr/local/bin/node_exporter
Restart=on-failure
RestartSec=60s
[Install]
WantedBy=multi-user.target

启动node_exporter

systemctl daemon-reload
systemctl enable node_exporter.service
systemctl start node_exporter.service

测试验证

查看Adapter日志

journalctl -u prometheus-postgresql-adapter.service -f

输出示例

Month Day HH:mm:ss timescaledb prometheus-postgresql-adapter[6953]: node_xfs_block_map_btree_records_inserted_total{device="sda1",instance="localhost:9100",job="node-exporter"} 0 1563284554124
Month Day HH:mm:ss timescaledb prometheus-postgresql-adapter[6953]: node_xfs_block_map_btree_records_inserted_total{device="sda2",instance="localhost:9100",job="node-exporter"} 0 1563284554124
Month Day HH:mm:ss timescaledb prometheus-postgresql-adapter[6953]: node_xfs_block_mapping_extent_list_compares_total{device="sda1",instance="localhost:9100",job="node-exporter"} 0 1563284554124
Month Day HH:mm:ss timescaledb prometheus-postgresql-adapter[6953]: node_xfs_block_mapping_extent_list_compares_total{device="sda2",instance="localhost:9100",job="node-exporter"} 0 1563284554124

查看网卡ens33总流量

PromQL

node_network_transmit_bytes_total{device="ens33"}

SQL

prometheus=> SELECT time, value AS "total transmitted bytes"
             FROM metrics
             WHERE labels->>'device' = 'ens33' AND
                   name='node_network_transmit_bytes_total'
             ORDER BY time;

输出示例

            time            | total transmitted bytes 
----------------------------+-------------------------
 YYYY-MM-DD 21:42:34.124+08 |                 9058222
 YYYY-MM-DD 21:42:39.123+08 |                 9095088
 YYYY-MM-DD 21:42:44.124+08 |                 9128038
 YYYY-MM-DD 21:42:49.124+08 |                 9161183
 YYYY-MM-DD 21:42:54.123+08 |                 9198099
 YYYY-MM-DD 21:42:59.124+08 |                 9231065

查系统最近24小时的5min平均负载

PromQL

只能返回一个值

node_load5

SQL

prometheus=> SELECT time_bucket('5 minutes', time) AS five_min_bucket, name, avg(value)
             FROM metrics
             WHERE (name='node_load5' OR name='node_memory_Active_bytes') AND
                   time > NOW() - interval '1 day'
             GROUP BY five_min_bucket,name
             ORDER BY five_min_bucket;

输出示例

    five_min_bucket     |           name           |        avg         
------------------------+--------------------------+--------------------
 YYYY-MM-DD 21:40:00+08 | node_load5               |              0.056
 YYYY-MM-DD 21:40:00+08 | node_memory_Active_bytes |   177269828.266667
 YYYY-MM-DD 21:45:00+08 | node_load5               | 0.0435593220338983
 YYYY-MM-DD 21:45:00+08 | node_memory_Active_bytes |   177018845.288136
 YYYY-MM-DD 21:50:00+08 | node_load5               | 0.0326666666666667
 YYYY-MM-DD 21:50:00+08 | node_memory_Active_bytes |   182458094.933333
 YYYY-MM-DD 21:55:00+08 | node_load5               |              0.024
 YYYY-MM-DD 21:55:00+08 | node_memory_Active_bytes |        185453363.2
(8 rows)