Skip to content

Latest commit

 

History

History
616 lines (429 loc) · 19 KB

20191027_04.md

File metadata and controls

616 lines (429 loc) · 19 KB
Raw

PostgreSQL timescaledb插件 pg_prometheus - PostgreSQL作为prometheus的时序数据库必备插件

作者

digoal

日期

2019-10-27

标签

PostgreSQL , prometheus , timescaledb , pg_prometheus

背景

timescaledb是PG的时序数据库插件，目前在github上已经有接近8000的star，是PG的一个非常火爆的插件。

在很多应用场景中都有时序数据，例如日志数据，物联网场景都传感器数据，金融行业的行为数据，监控数据等等。

Prometheus 是 Kubernetes 集群监控系统的重要组成部分，作为Prometheus 的后端存储数据库，有哪些考虑呢？

1、建议是一个时序数据库，例如postgresql+timescaledb。

2、建议后端数据库有时序数据类型，(pg_prometheus插件)

3、有列存储功能，支持更高效的数据计算，(pg 12开始支持扩展存储引擎接口，zedstore是一种列存)

4、有压缩能力，支持高性价比存储更多数据，(tuple压缩，文件系统压缩，扩展存储接口都可以)

5、有并行计算能力，支持更高效的计算，(9.6以上版本都支持并行计算，11已经基本完备)

6、有冷热分离存储能力，(FDW接口支持内部、外部分离存储)

7、有分片能力可以横向扩展，(xl, xc, citus，有非常多插件或形态)

8、有企业级能力，不需要过早的分片。(商业数据库级别的企业级能力)

9、有高效率检索能力，即使是文本内部搜索也应该可以非常高效查询。(gin, btree, gist, rum, bloom, brin, hash, spgist等，众多索引接口，比搜索引擎还要强大)

以上PG都具备，PG与prometheus简直是完美的配合。

为了方便prometheus用户使用PG作为后端存储，timescaledb开发了一个插件：pg_prometheus插件，支持了兼容prometheus的数据类型，操作符，索引支持等。

pg_prometheus插件

https://github.com/timescale/pg_prometheus

部署比较简单，以pg 12为例：

git clone https://github.com/timescale/pg_prometheus  
  
cd pg_prometheus/  
  
export PATH=$PGHOME/bin  
  
USE_PGXS=1 make  
  
USE_PGXS=1 make install  
  
/bin/mkdir -p '/home/pg12/pgsql12/lib'  
/bin/mkdir -p '/home/pg12/pgsql12/share/extension'  
/bin/mkdir -p '/home/pg12/pgsql12/share/extension'  
/bin/install -c -m 755  pg_prometheus.so '/home/pg12/pgsql12/lib/pg_prometheus.so'  
/bin/install -c -m 644 .//pg_prometheus.control '/home/pg12/pgsql12/share/extension/'  
/bin/install -c -m 644 .//sql/pg_prometheus--0.2.2.sql  '/home/pg12/pgsql12/share/extension/'  
  
cd $PGDATA  
vi postgresql.conf  
shared_preload_libraries = 'pg_prometheus'

重启数据库，并安装插件

pg_ctl restart -m fast  
  
psql  
postgres=# create extension pg_prometheus ;  
CREATE EXTENSION  
  
postgres=# SELECT create_prometheus_table('metrics');  
 create_prometheus_table   
-------------------------  
   
(1 row)  
  
  
postgres=# INSERT INTO metrics VALUES ('cpu_usage{service="nginx",host="machine1"} 34.6 1494595898000');  
INSERT 0 0  
  
postgres=# select * from metrics;  
                                           sample                                           |          time          |   name    | value |                   
               labels                                  
