AWS RDS Postgres Export to S3: 5 Easy Steps

Amazon Relational Database Service (RDS) allows users to set up and operate a relational database in the AWS cloud and is one of the most popular web services in the market. It can manage common database administration tasks seamlessly and provides resizable and cost-efficient capacity for an enterprise-grade relational database. Amazon RDS supports multiple versions of PostgreSQL. It allows you to create DB snapshots and instances, backups, and point-in-time restores. The DB instances that run PostgreSQL also support read replicas and provisioned IOPS, which can be easily created inside a VPC.

In this article, you will get a brief overview of AWS RDS for PostgreSQL and Amazon S3 before diving deep into the steps involved in AWS RDS Postgres Export to S3.

What is AWS RDS for PostgreSQL?

PostgreSQL has become the go-to Open-Source relational database for various startups and enterprise developers, primarily used to power mobile applications and leading businesses. With Amazon RDS you can set up, scale, and operate PostgreSQL deployments in the Cloud with considerable ease. Amazon RDS allows you to utilize scalable PostgreSQL deployments with resizable and cost-efficient hardware capacity in no-time flat.

It also manages time-consuming and complex administrative tasks like PostgreSQL software upgrades, backups for disaster recovery, replication for high availability and read throughput, and storage management. Amazon RDS for PostgreSQL allows you to leverage all functionalities of the PostgreSQL database engine like its applications, codes, and tools. 

Here are a few benefits of using AWS RDS for PostgreSQL:

• Predictable and Fast Storage: Amazon RDS offers two SSD-backed storage options for the user’s PostgreSQL database. The general purpose storage is aimed at providing cost-effective storage for medium-sized or small workloads. It also offers Provisioned IOPS that can deliver consistent performance up to 40,00 IOs per second for high-performance OLTP applications. Amazon RDS lets you provision additional storage on the fly keeping your growing storage requirements in mind. 
• Easy and Managed Deployments: You can launch and connect to a production-ready PostgreSQL database in minutes. Amazon RDS for PostgreSQL DB instances is pre-configured with parameters and settings for the server type you choose. These parameters offer fine-tuning and granular control of your PostgreSQL database. 
• Monitoring and Metrics: Amazon RDS for PostgreSQL offers Amazon CloudWatch metrics to go with your DB instances at no additional cost. You also get the Amazon RDS Enhanced Monitoring functionality that provides access to over 50 file systems, CPU, and disk I/O metrics. You can also monitor the key operational metrics through the AWS Management Console.    
• Isolation and Security: Amazon RDS guarantees a high level of security for your PostgreSQL databases through network isolation leveraging Amazon’s Virtual Private Cloud (VPC), encryption of data in transit using SSL, and encryption at rest using keys you can create and control through AWS Key Management Service (KMS). 
• High Availability and Read Replicas: You get enhanced durability and availability for your PostgreSQL databases through Amazon RDS Multi-AZ deployments. This makes them a natural fit for production database workloads. Amazon RDS Read Replicas make it exceptionally easy to elastically scale out beyond the capacity constraints of a single DB instance. You can do this for read-heavy database workloads.  
• Backup and Recovery: Amazon RDS offers an automated backup feature that enables the recovery of your PostgreSQL database instance to any point in time. This can be done within your specified retention period of up to 35 days. You can also carry out user-initiated backups of your DB instance. These full database backups will be stored in Amazon RDS until deleted manually.   

What is Amazon S3?

Amazon Simple Storage Service (S3) works as an object storage offering that supports industry-grade data availability, scalability, security, and performance. Customers of all sizes can leverage it to store and protect their data for various use cases such as mobile applications, websites, Big Data Analytics, IoT devices, and data lakes to name a few.

It also offers easy-to-use management features so you can organize your data and configure finely-tuned access controls to meet your specific business needs and compliance requirements.

Here are a few benefits of Amazon S3:

