遥测数据写入TimescaleDB指南

本指南详细阐述了如何配置和使用TimescaleDB来高效存储和管理遥测数据。特别针对使用4TB SATA硬盘作为数据存储介质的场景，提供了PostgreSQL参数的优化建议，以克服SATA硬盘的性能瓶颈。

1. TimescaleDB (PostgreSQL) 启动

TimescaleDB作为PostgreSQL的扩展，其启动方式与PostgreSQL相同。

确保TimescaleDB已正确安装并配置为PostgreSQL的扩展。
使用systemctl命令启动PostgreSQL服务：

sudo systemctl start postgresql

您也可以使用以下命令检查服务状态：

sudo systemctl status postgresql

2. PostgreSQL 配置优化 (针对SATA硬盘)

为了充分发挥4TB SATA硬盘的潜力，并缓解其I/O性能限制，需要对postgresql.conf进行关键调整。以下是基于提供的配置文档进行的详细说明。

重要提示： 修改配置文件后，务必重启PostgreSQL服务以使更改生效。

sudo systemctl restart postgresql

2.1 核心资源分配

内存是弥补SATA磁盘慢速的关键。通过增加内存分配，可以减少对慢速硬盘的读写次数。

shared_preload_libraries = 'timescaledb': 必需，预加载TimescaleDB扩展，使其在PostgreSQL启动时可用。
shared_buffers = 24GB: 设置共享缓冲区大小。对于慢速SATA硬盘，应尽可能大，以缓存更多数据。这里设置为24GB，假设系统有足够的物理内存。
effective_cache_size = 24GB: 告知查询优化器系统可用的总缓存大小（包括操作系统文件系统缓存和shared_buffers）。高值会鼓励优化器更多地使用索引扫描而不是顺序扫描。
maintenance_work_mem = 1GB: 用于维护操作（如VACUUM、CREATE INDEX、ALTER TABLE）的内存。较大的值可以提高这些操作的速度。
work_mem = 64MB: 用于内部排序和哈希表操作的内存。对于复杂的查询，增加此值可以避免将临时数据写入磁盘。

2.2 磁盘 I/O 适配 (SATA 硬盘的重大调整)

这是针对SATA硬盘性能瓶颈进行优化的核心部分。

random_page_cost = 4.0: 核心修改。随机页面读取的代价。SATA硬盘（尤其是HDD）的寻道时间长，随机读性能差。将其设置为4.0（相对于默认的4.0，文档建议HDD为4.0，SATA SSD为1.5-2.0），会极力促使PostgreSQL优化器避免随机读，尽可能走索引或利用内存缓存。
seq_page_cost = 1.0: 顺序页面读取的代价。SATA硬盘的顺序读性能相对较好，保持默认值1.0或微调即可。
effective_io_concurrency = 8: 核心修改。系统可以同时执行的并发I/O操作数量。SATA硬盘的并发处理能力有限。对于机械硬盘，建议设为2；对于SATA SSD，建议设为32。这里设置为8，是一个折衷值，旨在避免过高的并发请求导致磁盘队列堵塞。绝对不能使用NVMe级别的高并发值（如200）。
synchronous_commit = off: 核心修改。关闭同步提交。SATA硬盘的写入延迟高，每次事务提交都等待WAL（Write-Ahead Log）刷盘会导致系统卡顿。设置为off可以显著提高写入吞吐量，但存在极小的数据丢失风险（在数据库崩溃但操作系统未崩溃的情况下）。对于遥测数据这种可容忍少量数据丢失的场景，这是一个常见的性能优化手段。

2.3 WAL 与 Checkpoint (混合存储优化的关键)

WAL（预写日志）和检查点（Checkpoint）的配置对写入性能和数据恢复至关重要，尤其是在SATA硬盘上。