--------------------------------------------------------------------------------------------+------------------------+-----------+-------+-----------------  
-----------------------------------------------------  
 cpu_usage{host="machine1",service="nginx",metric_name="cpu_usage"} 34.600000 1494595898000 | 2017-05-12 21:31:38+08 | cpu_usage |  34.6 | {"host": "machin  
e1", "service": "nginx", "metric_name": "cpu_usage"}  
(1 row)  
  
postgres=# \dt  
             List of relations  
 Schema |      Name      | Type  |  Owner     
--------+----------------+-------+----------  
 public | metrics_copy   | table | postgres  
 public | metrics_labels | table | postgres  
 public | metrics_values | table | postgres  
(3 rows)

pg_prometheus插件加了哪些SQL功能

https://github.com/timescale/pg_prometheus/blob/master/sql/prometheus.sql

看看安装插件时调用的SQL即可：

包括新的数据类型，操作符，表，视图，用于创建metric的函数接口，写入metric或copy数据的函数接口等。

其中建metric 表的接口如下：

CREATE OR REPLACE FUNCTION create_prometheus_table(  
       metrics_view_name NAME,  
       metrics_values_table_name NAME = NULL,  
       metrics_labels_table_name NAME = NULL,  
       metrics_samples_table_name NAME = NULL,  
       metrics_copy_table_name NAME = NULL,  
       normalized_tables BOOL = TRUE,  
       use_timescaledb BOOL = NULL,  
       chunk_time_interval INTERVAL = interval '1 day'  
)

完整sql：

CREATE SCHEMA prometheus;  
  
CREATE TYPE prom_sample;  
  
CREATE FUNCTION prom_in(cstring)  
    RETURNS prom_sample  
    AS '$libdir/pg_prometheus', 'prom_in'  
    LANGUAGE C IMMUTABLE STRICT;  
  
CREATE FUNCTION prom_out(prom_sample)  
    RETURNS cstring  
    AS '$libdir/pg_prometheus', 'prom_out'  
    LANGUAGE C IMMUTABLE STRICT;  
  
CREATE TYPE prom_sample (  
   internallength = VARIABLE,  
   input = prom_in,  
   output = prom_out  
);  
  
  
-- Functions and operators  
  
CREATE FUNCTION to_prom(cstring)  
    RETURNS prom_sample  
    AS '$libdir/pg_prometheus', 'prom_in'  
    LANGUAGE C IMMUTABLE STRICT;  
  
CREATE FUNCTION prom_construct(TIMESTAMPTZ, TEXT, double precision, jsonb)  
    RETURNS prom_sample  
    AS '$libdir/pg_prometheus', 'prom_construct'  
    LANGUAGE C IMMUTABLE STRICT;  
  
CREATE FUNCTION prom_has_label(prom_sample, text)  
    RETURNS bool  
    AS '$libdir/pg_prometheus', 'prom_has_label'  
    LANGUAGE C IMMUTABLE STRICT;  
  
CREATE OPERATOR ? (  
   leftarg = prom_sample,  
   rightarg = text,  
   procedure = prom_has_label  
);  
  
CREATE FUNCTION prom_label_count(prom_sample)  
    RETURNS integer  
    AS '$libdir/pg_prometheus', 'prom_label_count'  
    LANGUAGE C IMMUTABLE STRICT;  
  
CREATE OPERATOR # (  
   leftarg = prom_sample,  
   procedure = prom_label_count  
);  
  
CREATE FUNCTION prom_label(prom_sample, text)  
    RETURNS text  
    AS '$libdir/pg_prometheus', 'prom_label'  
    LANGUAGE C IMMUTABLE STRICT;  
  
CREATE OPERATOR @ (  
   leftarg = prom_sample,  
   rightarg = text,  
   procedure = prom_label  
);  
  
CREATE FUNCTION prom_labels(prom_sample, include_name bool)  
    RETURNS jsonb  
    AS '$libdir/pg_prometheus', 'prom_labels'  
    LANGUAGE C IMMUTABLE STRICT;  
  
CREATE FUNCTION prom_labels(prom_sample)  
    RETURNS jsonb  
    AS '$libdir/pg_prometheus', 'prom_labels'  
    LANGUAGE C IMMUTABLE STRICT;  
  
CREATE OPERATOR @ (  
   leftarg = prom_sample,  
   procedure = prom_labels  
);  
  
CREATE FUNCTION prom_name(prom_sample)  
    RETURNS text  
    AS '$libdir/pg_prometheus', 'prom_name'  
    LANGUAGE C IMMUTABLE STRICT;  
  
CREATE OPERATOR | (  
   leftarg = prom_sample,  
   procedure = prom_name  
);  
  
CREATE FUNCTION prom_time(prom_sample)  
    RETURNS timestamptz  
    AS '$libdir/pg_prometheus', 'prom_time'  
    LANGUAGE C IMMUTABLE STRICT;  
  
CREATE OPERATOR ! (  
   leftarg = prom_sample,  
   procedure = prom_time  
);  
  
CREATE FUNCTION prom_value(prom_sample)  
    RETURNS float8  
    AS '$libdir/pg_prometheus', 'prom_value'  
    LANGUAGE C IMMUTABLE STRICT;  
  
CREATE OPERATOR -> (  
   leftarg = prom_sample,  
   procedure = prom_value  
);  
  
  
-- JSONB functions  
CREATE FUNCTION prom_jsonb(prom_sample)  
    RETURNS jsonb  
    AS '$libdir/pg_prometheus', 'prom_jsonb'  
    LANGUAGE C IMMUTABLE STRICT;  
  
CREATE OR REPLACE FUNCTION prometheus.insert_view_normal()  
    RETURNS TRIGGER LANGUAGE PLPGSQL AS  
$BODY$  
DECLARE  
    metric_labels     JSONB = prom_labels(NEW.sample);  
    metric_labels_id  INTEGER;  
    labels_table      NAME;  
    values_table      NAME;  
