安装下载

postgresql下载地址

同时去下载TimescaleDB，TimescaleDB下载地址，需要和postgresql的版本对应上

环境变量配置

系统环境Path添加，postgresql安装路径中bin

C:\Program Files\PostgreSQL\18\bin

安装TimescaleDB

解析安装包，管理员运行step.exe

安装过程中添加路径：C:\Program Files\PostgreSQL\18\data\postgresql.conf

安装完成后，下载Navicat Premium 17

连接验证

用户名默认为：postgres

密码：xxx《安装的时候输入的密码》

点击连接，测试

创建数据库

-- 1. 创建数据库（使用默认数据库）

-- 2. 启用 TimescaleDB 扩展

CREATE EXTENSION IF NOT EXISTS timescaledb;

-- 3. 验证安装

SELECT default_version, installed_version FROM pg_available_extensions WHERE name = 'timescaledb';

创建数据库表

CREATE TABLE vibration_data (
    time                    TIMESTAMPTZ NOT NULL,
    sensor_id               VARCHAR(255) NOT NULL,    -- 改为字符串类型（设备标识）
    x_raw                   SMALLINT NOT NULL,        -- X轴原始数据（16位ADC值）
    y_raw                   SMALLINT NOT NULL,        -- Y轴原始数据（16位ADC值）
    z_raw                   SMALLINT NOT NULL,        -- Z轴原始数据（16位ADC值）
    accelerometer_full_scale SMALLINT NOT NULL         -- 量程（0:±2g, 1:±16g, 2:±4g, 3:±8g）
);

转换为超表

-- 按 time 列自动分区，每天一个分区（数据块）
SELECT create_hypertable('vibration_data', 'time', chunk_time_interval => INTERVAL '1 day');

创建索引

-- 加速按传感器和时间范围的查询
CREATE INDEX idx_sensor_time ON vibration_data (sensor_id, time DESC);

开启压缩（节省 90%+ 存储空间）

-- 启用压缩，按 sensor_id 分段
ALTER TABLE vibration_data SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'sensor_id'
);

-- 添加自动压缩策略：7 天前的数据自动压缩
SELECT add_compression_policy('vibration_data', INTERVAL '7 days');

压缩后，数据块将自动转换为列式存储，极大降低长期存储成本

设置数据保留策略

-- 自动删除 180 天前的数据（按需调整天数）
SELECT add_retention_policy('vibration_data', INTERVAL '180 days');

这样数据库会自动清理过期数据，避免磁盘被写满

-- 使用量程还原物理值（mg）
SELECT time, sensor_id,
    CASE accelerometer_full_scale
        WHEN 0 THEN x_raw * 0.061
        WHEN 1 THEN x_raw * 0.488
        WHEN 2 THEN x_raw * 0.122
        WHEN 3 THEN x_raw * 0.244
    END AS x_mg
FROM vibration_data
WHERE sensor_id = '192.168.1.100:5000'
ORDER BY time DESC LIMIT 1000;