将您的数据迁移到 Timescale剧本

从多节点迁移到 TimescaleDB

以下说明将向您展示如何使用pg_dump和psql将数据从自托管的 TimescaleDB 多节点部署迁移到 Timescale 实例。为了不丢失任何数据，应将连接到数据库的应用程序下线。迁移的持续时间与数据库中存储的数据量成正比。如果您希望使用此方法迁移超过 400 GB 的数据，请打开支持请求以确保在您的 Timescale 实例上预先配置了足够的磁盘。

您可以直接从Timescale 控制台或通过电子邮件发送到support@timescale.com打开支持请求。

注意

在迁移的上下文中，您的现有生产数据库被称为“源”数据库，而您打算将数据迁移到的新的 Timescale 数据库被称为“目标”数据库。

先决条件

为了最少停机时间，应从具有低延迟、高吞吐量链接到其连接的数据库的机器上运行pg_dump和psql命令。

在开始之前，请确保您已

  • 在您将从其执行迁移的机器上安装 PostgreSQL 客户端库，您将需要pg_dump和psql。
  • 在 Timescale 中创建数据库服务.
  • 检查您使用的所有 PostgreSQL 扩展是否在 Timescale 上可用。有关更多信息，请参阅兼容扩展列表。
  • 检查目标数据库中的 PostgreSQL 版本是否大于或等于源数据库的版本。
  • 检查您在目标数据库和源数据库上都运行着完全相同的 Timescale 版本（主要/次要/修补程序版本必须完全相同）。有关更多信息，请参阅自托管 TimescaleDB 的升级说明。

从源迁移角色到目标

pg_dumpall -d "$SOURCE" \
  -l $DB_NAME \
  --quote-all-identifiers \
  --roles-only \
  --file=roles.sql
重要

某些提供商（如 Managed Service for TimescaleDB (MST) 和 AWS RDS）不支持角色密码转储。如果转储密码导致错误

pg_dumpall: error: query failed: ERROR:  permission denied for table pg_authid

执行命令时添加--no-role-passwords标志。在将角色恢复到目标数据库后，使用ALTER ROLE name WITH PASSWORD '<YOUR_PASSOWRD>';手动设置密码。

Timescale 服务不支持具有超级用户访问权限的角色。如果您的 SQL 转储包含具有此类权限的角色，您需要修改文件以符合安全模型。

您可以使用以下sed命令从您的roles.sql文件中删除不受支持的语句和权限

sed -i -E \
-e '/CREATE ROLE "postgres";/d' \
-e '/ALTER ROLE "postgres"/d' \
-e 's/(NO)*SUPERUSER//g' \
-e 's/(NO)*REPLICATION//g' \
-e 's/(NO)*BYPASSRLS//g' \
-e 's/GRANTED BY "[^"]*"//g' \
roles.sql
注意

此命令仅适用于 GNU 版本的 sed（有时称为 gsed）。对于 BSD 版本（macOS 上的默认版本），您需要添加一个额外的参数将-i标志更改为-i ''。

要检查 sed 版本，您可以使用命令sed --version。虽然 GNU 版本明确标识为 GNU，但 BSD 版本的 sed 通常不提供直接的 --version 标志，并且只输出“非法选项”错误。

此脚本的简要说明是

  • CREATE ROLE "postgres"; 和 ALTER ROLE "postgres"：这些语句被删除，因为它们需要超级用户访问权限，而 Timescale 不支持超级用户访问权限。

  • (NO)SUPERUSER | (NO)REPLICATION | (NO)BYPASSRLS：这些是需要超级用户访问权限的权限。

  • GRANTED BY role_specification：GRANTED BY 子句也可能具有需要超级用户访问权限的权限，因此应将其删除。注意：根据 TimescaleDB 文档，GRANTED BY 子句中的 GRANTOR 必须是当前用户，并且此子句主要用于 SQL 兼容性。因此，删除它很安全。

注意

为了方便起见，连接到源数据库和目标数据库的连接字符串在整个指南中分别被称为$SOURCE和$TARGET。这可以在您的 shell 中设置，例如

export SOURCE=postgres://<user>:<password>@<source host>:<source port>/<db_name>
export TARGET=postgres://<user>:<password>@<target host>:<target port>/<db_name>

迁移数据前的架构

将您的数据前数据从源数据库迁移到 Timescale 实例。这包括表和架构定义，以及有关序列、所有者和设置的信息。这并不包括特定于 Timescale 的架构。

