postgresql 10.10部署timesacledb1.3.2

背景

公司的zabbix监控用的是mysql数据库，mysql数据库单表超过1亿行数据的时候，会产生性能问题；由于zabbix自带housekeeper会定时清理过期的历史数据，同时又有好多数据在写入，导致产生过多的慢sql，影响zabbix server正常运行；现在考虑将mysql数据库替换成timescaledb时间序列数据库（官方已经支持）

zabbix官网博客： https://blog.zabbix.com/cn/zabbix-time-series-data-and-timescaledb-2/6959/
zabbix4.2实验性的支持timescaledb；zabbix4.4已经支持timescaledb；

下面自己先部署timescaledb1.3.2，简单熟悉一下时序数据库timescaledb

环境

系统：centos7
Cmake版本 3.9.2
PostgreSQL版本号10.10
TimescaleDB版本号1.3.2
虚拟机配置4CPU 8G内存 200G系统盘

postgresql 10.10

1.环境准备

yum install -y systemtap-sdt-devel.x86_64 gcc perl-ExtUtils-Embed readline readline-devel zlib zlib-devel openssl openssl-devel pam pam-devel libxml2 libxml2-devel  libxslt libxslt-devel tcl tcl-devel python python-devel

下载源码包：wget https://ftp.postgresql.org/pub/source/v10.10/postgresql-10.10.tar.gz

2.创建用户

useradd postgres

3.编译源码

./configure --prefix=/usr/local/postgresql --with-perl --with-tcl --with-python --with-openssl --with-pam --without-ldap --with-libxml --with-libxslt --enable-thread-safety --with-wal-blocksize=16 --with-blocksize=16  --enable-debug

make
make install

4.创建目录并把目录权限赋给postgres用户

mkdir /home/postgresql_data 存储数据的目录

chown -R postgres:postgres  /home/postgresql_data
chown -R /usr/local/postgresql 
chmod 750 /home/postgresql_data

5.配置环境变量

vi /etc/profile

export PATH=/usr/local/postgresql/bin:$PATH
export LD_LIBRARY_PATH=/usr/local/postgresql/lib
export PGDATA=/home/postgresql_data

source /etc/profile

6.初始化数据库并启动

切换到postgres用户，使用initdb初始化数据库，这样在/home/postgresql_data下会增加很多东西
su postgres

查看数据库初始化文档

启动数据库

pg_ctl -D /home/postgresql_data -l logfile start

7.修改密码

分为postgres用户密码和数据库密码，保持一致吧

passwd postgres

设置数据库密码：

su postgres
psql
ALTER USER postgres WITH PASSWORD '密码';

列出当前库

timescaledb

timescaledb部署参照：https://docs.timescale.com/v1.3/getting-started/installation
cmake版本需要大于3.4

git clone https://github.com/timescale/timescaledb.git
cd timescaledb
git checkout <release_tag>  # e.g., git checkout 1.3.2

# Bootstrap the build system
./bootstrap

# To build the extension
cd build && make

# To install
make install

配置PostgreSQL
切换到postgres用户

su - postgres

在$PGDATA/postgresql.conf添加配置

shared_preload_libraries = 'timescaledb'

重启PostgreSQL
切换到postgres用户

su - postgres
pg_ctl restart -D $PGDATA   (PGDATA=/home/postgresql_data)

验证TimescaleDB功能

登录PostgreSQL

psql -U postgres

创建名为tutorial的数据库

CREATE database tutorial;

切换到tutorial数据库

\c tutorial

加载TimescaleDB的extensions

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

示例输出

WARNING:  
WELCOME TO
 _____ _                               _     ____________  
|_   _(_)                             | |    |  _  \ ___ \ 
  | |  _ _ __ ___   ___  ___  ___ __ _| | ___| | | | |_/ / 
  | | | |  _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \ 
  | | | | | | | | |  __/\__ \ (_| (_| | |  __/ |/ /| |_/ /
  |_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
               Running version 1.3.2
For more information on TimescaleDB, please visit the following links:

 1. Getting started: https://docs.timescale.com/getting-started
 2. API reference documentation: https://docs.timescale.com/api
 3. How TimescaleDB is designed: https://docs.timescale.com/introduction/architecture

Note: TimescaleDB collects anonymous reports to better understand and assist our users.
For more information and how to disable, please see our docs https://docs.timescaledb.com/using-timescaledb/telemetry.

CREATE EXTENSION

创建一个普通SQL标准的表

CREATE TABLE conditions (
  time        TIMESTAMPTZ       NOT NULL,
  location    TEXT              NOT NULL,
  temperature DOUBLE PRECISION  NULL,
  humidity    DOUBLE PRECISION  NULL
);

查看表结构

tutorial=# \d conditions
                        Table "public.conditions"
   Column    |           Type           | Collation | Nullable | Default
-------------+--------------------------+-----------+----------+---------
 time        | timestamp with time zone |           | not null |
 location    | text                     |           | not null |
 temperature | double precision         |           |          |
 humidity    | double precision         |           |          |

使用create_hypertable创建hypertable

SELECT create_hypertable('conditions', 'time');
    create_hypertable
-------------------------
 (1,public,conditions,t)
(1 row)

这时再看表结构的时候，会发现不一样了

tutorial=# \d conditions
                        Table "public.conditions"
   Column    |           Type           | Collation | Nullable | Default
-------------+--------------------------+-----------+----------+---------
 time        | timestamp with time zone |           | not null |
 location    | text                     |           | not null |
 temperature | double precision         |           |          |
 humidity    | double precision         |           |          |
Indexes:
    "conditions_time_idx" btree ("time" DESC)
Triggers:
    ts_insert_blocker BEFORE INSERT ON conditions FOR EACH ROW EXECUTE PROCEDURE _timescaledb_internal.insert_blocker()

插入数据

tutorial=# INSERT INTO conditions(time, location, temperature, humidity) VALUES (NOW(), 'office', 70.0, 50.0);

查询数据

tutorial=# SELECT * FROM conditions ORDER BY time DESC LIMIT 100;
         time              | location | temperature | humidity
-------------------------------+----------+-------------+----------
 YYYY-MM-DD HH:mm:SS.354351+08 | office   |          70 |       50
(1 row)

TimescaleDB Tutorials
这里使用TimescaleDB官方的测试样例 https://docs.timescale.com/v1.3/tutorials/tutorial-hello-nyc

参考：

https://blog.csdn.net/xjc504751194/article/details/80094198
https://luanlengli.github.io/2019/05/25/PostgreSQL10%E6%90%AD%E5%BB%BA%E6%97%B6%E9%97%B4%E5%BA%8F%E5%88%97%E6%95%B0%E6%8D%AE%E5%BA%93TimescaleDB.html
https://blog.csdn.net/u010430471/article/details/81663248
https://docs.timescale.com/v1.3/getting-started/installation/rhel-centos/installation-source