文章目录
运用时间模型来构造的应用非常需要时序数据库的加持,包括未来大数据的趋势,时序数据库必然会成为一个新潮流。
📣 1.时序数据库
📣 2.TimescaleDB
📣 3.安装PG
✨3.1. rpm包下载
✨3.2 安装依赖包
✨3.3 始化安装
✨3.4 配置参数
cat >> /var/lib/pgsql/14/data/postgresql.conf <<"EOF"
listen_addresses = '*'
port=5432
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
EOF
cat << EOF > /var/lib/pgsql/14/data/pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
local all all trust
host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 md5
host replication all 0.0.0.0/0 md5
local replication all trust
EOF
✨3.5 重启
📣 4.TimescaleDB部署
✨ 4.1 repository
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
✨ 4.2 yum在线安装
1.Update your local repository list:
yum update --skip-broken --nobest
2.Install TimescaleDB:
yum install timescaledb-2-postgresql-14
在Red Hat Enterprise Linux 8上安装时,需要使用:
sudo dnf module disable postgresql
命令禁用系统中内置的PostgreSQL模块。
✨ 4.3 插件配置
📣 5.TimescaleDB使用
✨ 5.1 登陆PG
[root@rhel8 ~]# su - postgres
[postgres@rhel8 ~]$
[postgres@rhel8 ~]$ psql
psql (14.10)
Type "help" for help.
postgres=# create database jemdb;
CREATE DATABASE
postgres=# \c jemdb
You are now connected to database "jemdb" as user "postgres".
✨ 5.2 创建插件
1.创建插件
CREATE EXTENSION IF NOT EXISTS timescaledb;
2.删除插件
DROP EXTENSION IF EXISTS timescaledb;
jemdb=# \dx
List of installed extensions
Name | Version | Schema | Description
-------------+---------+------------+--------------------------------------------------------------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
timescaledb | 2.14.2 | public | Enables scalable inserts and complex queries for time-series data (Apache 2 Edition)
(2 rows)
✨ 5.3 使用超表
1、创建普通测试表
CREATE TABLE conditions (
time TIMESTAMPTZ NOT NULL,
location TEXT NOT NULL,
temperature DOUBLE PRECISION NULL,
humidity DOUBLE PRECISION NULL
);
2、基于time分区将上一步创建的普通表转换为超表
jemdb=# SELECT create_hypertable('conditions', 'time');
create_hypertable
-------------------------
(1,public,conditions,t)
(1 row)
3、插入数据并查询
jemdb=# 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_length
FROM _timescaledb_catalog.dimension c
JOIN _timescaledb_catalog.hypertable h
ON h.id = c.hypertable_id;