title: postGreSQL 插件 timescaleDB 安装使用 date: 2019-02-14 18:02:23

朴灿烈づ我的快乐病毒、 2022-03-07 03:54 86阅读 0赞

## 基本环境 ##

操作系统： centOS 7

postGreSQL ： 10

timescaleDB : 1.0 +

## postGreSQL安装 ##

[Centos7 安装Postgresql10.5和PostGIS][Centos7 _Postgresql10.5_PostGIS]

## timescaleDB 安装 ##

[PostgreSQL 时序数据库插件 timescaleDB 部署实践 - PostGIS + timescaleDB => PG时空数据库 -- 德哥][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/][http_pnunu.cn]

[Centos7 _Postgresql10.5_PostGIS]: https://blog.csdn.net/u010430471/article/details/81663248
[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 Docs - Setting up TimescaleDB]: https://docs.timescale.com/v1.2/getting-started/setup
[http_pnunu.cn]: http://pnunu.cn/