将您的数据迁移到 Timescale剧本

使用 pg_dump 带停机时间从 AWS RDS 迁移到 Timescale

本指南说明了将 Postgres 数据库从 AWS RDS 服务迁移到 Timescale 实例的过程。我们将使用 Postgres 社区工具（如 `pg_dump` 和 `pg_restore`）来促进迁移过程。

使用 pg_dump 迁移数据需要您的生产数据库停机一段时间，停机时间与数据库大小成正比。在迁移过程中，您的应用程序将无法写入您的生产数据库。如果您想要一个从您的 RDS 实例到 Timescale 的低停机时间迁移解决方案，请参阅 使用实时迁移从 AWS RDS 迁移到 Timescale

注意

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

先决条件

在开始迁移过程之前，您需要

  1. 您的 AWS RDS 实例的详细信息
  2. 中间机器

您的 AWS RDS 实例的详细信息

您需要有关您的 Postgres RDS 实例的以下信息

  • 端点
  • 端口
  • 主用户名
  • 主密码
  • VPC

要收集所需信息，请导航到“数据库”面板并选择您的 RDS 实例。

List of databases in RDS panel

从“连接性和安全”选项卡记录 **端点**、**端口** 和 **VPC** 信息，从“配置”选项卡记录 **主用户名**。请记住使用创建 Postgres RDS 实例时提供的 **主密码**。

Record endpoint, port, VPC details
Record master username

中间机器

为了执行 `pg_dump` 和 `pg_restore`，我们需要一个 EC2 实例。该实例应该能够访问您的 RDS 服务。因此，我们将 EC2 机器设置在与 RDS 服务相同的 VPC 中。

创建 EC2 实例

  1. 在 AWS 搜索中，输入“EC2”并选择服务下的“EC2”选项。

    Create an EC2 instance

  2. 点击“启动实例”。

    Start configuring your instance

  3. 配置您的 EC2 实例。

    a. 对于“应用程序和操作系统映像”，选择 Ubuntu Server LTS。

    b. 对于“实例类型”，至少使用 2 个 CPU 和 8 GB 内存。如果您的迁移涉及更大的数据库，您应该相应地选择。

    Choose instance type

    c. 对于“密钥对”，您可以选择使用现有密钥对或创建新的密钥对。这在从本地机器连接到 EC2 实例时将是必需的。

    d. 对于“网络设置”，选择您的 RDS 实例所在的相同 VPC。此外，将安全组的“源类型”修改为“我的 IP”，以便您的本地机器可以连接到 EC2 实例。

    Configure network

    e. 对于“配置存储”部分，调整卷大小以匹配数据库的大小。如有必要，您应该启用卷上的加密。

    Configure storage

  4. 查看实例的摘要，然后点击“启动实例”。

    Review EC2 instance

准备 EC2 实例

要准备 EC2 实例以进行迁移

  1. 导航到您的 EC2 实例，然后点击“连接”按钮。

    Connect to EC2 instance

  2. 选择“SSH 客户端”选项卡。

    Use SSH client

  3. 使用上一步下载的“密钥对”连接到您的 EC2 实例。

chmod 400 <key-pair>.pem
ssh -i "<key-pair>.pem" ubuntu@<EC2 instance's Public IPv4>
  1. 安装 PostgreSQL 客户端。
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget -qO- https://postgresql.ac.cn/media/keys/ACCC4CF8.asc | sudo tee /etc/apt/trusted.gpg.d/pgdg.asc &>/dev/null
sudo apt update
sudo apt install postgresql-client-15 -y # "postgresql-client-16" if your source DB is using PG 16.
psql --version && pg_dump --version

  1. 要允许 EC2 实例连接到 RDS 实例，您需要修改与您的 RDS 实例关联的安全组。

    a. 记录您的 EC2 实例的私有 IPv4 地址。

    Note private IPv4 address of your EC2 instance

    b. 转到与您的 RDS 实例关联的安全组，然后选择“编辑入站规则”。

    Edit inbound rules

    c. 选择“添加规则”。在“类型”字段中，选择“PostgreSQL”。对于“源”，选择“自定义”，并输入 EC2 实例的私有 IPv4 地址。为该规则添加适当的描述。最后，点击“保存规则”以应用更改。

    Add a new rule

  2. 从您的 EC2 实例验证与 RDS 服务的连接。请注意，系统将提示您输入主密码。这应该与您创建 AWS RDS 服务时使用的密码相同。