迁移数据前的架构

  1. 使用您的源数据库连接详细信息，将数据前架构从您的源数据库转储到dump_pre_data.dump文件。排除特定于 Timescale 的架构。如果系统提示您输入密码，请使用您的源数据库凭据

    pg_dump -U <SOURCE_DB_USERNAME> -W \
    -h <SOURCE_DB_HOST> -p <SOURCE_DB_PORT> -Fc -v \
    --section=pre-data --exclude-schema="_timescaledb*" \
    -f dump_pre_data.dump <DATABASE_NAME>

  2. 使用您的 Timescale 连接详细信息，将转储的数据从dump_pre_data.dump文件恢复到您的 Timescale 数据库。为了避免权限错误，请包含--no-owner标志

    pg_restore -U tsdbadmin -W \
    -h <HOST> -p <PORT> --no-owner -Fc \
    -v -d tsdb dump_pre_data.dump

在您的 Timescale 实例中恢复超表

在数据前迁移之后，您源数据库中的分布式超表将变为普通的 PostgreSQL 表。在您的 Timescale 实例中将它们重新创建为普通的超表以恢复它们。

注意

分布式超表通常使用额外的空间维度空间分区创建。虽然它们可能对允许在数据节点之间分配数据很有意义，但这些额外的维度在标准单节点部署中可能没有用。因此，在将分布式超表转换为普通的超表时，可能值得考虑删除这些空间维度。

在 Timescale 中恢复超表

  1. 连接到您的 Timescale 数据库

    psql "postgres://tsdbadmin:<PASSWORD>@<HOST>:<PORT>/tsdb?sslmode=require"

  2. 恢复超表

    SELECT create_hypertable(
       '<TABLE_NAME>', '<TIME_COLUMN_NAME>',
        chunk_time_interval =>
            INTERVAL '<CHUNK_TIME_INTERVAL>');

从源数据库复制数据

恢复您的超表后，返回到您的源数据库以按表复制数据。

从您的源数据库复制数据

  1. 连接到您的源数据库

    psql "postgres://<SOURCE_DB_USERNAME>:<SOURCE_DB_PASSWORD>@<SOURCE_DB_HOST>:<SOURCE_DB_PORT>/<SOURCE_DB_NAME>?sslmode=require"

  2. 将第一个表中的数据转储到.csv文件

    \copy (SELECT * FROM <TABLE_NAME>) TO <TABLE_NAME>.csv CSV

    对您要迁移的每个表和分布式超表重复此操作。

注意

如果您的分布式超表非常大，您可以分多个部分迁移每个分布式超表。按时间范围拆分每个分布式超表，并单独复制每个范围。例如

\copy (SELECT * FROM <TABLE_NAME> WHERE time > '2021-11-01' AND time < '2011-11-02') TO <TABLE_NAME_DATE_RANGE>.csv CSV

将数据恢复到 TimescaleDB 实例

将数据复制到.csv文件后，您可以通过从.csv文件复制数据将其恢复到 Timescale 实例。有两种方法：使用普通的 PostgreSQL COPY，或使用 TimescaleDB timescaledb-parallel-copy 函数。timescaledb-parallel-copy工具默认情况下不包含。您必须安装该工具。

使用 timescaledb-parallel-copy 将数据恢复到 Timescale

  1. 在命令提示符下，安装timescaledb-parallel-copy

    go get github.com/timescale/timescaledb-parallel-copy/cmd/timescaledb-parallel-copy

  2. 使用timescaledb-parallel-copy将数据导入您的 Timescale 数据库。将<NUM_WORKERS>设置为数据库中 CPU 数量的两倍。例如，如果您有 4 个 CPU，则<NUM_WORKERS>应为8。

    timescaledb-parallel-copy \
    --connection "host=<HOST> \
    user=tsdbadmin password=<PASSWORD> \
    port=<PORT> \
    sslmode=require" \
    --db-name tsdb \
    --table <TABLE_NAME> \
    --file <FILE_NAME>.csv \
    --workers <NUM_WORKERS> \
    --reporting-period 30s

    对您要迁移的每个表和超表重复此操作。

使用 COPY 将数据恢复到 Timescale

  1. 连接到您的 Timescale 数据库

    psql "postgres://tsdbadmin:<PASSWORD>@<HOST>:<PORT>/tsdb?sslmode=require"

  2. 将数据恢复到您的 Timescale 数据库

    \copy <TABLE_NAME> FROM '<TABLE_NAME>.csv' WITH (FORMAT CSV);

    对您要迁移的每个表和超表重复此操作。

迁移数据后的架构

迁移表和超表数据后，迁移您的 PostgreSQL 数据后架构。这包括有关约束的信息。

迁移数据后的架构

  1. 在命令提示符下，使用您的源数据库连接详细信息，将数据后架构从您的源数据库转储到dump_post_data.dump文件。排除特定于 Timescale 的架构。如果系统提示您输入密码，请使用您的源数据库凭据

    pg_dump -U <SOURCE_DB_USERNAME> -W \
    -h <SOURCE_DB_HOST> -p <SOURCE_DB_PORT> -Fc -v \
    --section=post-data --exclude-schema="_timescaledb*" \
    -f dump_post_data.dump <DATABASE_NAME>

  2. 使用您的连接详细信息，将转储的数据后架构从dump_post_data.dump文件恢复到您的 Timescale 数据库。为了避免权限错误，请包含--no-owner标志

    pg_restore -U tsdbadmin -W \
    -h <HOST> -p <PORT> --no-owner -Fc \
    -v -d tsdb dump_post_data.dump

