将数据从 RDS for PostgreSQL 数据库实例导出到 Amazon S3

您可以从 RDS for PostgreSQL 数据库实例中查询数据，并将数据直接导出到存储在 Amazon S3 存储桶中的文件中。为此，您首先要安装 RDS for PostgreSQL aws_s3 扩展。该扩展为您提供了用于将查询结果导出到 Amazon S3 的函数。接下来，您可以了解如何安装扩展以及如何将数据导出到 Amazon S3。

您可以从预调配或 Aurora Serverless v2 数据库实例中导出。Aurora Serverless v1 不支持这些步骤。

注意

不支持跨账户导出到 Amazon S3。

所有当前可用的 RDS for PostgreSQL 版本都支持将数据导出到 Amazon Simple Storage Service。有关详细版本信息，请参阅《Amazon RDS for PostgreSQL 版本注释》中的 Amazon RDS for PostgreSQL 更新。

如果您没有为导出设置桶，请参阅《Amazon Simple Storage Service 用户指南》中的以下主题。

• 设置 Amazon S3

• 创建存储桶

预设情况下，上载到 Amazon S3 的操作使用服务器端加密。如果您使用加密，则 Amazon S3 存储桶必须使用 AWS 托管式密钥 加密。目前，您无法将数据导出到使用客户托管式密钥加密的桶。

注意

您可以使用AWS Management Console、AWS CLI 或 Amazon RDS API 将数据库快照数据保存到 Amazon S3。有关更多信息，请参阅将数据库快照数据导出到 Amazon S3。

安装 aws_s3 扩展名

在将 Amazon Simple Storage Service 用于 RDS for PostgreSQL 数据库实例之前，您需要安装 aws_s3 扩展。此扩展提供将数据从 RDS for PostgreSQL 数据库实例中导出到 Amazon S3 桶的函数。它还提供从 Amazon S3 导入数据的函数。有关更多信息，请参阅将 Amazon S3 中的数据导入到 RDS for PostgreSQL 数据库实例。aws_s3 扩展依赖于 aws_commons 扩展（需要时自动安装）中的一些帮助程序函数。

安装 aws_s3 扩展

1. 使用 psql（或 pgAdmin）以具有 rds_superuser 权限的用户身份，连接到 RDS for PostgreSQL 数据库实例。如果您在设置过程中保留原定设置名称，则以 postgres 进行连接。

   psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password

2. 要安装扩展，请运行以下命令。

   postgres=> CREATE EXTENSION aws_s3 CASCADE;
   NOTICE: installing required extension "aws_commons"
   CREATE EXTENSION

3. 要验证扩展是否已安装，可以使用 psql \dx 元命令。

   postgres=> \dx
          List of installed extensions
       Name     | Version |   Schema   |                 Description
   -------------+---------+------------+---------------------------------------------
    aws_commons | 1.2     | public     | Common data types across AWS services
    aws_s3      | 1.1     | public     | AWS S3 extension for importing data from S3
    plpgsql     | 1.0     | pg_catalog | PL/pgSQL procedural language
   (3 rows)

从 Amazon S3 导入数据和将数据导出到 Amazon S3 的函数现在可供使用。

验证您的 RDS for PostgreSQL 版本支持导出到 Amazon S3

您可以使用 describe-db-engine-versions 命令验证 RDS for PostgreSQL 版本是否支持导出到 Amazon S3。以下示例验证对版本 10.14 的支持。

aws rds describe-db-engine-versions --region us-east-1 ^
--engine postgres --engine-version 10.14 | grep s3Export

如果输出包含字符串 "s3Export"，则该引擎支持 Amazon S3 导出。否则，该引擎不支持它们。

将数据导出到 Amazon S3 概述

要将存储在 RDS for PostgreSQL 数据库中的数据导出到 Amazon S3 存储桶，请使用以下过程。

将 RDS for PostgreSQL 数据导出到 S3

1. 标识要用于导出数据的 Amazon S3 文件路径。有关此过程的详细信息，请参阅指定要导出到的 Amazon S3 文件路径。

