基本环境操作系统： centOS 7postGreSQL ： 10timescaleDB : 1.0 +postGreSQL安装Centos7 安装Postgresql10.5和PostGIStimescaleDB 安装PostgreSQL 时序数据库插件 timescaleDB 部署实践 - PostGIS + timescaleDB => PG时空数据库 – 德哥官网安装文档：TimescaleDB Docs - InstallingtimescaleDB 设置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.sqlpsql -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/