• Cost-Effective Storage Classes: You can save costs without sacrificing performance by storing data across the S3 Storage Classes, which provides support for different data access levels at corresponding cost-effective rates. S3 Storage Class Analysis can be used to discover data that should be moved to a lower-cost storage class based on access patterns.
• Highly Supported Cloud Storage Service: You can store and protect your data in Amazon S3 by working with a partner from the AWS Partner Network, the largest community of technology and consulting cloud service providers. It recognizes the migration partners that transfer data to Amazon S3 and storage partners that offer integrated solutions for primary storage, backups, archives, and disaster recovery. 
• Data and Access Controls: Amazon S3 offers robust capabilities to manage access, cost, data replication, and replication. With Amazon S3 Access Points you can easily manage data access with specific permissions for your applications using a shared data set. Amazon S3 Replication helps you manage data replication within the region or to other regions. 

Understanding the Steps in AWS RDS Postgres Export to S3

As discussed before, Amazon RDS supports DB instances running multiple versions of PostgreSQL. Amazon RDS for PostgreSQL is compliant with various industry standards as well. Amazon RDS also provides access to databases on a DB instance using any standard SQL client. 

The following sections cover the 5 steps involved in AWS RDS Postgres Export to S3 in great detail.

• AWS RDS Postgres Export to S3: Exporting Data to Amazon S3
• AWS RDS Postgres Export to S3: PostgreSQL Version Verification
• AWS RDS Postgres Export to S3: Amazon S3 File Path Specification
• AWS RDS Postgres Export to S3: Configuring Access to Amazon S3 Bucket
• AWS RDS Postgres Export to S3: Exporting Query Data
• AWS RDS Postgres Export to S3: Troubleshooting access to Amazon S3

AWS RDS Postgres Export to S3: Exporting Data to Amazon S3

• To start the process of AWS RDS Postgres Export to S3 you need to export the data stored in an RDS instance for your PostgreSQL database to an Amazon S3 bucket, you first need to ensure that your RDS instance version for PostgreSQL supports Amazon S3 exports. Next, you need to install the required PostgreSQL extensions namely aws_s3 and aws_commons. Start psql and type in the following command:

CREATE EXTENSION IF NOT EXISTS aws_s3 CASCADE;

• The aws_s3 extension provides the aws_s3.query_export_to_s3 function that can be used to export data to Amazon S3. The aws_commons extension provides additional helper functions to the user for data export.
• Identify a database query to obtain the data, and export the query data by calling the aws_s3.query_export_to_s3 function. Once you are done with the preceding preparation tasks, you can use the aws_s3.query_export_to_s3 function to export query results to Amazon S3.  

AWS RDS Postgres Export to S3: PostgreSQL Version Verification

• Amazon S3 exports are currently supported for PostgreSQL 10.14, 11.9, 12.4, and later. You can verify the support by using the following command:

describe-db-engine-versions

• If the output includes the string “s3Export”, it means that the RDS engine supports Amazon S3 exports.  

AWS RDS Postgres Export to S3: Amazon S3 File Path Specification

• You need to specify the bucket name and the file path to identify the location in Amazon S3 where you want to export your data.
• The file path consists of an optional path prefix that identifies a virtual folder path and a file prefix that identifies the files to be stored. Large exports are stored in multiple files, each with a maximum size of approximately 6 GB.
• To hold the information about where the Amazon S3 File exports are to be stored, you can use the aws_commons._s3_uri function that can be used to create an aws_commons._s3_uri_1 composite structure as follows:

psql=> SELECT aws_commons.create_s3_uri(
   'sample-bucket',
   'sample-filepath',
   'us-west-2'
) AS s3_uri_1 gset

• This s3_uri_1 value can later be used as a parameter in the call to the aws_s3.query_export_to_s3 function.

AWS RDS Postgres Export to S3: Configuring Access to Amazon S3 Bucket

• To begin the data export to Amazon S3, you need to give your PostgreSQL DB instance relevant permissions to access the Amazon S3 bucket that will be containing the files.
• Identify the Amazon S3 path you wish to use for exporting data, and provide the necessary permissions to access the Amazon S3 bucket. This involves creating an IAM policy that provides access to the desired Amazon S3 bucket, creating an IAM role, attaching the policy to the role created, and finally adding the role to your DB instance.
• Once you have created the IAM policy, you need to note down the ARN (Amazon Resource Name) of the policy that will be needed when you attach the policy to an IAM role. Here’s the code snippet to simplify the creation of an IAM policy:

