时序数据库TimescaleDB安装部署以及常见使用

一、时序数据库

什么是时序数据库？顾名思义，用于处理按照时间变化顺序的数据的数据库即为时序数据库（time-series database），时序数据库专门优化处理带时间标签的数据，为什么会衍生时序数据库这一种新趋势呢？我们知道像PostgreSQL和MySQL这种关系型数据库对于短期需求不大的情况下下还是可以满足的，但是一旦数据量增长，其性能不足以支持频繁的添加和读取需求。运用时间模型来构造的应用非常需要时序数据库的加持，包括未来大数据的趋势，时序数据库必然会成为一个新潮流。

TimescaleDB简介：
对于TimescaleDB来说，在功能的丰富程度上战胜了排名更靠前的几位选手，但是对于性能上可能处于下风，因此TimescaleDB如何持续地发展下去、如何发展地更好，除了探寻在性能等综合素质方面的提升外，在PostgreSQL的肩膀上怎么样更好地适应现代化需求才是重中之重。

二、TimescaleDB部署

1、repository yum仓库配置

tee /etc/yum.repos.d/timescale_timescaledb.repo <<EOL
[timescale_timescaledb]
name=timescale_timescaledb
baseurl=https://packagecloud.io/timescale/timescaledb/el/$(rpm -E %{rhel})/\$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

2、yum在线安装

# pg源码安装
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# 这里是因为我是红帽8所以安装下面这个，这里如果安装错误后边安装TimescaleDB会报错
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpmyum install epel-releaseyum repolist all | grep pgdg
yum repolist enabled | grep pgdg

# 更新本地存储库列表
yum update --skip-broken --nobest# 安装TimescaleDB:这里要注意安装后边要对应pg版本，这里是pg14
yum install timescaledb-2-postgresql-14# 报错内容如下
timescale_timescaledb                                                                                                                                                                                                                       132  B/s | 833  B     00:06    
Error: Problem: package timescaledb-2-postgresql-14-2.17.2-0.el8.x86_64 from timescale_timescaledb requires postgresql14-server >= 14.0, but none of the providers can be installed- cannot install the best candidate for the job- package postgresql14-server-14.0-1PGDG.rhel8.x86_64 from pgdg14 is filtered out by modular filtering- package postgresql14-server-14.1-1PGDG.rhel8.x86_64 from pgdg14 is filtered out by modular filtering- package postgresql14-server-14.10-1PGDG.rhel8.x86_64 from pgdg14 is filtered out by modular filtering- package postgresql14-server-14.10-2PGDG.rhel8.x86_64 from pgdg14 is filtered out by modular filtering- package postgresql14-server-14.11-1PGDG.rhel8.x86_64 from pgdg14 is filtered out by modular filtering- package postgresql14-server-14.12-1PGDG.rhel8.x86_64 from pgdg14 is filtered out by modular filtering- package postgresql14-server-14.12-3PGDG.rhel8.x86_64 from pgdg14 is filtered out by modular filtering- package postgresql14-server-14.13-1PGDG.rhel8.x86_64 from pgdg14 is filtered out by modular filtering- package postgresql14-server-14.13-2PGDG.rhel8.x86_64 from pgdg14 is filtered out by modular filtering- package postgresql14-server-14.2-1PGDG.rhel8.x86_64 from pgdg14 is filtered out by modular filtering- package postgresql14-server-14.2-4PGDG.rhel8.x86_64 from pgdg14 is filtered out by modular filtering- package postgresql14-server-14.3-1PGDG.rhel8.x86_64 from pgdg14 is filtered out by modular filtering- package postgresql14-server-14.4-1PGDG.rhel8.x86_64 from pgdg14 is filtered out by modular filtering- package postgresql14-server-14.5-1PGDG.rhel8.x86_64 from pgdg14 is filtered out by modular filtering- package postgresql14-server-14.6-1PGDG.rhel8.x86_64 from pgdg14 is filtered out by modular filtering- package postgresql14-server-14.7-1PGDG.rhel8.x86_64 from pgdg14 is filtered out by modular filtering- package postgresql14-server-14.8-1PGDG.rhel8.x86_64 from pgdg14 is filtered out by modular filtering- package postgresql14-server-14.8-2PGDG.rhel8.x86_64 from pgdg14 is filtered out by modular filtering- package postgresql14-server-14.9-2PGDG.rhel8.x86_64 from pgdg14 is filtered out by modular filtering
(try to add '--skip-broken' to skip uninstallable packages or '--nobest' to use not only best candidate packages)
原因以及解决：
在Red Hat Enterprise Linux 8上安装时，需要使用:
sudo dnf module disable postgresql
命令禁用系统中内置的PostgreSQL模块。

3、插件配置

yum install -y postgresql14 postgresql14-server# 修改postgresql config文件，使timescaledb这个插件能正常工作,通过调优脚本来配置数据库
[root@TimescaleDB ~]# find / -name pg_config
/usr/pgsql-14/bin/pg_configsudo timescaledb-tune --pg-config=/usr/pgsql-14/bin/pg_config
报错：
[root@TimescaleDB ~]# sudo timescaledb-tune --pg-config=/usr/pgsql-14/bin/pg_config
exit: could not find postgresql.conf at any of these locations:
/etc/postgresql/14/main/postgresql.conf
/var/lib/pgsql/14/data/postgresql.conf
/var/lib/postgres/data/postgresql.conf
/var/lib/postgresql/data/postgresql.conf
原因：因为我们是yum源配置的，所以没有环境变量也没用进行initdb
解决方法：
su - postgres
cd /usr/pgsql-14/bin/
./initdb
./pg_ctl -D /var/lib/pgsql/14/data start
然后再次执行
su - root
sudo timescaledb-tune --pg-config=/usr/pgsql-14/bin/pg_config

##重启
systemctl restart postgresql-14
su - postgres
cd /usr/pgsql-14/bin/
./pg_ctl restart

4、TimescaleDB使用

登录pg

su - postgres
psql
create database timescaledb;
\c timescaledb

创建插件

CREATE EXTENSION IF NOT EXISTS timescaledb;
\dx 
psql -U postgres -d timescaledb 

使用超表

1、创建普通测试表

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

2、基于time分区将上一步创建的普通表转换为超表

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

超表（hypertable）是具有特殊功能的PostgreSQL表，可以很容易地处理时间序列数据。与它们交互就像与普通PostgreSQL表交互一样，但在幕后，超表会自动按时间将数据划分为块。在TimescaleDB中，超表与普通PostgreSQL表可以一起存在。超表用来存储时序数据，这样可以提高插入和查询的性能，而且可以访问一些有用的时间序列特性。普通PostgreSQL表用来存储其它关系型数据。

3、插入数据并查询

INSERT INTO conditions(time, location, temperature, humidity)
SELECT now(), to_char(i, 'FM0000'), random()*i, random()*i FROM generate_series(1,10000) i;

4.针对过去3小时的数据，每15分钟采集度量一次，按照时间和温度降序排序

SELECT time_bucket('15 minutes', time) AS fifteen_min,
location, COUNT(*),
MAX(temperature) AS max_temp,
MAX(humidity) AS max_hum
FROM conditions
WHERE time > NOW() - interval '3 hours'
GROUP BY fifteen_min, location
ORDER BY fifteen_min DESC, max_temp DESC;

5.更改现有超表上的块间隔长度

SELECT set_chunk_time_interval('conditions', INTERVAL '24 hours');SELECT h.table_name, c.interval_lengthFROM _timescaledb_catalog.dimension cJOIN _timescaledb_catalog.hypertable hON h.id = c.hypertable_id;

ok完成。