基本环境
操作系统：centOS 7
postGreSQL：10
timescaleDB : 1.0 +
postGreSQL 安装
Centos7 安装 Postgresql10.5 和 PostGIS
timescaleDB 安装
PostgreSQL 时序数据库插件 timescaleDB 部署实践 – PostGIS + timescaleDB => PG 时空数据库 — 德哥
官网安装文档：
TimescaleDB Docs – Installing
timescaleDB 设置
TimescaleDB Docs – Setting up TimescaleDB
实验
— 下载测试数据
wget https://timescaledata.blob.core.windows.net/datasets/weather_small.tar.gz
wget https://timescaledata.blob.core.windows.net/datasets/weather_big.tar.gz
— 创建数据库
create database weather;
— 解压下载文件
tar -zxvf weather_small.tar.gz
— 创建表结构
psql -U postgres -d weather < weather.sql
— 导入数据
psql -U postgres -d weather -c “\COPY conditions FROM weather_small_conditions.csv CSV”
psql -U postgres -d weather -c “\COPY locations FROM weather_small_locations.csv CSV”
— 查询测试
SELECT * FROM conditions c ORDER BY time DESC LIMIT 10;
SELECT time, c.device_id, location,
trunc(temperature, 2) temperature, trunc(humidity, 2) humidity
FROM conditions c
INNER JOIN locations l ON c.device_id = l.device_id
WHERE l.environment = ‘outside’
ORDER BY time DESC LIMIT 10;
SELECT date_trunc(‘hour’, time) “hour”,
trunc(avg(temperature), 2) avg_temp,
trunc(min(temperature), 2) min_temp,
trunc(max(temperature), 2) max_temp
FROM conditions c
WHERE c.device_id IN (
SELECT device_id FROM locations
WHERE location LIKE ‘field-%’
) GROUP BY “hour” ORDER BY “hour” ASC LIMIT 24;
注释
— 数据库启动
systemctl start postgresql-10
— 连接数据库
psql
— 创建数据库
CREATE database weather_big;
— 创建时序关系
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
— 导入数据
psql -U postgres -d weather_big < weather.sql
psql -U postgres -d weather_big -c “\COPY conditions FROM weather_big_conditions.csv CSV”
psql -U postgres -d weather_big -c “\COPY locations FROM weather_big_locations.csv CSV”
欢迎大家关注 http://pnunu.cn/