一.添加timescaledb插件
1.指令添加插件
# 登录
su postgres
psql
# 列出当前库
\l
# 添加timescaled 扩展
create extension timescaledb;
2.使用图形化工具添加插件
使用pgAdmin4、Dbeaver等工作可直接添加插件
二.普通表转为超表
1.普通超表
# 创建普通表,和mysql类似
CREATE TABLE sensor_data (
"time" timestamp with time zone NOT NULL,
device_id TEXT NOT NULL,
location TEXT NULL,
temperature NUMERIC NULL,
humidity NUMERIC NULL,
pm25 NUMERIC
);
# 建立时间索引
CREATE INDEX ON 表名(时间字段 DESC)
# 创建超表方式可自由组合
SELECT create_hypertable('表名', '时间字段');
# 将普通表的数据迁移到超标
INSERT INTO 超表 (SELECT * FROM 普通表); 2.附加参数
执行add_dimension()函数时， number_partitions 或 chunk_time_interval 必须选择一个。前者通过哈希分区，后者根据时间范围分区。
chunk_time_interval 选择：
- 如果分区列是TIMESTAMP, TIMESTAMPTZ, or DATE类型， 那么 chunk_time_interval 代表以毫秒为单位的时长。
# 分区覆盖时间范围，需大于0，缺省值7天
SELECT create_hypertable('表名', '时间主键', "第二个索引字段" , chunk_time_interval => 86400000000);
# 等同于
SELECT create_hypertable('表名', '时间主键', "第二个索引字段" , interval '24 hours');
# 等同于
SELECT create_hypertable('表名', '时间主键', "第二个索引字段" , interval '1 day');
# 等同于
SELECT create_hypertable('表名', '时间主键');
SELECT add_dimension('表名', '第二个索引字段', interval '1 day');
- 如果分区列是整型，那么 chunk_time_interval按照整型数值计数。
SELECT create_hypertable('表名', '时间主键', '第二个索引字段', 2);
# 等同于
SELECT create_hypertable('表名', '时间主键','第二个索引字段', number_partitions => 2);
# 等同于
SELECT create_hypertable('表名', '时间主键');
SELECT add_dimension('表名', '第二个索引字段', 2);三.timescaled 函数
1.设置时序元数据表数据块的时间间隔。设置后新生成的数据块使用新值，已有数据块不受影响。set_chunk_time_interval()
chunk_time_interval 可以是如下类型
- TIMESTAMP, TIMESTAMPTZ, DATE: 比如区间类型(interval ‘1 day’)
- INTEGER: 整型值
# 把指定表的数据块区间设置成24小时，分区键是TIMESTAMP类型。
SELECT set_chunk_time_interval('表名', interval '24 hours');
SELECT set_chunk_time_interval('表名', 86400000000);
# 把指定表的数据块区间设置成24小时，分区键是 UNIX 时间类型24 hours.
SELECT set_chunk_time_interval('表名', 86400000);2.设置时序元数据表的分区个数 set_number_partitions()
# 单分区表实例
SELECT set_number_partitions('表名', 2);
# 多分区表实例
SELECT set_number_partitions('表名', 2, '指定列');
# 将默认的7天分区超标修改为1天一分区以解决数据量过大的问题
SELECT set_chunk_time_interval('表名', interval '24 hours');3.显示时序元数据表的关联数据块。 show_chunks()
-
older_than 和 newer_than 参数可以用以下2中方式表示：
- 区间类型: 区间是指 now() - older_than 或者now() - newer_than。如果时间列不是TIMESTAMP, TIMESTAMPTZ, or DATE类型，返回错误。
- 时间戳，天，整型: TIMESTAMP / TIMESTAMPTZ / DATE 类型或者 SMALLINT / INT / BIGINT等数值类型. 类型需要和时序元数据表的时间列类型一致。
当两者同时使用时，函数返回两者的交集。例如，当出现newer_than => 4 months and older_than => 3 monthsW时，所有介于两者之间的数据块都会被删除掉。同样出现 newer_than => ‘2017-01-01’ and older_than => ‘2017-02-01’ 会删除介于’2017-01-01’ 和 ‘2017-02-01’的所有数据块. 如果2个时间段没有交集，则报错。
# 显示所有数据块。当没有创建表时，返回0.
SELECT show_chunks();
# 期望输出:
_timescaledb_internal._hyper_7_4_chunk
_timescaledb_internal._hyper_7_5_chunk
_timescaledb_internal._hyper_7_6_chunk
_timescaledb_internal._hyper_7_7_chunk
_timescaledb_internal._hyper_7_8_chunk
_timescaledb_internal._hyper_7_9_chunk
_timescaledb_internal._hyper_7_10_chunk
_timescaledb_internal._hyper_7_12_chunk
_timescaledb_internal._hyper_7_13_chunk
_timescaledb_internal._hyper_7_14_chunk
_timescaledb_internal._hyper_7_15_chunk
_timescaledb_internal._hyper_7_24_chunk
_timescaledb_internal._hyper_7_25_chunk
_timescaledb_internal._hyper_7_26_chunk
_timescaledb_internal._hyper_7_41_chunk
# 显示关联表的所有数据块（输出内容见下图）:
SELECT show_chunks('表名');
# 显示早于3个月的所有数据块：
SELECT show_chunks('表名', older_than => interval '3 months');
# 显示未来三个月的所有数据块。当时间出错时非常有用：
SELECT show_chunks('表名', newer_than => now() + interval '3 months');
# 显示指定表 时序元数据表上早于2021-10-06年的数据块:
SELECT show_chunks('表名', older_than => '2021-10-06'::date);
# 显示新于3个月的数据块：
SELECT show_chunks('表名', newer_than => interval '3 months');
# 显示早于3个月新于4个月的所有数据块：
SELECT show_chunks('表名', older_than => interval '3 months', newer_than => interval '4 months');
4.删除数据快 drop_chunks(）
# 删除早于3个月的所有数据块
SELECT drop_chunks(interval '3 months');
# 删除未来3个月的数据块。校订数据时非常有用:
SELECT drop_chunks(newer_than => now() + interval '3 months');
# 删除指定表 时序元数据表上早于3个月的数据块:
SELECT drop_chunks(interval '3 months', '表名');
# 删除指定表 时序元数据表上早于2020年1月1日的数据块:
SELECT drop_chunks('2020-01-01'::date, '表名');
# 删除指定表 时序元数据表上早于3个月的数据块，同时删除数据块的以来对象:
SELECT drop_chunks(interval '3 months', 'conditions', cascade => TRUE);
# 删除新于未来3个月的所有数据块:
SELECT drop_chunks(newer_than => interval '3 months');
# 删除早于3个月新于4个月的所有数据块：
SELECT drop_chunks(older_than => interval '3 months', newer_than => interval '4 months', table_name => 'conditions')5.数据块重新排序 reorder_chunk()
# _timescaledb_internal._hyper_1_10_chunk使用conditions_device_id_time_idx索引对块运行重新排序。
SELECT reorder_chunk('_timescaledb_internal._hyper_1_10_chunk', 'conditions_device_id_time_idx');6.移动数据块move_chunk()
将数据和索引移动到不同的表空间，可以在数据老化时将数据移动到成本更低的储存中。
move_chunk函数的作用类似于PostgreSQL CLUSTER命令和PostgreSQL ALTER TABLE命令的组合。 设置表空间的命令。move_chunk函数使用较低的锁级别，以便大部分进程都能读取chunk和hypertable。 会导致在操作期间略微增加磁盘使用。
SELECT move_chunk(
chunk => '_timescaledb_internal._hyper_1_4_chunk',
destination_tablespace => 'tablespace_2',
index_destination_tablespace => 'tablespace_3',
reorder_index => 'conditions_device_id_time_idx',
verbose => TRUE
);7.创建一个策略按指定索引重新排序已完成的块add_reorder_policy()
SELECT add_reorder_policy('conditions', 'conditions_device_id_time_idx');8.删除重新排序特定超表的策略 remove_reorder_policy()
# 删除表的现有重新排序策略（指定表如果存在）
SELECT remove_reorder_policy('表名', if_exists => true);9.磁盘操作，表空间的迁移，附加表空间attach_tablespace（）
将表空间附加到超级表并使用它存储块。表空间是文件系统上的一个目录，它允许控制单个表和索引在文件系统上的存储位置。一个常见的用例是为特定的存储磁盘创建一个表空间，允许表存储在那里。有关表空间的更多信息，请查看标准PostgreSQL文档。
TimescaleDB可以为每个超表管理一组表空间，从而自动在连接到超表的一组表空间上散布块。 如果对超表进行哈希分区，则TimescaleDB将尝试将属于同一分区的块放在同一表空间中。 更改附加到超表的表空间集也可能会更改放置行为。 没有附加表空间的超表将其块放置在数据库的默认表空间中。
在将表空间附加到超表之前，需要先创建表空间。 创建表空间后，可以将其同时附加到多个超表，以共享基础磁盘存储。 在调用create_hypertable之前，使用TABLESPACE选项将CREATE TABLE与常规表相关联的表空间将具有与在create_hypertable之后立即调用attach_tablespace相同的效果。
# 附加表空间disk1到超级表conditions上：
SELECT attach_tablespace('disk1', 'conditions');
SELECT attach_tablespace('disk2', 'conditions', if_not_attached => true);10.分离表空间detach_tablespace（）
当指定一个特定的超级表时，表空间只会与给定的超级表分离，因此可能会保持附加到其他超级表。
# disk1从 hypertable 中分离表空间conditions：
SELECT detach_tablespace('disk1', 'conditions');
SELECT detach_tablespace('disk2', 'conditions', if_attached => true);
# disk1从当前用户有权访问的所有超级表中分离表空间：
SELECT detach_tablespace('disk1');11.分离表空间detach_tablespaces（）
从超表中分离所有表空间。在超级表上发出此命令后，它将不再附加任何表空间。新的块将被放置在数据库的默认表空间中。
# 从 hypertable 中分离所有表空间conditions：
SELECT detach_tablespaces('conditions');12.显示附加到超表的表空间show_tablespaces（）
SELECT * FROM show_tablespaces('conditions');
show_tablespaces
------------------
disk1
disk213.为具有UTC时间的超表设置整数 now 函数 set_integer_now_func（）
此函数仅与具有整数（而不是 TIMESTAMP/TIMESTAMPTZ/DATE）时间值的超级表相关。对于这种超表，它设置了一个函数，now()以时间列的单位返回值（当前时间）。这对于在基于整数的表上运行某些策略是必要的。许多策略仅适用于特定生命周期的块，并且需要返回当前时间的函数来确定块的生命周期。
CREATE OR REPLACE FUNCTION unix_now() returns BIGINT LANGUAGE SQL STABLE as $$ SELECT extract(epoch from now())::BIGINT $$;
SELECT set_integer_now_func('test_table_bigint', 'unix_now');14.添加额外的分区维度 add_dimension（）
向 TimescaleDB 超表添加额外的分区维度。选择作为维度的列可以使用间隔分区（例如，用于第二次分区）或散列分区。
该add_dimension命令只能在表转换为超表（通过create_hypertable）后执行，但同样只能在空的超表上运行。
空间分区：强烈建议对分布式超级表使用空间分区，以实现高效的横向扩展性能。对于仅存 在于单个节点上的常规超表，额外的分区可用于特殊用例，不建议大多数用户使用。
空间分区使用散列：每个不同的项目都被散列到 N 个存储桶之一。请记住，我们已经在使用（灵活的）时间间隔来管理块大小；空间分区的主要目的是在同一时间间隔内跨多个数据节点（在分布式超级表的情况下）或跨多个磁盘（在单节点部署的情况下）实现并行化。
跨多个数据节点并行查询
在分布式超级表中，空间分区允许跨数据节点并行插入，即使插入的行共享同一时间间隔的时间戳，从而提高摄取率。查询性能也通过能够并行查询跨节点，特别是当（例如，如在查询的全部或部分的聚合可以被“按下”到数据节点的好处 avg(temperature) FROM conditions GROUP BY hour, location 使用时location作为一个空间分区）。有关 详细信息，请参阅我们关于在分布式超表中进行分区的 最佳实践。
在单个节点上并行化磁盘 I/O
并行 I/O 可以在两种情况下受益：(a) 两个或多个并发查询应该能够从不同的磁盘并行读取，或者 (b) 单个查询应该能够使用查询并行化从多个磁盘并行读取.
因此，寻找并行 I/O 的用户有两个选择：
- 跨多个物理磁盘使用 RAID 设置，并将单个逻辑磁盘公开给超级表（即，通过单个表空间）。
- 对于每个物理磁盘，向数据库添加一个单独的表空间。TimescaleDB 允许您将多个表空间实际添加到单个超级表（尽管在幕后，超级表的块分布在与该超级表关联的表空间中）。
我们建议尽可能使用 RAID 设置，因为它支持上述两种并行化形式（即，对单独磁盘的单独查询，对多个磁盘的并行查询）。多表空间方法只支持前者。使用 RAID 设置， 不需要空间分区。
也就是说，在使用空间分区时，我们建议每个磁盘使用 1 个空间分区。
TimescaleDB并没有从一个非常大的数字空间的分区中受益（如独特的项目数您在分区字段预期）。大量这样的分区会导致每个分区的负载平衡较差（使用散列将项目映射到分区），以及增加某些类型查询的计划延迟。
# 首先将 table 转换conditions为 hypertable，只需在 column 上进行时间分区time，然后在location四个分区上添加一个额外的分区键：
SELECT create_hypertable('表名', 'time');
SELECT add_dimension('表名', 'location', number_partitions => 4);
# 将表转换conditions为具有时间分区time和空间分区（2 个分区）的超表location，然后添加两个额外的维度。
SELECT create_hypertable('表名', 'time', 'location', 2);
SELECT add_dimension('表名', 'time_received', chunk_time_interval => INTERVAL '1 day');
SELECT add_dimension('表名', 'device_id', number_partitions => 2);
SELECT add_dimension('表名', 'device_id', number_partitions => 2, if_not_exists => true);
# 在具有一个访问节点和两个数据节点的集群的分布式超级表的多节点示例中，配置访问节点以访问两个数据节点。然后，将 table 转换conditions为仅在 column 上进行时间分区的分布式超表time，最后添加location 具有两个分区的空间分区维度（作为附加数据节点的数量）。
SELECT add_data_node('dn1', host => 'dn1.example.com');
SELECT add_data_node('dn2', host => 'dn2.example.com');
SELECT create_distributed_hypertable('表名', 'time');
SELECT add_dimension('表名', 'location', number_partitions => 2);15.创建索引 CREATE INDEX
此选项扩展了[ CREATE INDEX][postgres-createindex]的能力，可以为其创建索引的每个块使用单独的事务，而不是对整个超表使用单个事务。这允许批量INSERT在CREATE INDEX命令的大部分持续时间内同时执行并执行其他操作。在单个块上创建索引时，它的功能就像CREATE INDEX在该块上调用了常规程序一样，但是其他块完全不受阻塞。
如果操作中途失败，则可能不会在所有超表块上创建索引。如果发生这种情况，hypertable 根表上的索引将被标记为无效（这可以通过\d+在 hypertable 上运行看到）。索引仍然有效，并将在新块上创建，但如果您希望确保 all 块具有索引的副本，请删除并重新创建它。
# 匿名索引
CREATE INDEX ON 表名(time, device_id) WITH (timescaledb.transaction_per_chunk);
# 其他索引
CREATE INDEX ON 表名(time, location) USING brin
WITH (timescaledb.transaction_per_chunk);16.超表的总磁盘占用 hypertable_size（）
获取超表使用的总磁盘空间，即表本身（包括块）、表上的任何索引和任何 toast 表的大小总和。大小以字节为单位报告。这相当于total_bytes从hypertable_detailed_size函数的输出计算列的总和。
# 获取超表的大小信息
SELECT hypertable_size('表名') ;
hypertable_size
-----------------
73728
# 获取所有超表的大小信息
SELECT hypertable_name, hypertable_size(format('%I.%I', hypertable_schema, hypertable_name)::regclass)
FROM timescaledb_information.hypertables;
17.超表大小信息 hypertable_detailed_size（）
获取有关超级表使用的磁盘空间的详细信息，返回表本身的大小信息、表上的任何索引、任何 Toast 表以及所有表的总大小。所有大小都以字节为单位报告。如果该函数在分布式超级表上执行，它会以每个节点（包括访问节点）的单独行的形式返回大小信息。
列出的访问节点没有用户指定的节点名称。通常，访问节点不持有数据，但仍维护例如占用少量磁盘空间的索引信息。
# 获取超表的大小信息
SELECT * FROM hypertable_detailed_size('表名') ORDER BY node_name;
table_bytes | index_bytes | toast_bytes | total_bytes | node_name
-------------+-------------+-------------+-------------+-------------
16384 | 40960 | 0 | 57344 | data_node_1
8192 | 24576 | 0 | 32768 | data_node_2
0 | 8192 | 0 | 8192 |18.超表的索引 磁盘占用hypertable_index_size（）
获取 hypertable 上的索引使用的磁盘空间，包括在所有块上提供索引所需的磁盘空间。大小以字节为单位报告。
# 获取 hypertable 上特定索引的大小。
conditions_table
Table "public.conditions_table"
Column | Type | Collation | Nullable | Default
--------+--------------------------+-----------+----------+---------
time | timestamp with time zone | | not null |
device | integer | | |
volume | integer | | |
Indexes:
"second_index" btree ("time")
"test_table_time_idx" btree ("time" DESC)
"third_index" btree ("time")
SELECT hypertable_index_size('second_index');
hypertable_index_size
-----------------------
163840
SELECT pg_size_pretty(hypertable_index_size('second_index'));
pg_size_pretty
----------------
160 kB19.超表的快 磁盘占用 chunks_detailed_size（）
获取属于超表的块所使用的磁盘空间的信息，返回每个块表的大小信息、块上的任何索引、任何 toast 表以及与块关联的总大小。所有大小都以字节为单位报告。
如果该函数在分布式超级表上执行，它会将磁盘空间使用信息作为每个节点的单独行返回。不包括访问节点，因为它没有任何本地块数据。
可以通过timescaledb_information.chunks视图访问与块关联的其他元数据。
SELECT * FROM chunks_detailed_size('dist_table')
ORDER BY chunk_name, node_name;
chunk_schema | chunk_name | table_bytes | index_bytes | toast_bytes | total_bytes | node_name
-----------------------+-----------------------+-------------+-------------+-------------+-------------+-----------------------
_timescaledb_internal | _dist_hyper_1_1_chunk | 8192 | 32768 | 0 | 40960 | data_node_1
_timescaledb_internal | _dist_hyper_1_2_chunk | 8192 | 32768 | 0 | 40960 | data_node_2
_timescaledb_internal | _dist_hyper_1_3_chunk | 8192 | 32768 | 0 | 40960 | data_node_3四.分布式超表
1.创建分布式超表
创建分布在多节点环境中的 TimescaleDB 超表。使用此功能代替create_hypertable。创建分布式超表时。
# 创建一个表，该表将按“位置”列跨数据节点进行分区。请注意，空间分区的数量自动等于分配给此超级表的数据节点数量（在这种情况下，所有已配置的数据节点，因为 data_nodes未指定）。
SELECT create_distributed_hypertable('表名', '时间分区字段', '空间分区字段');
# 使用一组特定的数据节点创建一个表
# data_nodes用于分布式超级表的数据节点集。如果不存在，则默认为访问节点（创建分布式超级表的节点）已知的所有数据节点。
SELECT create_distributed_hypertable('表名', '时间分区字段', '空间分区字段',
data_nodes => '{ "data_node_1", "data_node_2", "data_node_4", "data_node_7" }');2.添加新的数据节点 add_data_node()
在访问节点上添加一个新的数据节点，供分布式超级表使用。添加数据节点后创建的分布式超级表将自动使用数据节点，而现有的分布式超级表需要额外的 attach_data_node.
如果数据节点已经存在，则该命令将根据 的值中止并显示错误或通知if_not_exists。
出于安全考虑，只有超级用户或具有必要权限的用户才能添加数据节点（详见下文）。添加数据节点时，访问节点也会尝试连接到数据节点，因此需要一种对其进行身份验证的方法。TimescaleDB 当前支持多种不同的此类身份验证方法以提高灵活性（包括信任、用户映射、密码和证书方法）。有关节点到节点身份验证的更多信息，请参阅[设置多节点 TimescaleDB][多节点] 。
除非bootstrap为 false，否则该函数将尝试通过以下方式引导数据节点：
- 创建其中给出的数据库
database将用作新的数据节点。 - 在新数据库中加载 TimescaleDB 扩展。
- 设置元数据使数据节点成为分布式数据库的一部分。
请注意，引导期间不会在新数据节点上自动创建用户角色。该distributed_exec 过程可用于在添加数据节点后在数据节点上创建其他角色。
错误
如果出现以下情况会报错：
- 该函数在事务内执行。
- 该函数在已经是数据节点的数据库中执行。
- 数据节点已经存在并且
if_not_exists是FALSE。 - 由于网络故障或无效配置（例如，错误的端口，或无法对用户进行身份验证），接入节点无法连接到数据节点。
- 如果
bootstrap是FALSE并且数据库之前没有被引导。
权限
要添加数据节点，您必须是超级用户或拥有外部数据包装器的USAGE 权限timescaledb_fdw。要将此类权限授予常规用户角色，请执行以下操作：
GRANT USAGE ON FOREIGN DATA WRAPPER timescaledb_fdw TO <newrole>;但是请注意，数据节点上可能仍然需要超级用户权限才能引导它，包括在数据节点上创建 TimescaleDB 扩展，除非它已经安装。
# 假设您有一个现有的超表，conditions并且想要time用作时间分区列和location空间分区列。您还想在两个数据节点上分发 hypertable 的块，dn1.example.com并且 dn2.example.com：
SELECT add_data_node('dn1', host => 'dn1.example.com');
SELECT add_data_node('dn2', host => 'dn2.example.com');
SELECT create_distributed_hypertable('conditions', 'time', 'location');
# 如果要使用此实例本地的两个数据节点创建分布式数据库，可以编写：
SELECT add_data_node('dn1', host => 'localhost', database => 'dn1');
SELECT add_data_node('dn2', host => 'localhost', database => 'dn2');
SELECT create_distributed_hypertable('conditions', 'time', 'location');3.附加数据节点 attach_data_node()
将数据节点附加到超表。数据节点之前应该是使用add_data_node.
创建分布式超级表时，默认情况下会使用该超级表的所有可用数据节点，但如果在创建超级表后添加 数据节点，则现有分布式超级表不会自动使用该数据节点。
如果您希望超表使用稍后创建的数据节点，则必须使用此函数将数据节点附加到超表。
# 将数据节点附加dn3到conditions 先前使用 create_distributed_hypertable.
SELECT * FROM attach_data_node('dn3','conditions');
hypertable_id | node_hypertable_id | node_name
--------------+--------------------+-------------
5 | 3 | dn3
(1 row)4.分离数据节点 detach_data_node()
从一个超级表或所有超级表中分离数据节点。
分离数据节点的原因包括：
- 数据节点不应再被超级表使用，需要从使用它的所有超级表中删除
- 您希望为分布式超级表分配更少的数据节点
# dn3从 中分离数据节点conditions：
SELECT detach_data_node('dn3', 'conditions');5.删除数据节点 delete_data_node（）
该函数在访问节点上执行以从本地数据库中删除数据节点。作为删除的一部分，如果满足权限和数据完整性要求，数据节点将从使用它的所有超表分离。有关更多信息，请参阅detach_data_node。
删除数据节点严格来说是本地操作；数据节点本身不受影响，数据节点上相应的远程数据库保持不变，包括其所有数据。该操作是本地的，以确保即使远程数据节点没有响应也能完成，并避免数据节点上的数据意外丢失。
如果add_data_node不先删除数据节点上的数据库或使用另一个数据库，则无法再次添加相同的数据节点。这是为了防止添加以前属于同一个或另一个分布式数据库但不再同步的数据节点。
# 删除名为 的数据节点dn1：
SELECT delete_data_node('dn1');6.分布式执行 distributed_exec()
此过程用于在访问节点上跨分布式数据库的数据节点执行 SQL 命令。例如，一个用例是在分布式数据库中创建所需的角色和权限。
该过程可以事务性地运行分布式命令，因此命令可以在任何地方或任何地方执行。但是，并非所有 SQL 命令都可以在事务中运行。这可以用参数切换transactional。请注意，如果执行不是事务性的，则其中一个数据节点上的故障将需要手动处理任何引入的不一致。
请注意，该命令不在接入节点本身上执行，并且不可能在一次调用中将多个命令链接在一起。
# testrole在分布式数据库中的所有数据节点上创建角色
CALL distributed_exec($$ CREATE USER testrole WITH LOGIN $$);
# testrole在两个特定数据节点上创建角色
CALL distributed_exec($$ CREATE USER testrole WITH LOGIN $$, node_list => '{ "dn1", "dn2" }');
# dist_database在数据节点上新建数据库，需要设置transactional为 FALSE
CALL distributed_exec('CREATE DATABASE dist_database', transactional => FALSE);7.设置超表上空间维度的分区（切片）数。新分区仅影响新块 set_number_partitions()
设置超表上空间维度的分区（切片）数。新分区仅影响新块。
# 对于具有单一空间维度的表：
SELECT set_number_partitions('conditions', 2);
# 对于具有多个空间维度的表
SELECT set_number_partitions('conditions', 2, 'device_id');8.将分布式超级表的复制因子设置为给定值 set_replication_factor()
将分布式超级表的复制因子设置为给定值。更改复制因子不会影响现有块的副本数。更改复制因子后创建的块将根据复制因子的新值进行复制。如果无法满足复制因子，由于附加数据节点的数量小于新的复制因子，该命令会因错误而中止。
如果现有块的副本数少于复制因子的新值，则该函数将打印警告。
错误
如果出现以下情况会报错：
hypertable不是分布式超级表。replication_factor小于1，不能在分布式超表上设置。replication_factor大于附加数据节点的数量。
如果需要更大的复制因子，则需要使用attach_data_node附加更多数据节点。
# 将分布式超级表的复制因子更新为2
SELECT set_replication_factor('conditions', 2);
# 如果分布式超级表的任何现有块的副本少于 2 个，则警告示例
WARNING: hypertable "conditions" is under-replicated
DETAIL: Some chunks have less than 2 replicas.
# 为具有 2 个附加数据节点的超表提供太大复制因子的示例
SELECT set_replication_factor('conditions', 3);
ERROR: too big replication factor for hypertable "conditions"
DETAIL: The hypertable has 2 data nodes attached, while the replication factor is 3.
HINT: Decrease the replication factor or attach more data nodes to the hypertable.9.复制快
TimescaleDB 允许您将现有块复制到多节点环境中的新位置。这允许每个数据节点作为某些块的主节点和其他块的备份节点。如果一个数据节点出现故障，它的数据块已经存在于其他节点上，这些节点可以接管为它们提供服务的职责。
实验性功能可能有错误！它们可能不向后兼容，并且可以在未来的版本中删除。使用这些功能的风险由您自己承担，并且不要在生产中使用任何实验性功能。
#
CALL timescaledb_experimental.copy_chunk(‘_timescaledb_internal._dist_hyper_1_1_chunk’, ‘data_node_2’, ‘data_node_3’);10.移除快
TimescaleDB 允许您将块移动到其他数据节点。当将新数据节点添加到集群并且您希望在更多节点之间重新平衡存储时，可以使用此方法。当一个节点需要从集群中删除时，它也很有用，这只有在所有块都复制到其他数据节点上时才会发生。
实验性功能可能有错误！它们可能不向后兼容，并且可以在未来的版本中删除。使用这些功能的风险由您自己承担，并且不要在生产中使用任何实验性功能。
CALL timescaledb_experimental.move_chunk(‘_timescaledb_internal._dist_hyper_1_1_chunk’, ‘data_node_2’, ‘data_node_3’);
[postgres-cluster]: https://www.postgresql.org/docs/current/sql-cluster.html
[postgres-altertable]: https://www.postgresql.org/docs/13/sql-altertable.html
[using-data-tiering]: timescaledb/how-to-guides/data-tiering/
export const _frontmatter = {}五.压缩
1.开启压缩
timescaledb.compress 启用/禁用压缩
ALTER TABLE <table_name> SET (timescaledb.compress, timescaledb.compress_orderby = '<column_name> [ASC | DESC] [ NULLS { FIRST | LAST } ] [, ...]',
timescaledb.compress_segmentby = '<column_name> [, ...]'
);
ALTER TABLE metrics SET (timescaledb.compress, timescaledb.compress_orderby = 'time DESC', timescaledb.compress_segmentby = 'device_id');2.添加自动压缩策略 add_compression_policy()
允许您设置一个策略，系统将根据该策略在达到给定年龄后在后台自动压缩块。
请注意，压缩策略只能在已启用压缩的超级表上创建，例如，通过ALTER TABLE命令设置timescaledb.compress和其他配置参数。
# 添加一个策略来压缩 'cpu' hypertable 上超过 60 天的块。
SELECT add_compression_policy('cpu', INTERVAL '60d');
# 将压缩块策略添加到具有基于整数的时间列的超表：
SELECT add_compression_policy('table_with_bigint_time', BIGINT '600000');3.移除压缩策略 remove_compression_policy()
# 从“cpu”表中删除压缩策略：
SELECT remove_compression_policy('cpu');4.压缩快 compress_chunk（）
compress_chunk 函数用于压缩特定的块。当用户想要更多地控制压缩调度时，它最常用于代替 add_compression_policy函数。对于大多数用户，我们建议改用策略框架。
# 压缩单个块
SELECT compress_chunk('_timescaledb_internal._hyper_1_2_chunk');5.解压缩快 decompress_chunk（）
如果您需要修改或向已压缩的块添加数据，则需要先解压缩该块。这对于回填旧数据特别有用。
在为了数据回填或更新而解压缩块之前，您应该首先停止在您计划执行此操作的超表上处于活动状态的任何压缩策略。更新和/或回填完成后，只需重新打开策略，系统就会重新压缩您的块。
# 解压单个快
SELECT decompress_chunk('_timescaledb_internal._hyper_2_2_chunk');6.获取与超表压缩相关的统计信息hypertable_compression_stats
获取与超表压缩相关的统计信息。所有大小都以字节为单位。
SELECT * FROM hypertable_compression_stats('conditions');
-[ RECORD 1 ]------------------+------
total_chunks | 4
number_compressed_chunks | 1
before_compression_table_bytes | 8192
before_compression_index_bytes | 32768
before_compression_toast_bytes | 0
before_compression_total_bytes | 40960
after_compression_table_bytes | 8192
after_compression_index_bytes | 32768
after_compression_toast_bytes | 8192
after_compression_total_bytes | 49152
node_name
# 使用pg_size_pretty以更人性化的格式获取输出。
SELECT pg_size_pretty(after_compression_total_bytes) as total
FROM hypertable_compression_stats('conditions');
-[ RECORD 1 ]--+------
total | 48 kB7.获取与超表压缩相关的特定于块的统计信息chunk_compression_stats()
所有大小都以字节为单位。
SELECT * FROM chunk_compression_stats('conditions')
ORDER BY chunk_name LIMIT 2;
-[ RECORD 1 ]------------------+----------------------
chunk_schema | _timescaledb_internal
chunk_name | _hyper_1_1_chunk
compression_status | Uncompressed
before_compression_table_bytes |
before_compression_index_bytes |
before_compression_toast_bytes |
before_compression_total_bytes |
after_compression_table_bytes |
after_compression_index_bytes |
after_compression_toast_bytes |
after_compression_total_bytes |
node_name |
-[ RECORD 2 ]------------------+----------------------
chunk_schema | _timescaledb_internal
chunk_name | _hyper_1_2_chunk
compression_status | Compressed
before_compression_table_bytes | 8192
before_compression_index_bytes | 32768
before_compression_toast_bytes | 0
before_compression_total_bytes | 40960
after_compression_table_bytes | 8192
after_compression_index_bytes | 32768
after_compression_toast_bytes | 8192
after_compression_total_bytes | 49152
node_name |
# 使用pg_size_pretty以更人性化的格式获取输出
SELECT pg_size_pretty(after_compression_total_bytes) AS total
FROM chunk_compression_stats('conditions')
WHERE compression_status = 'Compressed';
-[ RECORD 1 ]--+------
total | 48 kB六.连续聚合
TimescaleDB 允许用户以预定义的时间间隔自动重新计算聚合并实现结果。这适用于经常使用的查询。
1.创建物化视图
连续聚合对它们可以支持的查询类型有一些限制，下面有更详细的描述。例如，该FROM子句必须仅提供一个超表，即不支持连接、CTE、视图或子查询。该GROUP BY子句必须在 hypertable 的时间列上包含一个时间段，并且所有聚合都必须是可并行化的。
# 创建连续聚合
CREATE MATERIALIZED VIEW <view_name> [ ( column_name [, ...] ) ]
WITH ( timescaledb.continuous [, timescaledb.<option> = <value> ] )
AS
<select_query>
[WITH [NO] DATA]
SELECT <grouping_exprs>, <aggregate_functions>
FROM <hypertable>
[WHERE ... ]
GROUP BY time_bucket( <const_value>, <partition_col_of_hypertable> ),
[ optional grouping exprs>]
[HAVING ...]refresh_continuous_aggregate除非WITH NO DATA给出（WITH DATA默认值），否则视图将自动刷新（如下所述 ）。- 该
SELECT查询应在所述语法上述规定的形式，这是在下列项目讨论。 FROM在SELECT查询子句中 只能指定一个超表。这意味着不支持包含更多超表、联接、表、视图、子查询。- 中使用的超级表
SELECT可能没有启用行级安全策略。 - 该
GROUP BY子句必须包含 time_bucket 表达式。的time_bucket表达式必须使用Hypertable的的时间维度柱。 time_bucket_gapfill不允许在连续聚合中使用，但可以SELECT在连续聚合视图中运行。- 一般来说，可以在视图定义中使用PostgreSQL 并行化的聚合，这包括大多数与 PostgreSQL 一起分发的聚合。 不允许使用
ORDER BY,DISTINCT和FILTER子句进行聚合。 - 所有的功能和它们的参数包含在
SELECT，GROUP BY和HAVING条款必须是不可变的。 - 该视图不允许是安全屏障视图。
- 窗口函数不能与连续聚合结合使用。
# 创建一个连续的聚合视图
CREATE MATERIALIZED VIEW continuous_aggregate_view( timec, minl, sumt, sumh )
WITH (timescaledb.continuous) AS
SELECT time_bucket('1day', timec), min(location), sum(temperature), sum(humidity)
FROM conditions
GROUP BY time_bucket('1day', timec)
# 在同一个原始超表之上添加额外的连续聚合
CREATE MATERIALIZED VIEW continuous_aggregate_view( timec, minl, sumt, sumh )
WITH (timescaledb.continuous) AS
SELECT time_bucket('30day', timec), min(location), sum(temperature), sum(humidity)
FROM conditions
GROUP BY time_bucket('30day', timec);
CREATE MATERIALIZED VIEW continuous_aggregate_view( timec, minl, sumt, sumh )
WITH (timescaledb.continuous) AS
SELECT time_bucket('1h', timec), min(location), sum(temperature), sum(humidity)
FROM conditions
GROUP BY time_bucket('1h', timec);2.改变物化视图
ALTER MATERIALIZED VIEW语句可用于修改连续聚合视图的某些WITH子句选项。 ALTER MATERIALIZED VIEW语句在连续聚合视图上还支持以下 PostgreSQL 子句：
RENAME TO重命名连续聚合视图的子句；SET SCHEMA子句为连续聚合视图设置新模式；SET TABLESPACE将连续聚合视图的物化移动到新表空间的子句；OWNER TO子句为连续聚合视图设置新的所有者。
# 要为连续聚合禁用实时聚合
ALTER MATERIALIZED VIEW <view_name> SET ( timescaledb.<option> = <value> [, ... ] )
# 示例
ALTER MATERIALIZED VIEW contagg_view SET (timescaledb.materialized_only = true);当前可以修改的唯一选项ALTER MATERIALIZED VIEW是materialized_only。其他选项 continuous和create_group_indexes只能在创建连续聚合时设置。
3.删除物化视图
可以使用该DROP MATERIALIZED VIEW语句删除连续聚合视图。
此语句删除连续聚合及其所有内部对象。要同时删除其他相关对象（例如在连续聚合上定义的视图），请添加该CASCADE 选项。删除连续聚合不会影响派生连续聚合的基础超表中的数据。
# 删除现有的连续聚合
DROP MATERIALIZED VIEW <view_name>;4.创建自动刷新连续聚合的策略 add_continuous_aggregate_policy（）
该start_offset应大于end_offset。的start_offset和end_offset参数应区别指定取决于Hypertable的时间列的类型：
- 对于具有 TIMESTAMP、TIMESTAMPTZ 和 DATE 时间列的超表：偏移量应为 INTERVAL 类型
- 对于具有基于整数的时间戳的超表：偏移量应该是整数类型。
# 添加一个每小时刷新上个月一次的策略，从聚合中排除最近一小时（出于性能原因，建议排除仍然看到大量写入的存储桶）
SELECT add_continuous_aggregate_policy('conditions_summary',
start_offset => INTERVAL '1 month',
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '1 hour');5.刷新连续聚合条件 refresh_continuous_aggregate（）
在和 给出的刷新窗口中刷新连续聚合的所有桶。window_start``window_end
连续聚合在时间段（例如，最小值、最大值、平均 1 天的数据价值）中实现time_bucket聚合，这由创建连续聚合时指定的 时间间隔确定。因此，在刷新连续聚合时，只会刷新完全适合刷新窗口的桶。换句话说，不可能在例如半个桶上计算聚合。因此，任何不适合给定刷新窗口的存储桶都将被排除在外。
该函数期望窗口参数值具有与连续聚合的时间段表达式兼容的时间类型-例如，如果时间段在 中指定 TIMESTAMP WITH TIME ZONE，则开始和结束时间应为日期或时间戳类型。请注意，使用该TIMESTAMP WITH TIME ZONE类型的连续聚合 与 UTC 时区对齐，因此，如果在本地时区中指定window_start和，则 window_end在刷新时需要考虑任何相对于 UTC 的时区偏移，以便与存储桶边界对齐。
# 刷新2020-01-01 ~ 2020-02-01 conditions之间的连续聚合条件。
CALL refresh_continuous_aggregate('conditions', '2020-01-01', '2020-02-01');6.删除连续聚合的刷新策略 remove_continuous_aggregate_policy（）
# 从“cpu_view”连续聚合中删除刷新策略：
SELECT remove_continuous_aggregate_policy('cpu_view');七.数据保留
1.数据保留策略
创建策略以在后台按计划删除早于特定超表或连续聚合的给定间隔的块。（见drop_chunks）。这会实施数据保留策略，并将按计划删除数据。每个超级表只能存在一个保留策略。
# 创建数据保留策略以丢弃超过 6 个月的数据块：
SELECT add_retention_policy('conditions', INTERVAL '6 months');
# 创建时间列为整数的数据保留策略:
SELECT add_retention_policy('conditions', BIGINT '600000');2.删除删除特定超表块的策略。remove_retention_policy()
# 删除表的现有数据保留策略conditions
SELECT remove_retention_policy('conditions');八.自定义操作,定时计算等
1.添加任务 add_job()
# 注册事件每小时运行一次
CREATE OR REPLACE PROCEDURE user_defined_action(job_id int, config jsonb) LANGUAGE PLPGSQL AS
$$
BEGIN
RAISE NOTICE 'Executing action % with config %', job_id, config;
END
$$;
SELECT add_job('user_defined_action','1h');2.修改任务 alter_job（）
通过TimescaleDB的自动化框架调度的操作在后台工作程序中定期运行。 您可以使用alter_job更改它们的执行计划。 要更改现有的作业，必须通过job_id引用它。 执行给定动作的job_id及其当前调度可以在timescaledb_information中找到。 Jobs视图，其中列出了关于每个计划操作的信息，以及timescaledb_information.job_stats中的信息。 job_stats视图还包含关于每个作业最后运行时间的信息，以及决定新调度应该是什么的其他有用统计信息。
# 重新安排 ID 为 1000 的作业，使其每两天运行一次。
SELECT alter_job(1000, schedule_interval => INTERVAL '2 days');
# 在 hypertable 上禁用压缩策略的调度conditions。
SELECT alter_job(job_id, scheduled => false)
FROM timescaledb_information.jobs
WHERE proc_name = 'policy_compression' AND hypertable_name = 'conditions'
# 重新安排连续聚合作业，1015以便作业的下一次执行在指定时间（2020 年 3 月 15 日上午 9:00:00）开始。
SELECT alter_job(1015, next_start => '2020-03-15 09:00:00.0+00');3.移除任务 delete_job（）
# 删除作业 ID 为 1000 的作业。
SELECT delete_job(1000);4.执行任务 run_job（）
在当前会话中运行先前注册的作业。这适用于用户定义的操作和策略。由于run_job是作为存储过程实现的，因此不能在 SELECT 查询中执行，而必须使用CALL.
使用run_job. 您可以将其与更高的日志级别一起使用来帮助调试问题。
# 将向客户端显示的日志级别设置为DEBUG1并运行作业 ID 为 1000 的作业。
SET client_min_messages TO DEBUG1;
CALL run_job(1000);九.功能增强
1.单表近似行数
根据目录估计获取超表、分布式超表或常规 PostgreSQL 表的近似行数。此函数支持具有嵌套继承和声明式分区的表。
近似行计数的准确性取决于具有有关表或超表的最新统计信息的数据库，这些统计信息由 VACUUM、ANALYZE 和一些 DDL 命令更新。如果您在表或超表上配置了 auto-vacuum，或者表的更改相对不频繁，您可能不需要显式分析您的表，如下所示。否则，如果您的表统计信息太过时，运行此命令将更新您的统计信息并产生更准确的近似结果。
# 获取单个超级表的近似行数
ANALYZE conditions;
SELECT * FROM approximate_row_count('conditions');2.单表中的第一条数据first()
该first聚合可以让你得到一个列的值由另一个为有序。例如，first(temperature, time)将根据聚合组内的时间返回最早的温度值。
该last和first命令也不会使用索引，而是通过他们的组进行顺序扫描。它们主要用于GROUP BY聚合内的有序选择，而不是作为ORDER BY time DESC LIMIT 1查找最新值（将使用索引）的子句的替代。
# 通过device_id获取最早的温度
SELECT device_id, first(temp, time)
FROM metrics
GROUP BY device_id;3.单表中的最后一条数据last()
该last聚合可以让你得到一个列的值由另一个为有序。例如，last(temperature, time)将根据聚合组内的时间返回最新的温度值。
# 在过去一天中每 5 分钟获取一次每个设备的温度：
SELECT device_id, time_bucket('5 minutes', time) AS interval,
last(temp, time)
FROM metrics
WHERE time > now () - INTERVAL '1 day'
GROUP BY device_id, interval
ORDER BY interval DESC;4.直方图 histogram()
该histogram()函数将一组值的分布表示为一个等宽桶数组。它将数据集划分为指定数量的桶 ( nbuckets)，范围从输入min和max值。
返回值是一个包含nbuckets+2 个桶的数组，中间的nbuckets桶用于指定范围内的值，数组头部的第一个桶用于下限以下的值min，最后一个桶用于大于或等于max边界。每个桶在其下限是包含的，在其上限是不包括的。因此，等于 的值min包含在以 开头的存储桶中min，但等于 的值max包含在最后一个存储桶中。
# 从readings数据集中对设备的电池电量进行简单分类 20代表分桶直方图下限(>=)，60代表分桶直方图的上限(<), 5代表直方图桶（分区）数量的整数值
SELECT device_id, histogram(battery_level, 20, 60, 5)
FROM readings
GROUP BY device_id
LIMIT 10;
# 预期输出
device_id | histogram
------------+------------------------------
demo000000 | {0,0,0,7,215,206,572}
demo000001 | {0,12,173,112,99,145,459}
demo000002 | {0,0,187,167,68,229,349}
demo000003 | {197,209,127,221,106,112,28}
demo000004 | {0,0,0,0,0,39,961}
demo000005 | {12,225,171,122,233,80,157}
demo000006 | {0,78,176,170,8,40,528}
demo000007 | {0,0,0,126,239,245,390}
demo000008 | {0,0,311,345,116,228,0}
demo000009 | {295,92,105,50,8,8,442}5.时间桶 time_bucket()
这是标准 PostgreSQLdate_trunc函数的更强大版本。它允许任意时间间隔，而不是由 提供的秒、分、小时等date_trunc。返回值是存储桶的开始时间。以下是有效使用它的必要信息。
TIMESTAMPTZ 参数按 UTC 时间分桶。所以桶的对齐是在UTC时间。这样做的一个后果是每日存储桶与 UTC 午夜对齐，而不是本地时间。
如果用户希望按本地时间对齐存储桶，则 TIMESTAMPTZ 输入应在传递给 time_bucket 之前转换为 TIMESTAMP（此类转换将值转换为本地时间）（请参见下面的示例）。请注意，沿夏令时边界，此类转换后聚合到存储桶中的数据量是不规则的：例如，如果 bucket_width 为 2 小时，则夏令时边界上本地时间存储的 UTC 小时数可以是 3 小时或 1 小时。
# 简单的 5 分钟平均
SELECT time_bucket('5 minutes', time) AS five_min, avg(cpu)
FROM metrics
GROUP BY five_min
ORDER BY five_min DESC LIMIT 10;
# 时间偏移
SELECT time_bucket('5 minutes', time) + '2.5 minutes'
AS five_min, avg(cpu)
FROM metrics
GROUP BY five_min
ORDER BY five_min DESC LIMIT 10;
# 对于四舍五入，移动对齐方式，使桶的中间位于 5 分钟标记处（并报告桶的中间）：
SELECT time_bucket('5 minutes', time, '-2.5 minutes') + '2.5 minutes'
AS five_min, avg(cpu)
FROM metrics
GROUP BY five_min
ORDER BY five_min DESC LIMIT 10;
# 要移动存储桶的对齐方式，您可以使用 origin 参数（作为时间戳、timestamptz 或日期类型传递）。在此示例中，我们将一周的开始时间移至星期日（默认为星期一）。
SELECT time_bucket('1 week', timetz, TIMESTAMPTZ '2017-12-31')
AS one_week, avg(cpu)
FROM metrics
GROUP BY one_week
WHERE time > TIMESTAMPTZ '2017-12-01' AND time < TIMESTAMPTZ '2018-01-03'
ORDER BY one_week DESC LIMIT 10;
# 在本地时间而不是 UTC 存储 TIMESTAMPTZ
SELECT time_bucket(INTERVAL '2 hours', timetz::TIMESTAMP)
AS five_min, avg(cpu)
FROM metrics
GROUP BY five_min
ORDER BY five_min DESC LIMIT 10;我们在此示例中使用的原点参数的值为 ，即2017-12-31所分析期间内的星期日。但是，提供给函数的原点可以在分析数据之前、期间或之后。所有桶都是相对于这个原点计算的。因此，在此示例中，可以使用任何星期日。请注意，因为time < TIMESTAMPTZ '2018-01-03'在此示例中，最后一个存储桶将只有 4 天的数据。
上述转换为 TIMESTAMP 会根据服务器的时区设置将时间转换为本地时间
6.时间桶（下一代） time_bucket_ng()
该time_bucket_ng()（下一代）的实验功能是原始的更新版本time_bucket()的功能。虽然 time_bucket仅适用于小时间单位，但 time_bucket_ng() 除了小时间单位外还支持年和月。
实验性功能可能有错误！它们可能不向后兼容，并且可以在未来的版本中删除。使用这些功能的风险由您自己承担，并且不要在生产中使用任何实验性功能。
该time_bucket()和time_bucket_ng()功能相似，但不完全兼容。有两个主要区别。
首先，time_bucket_ng()不适用于 之前的时间戳origin，而适用time_bucket()。
其次，默认origin值不同。time_bucket()使用 3 Jan 2000 的起始日期，因为该日期是星期一。这对于每周存储桶效果更好。time_bucket_ng()使用 1 Jan 2000 的起始日期，因为它是月份和年份的第一天。这对于每月或每年的聚合效果更好。
对于具有向后兼容性time_bucket()的timezone参数是可选的。请注意，如果您在不带timezone参数的情况下调用函数的 TIMESTAMPTZ 版本，则时区默认为会话的时区，因此该函数不能与连续聚合一起使用。
# 以三个月为间隔创建存储桶数据：
SELECT timescaledb_experimental.time_bucket_ng('3 month', date '2021-08-01');
time_bucket_ng
----------------
2021-07-01
(1 row)
# time_bucket_ng()以一年为间隔存储数据
SELECT timescaledb_experimental.time_bucket_ng('1 year', date '2021-08-01');
time_bucket_ng
----------------
2021-01-01
(1 row)
# 要将时间分成多个桶，请time_bucket_ng()使用名为 的时间起点origin。默认原点是2000-01-01。time_bucket_ng不能使用早于 的时间戳origin：
SELECT timescaledb_experimental.time_bucket_ng('100 years', timestamp '1988-05-08');
ERROR: origin must be before the given date
# 要绕过命名限制，您可以覆盖默认值origin：
-- working with timestamps before 2000-01-01
SELECT timescaledb_experimental.time_bucket_ng('100 years', timestamp '1988-05-08', origin => '1900-01-01');
time_bucket_ng
---------------------
1900-01-01 00:00:00
-- unlike the default origin, which is Saturday, 2000-01-03 is Monday
SELECT timescaledb_experimental.time_bucket_ng('1 week', timestamp '2021-08-26', origin => '2000-01-03');
time_bucket_ng
---------------------
2021-08-23 00:00:00
# 使用指定时区中的月份来存储数据
-- note that timestamptz is displayed differently depending on the session parameters
SET TIME ZONE 'Europe/Moscow';
SET
SELECT timescaledb_experimental.time_bucket_ng('1 month', timestamptz '2001-02-03 12:34:56 MSK', timezone => 'Europe/Moscow');
time_bucket_ng
------------------------
2001-02-01 00:00:00+03
# 以 7 天为间隔跟踪莫斯科的温度
CREATE TABLE conditions(
day DATE NOT NULL,
city text NOT NULL,
temperature INT NOT NULL);
SELECT create_hypertable(
'conditions', 'day',
chunk_time_interval => INTERVAL '1 day'
);
INSERT INTO conditions (day, city, temperature) VALUES
('2021-06-14', 'Moscow', 26),
('2021-06-15', 'Moscow', 22),
('2021-06-16', 'Moscow', 24),
('2021-06-17', 'Moscow', 24),
('2021-06-18', 'Moscow', 27),
('2021-06-19', 'Moscow', 28),
('2021-06-20', 'Moscow', 30),
('2021-06-21', 'Moscow', 31),
('2021-06-22', 'Moscow', 34),
('2021-06-23', 'Moscow', 34),
('2021-06-24', 'Moscow', 34),
('2021-06-25', 'Moscow', 32),
('2021-06-26', 'Moscow', 32),
('2021-06-27', 'Moscow', 31);
CREATE MATERIALIZED VIEW conditions_summary_weekly
WITH (timescaledb.continuous) AS
SELECT city,
timescaledb_experimental.time_bucket_ng('7 days', day) AS bucket,
MIN(temperature),
MAX(temperature)
FROM conditions
GROUP BY city, bucket;
SELECT to_char(bucket, 'YYYY-MM-DD'), city, min, max
FROM conditions_summary_weekly
ORDER BY bucket;
to_char | city | min | max
------------+--------+-----+-----
2021-06-12 | Moscow | 22 | 27
2021-06-19 | Moscow | 28 | 34
2021-06-26 | Moscow | 31 | 32
(3 rows)7.间隙填充和插值
time_bucket_gapfill（）
该time_bucket_gapfill函数的工作原理与和time_bucket之间的间隔类似，但也激活间隙填充。它只能与聚合查询一起使用。和之外的值将通过，但在指定范围之外不会进行间隙填充。start``finish``start``finish
我们建议尽可能使用 WHERE 子句（而不是仅仅start和finish参数），因为开始和结束参数不会过滤输入行。因此，如果没有 WHERE 子句，这将导致 TimescaleDB 的规划器选择所有数据而不执行约束排除以从进一步处理中排除块，这将降低性能。
在time_bucket_gapfill必须在一个查询或子查询一个顶级表达式，如图上述实例。例如，您不能执行类似round(time_bucket_gapfill(...))或转换 gapfill 调用的结果（除非作为子查询，其中外部查询执行类型转换）。
# 获取过去 7 天内每天的指标值
SELECT
time_bucket_gapfill('1 day', time) AS day,
device_id,
avg(value) AS value
FROM metrics
WHERE time > now() - INTERVAL '1 week' AND time < now()
GROUP BY day, device_id
ORDER BY day;
day | device_id | value
------------------------+-----------+-------
2019-01-10 01:00:00+01 | 1 |
2019-01-11 01:00:00+01 | 1 | 5.0
2019-01-12 01:00:00+01 | 1 |
2019-01-13 01:00:00+01 | 1 | 7.0
2019-01-14 01:00:00+01 | 1 |
2019-01-15 01:00:00+01 | 1 | 8.0
2019-01-16 01:00:00+01 | 1 | 9.0
(7 row)
# 如果在某个时间间隔内没有可用的值，则获取过去 7 天内每天的度量值，将之前看到的值结转
SELECT
time_bucket_gapfill('1 day', time) AS day,
device_id,
avg(value) AS value,
locf(avg(value))
FROM metrics
WHERE time > now() - INTERVAL '1 week' AND time < now()
GROUP BY day, device_id
ORDER BY day;
day | device_id | value | locf
------------------------+-----------+-------+------
2019-01-10 01:00:00+01 | 1 | |
2019-01-11 01:00:00+01 | 1 | 5.0 | 5.0
2019-01-12 01:00:00+01 | 1 | | 5.0
2019-01-13 01:00:00+01 | 1 | 7.0 | 7.0
2019-01-14 01:00:00+01 | 1 | | 7.0
2019-01-15 01:00:00+01 | 1 | 8.0 | 8.0
2019-01-16 01:00:00+01 | 1 | 9.0 | 9.0
# 获取过去 7 天内每天插入缺失值的指标值：
SELECT
time_bucket_gapfill('5 minutes', time) AS day,
device_id,
avg(value) AS value,
interpolate(avg(value))
FROM metrics
WHERE time > now() - INTERVAL '1 week' AND time < now()
GROUP BY day, device_id
ORDER BY day;
day | device_id | value | interpolate
------------------------+-----------+-------+-------------
2019-01-10 01:00:00+01 | 1 | |
2019-01-11 01:00:00+01 | 1 | 5.0 | 5.0
2019-01-12 01:00:00+01 | 1 | | 6.0
2019-01-13 01:00:00+01 | 1 | 7.0 | 7.0
2019-01-14 01:00:00+01 | 1 | | 7.5
2019-01-15 01:00:00+01 | 1 | 8.0 | 8.0
2019-01-16 01:00:00+01 | 1 | 9.0 | 9.0
locf()
该locf功能（最后一次观察结转）允许您将聚合组中最后看到的值向前移转。它只能在带有time_bucket_gapfill的聚合查询中使用。该locf函数调用不能嵌套在其他函数调用。
因为 locf 函数依赖于在每个存储桶周期之前有值来结转，如果第一个存储桶不包含值，它可能没有足够的数据来填充值。例如，该函数需要在第一个时间段之前查看，但查询的外部时间谓词 WHERE time > … 通常会限制该函数仅评估该时间范围内的值。因此，prev表达式告诉函数如何查找时间谓词指定范围之外的值。在prev当没有先前的值由外部查询（即，在被查询的时间范围内的第一桶为空）返回表达只会进行评估。
# 获取过去 7 天内每个设备每天的平均温度，结转缺失读数的最后一个值：
SELECT
time_bucket_gapfill('1 day', time, now() - INTERVAL '1 week', now()) AS day,
device_id,
avg(temperature) AS value,
locf(avg(temperature))
FROM metrics
WHERE time > now () - INTERVAL '1 week'
GROUP BY day, device_id
ORDER BY day;
day | device_id | value | locf
------------------------+-----------+-------+------
2019-01-10 01:00:00+01 | 1 | |
2019-01-11 01:00:00+01 | 1 | 5.0 | 5.0
2019-01-12 01:00:00+01 | 1 | | 5.0
2019-01-13 01:00:00+01 | 1 | 7.0 | 7.0
2019-01-14 01:00:00+01 | 1 | | 7.0
2019-01-15 01:00:00+01 | 1 | 8.0 | 8.0
2019-01-16 01:00:00+01 | 1 | 9.0 | 9.0
(7 row)
# 获取过去 7 天内每个设备每天的平均温度，通过越界查找结转缺失读数的最后一个值
SELECT
time_bucket_gapfill('1 day', time, now() - INTERVAL '1 week', now()) AS day,
device_id,
avg(temperature) AS value,
locf(
avg(temperature),
(SELECT temperature FROM metrics m2 WHERE m2.time < now() - INTERVAL '2 week' AND m.device_id = m2.device_id ORDER BY time DESC LIMIT 1)
)
FROM metrics m
WHERE time > now () - INTERVAL '1 week'
GROUP BY day, device_id
ORDER BY day;
day | device_id | value | locf
------------------------+-----------+-------+------
2019-01-10 01:00:00+01 | 1 | | 1.0
2019-01-11 01:00:00+01 | 1 | 5.0 | 5.0
2019-01-12 01:00:00+01 | 1 | | 5.0
2019-01-13 01:00:00+01 | 1 | 7.0 | 7.0
2019-01-14 01:00:00+01 | 1 | | 7.0
2019-01-15 01:00:00+01 | 1 | 8.0 | 8.0
2019-01-16 01:00:00+01 | 1 | 9.0 | 9.0
(7 row)interpolate()
该interpolate函数对缺失值进行线性插值。它只能在带有time_bucket_gapfill的聚合查询中使用。该interpolate函数调用不能嵌套在其他函数调用。
因为插值函数依赖于在每个分桶周期之前和之后的值来计算内插值，所以如果这些桶不包含有效值，则它可能没有足够的数据来计算第一个和最后一个时间桶的插值。例如，插值需要在第一个时间段之前查看，但查询的外部时间谓词 WHERE time > … 通常会限制函数仅评估此时间范围内的值。因此，prev和next表达式告诉函数如何查找时间谓词指定范围之外的值。仅当外部查询没有返回合适的值（即，查询时间范围内的第一个和/或最后一个桶为空）时，才会评估这些表达式。返回的prevand记录next必须是时间、值元组。time 的数据类型需要与time_bucket_gapfill调用中的 time 数据类型相同。value 的数据类型需要value与interpolate调用的数据类型相同。
# 获取上周每个设备每天的温度，插入缺失的读数：
SELECT
time_bucket_gapfill('1 day', time, now() - INTERVAL '1 week', now()) AS day,
device_id,
avg(temperature) AS value,
interpolate(avg(temperature))
FROM metrics
WHERE time > now () - INTERVAL '1 week'
GROUP BY day, device_id
ORDER BY day;
day | device_id | value | interpolate
------------------------+-----------+-------+-------------
2019-01-10 01:00:00+01 | 1 | |
2019-01-11 01:00:00+01 | 1 | 5.0 | 5.0
2019-01-12 01:00:00+01 | 1 | | 6.0
2019-01-13 01:00:00+01 | 1 | 7.0 | 7.0
2019-01-14 01:00:00+01 | 1 | | 7.5
2019-01-15 01:00:00+01 | 1 | 8.0 | 8.0
2019-01-16 01:00:00+01 | 1 | 9.0 | 9.0
(7 row)
# 获取过去 7 天内每个设备每天的平均温度，通过对间隙填充时间范围之前和之后的值的查找查询来插入缺失的读数：
SELECT
time_bucket_gapfill('1 day', time, now() - INTERVAL '1 week', now()) AS day,
device_id,
avg(value) AS value,
interpolate(avg(temperature),
(SELECT (time,temperature) FROM metrics m2 WHERE m2.time < now() - INTERVAL '1 week' AND m.device_id = m2.device_id ORDER BY time DESC LIMIT 1),
(SELECT (time,temperature) FROM metrics m2 WHERE m2.time > now() AND m.device_id = m2.device_id ORDER BY time DESC LIMIT 1)
) AS interpolate
FROM metrics m
WHERE time > now () - INTERVAL '1 week'
GROUP BY day, device_id
ORDER BY day;
day | device_id | value | interpolate
------------------------+-----------+-------+-------------
2019-01-10 01:00:00+01 | 1 | | 3.0
2019-01-11 01:00:00+01 | 1 | 5.0 | 5.0
2019-01-12 01:00:00+01 | 1 | | 6.0
2019-01-13 01:00:00+01 | 1 | 7.0 | 7.0
2019-01-14 01:00:00+01 | 1 | | 7.5
2019-01-15 01:00:00+01 | 1 | 8.0 | 8.0
2019-01-16 01:00:00+01 | 1 | 9.0 | 9.0
(7 row)
8.平均数 AVG()
TimescaleDB 中的百分位数分两步计算。首先，我们必须创建一个百分位数估计器，它可以使用percentile_agg()或 一种高级聚合方法 uddsketch()或来创建 tdigest()。可以使用rollupfunction组合或重新聚合估算器。
创建估算器后，可以通过使用聚合结果作为以下函数的输入来获得所需的值：
百分位近似数 percentile_agg()
# 默认的百分位聚合函数。它使用UddSketch 算法， 有 200 个桶，初始最大误差为 0.001。这适用于百分比近似的最常见用例。有关百分位近似算法的更高级用法，请参阅高级用法。这将创建一个Uddsketch百分位数估计器，它通常与approx_percentile()访问器函数一起使用以提取近似百分位，但是它的形式可以使用rollup函数和/或任何 访问器函数重新聚合。
percentile_agg(
value DOUBLE PRECISION
) RETURNS UddSketch
# 使用percentile_agg()plusapprox_percentile访问器函数获取近似的第一个百分位数。
SELECT
approx_percentile(0.01, percentile_agg(data))
FROM generate_series(0, 100) data;
approx_percentile
-------------------
0.999
# 该percentile_agg函数通常用于创建连续聚合，之后您可以使用多个访问器进行回顾性分析。
CREATE MATERIALIZED VIEW foo_hourly
WITH (timescaledb.continuous)
AS SELECT
time_bucket('1 h'::interval, ts) as bucket,
percentile_agg(value) as pct_agg
FROM foo
GROUP BY 1;从百分位数估计中获取百分位数的近似值approx_percentile()
approx_percentile(
percentile DOUBLE PRECISION,
sketch uddsketch
) RETURNS DOUBLE PRECISION
SELECT
approx_percentile(0.01, percentile_agg(data))
FROM generate_series(0, 100) data;
approx_percentile
-------------------
0.999估计给定值将位于 UddSketch 中的哪个百分位
approx_percentile_rank(
value DOUBLE PRECISION,
sketch UddSketch
) RETURNS UddSketch
SELECT
approx_percentile_rank(99, percentile_agg(data))
FROM generate_series(0, 100) data;
approx_percentile_rank
----------------------------
0.9851485148514851归纳 rollup()
rollup(
sketch uddsketch
) RETURNS UddSketch
rollup(
digest tdigest
) RETURNS tdigest
# 我们将每小时连续的聚合重新聚合到每日存储桶中，与uddsketch&的用法tdigest类似：
CREATE MATERIALIZED VIEW foo_hourly
WITH (timescaledb.continuous)
AS SELECT
time_bucket('1 h'::interval, ts) as bucket,
percentile_agg(value) as pct_agg
FROM foo
GROUP BY 1;
SELECT
time_bucket('1 day'::interval, bucket) as bucket,
approx_percentile(0.95, rollup(pct_agg)) as p95,
approx_percentile(0.99, rollup(pct_agg)) as p99
FROM foo_hourly
GROUP BY 1;最大值max_val()
从 t-digest 中获取最大值（不适用于percentile_agg或uddsketch基于估计量）。当需要最大值和百分位数估计作为连续聚合的一部分时，提供此选项是为了节省空间。您可以简单地计算单个百分位数估计量，而无需指定单独的 max聚合，只需max_val从百分位数估计量中提取。
max_val(digest TDigest) RETURNS DOUBLE PRECISION
SELECT max_val(tdigest(100, data))
FROM generate_series(1, 100) data;
max_val
---------
100准确平均值 mean()
获取百分位数估计中所有值的精确平均值。（返回的百分位数是估计值，平均值是准确的）。当均值和百分位数估计都需要作为连续聚合的一部分时，这样做是为了节省空间。您可以简单地计算单个百分位数估计量，而无需指定单独的 avg聚合，只需从百分位数估计量中提取均值即可。
mean(sketch UddSketch) RETURNS DOUBLE PRECISION
mean(digest tdigest) RETURNS DOUBLE PRECISION
SELECT mean(percentile_agg(data))
FROM generate_series(0, 100) data;
mean
------
50相对于正确值的最大误差值error()
这将返回百分位数估计将具有的最大相对误差（相对于正确值）。这意味着实际值将落在由 定义的范围内approx_percentile(sketch) +/- approx_percentile(sketch)*error(sketch)。
SELECT error(percentile_agg(data))
FROM generate_series(0, 100) data;
error
-------
0.001最小值
从 t-digest 中获取最小值（不适用于percentile_agg或uddsketch基于估计量）。提供此功能是为了在需要最小值和百分位数估计值作为连续聚合的一部分时节省空间。您可以简单地计算单个百分位数估计量，而无需指定单独的 min聚合，只需min_val从百分位数估计量中提取。
min_val(digest TDigest) RETURNS DOUBLE PRECISION
SELECT min_val(tdigest(100, data))
FROM generate_series(1, 100) data;
min_val
-----------
1百分位数估计中包含的值的数量num_vals()
获取百分位数估计中包含的值的数量。当需要计数和百分位数估计作为连续聚合的一部分时，提供此选项是为了节省空间。您可以简单地计算单个百分位数估计量，而无需指定单独的 count聚合，只需num_vals从百分位数估计量中提取。
num_vals(sketch UddSketch) RETURNS DOUBLE PRECISION
num_vals(digest tdigest) RETURNS DOUBLE PRECISION
SELECT num_vals(percentile_agg(data))
FROM generate_series(0, 100) data;
num_vals
-----------
1019.时间加权平均函数
时间加权平均值通常用于时间序列未均匀采样的情况，因此传统平均值会产生误导性结果。考虑一个电压传感器，它每 5 分钟发送一次读数，或者每当值从前一个读数变化超过 1 V 时发送一次读数。如果结果通常是稳定的，但有一些快速移动的瞬态，所有点的简单平均将倾向于过度加权瞬态而不是稳定读数。时间加权平均值根据每个值根据其周围的点发生的持续时间对每个值进行加权，并为不均匀间隔的系列生成正确的结果。
TimescaleDB 工具包的时间加权平均值作为一个聚合实现，它使用最后一次观察结转 (LOCF) 方法或线性插值方法对每个值进行加权。
时间权重
# TimeWeightSummary从时间戳和关联值生成 a 的聚合。
time_weight(
method TEXT,
ts TIMESTAMPTZ,
value DOUBLE PRECISION
) RETURNS TimeWeightSummary
WITH t as (
SELECT
time_bucket('1 day'::interval, ts) as dt,
time_weight('Linear', ts, val) AS tw -- get a time weight summary
FROM foo
WHERE measure_id = 10
GROUP BY time_bucket('1 day'::interval, ts)
)
SELECT
dt,
average(tw) -- extract the average from the time weight summary
FROM t;插值方法详细信息
离散时间值并不总是允许明显计算时间加权平均值。为了计算时间加权平均值，我们需要选择如何对每个值进行加权。我们目前使用的两种方法是最后一次观察结转（LOCF）和线性插值。
在 LOCF 方法中，该值被视为在看到下一个值之前保持不变。当传感器或测量设备仅在值发生变化时才发送测量值时，通常使用 LOCF 方法。
线性插值方法将任何两个测量值之间的值视为它们位于连接两个测量值的线上。线性插值方法用于解决传感器不提供任何保证的不规则采样数据。
并行和排序
我们执行的时间加权平均计算需要对输入进行严格排序，因此这些计算在严格的 Postgres 意义上是不可并行化的。这是因为当 Postgres 执行并行处理时，它会随机分发行，基本上就像它看到它们给工作人员一样。但是，如果您的并行性可以保证不相交（在时间上）的行集，则算法可以并行化，只要在某个时间范围内，所有行都转到同一个工作人员。连续聚合和分布式超表都是这种情况（只要分区键在 group by 中，尽管聚合本身没有其他意义）。
如果尝试组合重叠TimeWeightSummaries，我们会抛出错误，例如，在我们上面的示例中，如果您尝试组合跨measure_ids它的摘要 会出错。这是因为插值技术实际上只在由单个measure_id. 然而，鉴于产生的时间加权平均值是一个无量纲量，时间加权平均值的简单平均值应该更好地代表跨设备的变化，因此对于跨多个时间序列的基线之类的建议将类似于：
WITH t as (SELECT measure_id,
average(
time_weight('LOCF', ts, val)
) as time_weighted_average
FROM foo
GROUP BY measure_id)
SELECT avg(time_weighted_average) -- use the normal avg function to average our time weighted averages
FROM t;在内部，看到的第一个和最后一个点以及计算出的加权总和存储在每个点中，TimeWeightSummary并TimeWeightSummary在重新聚合或调用 Postgres 组合函数时用于与相邻点 组合。通常，这些函数在多节点上下文中支持部分聚合和分区聚合，但不可并行化（在 Postgres 意义上，这要求它们接受潜在的重叠输入）。
因为它们需要有序集，所以聚合建立了一个输入数据缓冲区，对它进行排序，然后执行适当的聚合步骤。在事实证明内存太小而无法建立导致 OOM 或其他问题的点缓冲区的情况下，多级聚合可能很有用。按照我们上面的例子：
WITH t as (SELECT measure_id,
time_bucket('1 day'::interval, ts),
time_weight('LOCF', ts, val)
FROM foo
GROUP BY measure_id, time_bucket('1 day'::interval, ts)
)
SELECT measure_id,
average(
rollup(time_weight)
)
FROM t
GROUP BY measure_id;汇总 TimeWeightSummary
WITH t as (
SELECT
date_trunc('day', ts) as dt,
time_weight('Linear', ts, val) AS tw -- get a time weight summary
FROM foo
WHERE measure_id = 10
GROUP BY date_trunc('day', ts)
), q as (
SELECT rollup(tw) AS full_tw -- do a second level of aggregation to get the full time weighted average
FROM t
)
SELECT
dt,
average(tw), -- extract the average from the time weight summary
average(tw) / (SELECT average(full_tw) FROM q LIMIT 1) as normalized -- get the normalized average
FROM t;平均数average()
SELECT
id,
average(tws)
FROM (
SELECT
id,
time_weight('LOCF', ts, val) AS tws
FROM foo
GROUP BY id
) t十.信息视图
获取有关超表块的元数据 timescaledb_information.chunks
| 列名 | 类型 | 描述 |
|---|---|---|
hypertable_schema |
TEXT | 超表的架构名称 |
hypertable_name |
TEXT | 超表的表名 |
chunk_schema |
TEXT | 块的模式名称 |
chunk_name |
TEXT | 块的名称 |
primary_dimension |
TEXT | 作为主要维度的列的名称 |
primary_dimension_type |
REGTYPE | 作为主要维度的列的类型 |
range_start |
TIMESTAMP WITH TIME ZONE | 块尺寸范围的开始 |
range_end |
TIMESTAMP WITH TIME ZONE | 区块维度的范围结束 |
range_start_integer |
BIGINT | 块的维度范围的开始，如果维度类型是基于整数的 |
range_end_integer |
BIGINT | 块维度的范围结束，如果维度类型是基于整数的 |
is_compressed |
BOOLEAN | 块中的数据是否被压缩？ 请注意，对于分布式超级表，这是访问节点上块的缓存压缩状态。在某些情况下，访问节点和数据节点上的缓存状态将不同步。例如，如果用户在数据节点而不是访问节点上压缩或解压缩块，或者直接在数据节点上设置压缩策略。 使用chunk_compression_stats()函数获取分布式块的实时压缩状态。 |
chunk_tablespace |
TEXT | 块使用的表空间 |
data_nodes |
ARRAY | 复制块的节点。这仅适用于分布式超表的块 |
CREATE TABLESPACE tablespace1 location '/usr/local/pgsql/data1';
CREATE TABLE hyper_int (a_col integer, b_col integer, c integer);
SELECT table_name from create_hypertable('hyper_int', 'a_col', chunk_time_interval=> 10);
CREATE OR REPLACE FUNCTION integer_now_hyper_int() returns int LANGUAGE SQL STABLE as $$ SELECT coalesce(max(a_col), 0) FROM hyper_int $$;
SELECT set_integer_now_func('hyper_int', 'integer_now_hyper_int');
INSERT INTO hyper_int SELECT generate_series(1,5,1), 10, 50;
SELECT attach_tablespace('tablespace1', 'hyper_int');
INSERT INTO hyper_int VALUES( 25 , 14 , 20), ( 25, 15, 20), (25, 16, 20);
SELECT * FROM timescaledb_information.chunks WHERE hypertable_name = 'hyper_int';
-[ RECORD 1 ]----------+----------------------
hypertable_schema | public
hypertable_name | hyper_int
chunk_schema | _timescaledb_internal
chunk_name | _hyper_7_10_chunk
primary_dimension | a_col
primary_dimension_type | integer
range_start |
range_end |
range_start_integer | 0
range_end_integer | 10
is_compressed | f
chunk_tablespace |
data_nodes |
-[ RECORD 2 ]----------+----------------------
hypertable_schema | public
hypertable_name | hyper_int
chunk_schema | _timescaledb_internal
chunk_name | _hyper_7_11_chunk
primary_dimension | a_col
primary_dimension_type | integer
range_start |
range_end |
range_start_integer | 20
range_end_integer | 30
is_compressed | f
chunk_tablespace | tablespace1
data_nodes |获取连续聚合的元数据和设置信息timescaledb_information.continuous_aggregates
| 列名 | 类型 | 描述 |
|---|---|---|
hypertable_schema |
TEXT | 连续聚合视图中超表的架构 |
hypertable_name |
TEXT | 连续聚合视图中超表的名称 |
view_schema |
TEXT | 连续聚合视图的架构 |
view_name |
TEXT | 用户提供的连续聚合视图名称 |
view_owner |
TEXT | 连续聚合视图的所有者 |
materialized_only |
BOOLEAN | 查询连续聚合视图时仅返回物化数据。 |
materialization_hypertable_schema |
TEXT | 底层物化表的架构 |
materialization_hypertable_name |
TEXT | 底层物化表的名称 |
view_definition |
TEXT | SELECT 查询连续聚合视图 |
SELECT * FROM timescaledb_information.continuous_aggregates;
-[ RECORD 1 ]---------------------+-------------------------------------------------
hypertable_schema | public
hypertable_name | foo
view_schema | public
view_name | contagg_view
view_owner | postgres
materialized_only | f
materialization_hypertable_schema | _timescaledb_internal
materialization_hypertable_name | _materialized_hypertable_2
view_definition | SELECT foo.a, +
| COUNT(foo.b) AS countb +
| FROM foo +
| GROUP BY (time_bucket('1 day', foo.a)), foo.a;获取有关 hypertables 的压缩相关设置的信息 timescaledb_information.compression_settings
| 列名 | 类型 | 描述 |
|---|---|---|
hypertable_schema |
TEXT | 超表的架构名称 |
hypertable_name |
TEXT | 超表的表名 |
attname |
TEXT | 压缩设置中使用的列的名称 |
segmentby_column_index |
SMALLINT | attname 在 compress_segmentby 列表中的位置 |
orderby_column_index |
SMALLINT | attname 在 compress_orderby 列表中的位置 |
orderby_asc |
BOOLEAN | 如果这用于按 ASC 订购，则为 True，按 DESC 订购则为 False |
orderby_nullsfirst |
BOOLEAN | 如果此列的空值首先排序，则为 True，如果空值最后排序，则为 False |
CREATE TABLE hypertab (a_col integer, b_col integer, c_col integer, d_col integer, e_col integer);
SELECT table_name FROM create_hypertable('hypertab', 'a_col');
ALTER TABLE hypertab SET (timescaledb.compress, timescaledb.compress_segmentby = 'a_col,b_col',
timescaledb.compress_orderby = 'c_col desc, d_col asc nulls last');
SELECT * FROM timescaledb_information.compression_settings WHERE hypertable_name = 'hypertab';
hypertable_schema | hypertable_name | attname | segmentby_column_index | orderby_column_in
dex | orderby_asc | orderby_nullsfirst
-------------+------------+---------+------------------------+------------------
----+-------------+--------------------
public | hypertab | a_col | 1 |
| |
public | hypertab | b_col | 2 |
| |
public | hypertab | c_col | |
1 | f | t
public | hypertab | d_col | |
2 | t | f
(4 rows)获取有关数据节点的信息 timescaledb_information.data_nodes
| 列名 | 类型 | 描述 |
|---|---|---|
node_name |
TEXT | 数据节点名称。 |
owner |
REGCLASS | 添加数据节点的用户的 Oid。 |
options |
JSONB | 创建数据节点时使用的选项。 |
SELECT * FROM timescaledb_information.data_nodes;
node_name | owner | options
--------------+------------+--------------------------------
dn1 | postgres | {host=localhost,port=15431,dbname=test}
dn2 | postgres | {host=localhost,port=15432,dbname=test}
(2 rows)获取有关超表的元数据信息 timescaledb_information.hypertables
| 列名 | 类型 | 描述 |
|---|---|---|
hypertable_schema |
TEXT | 超表的架构名称 |
hypertable_name |
TEXT | 超表的表名 |
owner |
TEXT | 超级表的所有者 |
num_dimensions |
SMALLINT | 维数 |
num_chunks |
BIGINT | 块数 |
compression_enabled |
BOOLEAN | 是否在 hypertable 上启用了压缩？ |
is_distributed |
BOOLEAN | hypertable 是分布式的吗？ |
replication_factor |
SMALLINT | 分布式超表的复制因子 |
data_nodes |
ARRAY | 分布超表的节点 |
tablespaces |
ARRAY | 附加到超表的表空间 |
CREATE TABLE dist_table(time timestamptz, device int, temp float);
SELECT create_distributed_hypertable('dist_table', 'time', 'device', replication_factor => 2);
SELECT * FROM timescaledb_information.hypertables
WHERE hypertable_name = 'dist_table';
-[ RECORD 1 ]-------+-----------
hypertable_schema | public
hypertable_name | dist_table
owner | postgres
num_dimensions | 2
num_chunks | 3
compression_enabled | f
is_distributed | t
replication_factor | 2
data_nodes | {node_1, node_2}
tablespaces |获取有关超表维度的元数据，为超表的每个维度返回一行元数据 timescaledb_information.dimensions
获取有关超表维度的元数据，为超表的每个维度返回一行元数据。例如，对于时间和空间分区的超表，将为超表返回两行元数据。
基于时间的维度列具有整数数据类型（bigint、integer、smallint）或与时间相关的数据类型（timestamptz、timestamp、date）。该time_interval列是为使用时间数据类型的超表定义的。或者，对于使用整数数据类型的超表，定义了integer_interval和integer_now_func列。
对于基于空间的维度，将返回指定其数量的元数据num_partitions。在time_interval与integer_interval列不适用于基于空间的尺寸。
| 列名 | 类型 | 描述 |
|---|---|---|
hypertable_schema |
TEXT | 超表的架构名称 |
hypertable_name |
TEXT | 超表的表名 |
dimension_number |
BIGINT | hypertable 的维数，从 1 开始 |
column_name |
TEXT | 用于创建此维度的列的名称 |
column_type |
REGTYPE | 用于创建此维度的列的类型 |
dimension_type |
TEXT | 这是基于时间还是基于空间的维度？ |
time_interval |
INTERVAL | 如果列类型基于 Postgres 时间数据类型，则主维度的时间间隔 |
integer_interval |
BIGINT | 如果列类型是整数数据类型，则主维度的整数间隔 |
integer_now_func |
TEXT | 如果列类型是基于整数的数据类型，则用于主要维度的 integer_now 函数 |
num_partitions |
SMALLINT | 维度的分区数 |
# 获取有关超表维度的信息
--Create a time and space partitioned hypertable
CREATE TABLE dist_table(time timestamptz, device int, temp float);
SELECT create_hypertable('dist_table', 'time', 'device', chunk_time_interval=> INTERVAL '7 days', number_partitions=>3);
SELECT * from timescaledb_information.dimensions
ORDER BY hypertable_name, dimension_number;
-[ RECORD 1 ]-----+-------------------------
hypertable_schema | public
hypertable_name | dist_table
dimension_number | 1
column_name | time
column_type | timestamp with time zone
dimension_type | Time
time_interval | 7 days
integer_interval |
integer_now_func |
num_partitions |
-[ RECORD 2 ]-----+-------------------------
hypertable_schema | public
hypertable_name | dist_table
dimension_number | 2
column_name | device
column_type | integer
dimension_type | Space
time_interval |
integer_interval |
integer_now_func |
num_partitions | 2
# 获取有关具有 2 个基于时间的维度的超表维度的信息
CREATE TABLE hyper_2dim (a_col date, b_col timestamp, c_col integer);
SELECT table_name from create_hypertable('hyper_2dim', 'a_col');
SELECT add_dimension('hyper_2dim', 'b_col', chunk_time_interval=> '7 days');
SELECT * FROM timescaledb_information.dimensions WHERE hypertable_name = 'hyper_2dim';
-[ RECORD 1 ]-----+----------------------------
hypertable_schema | public
hypertable_name | hyper_2dim
dimension_number | 1
column_name | a_col
column_type | date
dimension_type | Time
time_interval | 7 days
integer_interval |
integer_now_func |
num_partitions |
-[ RECORD 2 ]-----+----------------------------
hypertable_schema | public
hypertable_name | hyper_2dim
dimension_number | 2
column_name | b_col
column_type | timestamp without time zone
dimension_type | Time
time_interval | 7 days
integer_interval |
integer_now_func |
num_partitions |显示有关在自动化框架中注册的所有任务的信息 timescaledb_information.jobs
| 列名 | 类型 | 描述 |
|---|---|---|
job_id |
INTEGER | 后台作业的id |
application_name |
TEXT | 策略或用户定义操作的名称 |
schedule_interval |
INTERVAL | 作业运行的时间间隔 |
max_runtime |
INTERVAL | 后台工作调度程序允许作业在停止之前运行的最长时间 |
max_retries |
INTEGER | 作业失败时重试的次数 |
retry_period |
INTERVAL | 调度程序在失败的作业重试之间等待的时间量 |
proc_schema |
TEXT | 作业执行的函数或过程的架构名称 |
proc_name |
TEXT | 作业执行的函数或过程的名称 |
owner |
TEXT | 作业的所有者 |
scheduled |
BOOLEAN | |
config |
JSONB | |
next_start |
TIMESTAMP WITH TIME ZONE | |
hypertable_schema |
TEXT | 超表的架构名称。NULL，如果这是用户定义的操作。 |
hypertable_name |
TEXT | 超表的表名。NULL，如果这是用户定义的操作。 |
# 获取有关任务的信息
SELECT * FROM timescaledb_information.jobs;
--This shows a job associated with the refresh policy for continuous aggregates
job_id | 1001
application_name | Refresh Continuous Aggregate Policy [1001]
schedule_interval | 01:00:00
max_runtime | 00:00:00
max_retries | -1
retry_period | 01:00:00
proc_schema | _timescaledb_internal
proc_name | policy_refresh_continuous_aggregate
owner | postgres
scheduled | t
config | {"start_offset": "20 days", "end_offset": "10
days", "mat_hypertable_id": 2}
next_start | 2020-10-02 12:38:07.014042-04
hypertable_schema | _timescaledb_internal
hypertable_name | _materialized_hypertable_2
# 查找与压缩策略相关的所有任务
SELECT * FROM timescaledb_information.jobs where application_name like 'Compression%';
-[ RECORD 1 ]-----+--------------------------------------------------
job_id | 1002
application_name | Compression Policy [1002]
schedule_interval | 15 days 12:00:00
max_runtime | 00:00:00
max_retries | -1
retry_period | 01:00:00
proc_schema | _timescaledb_internal
proc_name | policy_compression
owner | postgres
scheduled | t
config | {"hypertable_id": 3, "compress_after": "60 days"}
next_start | 2020-10-18 01:31:40.493764-04
hypertable_schema | public
hypertable_name | conditions
# 查找由用户定义的操作执行的任务
SELECT * FROM timescaledb_information.jobs where application_name like 'User-Define%';
-[ RECORD 1 ]-----+------------------------------
job_id | 1003
application_name | User-Defined Action [1003]
schedule_interval | 01:00:00
max_runtime | 00:00:00
max_retries | -1
retry_period | 00:05:00
proc_schema | public
proc_name | custom_aggregation_func
owner | postgres
scheduled | t
config | {"type": "function"}
next_start | 2020-10-02 14:45:33.339885-04
hypertable_schema |
hypertable_name |
-[ RECORD 2 ]-----+------------------------------
job_id | 1004
application_name | User-Defined Action [1004]
schedule_interval | 01:00:00
max_runtime | 00:00:00
max_retries | -1
retry_period | 00:05:00
proc_schema | public
proc_name | custom_retention_func
owner | postgres
scheduled | t
config | {"type": "function"}
next_start | 2020-10-02 14:45:33.353733-04
hypertable_schema |
hypertable_name |显示有关自动化框架运行的作业的信息和统计信息 timescaledb_information.job_stats
显示有关自动化框架运行的任务的信息和统计信息。这包括为用户定义的操作设置的任务和由为管理数据保留、连续聚合、压缩和其他自动化策略而创建的策略运行的任务。统计信息包括对管理任务和确定它们是否应该重新安排有用的信息，例如：用于实施策略的后台任务何时以及是否成功，以及何时安排下一次运行。
| 列名 | 类型 | 描述 |
|---|---|---|
hypertable_schema |
TEXT | 超表的架构名称 |
hypertable_name |
TEXT | 超表的表名 |
job_id |
INTEGER | 为实现策略而创建的后台作业的 ID |
last_run_started_at |
TIMESTAMP WITH TIME ZONE | 上次作业的开始时间 |
last_successful_finish |
TIMESTAMP WITH TIME ZONE | 作业成功完成的时间 |
last_run_status |
TEXT | 上次运行是成功还是失败 |
job_status |
TEXT | 工作状态。有效值为“正在运行”、“已安排”和“已暂停” |
last_run_duration |
INTERVAL | 作业上次运行的持续时间 |
next_scheduled_run |
TIMESTAMP WITH TIME ZONE | 下一次运行的开始时间 |
total_runs |
BIGINT | 此作业的总运行次数 |
total_successes |
BIGINT | 此作业成功的总次数 |
total_failures |
BIGINT | 此作业失败的总次数 |
# 获取特定超表的任务成功/失败信息
SELECT job_id, total_runs, total_failures, total_successes
FROM timescaledb_information.job_stats
WHERE hypertable_name = 'test_table';
job_id | total_runs | total_failures | total_successes
--------+------------+----------------+-----------------
1001 | 1 | 0 | 1
1004 | 1 | 0 | 1
(2 rows)
# 获取有关连续聚合策略相关统计信息的信息
SELECT js.* FROM
timescaledb_information.job_stats js, timescaledb_information.continuous_aggregates cagg
WHERE cagg.view_name = 'max_mat_view_timestamp'
and cagg.materialization_hypertable_name = js.hypertable_name;
-[ RECORD 1 ]----------+------------------------------
hypertable_schema | _timescaledb_internal
hypertable_name | _materialized_hypertable_2
job_id | 1001
last_run_started_at | 2020-10-02 09:38:06.871953-04
last_successful_finish | 2020-10-02 09:38:06.932675-04
last_run_status | Success
job_status | Scheduled
last_run_duration | 00:00:00.060722
next_scheduled_run | 2020-10-02 10:38:06.932675-04
total_runs | 1
total_successes | 1
total_failures | 0十一.管理功能
1.开始恢复数据库pg_restoretimescaledb_pre_restore
执行适当的操作以允许开始恢复数据库pg_restore。具体来说，这会将timescaledb.restoringGUC设置为on并停止任何可能一直在执行任务的后台工作人员，直到该timescaledb_post_restore 功能在恢复后运行。
运行后SELECT timescaledb_pre_restore()必须运行该timescaledb_post_restore函数才能正常使用数据库。
SELECT timescaledb_pre_restore();2.timescaledb_post_restore()
恢复数据库完成后执行正确的操作。具体来说，这会重置timescaledb.restoringGUC 并重新启动任何后台工作人员。
SELECT timescaledb_post_restore();3.查看遥测报告 get_telemetry_report()
如果启用了后台遥测，则返回发送到我们服务器的字符串。如果遥测未启用，则输出 INFO 消息确认遥测已禁用并返回 NULL 报告。
# 如果已启用遥测，请查看遥测报告。
SELECT get_telemetry_report();
# 如果遥测被禁用，请在本地查看遥测报告。
SELECT get_telemetry_report(always_display_report := true);4.转储timescaledb执行日志
为了在寻求支持和报告错误时提供帮助，TimescaleDB 包含一个 SQL 脚本，该脚本输出来自内部 TimescaleDB 表的元数据以及版本信息。
# dump_file.txt在将其与错误报告或支持问题一起发送之前进行检查。
psql [your connect flags] -d your_timescale_db < dump_meta_data.sql > dumpfile.txt十二.timescaledb 插入与表结构更新、数据更新
1.直接使用mybatis plus提供的api进行相关操作
2.类似mysql的insert、update方法操作单表
INSERT INTO conditions(time, location, temperature, humidity)
VALUES (NOW(), 'office', 70.0, 50.0);
INSERT INTO conditions
VALUES
(NOW(), 'office', 70.0, 50.0),
(NOW(), 'basement', 66.5, 60.0),
(NOW(), 'garage', 77.0, 65.2);\
UPDATE conditions SET temperature = 70.2, humidity = 50.0
WHERE time = '2017-07-28 11:42:42.846621+00' AND location = 'office';
# 修改在 10 分钟数据块中找到的所有行
UPDATE conditions SET temperature = temperature + 0.1
WHERE time >= '2017-07-28 11:40' AND time < '2017-07-28 11:50';
# 创建表
CREATE TABLE conditions (
time TIMESTAMPTZ NOT NULL,
location TEXT NOT NULL,
temperature DOUBLE PRECISION NULL,
humidity DOUBLE PRECISION NULL,
UNIQUE (time, location)
);
# 创建唯一索引
CREATE UNIQUE INDEX on conditions (time, location);
# 指定更新现有数据
INSERT INTO conditions
VALUES ('2017-07-28 11:42:42.846621+00', 'office', 70.2, 50.1)
ON CONFLICT (time, location) DO UPDATE
SET temperature = excluded.temperature,
humidity = excluded.humidity;唯一约束必须包括所有分区键。例如，如果表中只使用时间分区，系统要求time为约束的一部分：UNIQUE(time)，UNIQUE(time, location)，UNIQUE(location, time)等。另一方面，UNIQUE(location)是_not_有效的约束。
如果架构有一个额外的列device用作额外的分区维度，那么约束必须是UNIQUE(time, device)or UNIQUE(time, device, location)。在这种情况下，UNIQUE(time, location)将_不再_是一个有效的约束。
3.创建连续聚合，并手动/自动 更新
# 计算所有天气指标的每日平均值，以及温度的最大值和最小值。
-- Continuous aggs
-- define view
CREATE MATERIALIZED VIEW weather_metrics_daily
WITH (timescaledb.continuous)
AS
SELECT
time_bucket('1 day', time) as bucket,
city_name,
avg(temp_c) as avg_temp,
avg(feels_like_c) as feels_like_temp,
max(temp_c) as max_temp,
min(temp_c) as min_temp,
avg(pressure_hpa) as pressure,
avg(humidity_percent) as humidity_percent,
avg(rain_3h_mm) as rain_3h,
avg(snow_3h_mm) as snow_3h,
avg(wind_speed_ms) as wind_speed,
avg(clouds_percent) as clouds
FROM
weather_metrics
GROUP BY bucket, city_name
WITH NO DATA;
# 手动刷新
CALL refresh_continuous_aggregate('weather_metrics_daily','2010-01-01', '2021-01-01');
# 查询 2009 年 1 月 1 日之前的数据的连续聚合显示
SELECT * from weather_metrics_daily
WHERE bucket > '2009-01-01'
ORDER BY bucket ASC;
# 创建一个策略，每两周自动更新一次连续聚合：
SELECT add_continuous_aggregate_policy('weather_metrics_daily',
start_offset => INTERVAL '6 months',
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '14 days');
# 查询连续聚合
SELECT bucket, max_temp, avg_temp, min_temp
FROM weather_metrics_daily
WHERE bucket >= '2015-01-01' AND bucket < '2021-01-01'
AND city_name LIKE 'New York'
ORDER BY bucket ASC;
# 查询实时聚合
SELECT * from weather_metrics_daily
WHERE bucket > now() - 2 * INTERVAL '1 year'
ORDER BY bucket DESC;4.将CSV中的数据拷贝到表中
# 将当前目录中的weather_data.csv 中的数据拷贝到weather_metrics
-- copy data from weather_data.csv into weather_metrics
\copy weather_metrics (time, timezone_shift, city_name, temp_c, feels_like_c, temp_min_c, temp_max_c, pressure_hpa, humidity_percent, wind_speed_ms, wind_deg, rain_1h_mm, rain_3h_mm, snow_1h_mm, snow_3h_mm, clouds_percent, weather_type_id) from './weather_data.csv' CSV HEADER;5.创建压缩策略
# 设置策略以压缩 10 年以上的数据
SELECT add_compression_policy('weather_metrics', INTERVAL '10 years');
# 手动压缩
SELECT compress_chunk(i)
FROM show_chunks('weather_metrics', older_than => INTERVAL ' 10 years') i;十三.timescaled查询示例
1.基本数据统计查询示例
# 查询7天内所有数据 查询方式和mysl基本一致，时间条件使用timescaled的查询方式
SELECT suo, AVG(temperature) from sensor_data
WHERE temperature IS NOT NULL AND humidity > 0.5
AND time > now() - interval '7 day'
GROUP BY device_id;
# 分页查询
timescaled 分页不支持limit 1,2 的方式而是使用 limit offset
如果项目中使用了动态数据源，并且timescaled使用了mybatis plus的api查询分页，记得在配置mybatis plus的config
注入MybatisPlusInterceptor时的分页支持new PaginationInnerInterceptor()千万不要指定DbType为mysql否则查询timescaled的数据会报错。因为mysql在mybatis plus中指定使用第一种方式实现分页，但是mysql是支持第二种分页方式的
# 限制查询前100条
LIMIT 100
# 计算最近7天，每小时的异常次数
SELECT date_trunc('hour', time) as hour, firmware,
COUNT(error_msg) as errno FROM data
WHERE firmware > 50
AND time > now() - interval '7 day'
GROUP BY hour, firmware
ORDER BY hour DESC, errno DESC;
# 计算最近一小时内的平均数据
SELECT loc.region, AVG(bus.speed) FROM bus
INNER JOIN loc ON (bus.bus_id = loc.bus_id)
WHERE loc.city = 'nyc'
AND bus.time > now() - interval '1 hour'
GROUP BY loc.region;
# 展示最近12小时，每小时的平均值
SELECT date_trunc('hour', time) AS hour, AVG(weight)
FROM logs
WHERE device_type = 'pressure-sensor' AND customer_id = 440
AND time > now() - interval '12 hours'
GROUP BY hour;
hour | AVG(weight)
--------------------+--------------
2017-01-04 12:00 | 170.0
2017-01-04 13:00 | 174.2
2017-01-04 14:00 | 174.0
2017-01-04 15:00 | 178.6
2017-01-04 16:00 | 173.0
2017-01-04 17:00 | 169.9
2017-01-04 18:00 | 168.1
2017-01-04 19:00 | 170.2
2017-01-04 20:00 | 167.4
2017-01-04 21:00 | 168.6
# 查询最近24小时内每分钟指定条件的数量
SELECT date_trunc('minute', time) AS minute, COUNT(device_id)
FROM logs
WHERE cpu_level > 0.9 AND free_mem < 1024
AND time > now() - interval '24 hours'
GROUP BY minute
ORDER BY COUNT(device_id) DESC LIMIT 25;
minute | heavy_load_devices
--------------------+---------------------
2017-01-04 14:59 | 1653
2017-01-04 15:01 | 1650
2017-01-04 15:00 | 1605
2017-01-04 15:02 | 1594
2017-01-04 15:03 | 1594
2017-01-04 15:04 | 1561
2017-01-04 15:06 | 1499
2017-01-04 15:05 | 1460
2017-01-04 15:08 | 1459
# 查询指定条件的数量
SELECT date_trunc('hour', time) AS hour, COUNT(logs.device_id)
FROM logs
JOIN devices ON logs.device_id = devices.id
WHERE logs.temperature > 90 AND devices.location = 'SITE-1'
GROUP BY hour;
hour | COUNT(logs.device_id)
--------------------+------------------------
2017-01-04 12:00 | 994
2017-01-04 13:00 | 905
2017-01-04 14:00 | 875
2017-01-04 15:00 | 910
2017-01-04 16:00 | 905
2017-01-04 17:00 | 840
2017-01-04 18:00 | 801
2017-01-04 19:00 | 813
2017-01-04 20:00 | 798
# 查询过去 5 年每个城市的总降雪量
SELECT city_name, sum(snow_1h_mm)
FROM weather_metrics
WHERE time > now() - INTERVAL '5 years'
GROUP BY city_name;
# 查找每个城市在过去 6 个月内每 15 天的平均温度
SELECT time_bucket('15 days', time) as "bucket"
,city_name, avg(temp_c)
FROM weather_metrics
WHERE time > now() - (6* INTERVAL '1 month')
GROUP BY bucket, city_name
ORDER BY bucket DESC;
# 计算过去一年每个城市在 30 天时间段内的总降雪量 结果仅包括城市有降雪时间的 time_periods，而不是我们分析的特定时间段，即一年
SELECT time_bucket_gapfill('30 days', time) as bucket,
city_name, sum(snow_1h_mm) as sum
FROM weather_metrics
WHERE time > now() - INTERVAL '1 year' AND time < now()
GROUP BY bucket, city_name
ORDER BY bucket DESC;
# 查询过去3小时每个地点每十五分钟的信息
SELECT time_bucket('15 minutes', time) AS fifteen_min,
location, COUNT(*),
MAX(temperature) AS max_temp,
MAX(humidity) AS max_hum
FROM conditions
WHERE time > NOW() - INTERVAL '3 hours'
GROUP BY fifteen_min, location
ORDER BY fifteen_min DESC, max_temp DESC;
# 关联查询,过去一小时内各地点查询内容的数量
SELECT COUNT(DISTINCT location) FROM conditions
JOIN locations
ON conditions.location = locations.location
WHERE locations.air_conditioning = True
AND time > NOW() - INTERVAL '1 day'2.查询连续聚合
SELECT bucket, max_temp, avg_temp, min_temp
FROM weather_metrics_daily
WHERE bucket >= '2015-01-01' AND bucket < '2021-01-01'
AND city_name LIKE 'New York'
ORDER BY bucket ASC;3.高级分析查询示例
# 中位数和百分位数
# 使用percentile_cont以计算百分位数
SELECT percentile_cont(0.5)
WITHIN GROUP (ORDER BY temperature)
FROM conditions;
# 累计金额
使用sum(sum(column)) OVER(ORDER BY group)找到的累计总和
SELECT location, sum(sum(temperature)) OVER(ORDER BY location)
FROM conditions
GROUP BY location;
# 移动平均线
# 对于简单的移动平均线，请OVER在多行上使用窗口函数，然后在这些行上计算聚合函数。例如，要通过对十个最近的读数求平均值来找到设备的平滑温度：
SELECT time, AVG(temperature) OVER(ORDER BY time
ROWS BETWEEN 9 PRECEDING AND CURRENT ROW)
AS smooth_temp
FROM conditions
WHERE location = 'garage' and time > NOW() - INTERVAL '1 day'
ORDER BY time DESC;
# 增加
# 要计算值的增加，您需要考虑计数器重置。如果主机重新启动或容器重新启动，则可能会发生计数器重置。此示例查找发送的字节数，并将计数器重置考虑在内：
SELECT
time,
(
CASE
WHEN bytes_sent >= lag(bytes_sent) OVER w
THEN bytes_sent - lag(bytes_sent) OVER w
WHEN lag(bytes_sent) OVER w IS NULL THEN NULL
ELSE bytes_sent
END
) AS "bytes"
FROM net
WHERE interface = 'eth0' AND time > NOW() - INTERVAL '1 day'
WINDOW w AS (ORDER BY time)
ORDER BY time
# 速度
# 与增加一样， rate 适用于计数器单调增加的情况。如果您的采样间隔是可变的，或者您在不同系列之间使用不同的采样间隔，那么将值归一化为一个共同的时间间隔以使计算出的值具有可比性会很有帮助。此示例查找每秒发送的字节数，并考虑计数器重置：
SELECT
time,
(
CASE
WHEN bytes_sent >= lag(bytes_sent) OVER w
THEN bytes_sent - lag(bytes_sent) OVER w
WHEN lag(bytes_sent) OVER w IS NULL THEN NULL
ELSE bytes_sent
END
) / extract(epoch from time - lag(time) OVER w) AS "bytes_per_second"
FROM net
WHERE interface = 'eth0' AND time > NOW() - INTERVAL '1 day'
WINDOW w AS (ORDER BY time)
ORDER BY time
# delta
# 在许多监控和物联网用例中，设备或传感器报告的指标不会经常变化，任何变化都被视为异常。当您查询这些值随时间的变化时，您通常不想传输所有值，而只想传输观察到变化的值。这有助于最大限度地减少发送的数据量。您可以使用窗口函数和子选择的组合来实现这一点。此示例使用差异来过滤值未更改的行，并且仅传输值已更改的行：
SELECT time, value FROM (
SELECT time,
value,
value - LAG(value) OVER (ORDER BY time) AS diff
FROM hypertable) ht
WHERE diff IS NULL OR diff != 0;
# 时间段
# TimescaleDBtime_bucket函数扩展了 PostgreSQL date_trunc函数。时间桶接受任意时间间隔以及可选的偏移量并返回桶开始时间
SELECT time_bucket('5 minutes', time) AS five_min, avg(cpu)
FROM metrics
GROUP BY five_min
ORDER BY five_min DESC LIMIT 12;
# 第一和最后
# TimescaleDBfirst和last函数允许您按照另一列的顺序获取一列的值。这通常用于聚合。这些示例查找组的最后一个元素
SELECT location, last(temperature, time)
FROM conditions
GROUP BY location;
SELECT time_bucket('5 minutes', time) five_min, location, last(temperature, time)
FROM conditions
GROUP BY five_min, location
ORDER BY five_min DESC LIMIT 12;
# 直方图
# TimescaleDBhistogram函数允许您生成数据的直方图。本示例定义了一个直方图，其中定义了 60 到 85 范围内的五个桶。生成的直方图有七个桶；第一个用于低于最小阈值 60 的值，中间的五个 bin 用于指定范围内的值，最后一个用于高于 85 的值
SELECT location, COUNT(*),
histogram(temperature, 60.0, 85.0, 5)
FROM conditions
WHERE time > NOW() - INTERVAL '7 days'
GROUP BY location;
location | count | histogram
------------+-------+-------------------------
office | 10080 | {0,0,3860,6220,0,0,0}
basement | 10080 | {0,6056,4024,0,0,0,0}
garage | 10080 | {0,2679,957,2420,2150,1874,0}
# 填隙
# 您可以显示选定时间范围的记录，即使该范围的一部分不存在数据。这通常称为间隙填充，通常涉及为任何缺失数据记录空值的操作。
# 在此示例中，我们使用的交易数据包括time时间戳、asset_code被交易的时间、 price资产的时间和volume被交易的资产的时间。
# 为九月份每天交易的资产“TIMS”的交易量创建一个查询：
SELECT
time_bucket('1 day', time) AS date,
sum(volume) AS volume
FROM trades
WHERE asset_code = 'TIMS'
AND time >= '2021-09-01' AND time < '2021-10-01'
GROUP BY date
ORDER BY date DESC;
date | volume
------------------------+--------
2021-09-29 00:00:00+00 | 11315
2021-09-28 00:00:00+00 | 8216
2021-09-27 00:00:00+00 | 5591
2021-09-26 00:00:00+00 | 9182
2021-09-25 00:00:00+00 | 14359
2021-09-22 00:00:00+00 | 9855
# 您可以从输出中看到，没有包含 09-23、09-24 或 09-30 的记录，因为那些天没有记录交易数据。要包含每个缺失日的时间记录，您可以使用 TimescaleDBtime_bucket_gapfill 函数，该函数根据时间范围内的给定间隔生成一系列时间段。在此示例中，间隔为 1 天，跨越 9 月：
SELECT
time_bucket_gapfill('1 day', time) AS date,
sum(volume) AS volume
FROM trades
WHERE asset_code = 'TIMS'
AND time >= '2021-09-01' AND time < '2021-10-01'
GROUP BY date
ORDER BY date DESC;
date | volume
------------------------+--------
2021-09-30 00:00:00+00 |
2021-09-29 00:00:00+00 | 11315
2021-09-28 00:00:00+00 | 8216
2021-09-27 00:00:00+00 | 5591
2021-09-26 00:00:00+00 | 9182
2021-09-25 00:00:00+00 | 14359
2021-09-24 00:00:00+00 |
2021-09-23 00:00:00+00 |
2021-09-22 00:00:00+00 | 9855
# 您还可以使用 TimescaleDBtime_bucket_gapfill函数生成还包含时间戳的数据点。这对于需要甚至空值具有时间戳的图形库非常有用，以便它们可以准确地在图形中绘制间隙。在这个例子中，我们在过去两周内生成了 1080 个数据点，用空值填充空白，并为每个空值提供一个时间戳：
SELECT
time_bucket_gapfill(INTERVAL '2 weeks' / 1080, time, now() - INTERVAL '2 weeks', now()) AS btime,
sum(volume) AS volume
FROM trades
WHERE asset_code = 'TIMS'
AND time >= now() - INTERVAL '2 weeks' AND time < now()
GROUP BY btime
ORDER BY btime;
btime | volume
------------------------+----------
2021-03-09 17:28:00+00 | 1085.25
2021-03-09 17:46:40+00 | 1020.42
2021-03-09 18:05:20+00 |
2021-03-09 18:24:00+00 | 1031.25
2021-03-09 18:42:40+00 | 1049.09
2021-03-09 19:01:20+00 | 1083.80
2021-03-09 19:20:00+00 | 1092.66
2021-03-09 19:38:40+00 |
2021-03-09 19:57:20+00 | 1048.42
2021-03-09 20:16:00+00 | 1063.17
2021-03-09 20:34:40+00 | 1054.10
2021-03-09 20:53:20+00 | 1037.78
# 观察结转
# 如果您的数据集合仅在实际值发生变化时记录行，那么您的可视化可能仍需要所有数据点才能正确显示您的结果。在这种情况下，您可以结转上次观察到的值来填补空白。
SELECT
time_bucket_gapfill(INTERVAL '5 min', time, now() - INTERVAL '2 weeks', now()) as 5min,
meter_id,
locf(avg(data_value)) AS data_value
FROM my_hypertable
WHERE
time > now() - INTERVAL '2 weeks'
AND meter_id IN (1,2,3,4)
GROUP BY 5min, meter_id
# 最后一个点
# 您可以找到数据库中每个唯一项目的最后一个点。例如，每个物联网设备的最后记录测量值、资产跟踪中每个项目的最后位置或证券的最后价格。最小化最后一点要搜索的数据量的标准方法是使用时间谓词来严格限制要遍历的时间量或块数。除非所有项目在时间范围内至少有一条记录，否则此方法不起作用。更健壮的方法是使用最后点查询来确定每个唯一项的最后一条记录。
# 在此示例中，对于资产跟踪或车队管理很有用，我们为每辆被跟踪的车辆创建了一个元数据表，以及一个包含给定时间车辆位置的第二个时间序列表
CREATE TABLE vehicles (
vehicle_id INTEGER PRIMARY KEY,
vin_number CHAR(17),
last_checkup TIMESTAMP
);
CREATE TABLE location (
time TIMESTAMP NOT NULL,
vehicle_id INTEGER REFERENCES vehicles (vehicle_id),
latitude FLOAT,
longitude FLOAT
);
SELECT create_hypertable('location', 'time');
# 我们可以使用第一个表，它为我们提供了一组不同的车辆，LATERAL JOIN对位置表执行 a ：
SELECT data.* FROM vehicles v
INNER JOIN LATERAL (
SELECT * FROM location l
WHERE l.vehicle_id = v.vehicle_id
ORDER BY time DESC LIMIT 1
) AS data
ON true
ORDER BY v.vehicle_id, data.time DESC;
time | vehicle_id | latitude | longitude
----------------------------+------------+-----------+-------------
2017-12-19 20:58:20.071784 | 72 | 40.753690 | -73.980340
2017-12-20 11:19:30.837041 | 156 | 40.729265 | -73.993611
2017-12-15 18:54:01.185027 | 231 | 40.350437 | -74.651954
# 这种方法需要保留一个单独的表，其中包含不同的项目标识符或名称。您可以通过使用从超表到元数据表的外键来完成此操作，如示例中的REFERENCES定义所示。
# 元数据表可以通过业务逻辑填充，例如当车辆首次向系统注册时。或者，您可以在对超表执行插入或更新时使用触发器动态填充它。例如：
CREATE OR REPLACE FUNCTION create_vehicle_trigger_fn()
RETURNS TRIGGER LANGUAGE PLPGSQL AS
$BODY$
BEGIN
INSERT INTO vehicles VALUES(NEW.vehicle_id, NULL, NULL) ON CONFLICT DO NOTHING;
RETURN NEW;
END
$BODY$;
CREATE TRIGGER create_vehicle_trigger
BEFORE INSERT OR UPDATE ON location
FOR EACH ROW EXECUTE PROCEDURE create_vehicle_trigger_fn();十四.timescaled删除
1.使用mybatis plus api 单表删除
2.使用delete语句删除
DELETE FROM conditions WHERE temperature < 35 OR humidity < 60;
DELETE FROM conditions WHERE time < NOW() - INTERVAL '1 month';
# 运行大型DELETE操作后，建议用户 VACUUM或VACUUM FULLhypertable 回收被删除或废弃的行占用的存储空间
VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ table_and_columns [, ...] ]
where option can be one of:
FULL [ boolean ]
FREEZE [ boolean ]
VERBOSE [ boolean ]
ANALYZE [ boolean ]
DISABLE_PAGE_SKIPPING [ boolean ]
SKIP_LOCKED [ boolean ]
INDEX_CLEANUP { AUTO | ON | OFF }
PROCESS_TOAST [ boolean ]
TRUNCATE [ boolean ]
PARALLEL integer
and table_and_columns is:
table_name [ ( column_name [, ...] ) ]
# 示例
VACUUM (VERBOSE, ANALYZE) onek;十五.timescaled备份与还原
1.timescaledb-backup 工具
下载
https://github.com/timescale/timescaledb-backup/releases备份
可选参数
除了这两个必需参数之外，您还可以在使用ts-dump命令时指定这些可选参数：
| 范围 | 描述 | 默认 | 笔记 |
|---|---|---|---|
--jobs |
为转储运行的作业数 | 4 | 以并行模式运行，除非设置为 0。 |
--verbose |
详细输出 | 错误的 | |
--dump-roles |
用于pg_dumpall在运行转储之前转储角色（没有密码信息）。 |
真的 | 用于恢复对表的权限。 |
--dump-tablespaces |
用于pg_dumpall在运行转储之前转储表空间。 |
真的 | 如果您有多个表空间并且需要将表恢复到正确的表空间，请使用。 |
--dump-pause-jobs |
暂停可能会中断并行转储过程的后台作业。 | 真的 | 仅影响并行转储。 |
--dump-pause-UDAs |
在 TimescaleDB 2.0 及更高版本中，暂停作业时暂停用户定义的操作。 | 真的 | 仅影响作业暂停的并行转储。 |
--dump-job-finish-timeout |
在超时之前等待执行 DDL 的作业完成的秒数。 | 600（10分钟） | 仅影响作业暂停的并行转储。设置为 -1 以不等待。 |
--db-URI：要备份的数据库的位置。它应该是 Postgres URI格式，即 postgresql://[user[:password]@][host][:port][,...][/dbname][?param1=value1&...]
您可以根据需要在 URI 中指定参数，包括密码。
--dump-dir：存储备份的路径。确保目录路径存在，但目录本身不存在。该目录由脚本创建。
# 授权
chmod 777 ./ts-dump
# 以238的zy_dev 为例
./ts-dump --db-URI=postgresql://postgres:123456@127.0.0.1:5432/zy_dev --dump-dir=/home/postgresql/zy
从备份中恢复数据库
# 创建新的数据库zy_prod
# 授权
chmod 777 ./ts-restore
# exampledb从存储备份的目录将命名的数据库还原到新位置
./ts-restore --db-URI=postgresql://postgres:123456@127.0.0.1:5432/zy_prod --dump-dir=/home/postgresql/zy
# 恢复roles.sql和tablespaces.sql
psql -d <db-URI> -f <dump-dir>/roles.sql
psql -d <db-URI> -f <dump-dir>/tablespaces.sql
# 如
psql -d postgresql://postgres:123456@127.0.0.1:5432/zy_prod -f /home/postgresql/zy/roles.sql
psql -d postgresql://postgres:123456@127.0.0.1:5432/zy_prod -f /home/postgresql/zy//tablespaces.sql恢复的可选参数
| 范围 | 描述 | 默认 | 笔记 |
|---|---|---|---|
--jobs |
为恢复运行的作业数 | 4 | 尽可能以并行模式运行，除非设置为 0。 |
--verbose |
详细输出 | 真的 | |
--do-update |
恢复后立即将 TimescaleDB 版本更新为最新的默认版本。 | 真的 | 需要.so您要从中恢复的版本和要更新到的版本的文件。 |
2.使用 pg_dump 和 pg_restore 进行逻辑备份
备份
您可以使用本机 PostgreSQLpg_dump和pg_restore 命令备份和恢复整个数据库或单个超级表。
TimescaleDB不同版本之间的升级可以就地完成；您无需备份和恢复数据。
您可以pg_dump在命令提示符下使用该命令执行备份。例如，要备份名为 的数据库exampledb：
不要使用该pg_dump命令备份单个超级表。使用此方法创建的转储缺少从备份正确还原超表所需的信息。
/usr/pgsql-11/bin/pg_dump --file "/u01/pgsql.backup" --host "0.0.0.0" --port "5432" --username "postgres" --dbname "原数据库名" --verbose --role "postgres" --format=c --blobs --encoding "UTF8"从备份中恢复
# 在需要恢复的表中开启备份模式
SELECT timescaledb_pre_restore();
# 数据还原
pg_restore --username "postgres" --host "127.0.0.1" --port "5432" --role "postgres" --dbname "原数据库名" --verbose /u01/pgsql.backup
# 还原完毕结束备份模式
SELECT timescaledb_post_restore();备份单个超级表
该pg_dump命令提供了允许您指定要备份的表或模式的标志。但是，使用这些标志意味着转储缺少 TimescaleDB 理解它们之间关系所需的必要信息。即使您明确指定超表及其所有组成块，转储仍不会包含在还原时重新创建超表所需的所有信息。
您可以通过备份整个数据库，然后排除不想备份的表来备份单个超级表。您还可以使用此方法备份不是超级表的单个普通表。
# 在命令提示符下，备份 hypertable 架构
pg_dump -s -d old_db --table conditions -N _timescaledb_internal | \
grep -v _timescaledb_internal > schema.sql
# 将 hypertable 数据备份到 CSV 文件
psql -d old_db \
-c "\COPY (SELECT * FROM conditions) TO data.csv DELIMITER ',' CSV"从备份中恢复单个超级表
# 在命令提示符下，恢复架构
psql -d new_db < schema.sql
# 重新创建超表
psql -d new_db -c "SELECT create_hypertable('conditions', 'time')"
# 恢复数据
psql -d new_db -c "\COPY conditions FROM data.csv CSV"3.物理备份
对于完整实例物理备份（这对于启动新副本特别有用），pg_basebackup 适用于所有 TimescaleDB 安装。您还可以使用多个外部备份和还原管理器中的任何一个，例如pg_backrest、 或 barman。对于正在进行的物理备份，您可以使用 wal-e. 这些工具都允许您对整个实例进行在线物理备份，并且许多工具提供增量备份和其他自动化选项。
十六.主从热备
1.创建热备用户
# 用户名小写
CREATE ROLE [用户名] login replication encrypted password '[密码]';
# 如
CREATE ROLE chenqi login replication encrypted password 'chenqi123';
2.进入数据目录，修改postgresql.conf
# 修改以下参数
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 64
hot_standby = on3.配置主库的pg_hba.conf 文件
4.验证是否能从从库访问主节点
psql -h [主库ip] -U postgres
5.从库配置
# 停止从库服务
service postgresql-13 stop
# 清空从库的储存文件夹
rm -rf /home/postgresql_data/*
# 从主库上将数据备份到从库上
pg_basebackup -h 主节点IP -p 5432 -U 用户名 -Fp -Xs -Pv -R -D /home/postgresql_data
6.编辑从库数据目录下的standby.signal文件
# 加入配置
vim standby.signal
standby_mode = 'on'
7.修改postgresql.conf文件
#从机信息和连接用户
primary_conninfo = 'host=主节点IP port=5432 user=replica password=replica用户的密码'
#说明恢复到最新状态
recovery_target_timeline = latest
#大于主节点，正式环境应当重新考虑此值的大小
max_connections = 120
#说明这台机器不仅用于数据归档，还可以用于数据查询
hot_standby = on
#流备份的最大延迟时间
max_standby_streaming_delay = 30s
#向主机汇报本机状态的间隔时间
wal_receiver_status_interval = 10s
#r出现错误复制，向主机反馈
hot_standby_feedback = on 8.重启从库
service postgresql-13 restart
修改数据也能同步
9.验证主从搭建是否成功
select client_addr,sync_state from pg_stat_replication;
10.主从切换
查看库状态
主库故障，停止主库服务
提升从库为主库
su - postgres -c "pg_ctl promote"
server promoting11.相关问题
# 授权
chown -R postgres:postgres /mnt十七.数据迁移
1.从同一个postgreSQL迁移
# 创建一个与旧表具有相同表结构和其他约束的新空表，使用LIKE
# 此方法在创建old_table索引new_table时重新创建索引，以便在下一步将其转换为超表时，我们不必自己创建它们。它避免了一个步骤，但由于需要更新每个迁移行的索引，因此减慢了数据传输速度。
# 方便的方法
CREATE TABLE new_table (LIKE old_table INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES);
# 更快的方法,创建完成后需要手动添加索引
CREATE TABLE new_table (LIKE old_table INCLUDING DEFAULTS INCLUDING CONSTRAINTS EXCLUDING INDEXES);
# 将新表转换为 Hypertable
# 我们使用TimescaleDB函数create_hypertable转换new_table为超表，然后简单地INSERT从旧表中获取数据
-- Assuming 'time' is the time column for the dataset
SELECT create_hypertable('new_table', 'time');
-- Insert everything from old_table
INSERT INTO new_table SELECT * FROM old_table;
# 添加额外索引
CREATE INDEX on new_table (column_name, <options>)
2.从不同postgreSQL数据库迁移
# 复制架构和设置 Hypertables
# 使用pg_dump以下命令可以轻松完成复制数据库模式
pg_dump --schema-only -f old_db.bak old_db
# 创建新表
psql -d new_db < old_db.bak
# 连接客户端
psql -d new_db
# 创建超表
SELECT create_hypertable('conditions', 'time');
# 将数据备份到 CSV
# The following ensures 'conditions' outputs to a comma-separated .csv file
psql -d old_db -c "\COPY (SELECT * FROM conditions) TO old_db.csv DELIMITER ',' CSV"
# 将数据导入 TimescaleDB
# 使用文档前面从 .csv 导入 TimescaleDB节恢复数据3.迁移influxdb数据
# 下载 开源工具Outflux
https://github.com/timescale/outflux
# 使用 Influx CLI 客户端加载数据。该文件将首先创建“outflux_tutorial”数据库，然后进行插入
influx -import -path=outflux_taxit.txt -database=outflux_tutorial
模式发现验证和传输
Outflux 的功能之一是能够发现 InfluxDB 测量的模式，并验证是否存在可以保存传输数据的 TimescaleDB 表，或者创建可以满足模式要求的新表。
我们现在可以创建一个 TimescaleDB 超表，准备接收我们插入到 InfluxDB 实例中的演示数据。如果您按照教程操作并插入了示例taxi中的outflux_tutorial数据，则 InfluxDB 实例的数据库中应该有一个度量。
该schema-transferOutflux的命令可以用4间架构的战略合作：
ValidateOnly: 检查是否安装了 TimescaleDB 扩展，指定的数据库有一个带有正确列的超表，以及它是否正确分区，但不会执行修改CreateIfMissing: 运行所有检查ValidateOnly并创建和正确分区任何丢失的超表DropAndCreate: 删除与测量同名的任何现有表，并创建一个新的超表并对其进行正确分区DropCascadeAndCreate: 执行与 DropAndCreate 相同的操作，如果存在与测量名称相同的现有表，则执行级联表删除的额外强度
schema-transfer如果用户希望将度量的标签或字段作为单个 JSONB 列传输，则可以使用指定模式策略的参数调用该命令。默认情况下，每个标签和每个字段都创建为单独的列。
我们可以schema-transfer在我们的示例数据上使用 Outflux运行：
$ outflux schema-transfer outflux_tutorial taxi \
--input-server=http://localhost:8086 \
--output-conn="dbname=postgres user=postgres"该schema-transfer命令通过指定数据库 ( outflux_tutorial) 和测量值 ( taxi) 来执行。如果未指定测量值，则将传输数据库中的所有测量值。InfluxDB 服务器的位置由--input-server标志指定。目标数据库和其他连接选项是用--output-conn标志指定的。在这里，我们使用postgres用户和数据库连接到我们的服务器。如何指定用户名、密码和更多关于输入和输出连接的配置选项（包括识别哪些环境变量）可以在Outflux的GitHub 存储库中找到。默认情况下，schema-transfer使用CreateIfMissing策略执行。
这是schema-transfer使用DropAndCreate策略运行 Outflux并将所有标签放在单个 JSONB 列中的示例输出：
$ outflux schema-transfer outflux_tutorial taxi \
--input-server=http://localhost:8086 \
--output-conn="dbname=postgres user=postgres" \
--schema-strategy=DropAndCreate \
--tags-as-json
2019/03/27 12:10:30 Selected input database: outflux_tutorial
2019/03/27 12:10:30 Overriding PG environment variables for connection with: dbname=postgres
user=postgres
2019/03/27 12:10:30 Tags for measure 'taxi' will be combined into a single JSONB column
2019/03/27 12:10:30 pipe_taxi starting execution
2019/03/27 12:10:30 Discovering influx schema for measurement: taxi
2019/03/27 12:10:30 Discovered: DataSet { Name: taxi, Columns: [Column... 2019/03/27 12:10:30
Selected Schema Strategy: DropAndCreate
2019/03/27 12:10:30 Table taxi exists, dropping it
2019/03/27 12:10:30 Executing: DROP TABLE taxi
2019/03/27 12:10:30 Table taxi ready to be created
2019/03/27 12:10:30 Creating table with:
CREATE TABLE "taxi"("time" TIMESTAMP, "tags" JSONB, "fare" FLOAT, "mta_tax" FLOAT, "tip" FLOAT, "tolls" FLOAT)
2019/03/27 12:10:30 Preparing TimescaleDB extension:
CREATE EXTENSION IF NOT EXISTS timescaledb
2019/03/27 12:10:30 Creating hypertable with: SELECT create_hypertable('"taxi"', 'time');
2019/03/27 12:10:30 No data transfer will occur
2019/03/27 12:10:30 Schema Transfer complete in: 0.056 seconds数据迁移
模式传输很有用，但它不是我们构建 Outflux 的目的。您可以使用该命令将架构传输和数据迁移migrate合二为一。可用的连接选项是相同的（您可以在public repo上查看它们）。您可以传输完整的 InfluxDB 数据库，并将每个测量导出为单独的表，或者您可以选择要导出的测量。
您可以使用以下命令从数据库中的taxi测量传输所有示例数据outflux_tutorial：
$ outflux migrate outflux_tutorial taxi \
--input-server=http://localhost:8086 \
--output-conn="dbname=postgres user=postgres" \
--schema-strategy=DropAndCreate在这里，我们使用 DropAndCreate 策略，该策略将删除任何先前命名的表cpu并在管道数据之前创建它。migrate 命令支持多个标志，这些标志为用户选择要迁移的数据提供了灵活性。其中之一是--limit仅从按时间排序的 InfluxDB 数据库导出前 N 行的标志。具有 N=10 限制的 migrate 命令的输出应如下所示：
$ outflux migrate outflux_tutorial taxi \
--input-server=http://localhost:8086 \
--output-conn="dbname=postgres user=postgres" \
--schema-strategy=ValidateOnly --limit=10
2019/03/27 12:15:01 All pipelines scheduled
2019/03/27 12:15:01 Overriding PG environment variables for connection with: dbname=postgres
user=postgres
2019/03/27 12:15:01 pipe_taxi starting execution
2019/03/27 12:15:01 Discovering influx schema for measurement: taxi
2019/03/27 12:15:01 Discovered: DataSet { Name: taxi, Columns: [Column {... 2019/03/27 12:15:01
Selected Schema Strategy: ValidateOnly
2019/03/27 12:15:01 Table taxi exists. Proceeding only with validation
2019/03/27 12:15:01 existing hypertable 'taxi' is partitioned properly
2019/03/27 12:15:01 Starting extractor 'pipe_taxi_ext' for measure: taxi
2019/03/27 12:15:01 Starting data ingestor 'pipe_taxi_ing'
2019/03/27 12:15:01 pipe_taxi_ext: Extracting data from database 'outflux_tutorial'
2019/03/27 12:15:01 pipe_taxi_ext: SELECT "time", "location_id", "rating", "vendor", "fare",
"mta_tax", "tip", "tolls"
FROM "taxi"
LIMIT 10
2019/03/27 12:15:01 pipe_taxi_ext:Pulling chunks with size 15000
2019/03/27 12:15:01 Will batch insert 8000 rows at once. With commit strategy: CommitOnEachBatch
2019/03/27 12:15:01 pipe_taxi_ext: Extracted 10 rows from Influx
2019/03/27 12:15:01 pipe_taxi_ing: Complete. Inserted 10 rows.
2019/03/27 12:15:01 All pipelines finished
2019/03/27 12:15:01 Migration execution time: 0.055 seconds选择导出数据的另一种方法是使用--from和--to标志指定要导出的更窄时间窗口。要仅在 2020 年 1 月 1 日之后导出数据，请执行以下命令：
$ outflux migrate outflux_tutorial cpu \
--input-server=http://localhost:8086 \
--output-conn="dbname=postgres user=postgres" \
--schema-strategy=ValidateOnly --from=2020-01-01T00:00:00Z如果仔细观察输出，您可以看到数据是从 InfluxDB 服务器中分块提取的，默认大小为 15000，但可以通过指定--chunk-size标志进行更改。数据分批插入8000行（默认），也可以通过flag修改--batch-size。迁移命令的所有可能标志都列在 GitHub 文档 ( https://github.com/timescale/outflux#migrate ) 中，或者您可以通过执行来查看它们：
$ outflux migrate --help十八.版本更新
1.版本兼容
| TimescaleDB 发布 | 支持的 PostgreSQL 版本 |
|---|---|
| 1.7 | 9.6、10、11、12 |
| 2.0 | 11、12 |
| 2.1-2.3 | 11、12、13 |
| 2.4+ | 12、13 |
2.timscaledb 更新
ALTER EXTENSION timescaledb UPDATE;3.pgsql 更新
TimescaleDB 的每个版本都与特定版本的 PostgreSQL 兼容。随着时间的推移，我们将添加对新版本 PostgreSQL 的支持，同时放弃对旧版本的支持。
当支持的 PostgreSQL 版本发生变化时，您可能需要升级PostgreSQL 实例的版本（例如从 10 到 12），然后才能安装最新版本的 TimescaleDB。
要升级 PostgreSQL，您有两种选择，如 PostgreSQL 在线文档中所述。
用pg_upgrade是一种工具，它避免了在安装新版本后转储所有数据然后将其导入新 PostgreSQL 实例的需要。相反，pg_upgrade允许您保留当前 PostgreSQL 安装的数据文件，同时将新的 PostgreSQL 二进制运行时绑定到它们。目前，所有版本 8.4 及更高版本均支持此功能。
# 创建新的data文件夹
mkdir /mnt/data/postgresql_data_13/
# 授权
chmod 700 /mnt/data/postgresql_data_13/
chown -R postgres:postgres /mnt/data/postgresql_data_13/
# 记得在切postgres用户时处于一个可写目录否则会出现could not write to log file "pg_upgrade_internal.log"
cd /tmp
su postgres
pg_upgrade -b oldbindir -B newbindir -d olddatadir -D newdatadir
# 如
/usr/pgsql-11/bin/pg_upgrade --link -b /usr/pgsql-11/bin -B /usr/pgsql-13/bin -d /mnt/data/postgresql_data -D /mnt/data/postgresql_data_13十九.插件
可根据项目需要自定义安装需要的插件
监控：
pg_stat_statements
system_stats用于观察OS层的信息
pg_stat_kcache可以观察单条SQL花费多少cpu等
pg_stat_monitor：pg_stat_statements和pg_stat_kcache的结合体
pgpro_stats：等待事件统计、采样配置、自动化监控
pgsentinel、pgsampler：类Oracle ASH
命令行：
pg_top，类top工具
pgcenter：全能监控工具
pg_activity：命令行top工具
pg_sysstat：相对简陋
SQL：
pg_hint_plan：hint功能
pg_query：高亮，识别风险SQL
pg_plan_advsr：懒人优化
hypopg：类似MySQL虚拟索引
sr_plan：保存、篡改、固定 执行计划，Oracle兼容
pg_parallizator：并行创建索引
JOB：
pg_timetable
pg_cron
pg_agent
连接池：
pgbouncer
pgagroal
odyssey
巡检：
Toolkit——percona
sharding：
citus、pg_shardman、plproxy
FDW：
oracle_fdw
mysql_fdw
mongo_fdw
sqlserver_fdw
file_fdw
等等
https://wiki.postgresql.org/wiki/Foreign_data_wrappers
Other：
ADG：图数据库
pg_buffercache：观察buffer
pgstattuple：行级别的统计
pg_filedump、pg_waldump、pg_walminer、pg_fix：观察文件、日志，修改日志
pgtrashcan：垃圾回收站
pg_timeout：空闲会话超时，pg14引入idle_session_timeout参数
pgcrypto：数据加密模块
pg_audit、pg_log_userqueries、pgreplay：审计
pageinspect：内窥数据库BLOCK的内容
passwordcheck：密码复杂度检测
pg_buffercache：统计数据库shared buffer的内容
pgcrypto：加密插件
pg_freespacemap：观察数据库fsm文件内容
pgrowlocks：行锁统计
pgstattuple,：记录级别统计信息观察
pg_trgm：模糊查询, 相似文本查询
pg_visibility：观察数据库block的vm标签值(all visibility, frozen, dirty等)
pg_prewarm、pg_fincore：数据预热
tablefunc：行列转换，connect by
auto_explain、pg_show_plans：执行计划
zhparser、pg_jieba：中文分词
pg_trgm、pg_bigm(没有3个分词限制)、pgroonga：模糊查询
pg_similarity、cube、rum：相似查询
pg_pathman、pg_partman：分区
pg_qualstats：索引建议
pg_wait_sampling：等待事件采样
citext：大小写
pg_query_state：后台工作情况
session_exec：失败超过次数自动锁定
postgis：强大的地理空间数据
pg_readonly：设为只读，类似transaction_read_only
pg_tt：全局临时表
pg_dropbuffer、pg_dropcache：删除cache和buffer
set_user、pg_permissions、pg_restrict：ACL，权限进一步加强
diskquota：类Oracle profile，不过只能限制磁盘
pg_prioritize：进程优先级调度
sql_firewall：SQL防火墙
auth_delay：防破解、安全
timescaledb：时序数据库
md5hash、gzip（wget http://api.pgxn.org/dist/gzip/1.0.0/gzip-1.0.0.zip ）、pgzstd：加密压缩
ddlx、pgddl：获取DDL
uuid-ossp：uuid生成
pipelindb：流式计算
orafce：oracle兼容、package一些内置函数等
pg_roaringbitmap：精准营销
pg_repack、pg_sequeeze：冻结、重建、垃圾回收
AWR：pg_awr、pg_profile
逻辑复制、cdc相关：wal2json、wal2mongo、decoder_raw、pglogical、decoderbufs
zedstore, 行列混合存储
undam、zheap：undo引擎
pgpool：读写分离、负载均衡
附.pgsql+timescaledb安装
1.下载最新版本pgsql
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-$(rpm -E %{rhel})-x86_64/pgdg-redhat-repo-latest.noarch.rpm2.添加PostgreSQL repo
# Add timescaledb repo
sudo tee /etc/yum.repos.d/timescale_timescaledb.repo <<EOL
[timescale_timescaledb]
name=timescale_timescaledb
baseurl=https://packagecloud.io/timescale/timescaledb/el/$(rpm -E %{rhel})/\$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
EOL
sudo yum update -y3.如果是CenterOs8禁用内置的pgsql
if command -v dnf; then sudo dnf -qy module disable postgresql; fi4.安装timscaledb
sudo yum install -y timescaledb-2-postgresql-135.可初始配置（基本不用执行）
sudo timescaledb-tune6.修改默认数据目录
# 首先在/home下创建一个Postgresql的数据目录，指定所有者postgres同时分配权限
mkdir /home/postgresql_data
chown postgres:postgres /home/postgresql_data
chmod 750 /home/postgresql_data7.设置环境变量
# vim /etc/profile
# 环境变量配置
export PATH=/usr/pgsql-13/bin:$PATH
export LD_LIBRARY_PATH=/usr/pgsql-13/lib
export PGDATA=/home/postgresql_data
# 环境变量生效
source /etc/profile8.切换postgres用户 并初始化数据库
9.修改配置内容指定正确的data路径
10.监听所有地址
11.添加timescaledb库
12.配置数据库开机启动并启动数据库服务
systemctl enable postgresql-13.service
service postgresql-13 start
# 查看状态
service postgresql-13 status
service postgresql-13 stop
service postgresql-13 restart13.远程访问配置
添加如下行
host all all 0.0.0.0/0 md5
14.注意事项
必须开启服务器5432端口并设置用户权限alter role postgres with password ‘123456’;才能正常使用工具远程访问
15.windows中安装
下载pgsql安装包一直下一步即可
配置路径 + /bin 到环境变量
执行timescaled插件包中的setup.exe
输入psql中postgres.conf 路径到cmd中
本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 ALLBS！
- 支付宝
- 微信
- qq
评论