How to export and import data to PostgreSQL from S3
If you are working with PostgreSQL and S3 on your cloud project, most probably, there is a need to export and import between these services. In general, there are different libraries that are used for import and export features. In this blog, I am going to explain one of the popular and performant library that export and import data to PostgreSQL from S3.Let’s investigate aws_s3 extension;
aws_s3 extension
aws_s3 extension is a useful library that imports data from S3 to PostgreSQL and exports data from PostgreSQL to S3. When I test this library with millions of records, it is very fast if you compare it with other libraries. I would like to show you two steps to use that library
Let’s take a look at how to use it
Step 1 — Extension installation to database
As a first step, we need to install the extension to the PostgreSQL database
Run the following command in the database
CREATE EXTENSION aws_s3 CASCADE;
The extension is installed in the database. We are able to use aws_s3 extension, let’s take a look at how to use export/import functionality
Step 2— Running the query to export
Execute the following query
As you see, it is just a SQL script for export. You can execute this script from the database or Lambda function. Let’s tackle the key functionalities of the SQL script
- aws_s3.query_export_to_s3 → the library that you use to export from the database and store to S3
- sample_table → the table that is going to be exported
- my-bucket → S3 bucket that the data will be stored
- folder → the folder under the bucket
- eu-central-1 → S3 region
- format CSV, delimiter $$|$$ → file format and delimiter to separate the columns
Congrats! You export data and it took only two steps in terms of implementation
As you see, exporting and importing data from/to S3 and PostgreSQL is very efficient with aws_s3 extension library. You can also see the significant performance improvement when using aws_s3. AWS also provide some useful content that you can see below.
Useful links