# psql -h <rds_endpoint> -p <rds_port> -U postgres -c "select 1"
psql -h aws-rds-migration-guide-db.csupoydrdg9f.us-east-1.rds.amazonaws.com -p 5432 -U postgres -c "select 1"
Password for user postgres:
 ?column?
----------
        1
(1 row)
提示

如果您遇到问题，您可以通过打开支持请求或将您的问题带到 社区 Slack 中的 `#migration` 频道来获得帮助，该频道中的迁移方法开发人员可以帮助您。

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

执行数据迁移

迁移过程包括以下步骤

  1. 在 Timescale 中设置目标数据库实例。
  2. 将角色和模式从源迁移到目标。
  3. [可选]启用 TimescaleDB 超级表。
  4. 将数据从源迁移到目标。
  5. 通过与源比较来验证目标中的数据。

1. 在 Timescale 中设置目标数据库实例

在 Timescale 中创建数据库服务.

如果您打算迁移超过 400 GB，请打开支持请求以确保在您的 Timescale 实例上预先配置了足够的磁盘空间。

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

2. 将角色和模式从源迁移到目标

在开始迁移过程之前，您应该确保您的源数据库没有接收任何 DML 查询。这对于保证一致的迁移和确保生成的数据库准确反映您的源数据库至关重要。

重要

要从源到目标一致地迁移数据，您的生产应用程序不应该写入您的源数据库（即它们将需要停机），否则，在开始迁移过程之后写入源数据库的数据将不会出现在目标数据库中。

如果您想要在迁移过程中写入源数据库，请参阅 实时迁移 剧本。

2.a 将数据库角色从源迁移到目标

pg_dumpall -d "$SOURCE" \
  --quote-all-identifiers \
  --roles-only \
  --no-role-passwords \
  --file=roles.sql
重要

AWS RDS 不允许转储带有密码的角色，这就是为什么上面的命令用 `--no-role-passwords` 执行的原因。但是，当将角色迁移到您的 Timescale 实例完成时，您需要使用以下命令手动将密码分配给必要的角色：`ALTER ROLE name WITH PASSWORD 'password';`

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

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

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

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

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

此脚本的简要说明是

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

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

  • `CREATE ROLE "rds`、`ALTER ROLE “rds`、`TO "rds`、`GRANT "rds`：任何创建或更改以 rds 为前缀的角色都会被删除，因为它们在 Timescale 实例中没有任何用途。同样，对以“rds”为前缀的角色的任何授予或撤销也会被忽略。

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

2.b 从源数据库转储数据库模式

pg_dump -d "$SOURCE" \
  --format=plain \
  --quote-all-identifiers \
  --no-tablespaces \
  --no-owner \
  --no-privileges \
  --section=pre-data \
  --file=pre-data-dump.sql \
  --snapshot=$(cat /tmp/pgcopydb/snapshot)

  • `--section=pre-data` 用于仅转储表的定义、序列、检查约束和继承层次结构。它不包括索引、外键约束、触发器和规则。

  • `--snapshot` 用于指定同步的 快照，在对数据库进行转储时。

  • `--no-tablespaces` 是必需的，因为 Timescale 不支持除默认表空间之外的任何其他表空间。这是一个已知限制。

  • `--no-owner` 是必需的，因为 Timescale 的 `tsdbadmin` 用户不是超级用户，无法在所有情况下分配所有权。此标志意味着所有内容都由用于连接到目标的用户拥有，无论源中的所有权如何。这是一个已知限制。

  • `--no-privileges` 是必需的，因为 Timescale 的 `tsdbadmin` 用户不是超级用户，无法在所有情况下分配权限。此标志意味着分配给其他用户的权限必须在目标数据库中作为手动清理任务重新分配。这是一个已知限制。

