如何在Ubuntu18.04上安装和使用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 在一个地方同时存储业务数据和时间序列数据。

按照本教程，您将在 Ubuntu 18.04 上设置 TimescaleDB，对其进行配置，并学习如何使用它。您将创建时间序列数据库并进行简单查询。最后，您将看到如何删除不必要的数据。

先决条件

要遵循本教程，您将需要：

按照我们的 Ubuntu 18.04 初始服务器设置指南设置一台 Ubuntu 18.04 服务器，包括具有 sudo 权限和防火墙的非 root 用户。
PostgreSQL 安装在您的服务器上。按照如何在 Ubuntu 18.04 上安装和使用 PostgreSQL 的 Step 1 进行安装。

第 1 步 — 安装 TimescaleDB

TimescaleDB 在 Ubuntu 的默认软件包存储库中不可用，因此在此步骤中，您将从 TimescaleDB 个人软件包存档 (PPA) 安装它。

首先，添加 Timescale 的 APT 存储库：

sudo add-apt-repository ppa:timescale/timescaledb-ppa

按 ENTER 键确认此操作。

接下来，刷新您的 APT 缓存以更新您的软件包列表：

sudo apt update

您现在可以继续安装。本教程使用 PostgreSQL 版本 10；如果您使用的是不同版本的 PostgreSQL（例如 11 或 9.6），请替换以下命令中的值并运行它：

sudo apt install timescaledb-postgresql-10

TimescaleDB 现在已安装并可以使用。接下来，您将打开它并在 PostgreSQL 配置文件中调整与它相关的一些设置，以优化数据库。

第 2 步 — 配置 TimescaleDB

TimescaleDB 模块在默认的 PostgreSQL 配置设置下工作良好，但为了提高性能并更好地利用处理器、内存和磁盘资源，TimescaleDB 的开发人员建议配置一些单独的参数。这可以使用 timescaledb-tune 工具自动完成，也可以通过手动编辑服务器的 postgresql.conf 文件来完成。

在本教程中，您将使用 timescaledb-tune 工具，它将读取 postgresql.conf 文件并以交互方式建议进行更改。

运行以下命令以启动配置向导：

sudo timescaledb-tune

首先，您将被要求确认 PostgreSQL 配置文件的路径：

OutputUsing postgresql.conf at this path:
/etc/postgresql/10/main/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.backup201911181111

接下来，将提示您更改 shared_preload_libraries 变量以在启动 PostgreSQL 服务器时预加载 TimescaleDB 模块：

Outputshared_preload_libraries needs to be updated
Current:
#shared_preload_libraries = ''
Recommended:
shared_preload_libraries = 'timescaledb'
Is this okay? [(y)es/(n)o]:

shared_preload_libraries 接受一个逗号分隔的模块列表作为值，指定 PostgreSQL 在启动数据库服务器之前应该加载哪些模块。进行此更改会将 timescaledb 模块添加到该列表中。

注意：如果没有找到shared_preload_libraries指定的库，数据库服务器将无法启动。在调试使用 shared_preload_libraries 的应用程序时请记住这一点。有关这方面的更多信息，请参阅关于 shared_preload_libraries 的这篇 PostgresqlCO.NF 文章。

通过在此提示符下键入 y 并按 ENTER 启用 TimescaleDB 模块：

Output...
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.79 GB of available memory and 4 CPUs for PostgreSQL 10

Memory settings recommendations
Current:
shared_buffers = 128MB
#effective_cache_size = 4GB
#maintenance_work_mem = 64MB
#work_mem = 4MB
Recommended:
shared_buffers = 1994MB
effective_cache_size = 5982MB
maintenance_work_mem = 1021001kB
work_mem = 5105kB
Is this okay? [(y)es/(s)kip/(q)uit]:

timescaledb-tune 将自动检测服务器的可用内存并计算一些设置的推荐值。例如，shared_buffers 确定为缓存数据分配的内存量。默认情况下，此设置相对较低，以适应更广泛的平台，因此 timescaledb-tune 建议将值从 128MB 增加到 1994MB，以更好地利用资源更多空间来存储重复查询等缓存信息。 work_mem 变量也已增加以允许更复杂的排序。

如果您想了解有关为 PostgreSQL 调整内存设置的过程的更多信息，请参阅 PostgreSQL wiki 上的调整您的 PostgreSQL 服务器文章。

输入 y 接受值：

Output...
Is this okay? [(y)es/(s)kip/(q)uit]:  y
success: memory settings will be updated

此时，如果您的服务器有多个 CPU，您将找到并行设置的建议。这些设置决定了多个 CPU 如何并行进行同时查询以扫描数据库并更快地返回请求的数据。

拥有多个 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 = 13
max_parallel_workers_per_gather = 1
max_parallel_workers = 2
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 是一种日志记录方法，其中 PostgreSQL 在对数据库进行更改之前记录对数据文件的更改。通过优先考虑数据更改的最新记录，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: /etc/postgresql/10/main/postgresql.conf

结果，您将在 /etc/postgresql/10/main/postgresql.conf 处获得一个现成的配置文件。

注意： 如果您要自动安装，您还可以使用 --quiet 和 --yes 标志运行初始命令，这将自动应用所有建议并进行更改到 postgresql.conf 配置文件：

sudo timescaledb-tune --quiet --yes

为了使配置更改生效，您必须重新启动 PostgreSQL 服务：

sudo systemctl restart postgresql.service

现在数据库正在以最佳参数运行，并准备好使用时间序列数据。在接下来的步骤中，您将尝试使用这些数据：创建新的数据库和超表并执行操作。

第三步——创建一个新的数据库和超表

优化 TimescaleDB 设置后，您就可以使用时间序列数据了。 TimescaleDB 是作为 PostgreSQL 的扩展实现的，因此对时间序列数据的操作与关系数据操作没有太大区别。同时，数据库允许您将来自由组合时间序列和关系表的数据。

为了演示这一点，您将使用 PostgreSQL 命令创建一个数据库，然后启用 TimescaleDB 扩展来创建一个 hypertable，这是许多单个表的更高级别的抽象。超表是您将在 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

如前所述，与时间序列数据交互的主要点是超表，它由许多保存数据的单独表组成，称为 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

然后下载数据集并提取它：

wget https://timescaledata.blob.core.windows.net/datasets/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 删除超过一天的过时数据。

结论

您现在已经在 Ubuntu 18.04 服务器上设置了 TimescaleDB。您还尝试了创建超表、向其中插入数据、查询数据、压缩和删除不必要的记录。通过这些示例，您将能够利用 TimescaleDB 在存储时间序列数据方面优于传统关系数据库管理系统的主要优势，包括：

更高的数据摄取率
更快的查询性能
面向时间的功能

现在您知道如何存储时间序列数据，您可以使用这些数据创建图表。 TimescaleDB 与支持 PostgreSQL 的可视化工具兼容，例如 Grafana。您可以使用我们的如何在 Ubuntu 18.04 上安装和保护 Grafana 教程来了解有关这个流行的可视化工具的更多信息。