aws iam create-policy  --policy-name rds-s3-export-policy  --policy-document '{
     "Version": "2012-10-17",
     "Statement": [
       {
         "Sid": "s3export",
         "Action": [
           "S3:PutObject"
         ],
         "Effect": "Allow",
         "Resource": [
           "arn:aws:s3:::your-s3-bucket/*"
         ] 
       }
     ] 
   }'

• You can use the following AWS CLI command to create a role named rds-s3-export-role:

aws iam create-role  --role-name rds-s3-export-role  --assume-role-policy-document '{
     "Version": "2012-10-17",
     "Statement": [
       {
         "Effect": "Allow",
         "Principal": {
            "Service": "rds.amazonaws.com"
          },
         "Action": "sts:AssumeRole"
       }
     ] 
   }'

• The following AWS CLI command can be used to attach the policy to the IAM role created earlier:

aws iam attach-role-policy  --policy-arn your-policy-arn  --role-name rds-s3-export-role

• Finally, you need to add the IAM role to the DB instance. This can be carried out through the AWS Management Console or AWS CLI. If you decide to use the AWS Management Console for this step, sign in to the Console and choose the PostgreSQL DB instance name to display the details to start. 
• Go to the Connectivity and Security tab, in the Manage IAM roles section, choose the role you wish to add under the Add IAM roles to this instance section. Go to Feature and choose s3Export. Choose Add Role to finish the step.  

AWS RDS Postgres Export to S3: Exporting Query Data

• The last step in the process of AWS RDS Postgres Export to S3 is calling the aws_s3.query_export_to_s3 function.

This function requires two parameters, namely query and s3_info. The first one defines the query to be exported and verifies the Amazon S3 bucket to export to. You can also provide an optional parameter called options for defining various export parameters. To call it, you can use the following syntax:

aws_s3.query_export_to_s3(
    query text,    
    s3_info aws_commons._s3_uri_1,    
    options text
)

The aws_commons._s3_uri_1 composite type in the above syntax contains the following details about your S3 object:

• bucket: The name of the Amazon S3 bucket to contain the file.
• file_path: The Amazon S3 file name and path.
• region: The AWS Region that the bucket is in.

To build the aws_commons._s3_uri_1 structure for holding Amazon S3 file information, you can use the following syntax:

aws_commons.create_s3_uri(
   bucket text,
   file_path text,
   region text
)

• For this example, you can use the variable s3_uri_1 to identify a structure that has the information to identify the Amazon S3 file. You can use the aws_commons.create_s3_uri function to create the desired structure as follows:

psql=> SELECT aws_commons.create_s3_uri(
   'sample-bucket',
   'sample-filepath',
   'us-west-2'
) AS s3_uri_1 gset

AWS RDS Postgres Export to S3: Troubleshooting access to Amazon S3

You may face a few connectivity issues when trying to AWS RDS Postgres Export to S3. First make sure, that the VPC security group’s outbound access rules associated with your DB instance allow network connectivity. In particular, you must allow access to port 443 for SSL connections.

Conclusion

This article talks about the steps involved in setting up AWS RDS Postgres Export to S3 in great detail after a brief overview of AWS RDS for PostgreSQL and Amazon S3. Extracting complex data from a diverse set of data sources can be challenging, and this is where Hevo saves the day!

Visit our Website to Explore Hevo

Hevo offers a faster way to move data from Amazon S3, AWS RDS Postgres, and other Databases or SaaS applications into your desired destination to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code. Hevo, with its strong integration with 150+ sources and BI tools(Including 40+ Free Sources), allows you to not only export & load data but also transform & enrich your data & make it analysis-ready in a jiffy.

Want to take Hevo for a spin? Sign Up for a 14-day free trial. You can also look at the unbeatable pricing that will help you choose the right plan for your business needs.