wal_buffers = 64MB: WAL缓冲区大小。较大的WAL缓冲区可以减少对WAL文件的直接写入次数。
max_wal_size = 4GB, min_wal_size = 1GB: 设置WAL文件的最大和最小总大小。较大的WAL文件可以延长检查点之间的间隔，给SATA硬盘更多的时间在后台慢慢刷数据，而不是频繁地进行爆发式写入。
checkpoint_completion_target = 0.9: 极其重要。检查点完成的目标百分比。将其设置为0.9（非常高），意味着检查点操作会尽可能平滑地在整个检查点周期内完成，避免在检查点结束时集中进行大量I/O。这对于SATA硬盘低吞吐量的特性至关重要，可以避免“每半小时卡死一分钟”的现象，实现“细水长流”的写入。
checkpoint_timeout = 45min: 检查点之间的最大时间间隔。延长此超时时间可以减少检查点的频率，进一步减轻SATA硬盘的I/O压力。
bgwriter_delay, bgwriter_lru_maxpages: 配置文件中这两项被注释掉。它们用于控制后台写入器（bgwriter）的行为，使其更频繁地将脏页从共享缓冲区写入磁盘，从而避免在检查点时集中写入。虽然当前配置未启用，但其目的是让脏页在内存中多停留一段时间，利用大内存作为缓冲。

2.4 并行与连接

这些参数控制了数据库的最大连接数和并行处理能力。

max_connections = 200: 数据库允许的最大并发连接数。
max_worker_processes = 16: 系统可以启动的最大后台进程数。
max_parallel_workers = 8: 可以支持的最大并行工作进程数。
max_parallel_workers_per_gather = 4: 每个Gather或Gather Merge节点可以启动的最大并行工作进程数。
autovacuum_max_workers = 5: 自动清理（autovacuum）可以同时运行的最大工作进程数。
timescaledb.max_background_workers = 8: TimescaleDB扩展可以使用的最大后台工作进程数。

3. 数据库和用户设置

切换到PostgreSQL的默认用户postgres，然后创建数据库和专用的数据库用户，并授予必要的权限。

sudo -i -u postgres psql

-- 创建数据库
CREATE DATABASE test_db;

-- 创建用户并设置密码
CREATE USER user_test WITH PASSWORD 'your_secure_password'; -- 请替换 'your_secure_password' 为实际密码

-- 授予数据库连接权限
GRANT CONNECT ON DATABASE test_db TO user_test;

-- 授予数据库所有权限
GRANT ALL PRIVILEGES ON DATABASE test_db TO user_test;

-- 退出psql
\q

安全提示： 在生产环境中，应根据最小权限原则，仅授予用户所需的最低权限。

4. TimescaleDB 表结构、超表、压缩与索引

使用上述创建的用户登录到test_db数据库，然后创建遥测数据表、将其转换为TimescaleDB超表，并配置数据压缩和索引以优化存储和查询。

psql -U user_test -d test_db -h localhost

-- 创建TimescaleDB表结构
CREATE TABLE dms_data_gzdy (
    record_time TIMESTAMPTZ NOT NULL,  -- 使用TIMESTAMPTZ作为时间分区键
    station_name TEXT,
    feeder_gis_id TEXT,
    switch_name TEXT,
    switch_oid TEXT,
    switch_gis_id TEXT,
    switch_status INTEGER,
    switch_status_quality INTEGER,
    active_power DECIMAL(18,6),
    active_power_quality INTEGER,
    reactive_power DECIMAL(18,6),
    reactive_power_quality INTEGER,
    current_a DECIMAL(18,6),
    current_a_quality INTEGER,
    current_b DECIMAL(18,6),
    current_b_quality INTEGER,
    current_c DECIMAL(18,6),
    current_c_quality INTEGER,
    voltage_uab DECIMAL(18,6),
    voltage_uab_quality INTEGER,
    voltage_ubc DECIMAL(18,6),
    voltage_ubc_quality INTEGER,
    voltage_uca DECIMAL(18,6),
    voltage_uca_quality INTEGER,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    
    -- TimescaleDB需要时间列参与主键
    PRIMARY KEY (record_time, switch_oid)
);

