TimescaleDB是基于PostgreSQL数据库打造的一款时序数据库,插件化的形式,随着PostgreSQL的版本升级而升级,不会因为另立分支带来麻烦。
官方文档
https://docs.timescale.com/latest/api#hypertable-management
1.1 特点
1. 基于时序优化
2. 自动分片(按时间、空间自动分片(chunk))
3. 全SQL接口
4. 支持垂直于横向扩展
5. 支持时间维度、空间维度自动分区。空间维度指属性字段(例如传感器ID,用户ID等)
6. 支持多个SERVER,多个CHUNK的并行查询。分区在TimescaleDB中被称为chunk。
7. 自动调整CHUNK的大小
8. 内部写优化(批量提交、内存索引、事务支持、数据倒灌)。
内存索引,因为chunk size比较适中,所以索引基本上都不会被交换出去,写性能比较好。
数据倒灌,因为有些传感器的数据可能写入延迟,导致需要写以前的chunk,timescaleDB允许这样的事情发生(可配置)。
9. 复杂查询优化(根据查询条件自动选择chunk,最近值获取优化(最小化的扫描,类似递归收敛),limit子句pushdown到不同的server,chunks,并行的聚合操作)
10. 利用已有的PostgreSQL特性(支持GIS,JOIN等),方便的管理(流复制、PITR)
11. 支持自动的按时间保留策略(自动删除过旧数据)
2.1 安装PostgresSQL11.5
注:若需要使用navicat远程连接最好安装10版本或使用最新版本navicat,否则可能不能进行创建表等操作;安装10版本只需将下方各命令中10改为10即可
2.1.1 首先安装PostgreSQL的rpm
yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86\_64/pgdg-centos10-10-2.noarch.rpm -y
查看postgresql源
yum list | grep postgresql
安装postgresql11-contrib和postgresql11-server。
yum install postgresql11-contrib postgresql11-server -y
这样会给我们的系统增加一个postgres用户
2.1.2 创建用户
需要注意的是PostgresSQL是禁止使用超级管理员来运行服务命令的,所以我们需要创建一个账号来进行后续的PostgresSQL的安装。
我们创建一个用户组,向创建的用户组中添加一个用户账号,并且给该用户创建密码。
执行命令:(上面用户已经建立,这里可以直接执行第三行修改密码)
groupadd postgres
useradd -g postgres postgres
passwd postgres
密码设置为nugget123456
2.1.3 设置环境变量
vi /etc/profile
export PATH=/usr/pgsql-11/bin:$PATH
export LD_LIBRARY_PATH=/usr/pgsql-11/lib
export PGDATA=/var/lib/pgsql/11/data
source /etc/profile
2.1.4 初始化
切换到 postgres用户
su postgres
执行initdb初始化数据库,这样在/opt/postgresql_data下会增加很多东西,
2.1.5 配置数据库服务开机启动并启动数据库服务
systemctl enable postgresql-11.service
service postgresql-11 start
service postgresql-11 status
检查数据库状态,有绿色,没红色说明启动完成
这个过程中出现过一个错误,启动失败:
我这边将/home/postgresql_data下的postmaster.pid删除再重启服务就好了
2.1.6 登录数据库并设置数据库密码:
切换到 postgres用户
su postgres
执行 psql ,进入 postgres的命令行
psql
修改密码
ALTER USER postgres WITH PASSWORD ‘123456’;
\l 列出当前库:
pg_ctl stop -m fast //pg_ctl工具关闭postgresql数据库
pg_ctl start //pg_ctl工具启动postgresql数据库
2.2 安装PostGIS
1.先安装几个工具包
yum install wget net-tools epel-release -y
然后安装postgis
以下安装命令中“11”代表postgres版本
yum install postgis24_11 postgis24_11-client -y
安装拓展工具
yum install ogr_fdw11 -y
yum install pgrouting_11 -y
2.创建数据库spatial_testdb
CREATE DATABASE spatial_testdb OWNER postgres;
进入
\c spatial_testdb
安装PostGis扩展
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION ogr_fdw;
然后可以验证是否安装成功
SELECT postgis_full_version();
3.创建空间数据表
存储城市信息(cities),并添加一个存储空间位置的列
CREATE TABLE cities(id varchar(20),name varchar(50));
SELECT AddGeometryColumn (‘cities’, ‘the_geom’, 4326, ‘POINT’, 2);
之后插入数据
INSERT INTO cities(id, the_geom, name) VALUES (1,ST_GeomFromText(‘POINT(-0.1257 51.508)’,4326),’London, England’);
INSERT INTO cities (id, the_geom, name) VALUES (2,ST_GeomFromText(‘POINT(-81.233 42.983)’,4326),’London, Ontario’);
INSERT INTO cities (id, the_geom, name) VALUES (3,ST_GeomFromText(‘POINT(27.91162491 -33.01529)’,4326),’East London,SA’);
4.查询
SELECT * FROM cities;
SELECT id, ST_AsText(the_geom), ST_AsEwkt(the_geom), ST_X(the_geom), ST_Y(the_geom) FROM cities;
空间查询城市相互距离
SELECT p1.name,p2.name,ST_Distance_Sphere(p1.the_geom,p2.the_geom) FROM cities AS p1, cities AS p2 WHERE p1.id > p2.id;
2.3 设置远程连接
1.修改配置文件
首先修改/var/lib/pgsql/11/data/pg_hba.conf,改为:
其次修改/home/postgresql_data/postgresql.conf,改为:
之后重启服务
service postgresql-11 restart
start postgresql-11.service
如果服务器开启了防火墙,需要打开服务端口
firewall-cmd –add-service=postgresql –permanent 开放postgresql服务
firewall-cmd –reload 重载防火墙
2.4 远程连接
2.4.1 navicat
文件->新建连接->PostgreSQl
2.4.2 可选操作-pgAdmin
这里使用pgAdmin进行远程连接,下载地址:
https://www.pgadmin.org/download/pgadmin-4-windows/。
选择创建服务器,填入相应内容,主机名称填自己服务器的IP
2.5 安装timescalDB
2.5.1 yum源安装
添加timescaleDB源
cat > /etc/yum.repos.d/timescale_timescaledb.repo <<EOL
[timescale_timescaledb]
name=timescale_timescaledb
baseurl=https://packagecloud.io/timescale/timescaledb/el/7/\\$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
更新源
yum update -y
安装
yum install -y timescaledb-postgresql-11
配置 postgresql.conf 使用 postgres 启动时加载 ‘timescaledb’
vi /var/lib/pgsql/11/data/postgresql.conf
在文件末尾添加
shared_preload_libraries = ‘timescaledb’
重启服务
service postgresql-11 restart
2.5.2 安装检验
切换到 postgres用户
su postgres
执行 psql ,进入 postgres的命令行
psql
执行 以下语句,如果出现 下图 就说明 安装 成功
create extension timescaledb;
3.1 进入数据库
切换到 postgres用户
su postgres
执行 psql ,进入 postgres的命令行
psql
3.2 重启服务
service postgresql-11 restart
start postgresql-11.service
3.3 PostgreSQL常用命令
修改密码
ALTER USER postgres WITH PASSWORD ‘123456’;
\l 列出当前库
pg_ctl stop -m fast //pg_ctl工具关闭postgresql数据库
pg_ctl start //pg_ctl工具启动postgresql数据库
\q 退出
3.4 timescaleDB创建表并插入数据
hypertable 是 timescaledb 抽象的 一张表,让用户操作 hypertable 就像 操作 postgres的普通表一样,在内部,timescaledb 自动将hypertable 分割成块, timescaledb 会自动操作和管理 hypertable 的分区表。这对于用户来说是透明的
创建超表有两个步骤
1 使用 postgres的标准语法创建表
CREATE TABLE conditions (
time TIMESTAMPTZ NOT NULL,
location TEXT NOT NULL,
temperature DOUBLE PRECISION NULL
);
2 使用 create_hypertable 函数 将postgres标准表转化为 hypertable
create_hypertable 有两个参数 ,第一个参数 是 表名,第二个参数 是分区列,一般为 TIMESTAMPTZ类型
例如
SELECT create_hypertable(‘conditions’, ‘time’);
插入 数据
INSERT INTO conditions(time, location, temperature)
VALUES (NOW(), ‘office’, 70.0);
INSERT INTO conditions
VALUES
(NOW(), ‘office’, 70.0),
(NOW(), ‘basement’, 66.5),
(NOW(), ‘garage’, 77.0);
- windows下psql认证失败
在Windows环境下使用psql 命令出现认证失败。
网上找了很多,都没讲到要点
D:\program\PostgreSql\bin>psql
口令:
psql: 致命错误: 用户 “Lyncent” Password 认证失败
使用psql –help发现 当且仅当输入psql的时候,实际是后面跟着缺省 用户名,就是本地PC的用户名,
实际postgresql数据库里面根本没有这个用户;
初始用户只有postgres而已;
因此 使用
psql -U postgres 然后根据提示输入密码即可登录成功。