无法使用 aws_s3.query_export_to_s3 函数将 AWS RDS Postgres 表导出到 S3 中的 CSV [英] Unable to export AWS RDS Postgres table to CSV in S3, using aws_s3.query_export_to_s3 function
问题描述
我密切关注有关将 AWS RDS Postgres 表作为 CSV 导出到 S3 的文档,但仍然无法使其工作.文档网址
更具体地说,在创建 aws_s3
扩展后
如果不存在则创建扩展 aws_s3 CASCADE;
我试图执行这个函数:
SELECT * from aws_s3.query_export_to_s3('select * from users limit 10', 'sample-s3-bucket', '/users_demo.csv');
失败并出现以下错误:
<块引用>[42883] 错误:函数 aws_s3.query_export_to_s3(未知,未知,未知)不存在提示:没有函数匹配给定的名称和参数类型.您可能需要添加显式类型转换.
没有记录此错误(或者我找不到相关文档).扩展中似乎缺少 aws_s3.query_export_to_s3
函数!
我尝试查看此函数 aws_s3.query_export_to_s3
是否确实缺失.我已经试过了:
- 列出可用的扩展显示
aws_s3
esxtension 已安装:
SELECT * FROM pg_available_extensions where name like '%aw%';
结果:
名称 |default_version |安装版本|评论-------------+------------------+------+---------------------------------------------aws_s3 |1.0 |1.0 |用于从 S3 导入数据的 AWS S3 扩展aws_commons |1.0 |1.0 |跨 AWS 服务的通用数据类型(2 行)
- 列出扩展函数不会显示
query_export_to_s3
函数:
SELECT e.extname, ne.nspname AS extschema, p.proname, np.nspname AS proschemaFROM pg_catalog.pg_extension AS eINNER JOIN pg_catalog.pg_depend AS d ON (d.refobjid = e.oid)内部连接 pg_catalog.pg_proc AS p ON (p.oid = d.objid)INNER JOIN pg_catalog.pg_namespace AS ne ON (ne.oid = e.extnamespace)INNER JOIN pg_catalog.pg_namespace AS np ON (np.oid = p.pronamespace)WHERE d.deptype = 'e' AND e.extname like '%aws%'按 1、3 排序;
结果:
扩展名 |extschema |名誉|原图-------------+-----------+------------------------+------------aws_commons |公共|create_aws_credentials |aws_commonsaws_commons |公共|create_s3_uri |aws_commonsaws_s3 |公共|table_import_from_s3 |aws_s3aws_s3 |公共|table_import_from_s3 |aws_s3(4 行)
- 最后,删除并重新创建扩展 aws_s3 不起作用.
更多信息:PostgreSQL 12.3 on x86_64-pc-linux-gnu
.
任何人都可以确认这个功能实际上是缺失的吗?换句话说,任何人都可以使用这个功能吗?
尝试升级到 Postgres 12.4.我遇到了类似的问题,这就是 AWS 支持告诉我的(回复粘贴在下面).[已编辑]
更新
一开始我还没有完全搞定这个工作,但可以确认升级到 Postgres 12.4,并且删除并重新创建扩展工作.
DROP EXTENSION aws_s3 CASCADE;删除扩展 aws_commons 级联;创建扩展 aws_s3 级联;
AWS Support 的原始回复:
基于 describe-db-engine-versions[1] 的输出,我只能看到以下特定引擎版本支持 s3Export 功能.因此12.2 版本不支持导出到 S3 功能.
<预><代码>[{引擎":postgres",引擎版本":10.14",支持的功能名称":[s3Import",s3Export"]},{引擎":postgres",引擎版本":11.9",支持的功能名称":[s3Import",s3Export"]},{引擎":postgres",引擎版本":12.4",支持的功能名称":[s3Import",s3Export"]]]I followed closely the documentation regarding exporting AWS RDS Postgres tables to S3 as CSV and still could not make it work. Documentation URL
More specifically, after creating the aws_s3
extension
CREATE EXTENSION IF NOT EXISTS aws_s3 CASCADE;
I tried to execute this function:
SELECT * from aws_s3.query_export_to_s3('select * from users limit 10', 'sample-s3-bucket', '/users_demo.csv');
which failed with the following error:
[42883] ERROR: function aws_s3.query_export_to_s3(unknown, unknown, unknown) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts.
This error is not documented (or I could not find relevant documentation). It seems the aws_s3.query_export_to_s3
function is actually missing from the extension!
I tried to see if this function aws_s3.query_export_to_s3
is actually missing. I have already tried:
- Listing the available extensions shows
aws_s3
esxtension as installed:
SELECT * FROM pg_available_extensions where name like '%aw%';
Result:
name | default_version | installed_version | comment
-------------+-----------------+-------------------+---------------------------------------------
aws_s3 | 1.0 | 1.0 | AWS S3 extension for importing data from S3
aws_commons | 1.0 | 1.0 | Common data types across AWS services
(2 rows)
- Listing the extension functions does not show the
query_export_to_s3
function:
SELECT e.extname, ne.nspname AS extschema, p.proname, np.nspname AS proschema
FROM pg_catalog.pg_extension AS e
INNER JOIN pg_catalog.pg_depend AS d ON (d.refobjid = e.oid)
INNER JOIN pg_catalog.pg_proc AS p ON (p.oid = d.objid)
INNER JOIN pg_catalog.pg_namespace AS ne ON (ne.oid = e.extnamespace)
INNER JOIN pg_catalog.pg_namespace AS np ON (np.oid = p.pronamespace)
WHERE d.deptype = 'e' AND e.extname like '%aws%'
ORDER BY 1, 3;
Result:
extname | extschema | proname | proschema
-------------+-----------+------------------------+-------------
aws_commons | public | create_aws_credentials | aws_commons
aws_commons | public | create_s3_uri | aws_commons
aws_s3 | public | table_import_from_s3 | aws_s3
aws_s3 | public | table_import_from_s3 | aws_s3
(4 rows)
- Finally, Dropping and recreating the extension aws_s3 did not work.
More info: PostgreSQL 12.3 on x86_64-pc-linux-gnu
.
Can anyone please confirm that this function is actually missing? In other words, can anyone use this function?
Try upgrading to Postgres 12.4. I'm having a similar problem and that's what AWS support told me (response pasted below). [edited]
Update
Initially I hadn't fully got this working, but can confirm upgrading to Postgres 12.4, and dropping and recreating the extension worked.
DROP EXTENSION aws_s3 CASCADE;
DROP EXTENSION aws_commons CASCADE;
CREATE EXTENSION aws_s3 CASCADE;
Original response from AWS Support:
Based on output of describe-db-engine-versions[1] I can see that only the below specific engine versions support s3Export feature. Hence version 12.2 does not support export to S3 feature.
[
{
"Engine": "postgres",
"EngineVersion": "10.14",
"SupportedFeatureNames": [
"s3Import",
"s3Export"
]
},
{
"Engine": "postgres",
"EngineVersion": "11.9",
"SupportedFeatureNames": [
"s3Import",
"s3Export"
]
},
{
"Engine": "postgres",
"EngineVersion": "12.4",
"SupportedFeatureNames": [
"s3Import",
"s3Export"
]
} ]
这篇关于无法使用 aws_s3.query_export_to_s3 函数将 AWS RDS Postgres 表导出到 S3 中的 CSV的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!