将数据从 Aurora PostgreSQL 数据库集群导出到 Amazon S3 - Amazon Aurora
AWS 文档中描述的 AWS 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 AWS 服务入门

将数据从 Aurora PostgreSQL 数据库集群导出到 Amazon S3

您可以从 Aurora PostgreSQL 数据库集群中查询数据,并将数据直接导出到存储在 Amazon S3 存储桶中的文件中。为此,您可以使用 Aurora PostgreSQL 提供的 aws_s3 PostgreSQL 扩展。

注意

要将数据从 Aurora PostgreSQL 导出到 Amazon S3,您的数据库必须运行下列 PostgreSQL 引擎版本之一:

  • PostgreSQL 版本 10.11 或更高版本

  • PostgreSQL 版本 11.6 或更高版本

有关使用 Amazon S3 存储数据的更多信息,请参阅 Amazon Simple Storage Service 入门指南 中的创建存储桶

将数据导出到 Amazon S3 概述

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

Aurora PostgreSQL 数据导出到 S3

  1. 安装所需的 PostgreSQL 扩展。这些包括 aws_s3aws_commons 扩展。为此,请启动 psql 并使用以下命令。

    CREATE EXTENSION IF NOT EXISTS aws_s3 CASCADE;

    aws_s3 扩展提供用于将数据导出到 Amazon S3 的 aws_s3.export_query_to_s3 函数。包含 aws_commons 扩展以提供额外的帮助程序函数。

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

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

    要将数据导出到 Amazon S3 文件,请为 Aurora PostgreSQL 数据库集群提供权限以访问导出将用于存储的 Amazon S3 存储桶。此操作包括以下步骤:

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

    2. 创建一个 IAM 角色。

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

    4. 将此 IAM 角色添加到数据库集群。

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

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

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

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

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

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

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

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

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

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

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

  • AWS 区域(可选)– Amazon S3 存储桶所在的 AWS 区域。如果您未指定 AWS 区域值,则 Aurora 会将文件保存到导出的数据库集群所在的同一 AWS 区域中的 Amazon S3 中。

    注意

    目前,AWS 区域必须与导出的数据库集群的区域相同。

    有关 AWS 区域名称和关联值的列表,请参阅 区域和可用区

要保存有关导出的存储位置的 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

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

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

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

为此,请使用以下过程。

通过 IAM 角色向 Aurora PostgreSQL 数据库集群授予对 Amazon S3 的访问权限

  1. 创建一个 IAM 策略。

    该策略提供存储桶和对象权限,以允许 Aurora PostgreSQL 数据库集群访问 Amazon S3。

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

    1. 在策略中包含以下必需操作,以允许将文件从 Aurora PostgreSQL 集群传输到 Amazon S3 存储桶:

      • s3:PutObject

      • s3:AbortMultipartUpload

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

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

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

    警告

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

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

    在您创建策略之后,请记下策略的 Amazon 资源名称 (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" ], "Effect": "Allow", "Resource": [ "arn:aws:s3:::your-s3-bucket/*" ] } ] }'
  2. 创建一个 IAM 角色。

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

    以下示例说明了如何使用 AWS CLI 命令创建一个名为 rds-s3-export-role 的角色。

    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" } ] }'
  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 管理控制台 或 AWS CLI 执行该操作,如下所述。

使用控制台为 PostgreSQL 数据库集群添加 IAM 角色

  1. 通过以下网址登录 AWS 管理控制台并打开 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 (添加角色)

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

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

    对于 Linux、macOS 或 Unix:

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

    对于 Windows:

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

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

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

先决条件

在使用 aws_s3.export_query_to_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.export_query_to_s3

下面说明了调用 aws_s3.export_query_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_s3.query_export_to_s3 函数调用中包含 aws_commons.create_s3_uri 函数调用来创建结构,如下所示。

    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。其他文件名具有相同的文件前缀,但追加了 _partXXXX 表示 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.export_query_to_s3。有关访问 Amazon S3 中的文件的更多信息,请参阅 Amazon Simple Storage Service 入门指南 中的查看对象

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

以下示例说明如何调用 aws_s3.export_query_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.export_query_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 时遇到连接问题,请参阅以下内容以了解相应的建议:

函数引用

aws_s3.export_query_to_s3

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

两个必需的参数为 querys3_info。这两个参数定义了要导出的查询,并标识了要导出到的 Amazon S3 存储桶。一个称作 options 的可选参数,用于定义各种导出参数。有关使用 aws_s3.export_query_to_s3 函数的示例,请参阅使用 aws_s3.export_query_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 区域名称和关联值的列表,请参阅 区域和可用区

    目前,此值必须是导出的数据库集群的 AWS 区域。默认值为导出的数据库集群的 AWS 区域。

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

选项

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

备用输入参数

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

不使用 s3_info 参数来标识 Amazon S3 文件,而使用 bucketfile_pathregion 参数的组合。

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

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

存储桶

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

file_path

包含文件的 Amazon S3 路径的必需文本字符串。

区域

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

目前,此值必须是导出的数据库集群的 AWS 区域。默认值为导出的数据库集群的 AWS 区域。

选项

一个包含 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_s3.export_query_to_s3 函数的 s3_info 参数中使用 aws_commons.create_s3_uri 函数的结果。有关使用 aws_commons.create_s3_uri 函数的示例,请参阅指定要导出到的 Amazon S3 文件路径

语法

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

输入参数

存储桶

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

file_path

包含文件的 Amazon S3 路径的必需文本字符串。

区域

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