PostgreSQL Trigger to S3

Setup

terraform init terraform apply -auto-approve
  • this_rds_cluster_endpoint
  • this_rds_cluster_master_password
  • this_s3_db_bucket_id
./add_role_to_db_cluster.sh

Connecting to the DB

CREATE EXTENSION IF NOT EXISTS aws_s3 CASCADE; CREATE TABLE sample_table (bid bigint PRIMARY KEY, name varchar(80));

Output to S3

INSERT INTO sample_table (bid,name) 
VALUES (1, 'Monday'), (2,'Tuesday'), (3, 'Wednesday');
SELECT * FROM aws_s3.query_export_to_s3( 
'select * from sample_table',
aws_commons.create_s3_uri(
'S3_BUCKET_NAME',
'test.csv',
'us-east-1'),
options :='format csv, delimiter $$,$$' );
Query 1 ERROR: ERROR: credentials stored with the database cluster can't be accessed 
HINT: Has the IAM role Amazon Resource Name (ARN) been associated with the feature-name "s3Export"?
CONTEXT: SQL function "query_export_to_s3" statement 1
  • Rows Updated
  • Files Uploaded
  • Bytes Uploaded
aws s3api list-objects --bucket S3_BUCKET_NAME --query 'Contents[].Key'
aws s3api get-object --bucket terraform-20200801185401781600000002 --key test.csv output.csv && cat output.csv
CREATE OR REPLACE FUNCTION export_to_s3()
RETURNS TRIGGER
AS $export_to_s3$
BEGIN
PERFORM aws_s3.query_export_to_s3(
'select * from sample_table',
aws_commons.create_s3_uri(
'S3_BUCKET_NAME',
'test.csv',
'us-east-1'),
options :='format csv, delimiter $$,$$'
);
RETURN NEW;
END;
$export_to_s3$ LANGUAGE plpgsql;
CREATE TRIGGER sample_table_trg
AFTER INSERT OR DELETE OR UPDATE OR TRUNCATE
ON sample_table
FOR EACH STATEMENT
EXECUTE PROCEDURE export_to_s3();

Testing

INSERT INTO sample_table (bid,name) VALUES (4, 'Thursday');
aws s3api get-object --bucket terraform-20200801185401781600000002 --key test.csv output.csv && cat output.csv

Shutdown

terraform destroy -auto-approve

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
JD

JD

Drink to Code and Code to Drink