故障排除

如果您在迁移过程中看到这些错误，您可以安全地忽略它们。迁移仍然成功完成。

pg_restore: error: could not execute query: ERROR:  relation "<relation_name>" already exists
pg_restore: error: could not execute query: ERROR:  trigger "ts_insert_blocker" for relation "<relation_name>" already exists

重新创建连续聚合

当您分别传输架构和数据时，连续聚合不会默认迁移。您可以通过重新创建连续聚合定义并在您的 Timescale 数据库上重新计算结果来恢复它们。重新计算的连续聚合只聚合您 Timescale 数据库中现有的数据。它们不包括已删除的原始数据。

重新创建连续聚合

  1. 连接到您的源数据库

    psql "postgres://<SOURCE_DB_USERNAME>:<SOURCE_DB_PASSWORD>@<SOURCE_DB_HOST>:<SOURCE_DB_PORT>/<SOURCE_DB_NAME>?sslmode=require"

  2. 获取您现有连续聚合定义的列表

    SELECT view_name, view_definition FROM timescaledb_information.continuous_aggregates;

    此查询返回所有连续聚合的名称和定义。例如

    view_name       |                                            view_definition
    ----------------+--------------------------------------------------------------------------------------------------------
    avg_fill_levels |  SELECT round(avg(fill_measurements.fill_level), 2) AS avg_fill_level,                                +
                    |     time_bucket('01:00:00'::interval, fill_measurements."time") AS bucket,                            +
                    |     fill_measurements.sensor_id                                                                       +
                    |     FROM fill_measurements                                                                            +
                    |     GROUP BY (time_bucket('01:00:00'::interval, fill_measurements."time")), fill_measurements.sensor_id;
    (1 row)

  3. 连接到您的 Timescale 数据库

    psql "postgres://tsdbadmin:<PASSWORD>@<HOST>:<PORT>/tsdb?sslmode=require"

  4. 重新创建每个连续聚合定义

    CREATE MATERIALIZED VIEW <VIEW_NAME>
    WITH (timescaledb.continuous) AS
    <VIEW_DEFINITION>

重新创建策略

默认情况下，当您分别传输架构和数据时，策略不会迁移。在您的 Timescale 数据库上重新创建它们。

重新创建策略

  1. 连接到您的源数据库

    psql "postgres://<SOURCE_DB_USERNAME>:<SOURCE_DB_PASSWORD>@<SOURCE_DB_HOST>:<SOURCE_DB_PORT>/<SOURCE_DB_NAME>?sslmode=require"

  2. 获取您现有策略的列表。此查询返回所有策略的列表，包括连续聚合刷新策略、保留策略、压缩策略和重新排序策略

    SELECT application_name, schedule_interval, retry_period,
        config, hypertable_name
        FROM timescaledb_information.jobs WHERE owner = '<SOURCE_DB_USERNAME>';

  3. 连接到您的 Timescale 数据库

    psql "postgres://tsdbadmin:<PASSWORD>@<HOST>:<PORT>/tsdb?sslmode=require"

  4. 重新创建每个策略。有关重新创建策略的更多信息，请参阅有关连续聚合刷新策略、保留策略、压缩策略和重新排序策略的部分。

更新表统计信息

通过对您的整个数据集运行ANALYZE来更新您的表统计信息。请注意，这可能需要一些时间，具体取决于您的数据库的大小

ANALYZE;

故障排除

如果您在运行ANALYZE时看到以下形式的错误，您可以安全地忽略它们

WARNING:  skipping "<TABLE OR INDEX>" --- only superuser can analyze it

跳过的表和索引对应于无法访问的系统目录。跳过它们不会影响数据的统计信息。

关键词

迁移停机时间

在此页面上发现问题？报告问题 或在 GitHub 上编辑此页面。

上一页从 AWS RDS 使用实时迁移下一页故障排除

相关内容

剧本
将您的数据库迁移到 Timescale 的分步剧本
使用 pg_dump 从 AWS RDS 迁移到 Timescale
使用 pg_dump 将 AWS RDS 中的 PostgreSQL 数据库迁移到 Timescale
实时迁移
以低停机时间迁移整个数据库
常见问题解答
实时迁移常见问题解答
使用实时迁移从 AWS RDS 迁移到 Timescale
使用实时迁移将 AWS RDS 中的 PostgreSQL 数据库迁移到 Timescale
使用实时迁移从 PostgreSQL 迁移
使用低停机时间的实时迁移方法从 PostgreSQL 数据库迁移