postGreSQL 插件 timescaleDB 安装使用

基本环境

操作系统： centOS 7

postGreSQL ： 10

timescaleDB : 1.0 +

postGreSQL安装

Centos7 安装Postgresql10.5和PostGIS

https://blog.csdn.net/u010430471/article/details/81663248

timescaleDB 安装

PostgreSQL 时序数据库插件 timescaleDB 部署实践 - PostGIS + timescaleDB => PG时空数据库 -- 德哥

https://github.com/digoal/blog/blob/master/201801/20180129_01.md

官网安装文档

TimescaleDB Docs - Installing

https://docs.timescale.com/v1.2/getting-started/installation/rhel-centos/installation-yum

timescaleDB 设置

TimescaleDB Docs - Setting up TimescaleDB

https://docs.timescale.com/v1.2/getting-started/setup

实验

 1    -- 下载测试数据 2    wget https://timescaledata.blob.core.windows.net/datasets/weather_small.tar.gz 3 4    wget https://timescaledata.blob.core.windows.net/datasets/weather_big.tar.gz 5 6    -- 创建数据库 7    create database weather; 8 9    -- 解压下载文件10    tar -zxvf weather_small.tar.gz1112    -- 创建表结构13    psql -U postgres -d weather < weather.sql1415    -- 导入数据16    psql -U postgres -d weather -c "\COPY conditions FROM weather_small_conditions.csv CSV"17    psql -U postgres -d weather -c "\COPY locations FROM weather_small_locations.csv CSV"181920    -- 查询测试2122    SELECT * FROM conditions c ORDER BY time DESC LIMIT 10;232425    SELECT time, c.device_id, location,26    trunc(temperature, 2) temperature, trunc(humidity, 2) humidity27    FROM conditions c28    INNER JOIN locations l ON c.device_id = l.device_id29    WHERE l.environment = 'outside'30    ORDER BY time DESC LIMIT 10;313233    SELECT date_trunc('hour', time) "hour",34    trunc(avg(temperature), 2) avg_temp,35    trunc(min(temperature), 2) min_temp,36    trunc(max(temperature), 2) max_temp37    FROM conditions c38    WHERE c.device_id IN (39        SELECT device_id FROM locations40        WHERE location LIKE 'field-%'41    ) GROUP BY "hour" ORDER BY "hour" ASC LIMIT 24;

注释

 1-- 数据库启动 2systemctl start postgresql-10 3 4-- 连接数据库 5psql 6 7-- 创建数据库 8CREATE database weather_big; 910-- 创建时序关系11CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;1213-- 导入数据14psql -U postgres -d weather_big < weather.sql15psql -U postgres -d weather_big -c "\COPY conditions FROM weather_big_conditions.csv CSV"16psql -U postgres -d weather_big -c "\COPY locations FROM weather_big_locations.csv CSV"