CODEWITHSUNDEEP
postgresqlamazon-web-servicesamazon-s3export-to-csv
gnu_horns
gnu_horns

Reputation: 43

Unable to export AWS RDS Postgres table to CSV in S3, using aws_s3.query_export_to_s3 function

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:

  1. 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)
  1. 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)
  1. 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?

Upvotes: 4

Views: 7086

Answers (2)

Michaël Vaes
Michaël Vaes

Reputation: 81

Upgrading the RDS instance to 12.4 and executing the following worked for me.

DROP EXTENSION aws_s3;
DROP EXTENSION aws_commons;
CREATE EXTENSION aws_s3 CASCADE;

Upvotes: 2

credator
credator

Reputation: 56

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"
        ]
    } ]

Upvotes: 4

Related Questions