如何将数据文件从 s3 导入 postgresql rds
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20257226/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to import data files from s3 to postgresql rds
提问by user3044239
I am very new to AWS, and Postgresql.
我对 AWS 和 Postgresql 非常陌生。
- I have created a Postgresql db (using rds on was)
- I have uploaded several documents to multiple s3 buckets
- I have a EC2 (Amazon Linux 64 bit) running
- 我创建了一个 Postgresql 数据库（使用 rds on was）
- 我已将多个文档上传到多个 s3 存储桶
- 我有一个 EC2（Amazon Linux 64 位）正在运行
I tried to use a data pipeline, but nothing seems to be available (template) for Postgres. I can't figure out how to connect to my RDS instance and import/export data from postgres.
我尝试使用数据管道，但 Postgres 似乎没有可用的（模板）。我不知道如何连接到我的 RDS 实例并从 postgres 导入/导出数据。
I assumed that I could use EC2 to grab from my S3 bucket and import into Postgres in lieu of no data pipeline template being available. If it is possible I have no idea how.. Please advise if possible..
我假设我可以使用 EC2 从我的 S3 存储桶中抓取并导入 Postgres，而不是没有可用的数据管道模板。如果可能的话，我不知道如何.. 如果可能，请告知..
回答by jcz
I wish AWS extends COPY command in RDS Postgresql as they did in Redshift. But for now they haven't and we have to do it by ourselves.
我希望 AWS 在 RDS Postgresql 中扩展 COPY 命令，就像他们在 Redshift 中所做的那样。但现在他们还没有，我们必须自己做。
- Install
awsclion your EC2 box (it might have been installed by default) - Configure your
awscliwith credentials - Use
aws s3 syncoraws s3 cpcommmands to download from s3 to your local directory - Use psql command to
\COPYthe files into your RDS (requires\to copy from client directory)
- 安装
awscli在您的 EC2 机器上（默认情况下可能已安装） awscli使用凭据配置您的- 使用
aws s3 sync或aws s3 cp命令从 s3 下载到本地目录 - 使用 psql 命令
\COPY将文件放入 RDS（需要\从客户端目录复制）
Example:
例子：
aws s3 cp s3://bucket/file.csv /mydirectory/file.csv
psql -h your_rds.amazonaws.com -U username -d dbname -c '\COPY table FROM ''file.csv'' CSV HEADER'
回答by quiver
S3 -> RDS direct load is now possible for PostgreSQL Aurora and RDS PostgreSQL >= 11.1 as aws_s3extension.
S3 -> RDS 直接加载现在可以用于 PostgreSQL Aurora 和 RDS PostgreSQL >= 11.1 作为aws_s3扩展。
- Amazon Aurora with PostgreSQL Compatibility Supports Data Import from Amazon S3
- Amazon RDS for PostgreSQL Now Supports Data Import from Amazon S3
Parameters are similar to those of PostgreSQL COPYcommand
参数与PostgreSQL COPY命令类似
psql=> SELECT aws_s3.table_import_from_s3(
'table_name', '', '(format csv)',
'BUCKET_NAME', 'path/to/object', 'us-east-2'
);
Be warned that this feature does not work for older versions.
请注意，此功能不适用于旧版本。
回答by Chris Johnson
The prior answers have been superseded by more recent events at AWS.
之前的答案已被 AWS 上最近发生的事件所取代。
There is now excellent support for S3-to-RDS-database loading via the Data Pipeline service (which can be used for many other data conversion tasks too, this is just one example).
现在通过 Data Pipeline 服务对 S3 到 RDS 数据库加载提供了出色的支持（该服务也可用于许多其他数据转换任务，这只是一个示例）。
This AWS article is for S3-to-RDS-MySQL. Should be very similar for RDS-Postgres.
这篇 AWS 文章适用于 S3 到 RDS-MySQL。对于 RDS-Postgres 应该非常相似。
http://docs.aws.amazon.com/datapipeline/latest/DeveloperGuide/dp-template-copys3tords.html
http://docs.aws.amazon.com/datapipeline/latest/DeveloperGuide/dp-template-copys3tords.html
回答by donnoyet
if you can launch the psql client and connect to RDS on EC2 instance, you should be able to use the following command:
如果您可以启动 psql 客户端并连接到 EC2 实例上的 RDS，您应该可以使用以下命令：
\copy customer_orders from 'myfile.csv' with DELIMITER ','
\从'myfile.csv' 复制customer_orders 与DELIMITER ','