2.c 将角色和模式加载到目标数据库中

psql -X -d "$TARGET" \
  -v ON_ERROR_STOP=1 \
  --echo-errors \
  -f roles.sql \
  -f pre-data-dump.sql

3. [可选]启用 TimescaleDB 超级表

在从源数据库恢复数据之前，您可能需要考虑将标准 Postgres 表转换为 TimescaleDB 超级表。迁移过程的这一阶段为这种转换提供了最佳机会。从本质上讲，您将想要转换包含时间序列数据的 Postgres 表。对于您计划在目标数据库中转换为超级表的每个表，请执行以下命令

psql -X -d "$TARGET" \
  -v ON_ERROR_STOP=1 \
  -c "SELECT create_hypertable('<table name>', '<time column name>')"

更详细的说明可以在 超级表文档 中找到。转换表后，您可以按照指南启用更多 Timescale 功能，如 保留 和 压缩。

重要

此步骤是可选的，但我们强烈建议您现在执行此步骤。

虽然在迁移完成后将表转换为超级表是可能的，但这需要有效地重新写入表中的所有数据，这会锁定表在操作持续时间内，并阻止写入。

4. 将数据从源数据库迁移到目标数据库

4.a 从源数据库转储数据

使用 pg_dump，将数据从源数据库转储到中间存储

pg_dump -d "$SOURCE" \
  --format=plain \
  --quote-all-identifiers \
  --no-tablespaces \
  --no-owner \
  --no-privileges \
  --data-only \
  --file=dump.sql

4b. 转储数据库索引、约束和其他对象

pg_dump -d "$SOURCE" \
  --format=plain \
  --quote-all-identifiers \
  --no-tablespaces \
  --no-owner \
  --no-privileges \
  --section=post-data \
  --file=post-data-dump.sql \
  --snapshot=$(cat /tmp/pgcopydb/snapshot)

  • --section=post-data 用于转储后数据项，包括索引、触发器、规则和约束的定义，但不包括已验证的检查约束。

  • `--snapshot` 用于指定同步的 快照，在对数据库进行转储时。

  • `--no-tablespaces` 是必需的，因为 Timescale 不支持除默认表空间之外的任何其他表空间。这是一个已知限制。

  • `--no-owner` 是必需的，因为 Timescale 的 `tsdbadmin` 用户不是超级用户，无法在所有情况下分配所有权。此标志意味着所有内容都由用于连接到目标的用户拥有，无论源中的所有权如何。这是一个已知限制。

  • `--no-privileges` 是必需的，因为 Timescale 的 `tsdbadmin` 用户不是超级用户，无法在所有情况下分配权限。此标志意味着分配给其他用户的权限必须在目标数据库中作为手动清理任务重新分配。这是一个已知限制。

4.b 将数据恢复到目标数据库

将转储文件恢复到 Timescale 实例

psql -d $TARGET -v ON_ERROR_STOP=1 --echo-errors \
    -f dump.sql \
    -f post-data-dump.sql

通过对所有数据运行 ANALYZE 来更新表统计信息

psql -d $TARGET -c "ANALYZE;"

5. 验证目标数据库中的数据

通过连接到目标数据库并查询已恢复的数据来验证数据是否已成功恢复。

验证数据存在并返回预期结果后，您可以重新配置应用程序以使用目标数据库。

关键词

数据迁移postgresqlRDS

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

上一步剧本下一步从 AWS RDS 使用实时迁移

相关内容

使用实时迁移从 AWS RDS 迁移到 Timescale
使用实时迁移将 AWS RDS 中的 PostgreSQL 数据库迁移到 Timescale
将您的数据迁移到 Timescale
将现有数据库迁移到 Timescale
剧本
将您的数据库迁移到 Timescale 的分步剧本
从多节点迁移到 Timescale
使用 COPY 将整个多节点部署迁移到 Timescale 实例，并带有停机时间
将您的 PostgreSQL 数据库迁移到自托管 TimescaleDB
将现有 PostgreSQL 数据库迁移到 Timescale
排查已知问题
排查数据库迁移中的已知问题