The following are the steps I have taken:
Followed the guide to create and connect PostgreSQL Database > https://aws.amazon.com/getting-started/tutorials/create-connect-postgresql-db/ > this was successful.
Followed
- Install the required PostgreSQL extensions. These include the aws_s3 and aws_commons extensions.
CREATE EXTENSION aws_s3 CASCADE; - Identify the database table and Amazon S3 file to use.
CREATE TABLE t1 (col1 varchar(80), col2 varchar(80), col3 varchar(80)); - Use the aws_commons.create_s3_uri function to create an aws_commons._s3_uri_1 structure to hold the Amazon S3 file information.
SELECT aws_commons.create_s3_uri( 'random', 'test.csv', 'us-east-1' ) AS s3_uri
Error: schema "aws_commons does not exist"
When I run the following code to identify extensions: Select * from pg_available_extensions where name like '%aws%'
| name | installed version | comment |
|---|---|---|
| aws_commons | 1.1 | Common data types across AWS services |
| aws_s3 | 1.1 | AWS S3 extension for importing data from s3 |
4.IAM
- Created an IAM policy that provides access to the Amazon S3 bucket(Service:S3, Access level:List,read,write,tagging,permission management).
- Created an IAM role and attached the policy to the role.
- Then assigned the IAM role to the DB instance.
Error: schema "aws_commons" does not exist