如何在CentOS7上安装和使用TimescaleDB
作为 Write for DOnations 计划的一部分，作者选择了 计算机历史博物馆 来接受捐赠。
介绍
许多应用程序，例如监控系统和数据收集系统，都会积累数据以供进一步分析。 这些分析通常着眼于一段数据或系统随时间变化的方式。 在这些情况下，数据被表示为一个时间序列，每个数据点都带有一个时间戳。 一个示例如下所示：
2019-11-01 09:00:00 server.cpu.1 0.9 2019-11-01 09:00:00 server.cpu.15 0.8 2019-11-01 09:01:00 server.cpu.1 0.9 2019-11-01 09:01:00 server.cpu.15 0.8 ...
由于 物联网 (IoT) 和工业物联网的新部署，时间序列数据的相关性最近有所增长。 收集各种时间序列信息的设备越来越多：健身追踪器、智能手表、家庭气象站和各种传感器等等。 这些设备收集了大量信息，所有这些数据都必须存储在某个地方。
经典的关系数据库最常用于存储数据，但它们并不总是适用于时间序列的海量数据。 当您需要处理大量时间序列数据时，关系数据库可能会太慢。 正因为如此，专门优化的数据库，称为 NoSQL 数据库 ，已经创建，以避免关系数据库的问题。
TimescaleDB 是一个开源数据库，针对存储时间序列数据进行了优化。 它作为 PostgreSQL 的扩展实现，结合了关系数据库的易用性和 NoSQL 数据库的速度。 因此，它允许您使用 PostgreSQL 在一个地方同时存储业务数据和时间序列数据。
按照本教程，您将在 CentOS 7 上设置 TimescaleDB，对其进行配置，并学习如何使用它。 您将完成创建时间序列数据库和进行简单查询的过程。 最后，您将看到如何删除不必要的数据。
先决条件
要遵循本教程，您将需要：
- 按照我们的 Initial Server Setup with CentOS 7 指南设置一台 CentOS 7 服务器，包括具有 sudo 权限的非 root 用户和使用
firewalld设置的防火墙。 要设置firewalld，请按照 新 CentOS 7 服务器 教程的附加推荐步骤的“配置基本防火墙”部分。 - PostgreSQL 安装在您的服务器上。 按照我们的 如何在 CentOS 7 上安装和使用 PostgreSQL 指南进行安装和配置。
第 1 步 — 安装 TimescaleDB
TimescaleDB 在 CentOS 默认软件包存储库中不可用，因此在此步骤中，您将从 TimescaleDB 的第三方存储库安装它。
首先，创建一个新的存储库文件：
sudo vi /etc/yum.repos.d/timescaledb.repo
按 i 进入插入模式，并将以下配置粘贴到文件中：
/etc/yum.repos.d/timescaledb.repo
[timescale_timescaledb] name=timescale_timescaledb baseurl=https://packagecloud.io/timescale/timescaledb/el/7/$basearch repo_gpgcheck=1 gpgcheck=0 enabled=1 gpgkey=https://packagecloud.io/timescale/timescaledb/gpgkey sslverify=1 sslcacert=/etc/pki/tls/certs/ca-bundle.crt metadata_expire=300
完成后，按 ESC 退出插入模式，然后按 :wq 和 ENTER 保存并退出文件。 要了解有关文本编辑器 vi 及其后续 vim 的更多信息，请查看我们的 在云服务器上安装和使用 Vim 文本编辑器 教程。
您现在可以继续安装。 本教程使用 PostgreSQL 版本 11； 如果您使用的是不同版本的 PostgreSQL（例如 9.6 或 11），请替换以下命令中的值并运行它：
sudo yum install -y timescaledb-postgresql-11
TimescaleDB 现在已安装并可以使用。 接下来，您将打开它并在 PostgreSQL 配置文件中调整与它相关的一些设置，以优化数据库。
第 2 步 — 配置 TimescaleDB
TimescaleDB 模块可以在默认的 PostgreSQL 配置设置下正常工作，但为了提高性能并更好地利用处理器、内存和磁盘资源，TimescaleDB 的开发人员建议配置一些单独的参数。 这可以使用 timescaledb-tune 工具自动完成，也可以通过手动编辑服务器的 postgresql.conf 文件来完成。
在本教程中，您将使用 timescaledb-tune 工具 。 它读取 postgresql.conf 文件并以交互方式建议进行更改。
运行以下命令以启动配置向导：
sudo timescaledb-tune --pg-config=/usr/pgsql-11/bin/pg_config
首先，您将被要求确认 PostgreSQL 配置文件的路径：
OutputUsing postgresql.conf at this path: /var/lib/pgsql/11/data/postgresql.conf Is this correct? [(y)es/(n)o]:
该实用程序会自动检测配置文件的路径，因此输入 y 进行确认：
Output... Is this correct? [(y)es/(n)o]: y Writing backup to: /tmp/timescaledb_tune.backup201912191633
接下来，通过在下一个提示符处键入 y 并按 ENTER 来启用 TimescaleDB 模块：
Outputshared_preload_libraries needs to be updated Current: #shared_preload_libraries = '' Recommended: shared_preload_libraries = 'timescaledb' Is this okay? [(y)es/(n)o]: y success: shared_preload_libraries will be updated
根据您的服务器和 PostgreSQL 版本的特性，您将可以调整您的设置。 按 y 开始调谐过程：
OutputTune memory/parallelism/WAL and other settings? [(y)es/(n)o]: y Recommendations based on 7.64 GB of available memory and 4 CPUs for PostgreSQL 11 Memory settings recommendations Current: shared_buffers = 128MB #effective_cache_size = 4GB #maintenance_work_mem = 64MB #work_mem = 4MB Recommended: shared_buffers = 1955MB effective_cache_size = 5865MB maintenance_work_mem = 1001121kB work_mem = 5005kB Is this okay? [(y)es/(s)kip/(q)uit]:
timescaledb-tune 将自动检测服务器的可用内存并计算 shared_buffers、effective_cache_size、maintenance_work_mem 和 work_mem 设置的推荐值。 如果您想了解有关如何完成此操作的更多信息，请查看 timescaledb-tune 的 GitHub 页面。
如果这些设置看起来没问题，请输入 y：
Output... Is this okay? [(y)es/(s)kip/(q)uit]: y success: memory settings will be updated
此时，如果您的服务器有多个 CPU，您将找到并行设置的建议。 但是，如果您有一个 CPU，timescaledb-tune 将直接将您发送到 WAL 设置。
拥有多个 CPU 的人会遇到这样的建议：
OutputParallelism settings recommendations Current: missing: timescaledb.max_background_workers #max_worker_processes = 8 #max_parallel_workers_per_gather = 2 #max_parallel_workers = 8 Recommended: timescaledb.max_background_workers = 8 max_worker_processes = 15 max_parallel_workers_per_gather = 2 max_parallel_workers = 4 Is this okay? [(y)es/(s)kip/(q)uit]:
这些设置调节处理请求和后台任务的 workers 的数量。 您可以从 TimescaleDB 和 PostgreSQL 文档中了解有关这些设置的更多信息。
键入 y 然后 ENTER 以接受这些设置：
Output... Is this okay? [(y)es/(s)kip/(q)uit]: y success: parallelism settings will be updated
接下来，您将找到 Write Ahead Log (WAL) 的建议：
OutputWAL settings recommendations Current: #wal_buffers = -1 #min_wal_size = 80MB #max_wal_size = 1GB Recommended: wal_buffers = 16MB min_wal_size = 4GB max_wal_size = 8GB Is this okay? [(y)es/(s)kip/(q)uit]:
WAL 保持数据完整性，但默认设置会导致 I/O 效率低下，从而降低写入性能。 键入并输入 y 以优化这些设置：
Output... Is this okay? [(y)es/(s)kip/(q)uit]: y success: WAL settings will be updated
您现在会发现一些其他建议：
OutputMiscellaneous settings recommendations Current: #default_statistics_target = 100 #random_page_cost = 4.0 #checkpoint_completion_target = 0.5 #max_locks_per_transaction = 64 #autovacuum_max_workers = 3 #autovacuum_naptime = 1min #effective_io_concurrency = 1 Recommended: default_statistics_target = 500 random_page_cost = 1.1 checkpoint_completion_target = 0.9 max_locks_per_transaction = 64 autovacuum_max_workers = 10 autovacuum_naptime = 10 effective_io_concurrency = 200 Is this okay? [(y)es/(s)kip/(q)uit]:
所有这些不同的参数都旨在提高性能。 例如，SSD 可以处理许多并发请求，因此 effective_io_concurrency 的最佳值可能是数百个。 您可以在 PostgreSQL 文档 中找到有关这些选项的更多信息。
按 y 然后按 ENTER 继续。
Output... Is this okay? [(y)es/(s)kip/(q)uit]: y success: miscellaneous settings will be updated Saving changes to: /var/lib/pgsql/11/data/postgresql.conf
结果，您将在 /var/lib/pgsql/11/data/postgresql.conf 处获得一个现成的配置文件。
注意： 如果您是从头开始安装，您也可以运行带有 --quiet 和 --yes 标志的初始命令，这将自动应用所有建议并将更改 postgresql.conf 配置文件：
sudo timescaledb-tune --pg-config=/usr/pgsql-11/bin/pg_config --quiet --yes
为了使配置更改生效，您必须重新启动 PostgreSQL 服务：
sudo systemctl restart postgresql-11.service
现在数据库正在以最佳参数运行，并准备好使用时间序列数据。 在接下来的步骤中，您将尝试使用这些数据：创建新的数据库和超表并执行操作。
第三步——创建一个新的数据库和超表
优化 TimescaleDB 设置后，您就可以使用时间序列数据了。 TimescaleDB 是作为 PostgreSQL 的扩展实现的，因此对时间序列数据的操作与关系数据操作没有太大区别。 同时，数据库允许您将来自由组合时间序列和关系表的数据。
首先，您将创建一个新数据库并为其打开 TimescaleDB 扩展。 登录到您的 PostgreSQL 数据库：
sudo -u postgres psql
现在创建一个新数据库并连接到它。 本教程将数据库命名为 timeseries：
CREATE DATABASE timeseries; \c timeseries
您可以在以下位置找到有关使用 PostgreSQL 数据库的更多信息我们的如何在云服务器上的 PostgreSQL 中创建、删除和管理表教程 .
最后，启用 TimescaleDB 扩展：
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
您将看到以下输出：
OutputWARNING:
WELCOME TO
_____ _ _ ____________
|_ _(_) | | | _ \ ___ \
| | _ _ __ ___ ___ ___ ___ __ _| | ___| | | | |_/ /
| | | | _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \
| | | | | | | | | __/\__ \ (_| (_| | | __/ |/ /| |_/ /
|_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
Running version 1.5.1
For more information on TimescaleDB, please visit the following links:
1. Getting started: https://docs.timescale.com/getting-started
2. API reference documentation: https://docs.timescale.com/api
3. How TimescaleDB is designed: https://docs.timescale.com/introduction/architecture
Note: TimescaleDB collects anonymous reports to better understand and assist our users.
For more information and how to disable, please see our docs https://docs.timescaledb.com/using-timescaledb/telemetry.
CREATE EXTENSION
与时间序列数据交互的主要点是 hypertables，它是许多保存数据的单个表的抽象，称为 chunks。
要创建超表，先从常规 SQL 表开始，然后通过函数 create_hypertable 将其转换为超表。
制作一个表格来存储数据，以便随着时间的推移跟踪一组设备的温度和湿度：
CREATE TABLE conditions ( time TIMESTAMP WITH TIME ZONE NOT NULL, device_id TEXT, temperature NUMERIC, humidity NUMERIC );
此命令将创建一个名为 conditions 的表，其中包含四列。 第一列将存储时间戳，其中包括时区，不能为空。 接下来，您将使用时间列将表转换为按时间分区的超表：
SELECT create_hypertable('conditions', 'time');
此命令调用 create_hypertable() 函数，该函数从 PostgreSQL 表创建 TimescaleDB 超表，替换后者。
您将收到以下输出：
Output create_hypertable ------------------------- (1,public,conditions,t) (1 row)
在这一步中，您创建了一个新的超表来存储时间序列数据。 现在您可以通过写入超表来填充数据，然后执行删除它的过程。
第 4 步 — 写入和删除数据
在此步骤中，您将使用标准 SQL 命令插入数据并从外部源导入大量数据。 这将向您展示 TimescaleDB 的关系数据库方面。
首先，尝试基本命令。 可以使用标准的 INSERT SQL 命令将数据插入到超表中。 使用以下命令为理论设备 weather-pro-000000 插入一些样本 temperature 和 humidity 数据：
INSERT INTO conditions(time, device_id, temperature, humidity) VALUES (NOW(), 'weather-pro-000000', 84.1, 84.1);
您将收到以下输出：
OutputINSERT 0 1
您还可以一次插入多行数据。 尝试以下操作：
INSERT INTO conditions
VALUES
(NOW(), 'weather-pro-000002', 71.0, 51.0),
(NOW(), 'weather-pro-000003', 70.5, 50.5),
(NOW(), 'weather-pro-000004', 70.0, 50.2);
您将收到以下信息：
OutputINSERT 0 3
您还可以使用 RETURNING 语句指定 INSERT 命令将返回部分或全部插入的数据：
INSERT INTO conditions VALUES (NOW(), 'weather-pro-000002', 70.1, 50.1) RETURNING *;
您将看到以下输出：
Output time | device_id | temperature | humidity -------------------------------+--------------------+-------------+---------- 2019-09-15 14:14:01.576651+00 | weather-pro-000002 | 70.1 | 50.1 (1 row)
如果要从超表中删除数据，请使用标准的 DELETE SQL 命令。 运行以下命令删除 temperature 高于 80 或 humidity 高于 50 的任何数据：
DELETE FROM conditions WHERE temperature > 80; DELETE FROM conditions WHERE humidity > 50;
删除操作后，建议使用VACUUM命令，该命令会回收已删除数据仍使用的空间。
VACUUM conditions;
您可以在 PostgreSQL 文档 中找到有关 VACUUM 命令的更多信息。
这些命令适用于小规模数据输入，但由于时间序列数据通常同时从多个设备生成大量数据集，因此了解如何一次插入数百或数千行也很重要。 如果您以结构化形式准备了来自外部源的数据，例如 csv 格式，则可以快速完成此任务。
为了对此进行测试，您将使用一个样本数据集，该数据集代表来自不同位置的温度和湿度数据。 它由 TimescaleDB 开发人员创建，允许您测试他们的数据库。 您可以在 TimescaleDB 文档 中查看有关示例数据集的更多信息。
让我们看看如何将 weather_small 示例数据集中的数据导入数据库。 首先，退出 Postgresql：
\q
然后下载数据集并提取它：
cd /tmp curl https://timescaledata.blob.core.windows.net/datasets/weather_small.tar.gz -o weather_small.tar.gz tar -xvzf weather_small.tar.gz
接下来，将温度和湿度数据导入数据库：
sudo -u postgres psql -d timeseries -c "\COPY conditions FROM weather_small_conditions.csv CSV"
这将连接到 timeseries 数据库并执行 \COPY 命令，将所选文件中的数据复制到 conditions 超表中。 它将运行几秒钟。
将数据输入到表中后，您将收到以下输出：
OutputCOPY 1000000
在此步骤中，您手动和批量将数据添加到超表中。 接下来，继续执行查询。
第 5 步 — 查询数据
现在您的表包含数据，您可以执行各种查询来分析它。
要开始，请登录到数据库：
sudo -u postgres psql -d timeseries
如前所述，要使用超表，您可以使用标准 SQL 命令。 例如，要显示 conditions 超表中的最后 10 个条目，请运行以下命令：
SELECT * FROM conditions LIMIT 10;
您将看到以下输出：
Output time | device_id | temperature | humidity ------------------------+--------------------+--------------------+---------- 2016-11-15 12:00:00+00 | weather-pro-000000 | 39.9 | 49.9 2016-11-15 12:00:00+00 | weather-pro-000001 | 32.4 | 49.8 2016-11-15 12:00:00+00 | weather-pro-000002 | 39.800000000000004 | 50.2 2016-11-15 12:00:00+00 | weather-pro-000003 | 36.800000000000004 | 49.8 2016-11-15 12:00:00+00 | weather-pro-000004 | 71.8 | 50.1 2016-11-15 12:00:00+00 | weather-pro-000005 | 71.8 | 49.9 2016-11-15 12:00:00+00 | weather-pro-000006 | 37 | 49.8 2016-11-15 12:00:00+00 | weather-pro-000007 | 72 | 50 2016-11-15 12:00:00+00 | weather-pro-000008 | 31.3 | 50 2016-11-15 12:00:00+00 | weather-pro-000009 | 84.4 | 87.8 (10 rows)
此命令可让您查看数据库中的数据。 由于数据库包含一百万条记录，您使用 LIMIT 10 将输出限制为 10 个条目。
要查看最近的条目，请按时间降序对数据数组进行排序：
SELECT * FROM conditions ORDER BY time DESC LIMIT 20;
这将输出前 20 个最近的条目。
您还可以添加过滤器。 例如，要查看来自 weather-pro-000000 设备的条目，请运行以下命令：
SELECT * FROM conditions WHERE device_id = 'weather-pro-000000' ORDER BY time DESC LIMIT 10;
在这种情况下，您将看到 weather-pro-000000 设备记录的 10 个最近的温度和湿度数据点。
除了标准的 SQL 命令外，TimescaleDB 还提供了许多对时间序列数据分析有用的特殊函数。 例如，要查找温度值的中位数，您可以使用以下带有 percentile_cont 函数的查询：
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY temperature) FROM conditions WHERE device_id = 'weather-pro-000000';
您将看到以下输出：
Output percentile_cont
-----------------
40.5
(1 row)
通过这种方式，您将看到 weather-pro-00000 传感器所在的整个观察期间的温度中值。
要显示每个传感器的最新值，您可以使用 last 函数：
select device_id, last(temperature, time) FROM conditions GROUP BY device_id;
在输出中，您将看到所有传感器和相关最新值的列表。
要获取第一个值，请使用 first 函数。
下面的例子更复杂。 它将显示所选传感器在过去 24 小时内的每小时平均、最低和最高温度：
SELECT time_bucket('1 hour', time) "hour",
trunc(avg(temperature), 2) avg_temp,
trunc(min(temperature), 2) min_temp,
trunc(max(temperature), 2) max_temp
FROM conditions
WHERE device_id = 'weather-pro-000000'
GROUP BY "hour" ORDER BY "hour" DESC LIMIT 24;
在这里，您使用了 time_bucket 函数，它作为 PostgreSQL date_trunc 函数的更强大版本。 结果，您将看到一天中的哪些时段温度升高或降低：
Output hour | avg_temp | min_temp | max_temp ------------------------+----------+----------+---------- 2016-11-16 21:00:00+00 | 42.00 | 42.00 | 42.00 2016-11-16 20:00:00+00 | 41.92 | 41.69 | 42.00 2016-11-16 19:00:00+00 | 41.07 | 40.59 | 41.59 2016-11-16 18:00:00+00 | 40.11 | 39.79 | 40.59 2016-11-16 17:00:00+00 | 39.46 | 38.99 | 39.79 2016-11-16 16:00:00+00 | 38.54 | 38.19 | 38.99 2016-11-16 15:00:00+00 | 37.56 | 37.09 | 38.09 2016-11-16 14:00:00+00 | 36.62 | 36.39 | 37.09 2016-11-16 13:00:00+00 | 35.59 | 34.79 | 36.29 2016-11-16 12:00:00+00 | 34.59 | 34.19 | 34.79 2016-11-16 11:00:00+00 | 33.94 | 33.49 | 34.19 2016-11-16 10:00:00+00 | 33.27 | 32.79 | 33.39 2016-11-16 09:00:00+00 | 33.37 | 32.69 | 34.09 2016-11-16 08:00:00+00 | 34.94 | 34.19 | 35.49 2016-11-16 07:00:00+00 | 36.12 | 35.49 | 36.69 2016-11-16 06:00:00+00 | 37.02 | 36.69 | 37.49 2016-11-16 05:00:00+00 | 38.05 | 37.49 | 38.39 2016-11-16 04:00:00+00 | 38.71 | 38.39 | 39.19 2016-11-16 03:00:00+00 | 39.72 | 39.19 | 40.19 2016-11-16 02:00:00+00 | 40.67 | 40.29 | 40.99 2016-11-16 01:00:00+00 | 41.63 | 40.99 | 42.00 2016-11-16 00:00:00+00 | 42.00 | 42.00 | 42.00 2016-11-15 23:00:00+00 | 42.00 | 42.00 | 42.00 2016-11-15 22:00:00+00 | 42.00 | 42.00 | 42.00 (24 rows)
您可以在 TimescaleDB 文档 中找到更多有用的功能。
现在您知道如何处理您的数据了。 接下来，您将了解如何删除不必要的数据以及如何压缩数据。
第 6 步 — 配置数据压缩和删除
随着数据的积累，它将占用硬盘上越来越多的空间。 为了节省空间，最新版本的 TimescaleDB 提供了数据压缩功能。 此功能不需要调整任何文件系统设置，可用于快速提高数据库效率。 有关此压缩如何工作的更多信息，请查看来自 TimescaleDB 的 this Compression article。
首先，启用超表的压缩：
ALTER TABLE conditions SET ( timescaledb.compress, timescaledb.compress_segmentby = 'device_id' );
您将收到以下数据：
OutputNOTICE: adding index _compressed_hypertable_2_device_id__ts_meta_sequence_num_idx ON _timescaledb_internal._compressed_hypertable_2 USING BTREE(device_id, _ts_meta_sequence_num) ALTER TABLE
注意：你也可以设置TimescaleDB在指定的时间段内压缩数据。 例如，您可以运行：
SELECT add_compress_chunks_policy('conditions', INTERVAL '7 days');
在本例中，数据将在一周后自动压缩。
您可以使用以下命令查看压缩数据的统计信息：
SELECT * FROM timescaledb_information.compressed_chunk_stats;
然后，您将看到一个包含其状态的块列表：压缩状态以及未压缩和压缩数据占用了多少空间（以字节为单位）。
如果您不需要长时间存储数据，您可以删除过期数据以释放更多空间。 为此有一个特殊的 drop_chunks 函数。 它允许您删除数据早于指定时间的块：
SELECT drop_chunks(interval '24 hours', 'conditions');
此查询将从超表 conditions 中删除仅包含一天前数据的所有块。
您将收到以下输出：
Output drop_chunks ---------------------------------------- _timescaledb_internal._hyper_1_2_chunk (1 row)
要自动删除旧数据，可以配置cron任务。 请参阅我们的教程以了解有关 如何使用 cron 自动执行各种系统任务 的更多信息。
退出数据库：
\q
接下来，使用以下命令编辑您的 crontab，该命令应从 shell 运行：
crontab -e
现在将以下行添加到文件末尾：
crontab
... 0 1 * * * /usr/bin/psql -h localhost -p 5432 -U postgres -d postgres -c "SELECT drop_chunks(interval '24 hours', 'conditions');" >/dev/null 2>&1
该作业将在每天凌晨 1:00 删除超过一天的过时数据。
结论
您现在已经在您的 CentOS 服务器上设置了 TimescaleDB。 您还尝试了创建超表、向其中插入数据、查询数据、压缩和删除不必要的记录。 通过这些示例，您将能够利用 TimescaleDB 在存储时间序列数据方面优于传统关系数据库管理系统的主要优势，包括：
- 更高的数据摄取率
- 更快的查询性能
- 面向时间的功能