BEGIN  
    IF TG_NARGS != 2 THEN  
        RAISE EXCEPTION 'insert_view_normal requires 2 parameters';  
    END IF;  
  
    values_table := TG_ARGV[0];  
    labels_table := TG_ARGV[1];  
  
    -- Insert labels  
    EXECUTE format('SELECT id FROM %I l WHERE %L = l.labels AND %L = l.metric_name',  
          labels_table, metric_labels, prom_name(NEW.sample)) INTO metric_labels_id;  
  
    IF metric_labels_id IS NULL THEN  
      EXECUTE format(  
          $$  
          INSERT INTO %I (metric_name, labels) VALUES (%L, %L) RETURNING id  
          $$,  
          labels_table,  
          prom_name(NEW.sample),  
          metric_labels  
      ) INTO STRICT metric_labels_id;  
    END IF;  
  
    EXECUTE format('INSERT INTO %I (time, value, labels_id) VALUES (%L, %L, %L)',  
          values_table, prom_time(NEW.sample), prom_value(NEW.sample), metric_labels_id);  
  
    RETURN NULL;  
END  
$BODY$;  
  
CREATE OR REPLACE FUNCTION prometheus.insert_view_sample()  
    RETURNS TRIGGER LANGUAGE PLPGSQL AS  
$BODY$  
DECLARE  
    sample_table      NAME;  
BEGIN  
    IF TG_NARGS != 1 THEN  
        RAISE EXCEPTION 'insert_view_sample requires 1 parameter';  
    END IF;  
  
    sample_table := TG_ARGV[0];  
  
    EXECUTE format('INSERT INTO %I (sample) VALUES (%L)',  
          sample_table, NEW.sample);  
  
    RETURN NULL;  
END  
$BODY$;  
  
  
CREATE OR REPLACE FUNCTION create_prometheus_table(  
       metrics_view_name NAME,  
       metrics_values_table_name NAME = NULL,  
       metrics_labels_table_name NAME = NULL,  
       metrics_samples_table_name NAME = NULL,  
       metrics_copy_table_name NAME = NULL,  
       normalized_tables BOOL = TRUE,  
       use_timescaledb BOOL = NULL,  
       chunk_time_interval INTERVAL = interval '1 day'  
)  
    RETURNS VOID LANGUAGE PLPGSQL VOLATILE AS  
$BODY$  
DECLARE  
    timescaledb_ext_relid OID = NULL;  