-- 启用TimescaleDB扩展（如果尚未启用）并创建hypertable
CREATE EXTENSION IF NOT EXISTS timescaledb;
SELECT create_hypertable('dms_data_gzdy', 'record_time', chunk_time_interval => INTERVAL '1 day');


-- 设置压缩策略
ALTER TABLE dms_data_gzdy SET (
    timescaledb.compress,
    -- 按设备ID分组，查询特定设备时只读相关块
    timescaledb.compress_segmentby = 'switch_oid',
    -- 按时间排序，保持查询时的有序性
    timescaledb.compress_orderby = 'record_time ASC'
);

-- 添加3天后自动压缩策略
SELECT add_compression_policy('dms_data_gzdy', INTERVAL '3 days');

-- 创建优化查询的索引
CREATE INDEX idx_dms_switch_oid ON dms_data_gzdy (switch_oid, record_time);
CREATE INDEX idx_dms_feeder_gis_id ON dms_data_gzdy (feeder_gis_id, record_time);
CREATE INDEX idx_dms_station_name ON dms_data_gzdy (station_name, record_time);

-- 退出psql
\q

5. 数据导入策略

在数据导入方面，需要区分普通表和TimescaleDB超表。

普通表 dev_accounting:
对于非超表，可以使用标准的pg_dump和psql工具进行数据导入。例如，通过dump_pg.sh脚本导入数据。
```
# 假设 dump_pg.sh 包含了 pg_restore 或 psql -f 命令
./dump_pg.sh
```
TimescaleDB 超表 dms_data_gzdy:
由于dms_data_gzdy是TimescaleDB超表，其内部数据存储机制与普通表不同，无法直接通过pg_dump/pg_restore进行数据导入。必须通过专门的ETL（Extract, Transform, Load）脚本将数据写入。这通常涉及读取源数据，进行必要的清洗和转换，然后通过INSERT语句将数据逐条或批量插入到超表中。

6. ETL 脚本部署

efile.sh 和 etl.py 是将数据清洗后写入TimescaleDB的关键ETL脚本。它们需要部署到crontab中进行定时调度。

冷启动数据处理:
在系统冷启动时，可能存在大量历史数据。为了避免一次性处理导致系统过载，建议预先将现有的E文件删除，等待新的调度推送的E文件上来，由ETL脚本逐步处理。
激活Python虚拟环境:
etl.py脚本可能依赖特定的Python库，这些库通常安装在虚拟环境中。因此，在efile.sh脚本中，需要激活相应的虚拟环境。

请在efile.sh脚本中增加以下一行，确保etl.py在正确的环境中运行：
```
#!/bin/bash
# ... 其他脚本内容 ...

# 激活Python虚拟环境
source /home/hello/tensorflow_project/tf_env/bin/activate

# ... 调用 etl.py 脚本的命令 ...
python /path/to/your/etl.py

# ... 其他脚本内容 ...
```
Crontab 部署:
将efile.sh和etl.py的执行命令添加到crontab中，设置合适的调度频率。
```
crontab -e
```
添加类似以下内容的行（例如，每5分钟执行一次）：
```
*/5 * * * * /path/to/your/efile.sh >> /var/log/efile_etl.log 2>&1
```
注意： 确保/path/to/your/efile.sh是脚本的完整路径，并且脚本具有执行权限（chmod +x efile.sh）。同时，检查日志文件路径是否可写。

7. API 部署

最后一步是部署新的API版本。

部署modperl目录下的API v1.1版本。
确保新的API版本能够正确连接到TimescaleDB并进行数据读写。
在确认v1.1版本稳定运行后，下线原有的v1.0版本。

遵循以上步骤，您将能够成功地将遥测数据高效地写入TimescaleDB，并通过优化配置充分利用SATA硬盘的存储能力。