2. 提供权限以访问 Amazon S3 存储桶。

   要将数据导出到 Amazon S3 文件，请为 RDS for PostgreSQL 数据库实例提供权限以访问导出将用于存储的 Amazon S3 存储桶。此操作包括以下步骤：

   1. 创建一个 IAM 策略，该策略提供对要导出到的 Amazon S3 存储桶的访问权限。

   2. 创建一个 IAM 角色。

   3. 将您创建的策略附在您创建的角色上。

   4. 将此 IAM 角色添加到数据库实例。

   有关此过程的详细信息，请参阅设置 Amazon S3 存储桶的访问权限。

3. 标识数据库查询以获取数据。通过调用 aws_s3.query_export_to_s3 函数来导出查询数据。

   在完成上述准备任务后，请使用 aws_s3.query_export_to_s3 函数将查询结果导出到 Amazon S3。有关此过程的详细信息，请参阅使用 aws_s3.query_export_to_s3 函数导出查询数据。

指定要导出到的 Amazon S3 文件路径

指定以下信息以标识要将数据导出到的 Amazon S3 中的位置：

• 存储桶名称 – 存储桶是 Amazon S3 对象或文件的容器。

  有关使用 Amazon S3 存储数据的更多信息，请参阅 Amazon Simple Storage Service 用户指南中的创建存储桶和查看对象。

• 文件路径 – 文件路径标识 Amazon S3 存储桶中存储导出的位置。文件路径包含以下内容：

  • 用于标识虚拟文件夹路径的可选路径前缀。

  • 用于标识要存储的一个或多个文件的文件前缀。较大的导出将存储在多个文件中，每个文件的最大大小约为 6 GB。其他文件名具有相同的文件前缀，但追加了 _partXX。XX 表示 2，然后表示 3，依此类推。

  例如，具有 exports 文件夹和 query-1-export 文件前缀的文件路径为 /exports/query-1-export。

• AWS区域（可选）– Amazon S3 存储桶所在的AWS区域。如果您未指定AWS区域值，则 Amazon RDS 会将文件保存到导出的数据库实例所在的同一AWS区域中的 Amazon S3 中。

  注意

  目前，AWS 区域必须与导出的数据库实例的区域相同。

  有关 AWS 区域名称和关联值的列表，请参阅区域、可用区和 Local Zones。

要保存有关导出的存储位置的 Amazon S3 文件信息，您可以使用 aws_commons.create_s3_uri 函数创建一个 aws_commons._s3_uri_1 复合结构，如下所示。

psql=> SELECT aws_commons.create_s3_uri(
   'sample-bucket',
   'sample-filepath',
   'us-west-2'
) AS s3_uri_1 \gset

稍后，您可以在对 s3_uri_1 函数的调用中将该 aws_s3.query_export_to_s3 值作为参数提供。有关示例，请参阅 使用 aws_s3.query_export_to_s3 函数导出查询数据。

设置 Amazon S3 存储桶的访问权限

要将数据导出到 Amazon S3，请为 PostgreSQL 数据库实例提供权限以访问文件将保存到的 Amazon S3 存储桶。

为此，请使用以下过程。

通过 IAM 角色向 PostgreSQL 数据库实例授予访问 Amazon S3 的权限

