Connecting PostgreSQL RDS instance to S3
This method can be used to load a large amount of data into an RDS instance running on cloud.gov and utilizes an AWS PostgreSQL S3 extension in the process described in the AWS documentation for Importing data from S3 into RDS for PostgreSQL.
This process, outlined in the AWS documentation, will work with your cloud.gov PostgreSQL database and S3 buckets, given the following:
- You have created a PostgreSQL RDS database with the aws-rds broker in your space.
- You have created an S3 bucket with the s3-broker service in your space.
- You have created a service-key and retrieve the credentials for your S3 bucket.
AWS S3 Extension
First you must install the AWS S3 extension.
You will need to access the PostgreSQL database on the cli using one of the methods detailed here.
Once you have installed the AWS S3 extension, you then need to gather information to provide for the PostgreSQL RDS to S3 connection function which consist of:
The name of the table on your RDS for PostgreSQL DB instance
This is the table into which the
aws_s3.table_import_from_s3function is to import the data.
- The S3 bucket name
- The S3 file path
The S3 file type
These can be obtained from the service key for your S3 service. To get the credentials such as the bucket name, region and file type, these can be obtained from the service key, you can run
cf service-key <SERVICE_INSTANCE_NAME> <KEY_NAME>; you will see a JSON description of the credentials. Treat these values as sensitive!
The AWS Region: us-gov-west-1
- S3 Bucket Access Key Id and Secret obtained in your S3 service-key credentials
You will then set up permissions on your RDS for PostgreSQL DB instance to allow access to the file on the Amazon S3 bucket using the previously obtained credentials.
Finally you should be able to import the data into the table you have selected by issuing the
aws_s3.table_import_from_s3 function as described here.