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 - 公益是一辈子的事.