1. 创建一个 IAM policy。

   该策略提供存储桶和对象权限，以允许 PostgreSQL 数据库实例访问 Amazon S3。

   在创建此策略的过程中，请执行以下步骤：

   1. 在策略中包含以下必需操作，以允许将文件从 PostgreSQL 数据库实例传输到 Amazon S3 存储桶：

      • s3:PutObject

      • s3:AbortMultipartUpload

   2. 包含用于标识 Amazon S3 存储桶以及其中的对象的 Amazon Resource Name (ARN)。用于访问 Amazon S3 的 ARN 格式为：arn:aws:s3:::your-s3-bucket/*

   有关为 Amazon RDS for PostgreSQL 创建 IAM 策略的更多信息，请参阅 创建和使用适用于 IAM 数据库访问的 IAM 策略。另请参阅 IAM 用户指南中的教程：创建和附加您的第一个客户托管策略。

   以下 AWS CLI 命令使用这些选项创建一个名为 rds-s3-export-policy 的 IAM 策略。它授予访问名为 your-s3-bucket 的存储桶的权限。

   警告

   我们建议您在一个私有 VPC 中设置数据库，该 VPC 配置了用于访问特定存储桶的终端节点策略。有关更多信息，请参阅 Amazon VPC 用户指南 中的对 Amazon S3 使用终端节点策略。

   强烈建议您不要创建具有所有资源访问权限的策略。此访问权限可能会对数据安全造成威胁。如果您使用 S3:PutObject 创建一个向 "Resource":"*" 授予对所有资源的访问权限的策略，则具有导出特权的用户可以将数据导出到您账户中的所有存储桶。此外，用户可以将数据导出到 AWS 区域内的任何可公开写入的存储桶。

   在您创建策略之后，请记下策略的 Amazon Resource Name (ARN)。在将策略附加到 IAM 角色时，您在后面的步骤中需要使用 ARN。

   aws iam create-policy  --policy-name rds-s3-export-policy  --policy-document '{
        "Version": "2012-10-17",
        "Statement": [
          {
            "Sid": "s3export",
            "Action": [
              "s3:PutObject",
              "s3:AbortMultipartUpload"
            ],
            "Effect": "Allow",
            "Resource": [
              "arn:aws:s3:::your-s3-bucket/*"
            ] 
          }
        ] 
      }'

2. 创建一个 IAM 角色。

   这样，Amazon RDS 就可以担任该 IAM 角色以代表您访问 Amazon S3 存储桶。有关更多信息，请参阅 IAM 用户指南中的创建向 IAM 用户委派权限的角色。

   我们建议在基于资源的策略中使用 aws:SourceArn 和 aws:SourceAccount 全局条件上下文键，以限制对特定资源的服务权限。这是防范混淆代理人问题最有效的方法。

   如果同时使用全局条件上下文键和包含账户 ID 的 aws:SourceArn 值，则 aws:SourceAccount 值和 aws:SourceArn 值中的账户在同一策略语句中使用时，必须使用相同的账户 ID。

   • 如果您想对单个资源进行跨服务访问，请使用 aws:SourceArn。

   • 如果您想允许该账户中的任何资源与跨服务使用操作相关联，请使用 aws:SourceAccount。

   在策略中，确保使用具有资源的完整 ARN 的 aws:SourceArn 全局条件上下文键。以下示例说明了如何使用 AWS CLI 命令创建一个名为 rds-s3-export-role 的角色来实现该目的。

   例

   对于 Linux、macOS 或 Unix：

   aws iam create-role  \
       --role-name rds-s3-export-role  \
       --assume-role-policy-document '{
        "Version": "2012-10-17",
        "Statement": [
          {
            "Effect": "Allow",
            "Principal": {
               "Service": "rds.amazonaws.com"
             },
            "Action": "sts:AssumeRole",
            "Condition": {
                "StringEquals": {
                   "aws:SourceAccount": "111122223333",
                   "aws:SourceArn": "arn:aws:rds:us-east-1:111122223333:db:dbname"
                   }
                }
          }
        ] 
      }'

   对于 Windows：

   aws iam create-role  ^
       --role-name rds-s3-export-role  ^
       --assume-role-policy-document '{
        "Version": "2012-10-17",
        "Statement": [
          {
            "Effect": "Allow",
            "Principal": {
               "Service": "rds.amazonaws.com"
             },
            "Action": "sts:AssumeRole",
            "Condition": {
                "StringEquals": {
                   "aws:SourceAccount": "111122223333",
                   "aws:SourceArn": "arn:aws:rds:us-east-1:111122223333:db:dbname"
                   }
                }
          }
        ] 
      }'

3. 将您创建的 IAM 策略附加到您创建的 IAM 角色。

   以下 AWS CLI 命令将以前创建的策略附加到一个名为 rds-s3-export-role. 的角色。请将 your-policy-arn 替换为您在前面的步骤中记下的策略 ARN。

   aws iam attach-role-policy  --policy-arn your-policy-arn  --role-name rds-s3-export-role  

4. 将该 IAM 角色添加到数据库实例中。您可以使用 AWS Management Console 或 AWS CLI 执行该操作，如下所述。

控制台

使用控制台为 PostgreSQL 数据库实例添加 IAM 角色

1. 登录 AWS Management Console 并通过以下网址打开 Amazon RDS 控制台：https://console.aws.amazon.com/rds/。

2. 选择 PostgreSQL 数据库实例名称以显示其详细信息。

3. 在 Connectivity & security (连接性和安全性) 选项卡上的 Manage IAM roles (管理 IAM 角色) 部分中，在 Add IAM roles to this instance (向此实例添加 IAM 角色) 下选择要添加的角色。

4. 在 Feature (功能) 下，选择 s3Export。

5. 选择 Add role (添加角色)。

AWS CLI

使用 CLI 为 PostgreSQL 数据库实例添加 IAM 角色

• 使用以下命令将角色添加到名为 my-db-instance 的 PostgreSQL 数据库实例中。将 your-role-arn 替换为您在上一步中记下的角色 ARN。使用 s3Export 作为 --feature-name 选项的值。

  例

  对于 Linux、macOS 或 Unix：

  aws rds add-role-to-db-instance \
     --db-instance-identifier my-db-instance \
     --feature-name s3Export \
     --role-arn your-role-arn   \
     --region your-region

  对于 Windows：

  aws rds add-role-to-db-instance ^
     --db-instance-identifier my-db-instance ^
     --feature-name s3Export ^
     --role-arn your-role-arn ^
     --region your-region     

使用 aws_s3.query_export_to_s3 函数导出查询数据

通过调用 aws_s3.query_export_to_s3 函数将 PostgreSQL 数据导出到 Amazon S3。

主题

• 先决条件
• 调用 aws_s3.query_export_to_s3
• 导出到使用自定义分隔符的 CSV 文件
• 导出到具有编码的二进制文件

先决条件

在使用 aws_s3.query_export_to_s3 函数之前，请确保完成以下先决条件：

• 安装所需的 PostgreSQL 扩展，如将数据导出到 Amazon S3 概述中所述。

• 确定要将数据导出到 Amazon S3 的位置，如指定要导出到的 Amazon S3 文件路径中所述。

• 确保数据库实例具有 设置 Amazon S3 存储桶的访问权限 中所述的对 Amazon S3 的访问权限。

以下示例使用一个称作 sample_table 的数据库表。这些示例将数据导出到一个称作 sample-bucket 的存储桶中。使用以下 SQL 语句在 psql 中创建示例表和数据。

psql=> CREATE TABLE sample_table (bid bigint PRIMARY KEY, name varchar(80));
psql=> INSERT INTO sample_table (bid,name) VALUES (1, 'Monday'), (2,'Tuesday'), (3, 'Wednesday');

调用 aws_s3.query_export_to_s3

下面说明了调用 aws_s3.query_export_to_s3 函数的基本方法。

这些示例使用变量 s3_uri_1 确定包含用于标识 Amazon S3 文件的信息的结构。使用 aws_commons.create_s3_uri 函数可创建结构。

psql=> SELECT aws_commons.create_s3_uri(
   'sample-bucket',
   'sample-filepath',
   'us-west-2'
) AS s3_uri_1 \gset

尽管以下两个 aws_s3.query_export_to_s3 函数调用的参数不同，但这些示例的结果是相同的。sample_table 表的所有行都将导出到一个称作 sample-bucket 的存储桶中。

psql=> SELECT * FROM aws_s3.query_export_to_s3('SELECT * FROM sample_table', :'s3_uri_1');

psql=> SELECT * FROM aws_s3.query_export_to_s3('SELECT * FROM sample_table', :'s3_uri_1', options :='format text');

参数如下所述：

• 'SELECT * FROM sample_table' – 第一个参数是包含 SQL 查询的必需文本字符串。PostgreSQL 引擎将运行此查询。查询的结果将复制到其他参数中标识的 S3 存储桶。

• :'s3_uri_1' – 此参数是标识 Amazon S3 文件的结构。此示例使用变量来标识先前创建的结构。相反，您可通过内联方式在 aws_commons.create_s3_uri 函数调用中包含 aws_s3.query_export_to_s3 函数调用来创建结构，如下所示。

  SELECT * from aws_s3.query_export_to_s3('select * from sample_table', 
     aws_commons.create_s3_uri('sample-bucket', 'sample-filepath', 'us-west-2') 
  );

• options :='format text' – options 参数是包含 PostgreSQL COPY 参数的可选文本字符串。复制过程使用 PostgreSQL COPY 命令的参数和格式。

如果指定的文件在 Amazon S3 存储桶中不存在，则会创建该文件。如果该文件已存在，则将覆盖该文件。以下是用于访问 Amazon S3 中的导出数据的语法。

s3-region://bucket-name[/path-prefix]/file-prefix

较大的导出将存储在多个文件中，每个文件的最大大小约为 6 GB。其他文件名具有相同的文件前缀，但追加了 _partXX。XX 表示 2，然后表示 3，依此类推。例如，假设您指定用于存储数据文件的路径，如下所示。

s3-us-west-2://my-bucket/my-prefix

如果导出必须创建三个数据文件，则 Amazon S3 存储桶将包含以下数据文件。

s3-us-west-2://my-bucket/my-prefix
s3-us-west-2://my-bucket/my-prefix_part2
s3-us-west-2://my-bucket/my-prefix_part3

有关此函数的完整参考以及其他调用方法，请参阅aws_s3.query_export_to_s3。有关访问 Amazon S3 中的文件的更多信息，请参阅 Amazon Simple Storage Service 用户指南中的查看对象。

导出到使用自定义分隔符的 CSV 文件

以下示例说明如何调用 aws_s3.query_export_to_s3 函数以将数据导出到使用自定义分隔符的文件。此示例使用 PostgreSQL COPY 命令的参数来指定逗号分隔值 (CSV) 格式和冒号 (:) 分隔符。

SELECT * from aws_s3.query_export_to_s3('select * from basic_test', :'s3_uri_1', options :='format csv, delimiter $$:$$');

导出到具有编码的二进制文件

以下示例说明如何调用 aws_s3.query_export_to_s3 函数以将数据导出到具有 Windows-1253 编码的二进制文件。

SELECT * from aws_s3.query_export_to_s3('select * from basic_test', :'s3_uri_1', options :='format binary, encoding WIN1253');

Amazon S3 访问故障排除

如果在尝试将数据导出到 Amazon S3 时遇到连接问题，请首先确认与数据库实例关联的 VPC 安全组的出站访问规则是否允许网络连接。具体而言，安全组必须有一条规则允许数据库实例将 TCP 流量发送到端口 443 和任何 IPv4 地址（0.0.0/0）。有关更多信息，请参阅通过创建安全组提供对 VPC 中的数据库实例的访问。

另请参阅以下建议：

• 对 Amazon RDS 身份和访问权限问题进行故障排除

• Amazon Simple Storage Service 用户指南中的排查 Amazon S3 的问题

• IAM 用户指南中的排查 Amazon S3 和 IAM 的问题

函数参考

函数

• aws_s3.query_export_to_s3
• aws_commons.create_s3_uri

aws_s3.query_export_to_s3

将 PostgreSQL 查询结果导出到 Amazon S3 存储桶。aws_s3 扩展提供 aws_s3.query_export_to_s3 函数。

两个必需的参数为 query 和 s3_info。这两个参数定义了要导出的查询，并标识了要导出到的 Amazon S3 存储桶。一个称作 options 的可选参数，用于定义各种导出参数。有关使用 aws_s3.query_export_to_s3 函数的示例，请参阅使用 aws_s3.query_export_to_s3 函数导出查询数据。

语法

aws_s3.query_export_to_s3(
    query text,    
    s3_info aws_commons._s3_uri_1,    
    options text
)

输入参数

query

一个必需的文本字符串，其中包含 PostgreSQL 引擎运行的 SQL 查询。此查询的结果将复制到 s3_info 参数中标识的 S3 存储桶。

s3_info

包含有关 S3 对象的以下信息的 aws_commons._s3_uri_1 复合类型：

• bucket – 要包含文件的 Amazon S3 存储桶的名称。

• file_path – Amazon S3 文件名和路径。

• region – 存储桶所在的AWS区域。有关 AWS 区域名称和关联值的列表，请参阅区域、可用区和 Local Zones。

  目前，此值必须是导出的数据库实例的同一个 AWS 区域。默认值为导出的数据库实例的 AWS 区域。

要创建 aws_commons._s3_uri_1 复合结构，请参阅 aws_commons.create_s3_uri 函数。

options

一个包含 PostgreSQL COPY 命令的参数的可选文本字符串。这些参数指定了在导出时复制数据的方式。有关更多详细信息，请参阅 PostgreSQL COPY 文档。

备用输入参数

为帮助进行测试，您可以使用一组扩展的参数而非 s3_info 参数。下面是 aws_s3.query_export_to_s3 函数的其他语法变化。

不使用 s3_info 参数来标识 Amazon S3 文件，而使用 bucket、file_path 和 region 参数的组合。

aws_s3.query_export_to_s3(
    query text,    
    bucket text,    
    file_path text,    
    region text,    
    options text
)

query

一个必需的文本字符串，其中包含 PostgreSQL 引擎运行的 SQL 查询。此查询的结果将复制到 s3_info 参数中标识的 S3 存储桶。

bucket

一个包含 Amazon S3 存储桶（其中包含文件）的名称的必需文本字符串。

file_path

包含 Amazon S3 文件名（包含文件路径）的必填文本字符串。

region

一个包含存储桶所在的 AWS 区域的可选文本字符串。有关 AWS 区域名称和关联值的列表，请参阅区域、可用区和 Local Zones。

目前，此值必须是导出的数据库实例的同一个 AWS 区域。默认值为导出的数据库实例的 AWS 区域。

options

一个包含 PostgreSQL COPY 命令的参数的可选文本字符串。这些参数指定了在导出时复制数据的方式。有关更多详细信息，请参阅 PostgreSQL COPY 文档。

输出参数

aws_s3.query_export_to_s3(
    OUT rows_uploaded bigint,
    OUT files_uploaded bigint,
    OUT bytes_uploaded bigint
)

rows_uploaded

针对给定查询成功上传到 Amazon S3 的表行的数目。

files_uploaded

已上传到 Amazon S3 的文件的数目。以约 6 GB 的大小创建文件。创建的每个附加文件的名称都追加了 _partXX。根据需要，XX 表示 2，然后表示 3，依此类推。

bytes_uploaded

已上传到 Amazon S3 的字节的总数。

示例

psql=> SELECT * from aws_s3.query_export_to_s3('select * from sample_table', 'sample-bucket', 'sample-filepath');
psql=> SELECT * from aws_s3.query_export_to_s3('select * from sample_table', 'sample-bucket', 'sample-filepath','us-west-2');
psql=> SELECT * from aws_s3.query_export_to_s3('select * from sample_table', 'sample-bucket', 'sample-filepath','us-west-2','format text');

aws_commons.create_s3_uri

创建 aws_commons._s3_uri_1 结构来保存 Amazon S3 文件信息。在 aws_commons.create_s3_uri 函数的 s3_info 参数中使用 aws_s3.query_export_to_s3 函数的结果。有关使用 aws_commons.create_s3_uri 函数的示例，请参阅指定要导出到的 Amazon S3 文件路径。

语法

aws_commons.create_s3_uri(
   bucket text,
   file_path text,
   region text
)

输入参数

bucket

包含文件的 Amazon S3 存储桶名称的必需文本字符串。

file_path

包含 Amazon S3 文件名（包含文件路径）的必填文本字符串。

region

一个包含文件所在的 AWS 区域的必需文本字符串。有关 AWS 区域名称和关联值的列表，请参阅区域、可用区和 Local Zones。