摘要：基本環(huán)境操作系統(tǒng)安裝安裝和安裝時(shí)序數(shù)據(jù)庫(kù)插件部署實(shí)踐時(shí)空數(shù)據(jù)庫(kù)德哥官網(wǎng)安裝文檔設(shè)置實(shí)驗(yàn)下載測(cè)試數(shù)據(jù)創(chuàng)建數(shù)據(jù)庫(kù)解壓下載文件創(chuàng)建表結(jié)構(gòu)導(dǎo)入數(shù)據(jù)查詢測(cè)試注釋數(shù)據(jù)庫(kù)啟動(dòng)連接數(shù)據(jù)庫(kù)創(chuàng)

操作系統(tǒng)： centOS 7

postGreSQL ： 10

timescaleDB : 1.0 +

Centos7 安裝Postgresql10.5和PostGIS

PostgreSQL 時(shí)序數(shù)據(jù)庫(kù)插件 timescaleDB 部署實(shí)踐 - PostGIS + timescaleDB => PG時(shí)空數(shù)據(jù)庫(kù) -- 德哥

官網(wǎng)安裝文檔：

TimescaleDB Docs - Installing

TimescaleDB Docs - Setting up TimescaleDB

    -- 下載測(cè)試數(shù)據(jù)
    wget https://timescaledata.blob.core.windows.net/datasets/weather_small.tar.gz

    wget https://timescaledata.blob.core.windows.net/datasets/weather_big.tar.gz

    -- 創(chuàng)建數(shù)據(jù)庫(kù)
    create database weather;

    -- 解壓下載文件
    tar -zxvf weather_small.tar.gz

    -- 創(chuàng)建表結(jié)構(gòu)
    psql -U postgres -d weather < weather.sql

    -- 導(dǎo)入數(shù)據(jù)
    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"


    -- 查詢測(cè)試

    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;

注釋

-- 數(shù)據(jù)庫(kù)啟動(dòng)
systemctl start postgresql-10

-- 連接數(shù)據(jù)庫(kù)
psql

-- 創(chuàng)建數(shù)據(jù)庫(kù)
CREATE database weather_big;

-- 創(chuàng)建時(shí)序關(guān)系
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

-- 導(dǎo)入數(shù)據(jù)
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"

歡迎大家關(guān)注 http://pnunu.cn/