BEGIN  
    SELECT oid FROM pg_extension  
    WHERE extname = 'timescaledb'  
    INTO timescaledb_ext_relid;  
  
    IF use_timescaledb IS NULL THEN  
      IF timescaledb_ext_relid IS NULL THEN  
        use_timescaledb := FALSE;  
      ELSE  
        use_timescaledb := TRUE;  
      END IF;  
    END IF;  
  
    IF use_timescaledb AND  timescaledb_ext_relid IS NULL THEN  
      RAISE 'TimescaleDB not installed';  
    END IF;  
  
    IF metrics_view_name IS NULL THEN  
       RAISE EXCEPTION 'Invalid table name';  
    END IF;  
  
    IF metrics_values_table_name IS NULL THEN  
       metrics_values_table_name := format('%I_values', metrics_view_name);  
    END IF;  
  
    IF metrics_labels_table_name IS NULL THEN  
       metrics_labels_table_name := format('%I_labels', metrics_view_name);  
    END IF;  
  
    IF metrics_samples_table_name IS NULL THEN  
       metrics_samples_table_name := format('%I_samples', metrics_view_name);  
    END IF;  
  
    IF metrics_copy_table_name IS NULL THEN  
       metrics_copy_table_name := format('%I_copy', metrics_view_name);  
    END IF;  
  
  
  
    IF normalized_tables THEN  
        -- Create labels table  
        EXECUTE format(  
            $$  
            CREATE TABLE %I (  
                  id SERIAL PRIMARY KEY,  
                  metric_name TEXT NOT NULL,  
                  labels jsonb,  
                  UNIQUE(metric_name, labels)  
            )  
            $$,  
            metrics_labels_table_name  
        );  
        -- Add a GIN index on labels  
        EXECUTE format(  
            $$  
            CREATE INDEX %I_labels_idx ON %1$I USING GIN (labels)  
            $$,  
            metrics_labels_table_name  
        );  
  
         -- Add a index on metric name  
        EXECUTE format(  
            $$  
            CREATE INDEX %I_metric_name_idx ON %1$I USING BTREE (metric_name)  
            $$,  
            metrics_labels_table_name  
        );  
  
        EXECUTE format(  
          $$  
          CREATE TABLE %I (sample prom_sample NOT NULL)  
          $$,  
          metrics_copy_table_name  
        );  
  
        -- Create normalized metrics table  
        IF use_timescaledb THEN  
          --does not support foreign  references  
          EXECUTE format(  
              $$  
              CREATE TABLE %I (time TIMESTAMPTZ, value FLOAT8, labels_id INTEGER)  
              $$,  
              metrics_values_table_name  
          );  
        ELSE  
          EXECUTE format(  
              $$  
              CREATE TABLE %I (time TIMESTAMPTZ, value FLOAT8, labels_id INTEGER REFERENCES %I(id))  
              $$,  
              metrics_values_table_name,  
              metrics_labels_table_name  
          );  
        END IF;  
  
        -- Make metrics table a hypertable if the TimescaleDB extension is present  
        IF use_timescaledb THEN  
           PERFORM create_hypertable(metrics_values_table_name::regclass, 'time',  
                   chunk_time_interval => _timescaledb_internal.interval_to_usec(chunk_time_interval));  
        END IF;  
  
        -- Create labels ID column index  
        EXECUTE format(  
            $$  
            CREATE INDEX %I_labels_id_idx ON %1$I USING BTREE (labels_id, time desc)  
            $$,  
            metrics_values_table_name  
        );  
  
        -- Create a view for the metrics  
        EXECUTE format(  
            $$  
            CREATE VIEW %I AS   
            SELECT prom_construct(m.time, l.metric_name, m.value, l.labels) AS sample,  
                   m.time AS time, l.metric_name AS name,  m.value AS value, l.labels AS labels  
            FROM %I AS m  
            INNER JOIN %I l ON (m.labels_id = l.id)  
            $$,  
            metrics_view_name,  
            metrics_values_table_name,  
            metrics_labels_table_name  
        );  
  
        EXECUTE format(  
            $$  
            CREATE TRIGGER insert_trigger INSTEAD OF INSERT ON %I  
            FOR EACH ROW EXECUTE PROCEDURE prometheus.insert_view_normal(%L, %L)  
            $$,  
            metrics_view_name,  
            metrics_values_table_name,  
            metrics_labels_table_name  
        );  
  
        EXECUTE format(  
            $$  
            CREATE TRIGGER insert_trigger BEFORE INSERT ON %I  
            FOR EACH ROW EXECUTE PROCEDURE prometheus.insert_view_normal(%L, %L)  
            $$,  
            metrics_copy_table_name,  
            metrics_values_table_name,  
            metrics_labels_table_name  
        );  
  
  
    ELSE  
        EXECUTE format(  
          $$  
          CREATE TABLE %I (sample prom_sample NOT NULL)  
          $$,  
          metrics_samples_table_name  
        );  
  
        -- Create labels index on raw samples table  
        EXECUTE format(  
            $$  
            CREATE INDEX %I_labels_idx ON %1$I USING GIN (prom_labels(sample))  
            $$,  
            metrics_samples_table_name  
        );  
  
        -- Create time index on raw samples table  
        EXECUTE format(  
            $$  
            CREATE INDEX %I_time_idx ON %1$I USING BTREE (prom_time(sample))  
            $$,  
            metrics_samples_table_name  
        );  
  
        -- Create a view for the metrics  
        EXECUTE format(  
            $$  
            CREATE VIEW %I AS   
            SELECT sample AS sample, prom_time(sample) AS time, prom_name(sample) AS name, prom_value(sample) AS value, prom_labels(sample) AS labels  
            FROM %I  
            $$,  
            metrics_view_name,  
            metrics_samples_table_name  
        );  
  
        EXECUTE format(  
            $$  
            CREATE TRIGGER insert_trigger INSTEAD OF INSERT ON %I  
            FOR EACH ROW EXECUTE PROCEDURE prometheus.insert_view_sample(%L)  
            $$,  
            metrics_view_name,  
            metrics_samples_table_name  
        );  
  
    END IF;  
  
END  
$BODY$;

其他功能说明详见readme

https://github.com/timescale/pg_prometheus

Prometheus remote storage adapter for PostgreSQL

如果你选择了PG作为Prometheus的存储，那么Prometheus端需要安装这个适配器，PG可以是一个远程的数据库，只要安装了pg_prometheus插件即可。

https://github.com/timescale/prometheus-postgresql-adapter

小结

PG 12的分区性能有了巨大的进步，PG 12结合pg_prometheus插件可以很好的存储prometheus的时序数据，作为标配的时序数据库使用。

prometheus的市场非常大，也是timescale为什么要开放pg_prometheus插件的主要原因。

参考

https://github.com/timescale/pg_prometheus

https://github.com/timescale/prometheus-postgresql-adapter

https://github.com/timescale/timescaledb

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.

digoal's wechat

PolarDB 学习图谱: 训练营、培训认证、在线互动实验、解决方案、生态合作、写心得拿奖品

购买PolarDB云服务折扣活动进行中, 55元起

About 德哥