将数据从 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 不支持这些步骤。
所有当前可用的 RDS for PostgreSQL 版本都支持将数据导出到 Amazon Simple Storage Service。有关详细版本信息,请参阅《Amazon RDS for PostgreSQL 版本注释》https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-versions.html中的 Amazon RDS for PostgreSQL 更新。
如果您没有为导出设置桶,请参阅《Amazon Simple Storage Service 用户指南》中的以下主题。
预设情况下,上载到 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
扩展
使用 psql(或 pgAdmin)以具有
rds_superuser
权限的用户身份,连接到 RDS for PostgreSQL 数据库实例。如果您在设置过程中保留原定设置名称,则以postgres
进行连接。psql --host=
111122223333
.aws-region
.rds.amazonaws.com --port=5432 --username=postgres --password要安装扩展,请运行以下命令。
postgres=>
CREATE EXTENSION aws_s3 CASCADE;NOTICE: installing required extension "aws_commons" CREATE EXTENSION
要验证扩展是否已安装,可以使用 psql
\dx
元命令。postgres=>
\dxList 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
-
标识要用于导出数据的 Amazon S3 文件路径。有关此过程的详细信息,请参阅指定要导出到的 Amazon S3 文件路径。
-
提供权限以访问 Amazon S3 存储桶。
要将数据导出到 Amazon S3 文件,请为 RDS for PostgreSQL 数据库实例提供权限以访问导出将用于存储的 Amazon S3 存储桶。此操作包括以下步骤:
-
创建一个 IAM 策略,该策略提供对要导出到的 Amazon S3 存储桶的访问权限。
-
创建一个 IAM 角色。
-
将您创建的策略附在您创建的角色上。
-
将此 IAM 角色添加到数据库实例。
有关此过程的详细信息,请参阅设置 Amazon S3 存储桶的访问权限。
-
-
标识数据库查询以获取数据。通过调用
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。其他文件名具有相同的文件前缀,但追加了
_part
。XX
表示 2,然后表示 3,依此类推。XX
例如,具有
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 的权限
-
创建一个 IAM policy。
该策略提供存储桶和对象权限,以允许 PostgreSQL 数据库实例访问 Amazon S3。
在创建此策略的过程中,请执行以下步骤:
-
在策略中包含以下必需操作,以允许将文件从 PostgreSQL 数据库实例传输到 Amazon S3 存储桶:
-
s3:PutObject
-
s3:AbortMultipartUpload
-
-
包含用于标识 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" ], "Effect": "Allow", "Resource": [ "arn:aws:s3:::
your-s3-bucket
/*" ] } ] }' -
-
创建一个 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
" } } } ] }' -
将您创建的 IAM 策略附加到您创建的 IAM 角色。
以下 AWS CLI 命令将以前创建的策略附加到一个名为
rds-s3-export-role.
的角色。请将
替换为您在前面的步骤中记下的策略 ARN。your-policy-arn
aws iam attach-role-policy --policy-arn
your-policy-arn
--role-name rds-s3-export-role -
将该 IAM 角色添加到数据库实例中。您可以使用 AWS Management Console 或 AWS CLI 执行该操作,如下所述。
使用控制台为 PostgreSQL 数据库实例添加 IAM 角色
登录AWS Management Console并通过以下网址打开 Amazon RDS 控制台:https://console.aws.amazon.com/rds/
。 -
选择 PostgreSQL 数据库实例名称以显示其详细信息。
-
在 Connectivity & security (连接性和安全性) 选项卡上的 Manage IAM roles (管理 IAM 角色) 部分中,在 Add IAM roles to this instance (向此实例添加 IAM 角色) 下选择要添加的角色。
-
在 Feature (功能) 下,选择 s3Export。
-
选择 Add role (添加角色)。
使用 CLI 为 PostgreSQL 数据库实例添加 IAM 角色
-
使用以下命令将角色添加到名为
my-db-instance
的 PostgreSQL 数据库实例中。将
替换为您在上一步中记下的角色 ARN。使用your-role-arn
s3Export
作为--feature-name
选项的值。例
对于 Linux、macOS 或 Unix:
aws rds add-role-to-db-instance \ --db-instance-identifier
my-db-instance
\ --feature-name s3Export \ --role-arnyour-role-arn
\ --regionyour-region
对于 Windows:
aws rds add-role-to-db-instance ^ --db-instance-identifier
my-db-instance
^ --feature-name s3Export ^ --role-arnyour-role-arn
^ --regionyour-region
使用 aws_s3.query_export_to_s3 函数导出查询数据
通过调用 aws_s3.query_export_to_s3 函数将 PostgreSQL 数据导出到 Amazon S3。
先决条件
在使用 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
参数是包含 PostgreSQLCOPY
参数的可选文本字符串。复制过程使用 PostgreSQL COPY命令的参数和格式。
如果指定的文件在 Amazon S3 存储桶中不存在,则会创建该文件。如果该文件已存在,则将覆盖该文件。以下是用于访问 Amazon S3 中的导出数据的语法。
s3-
region
://bucket-name
[/path-prefix
]/file-prefix
较大的导出将存储在多个文件中,每个文件的最大大小约为 6 GB。其他文件名具有相同的文件前缀,但追加了 _part
。XX
表示 2,然后表示 3,依此类推。例如,假设您指定用于存储数据文件的路径,如下所示。XX
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
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/0)。有关更多信息,请参阅通过创建安全组提供对 VPC 中的数据库实例的访问。
另请参阅以下建议:
-
Amazon Simple Storage Service 用户指南中的排查 Amazon S3 的问题
-
IAM 用户指南中的排查 Amazon S3 和 IAM 的问题
函数参考
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 的大小创建文件。创建的每个附加文件的名称都追加了
_part
。根据需要,XX
表示 2,然后表示 3,依此类推。XX
- 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。