PostgreSQL RDS Creation/Migration in AWS

This article describes about process to create a database from an existing one in AWS, we will cover the steps to migrate your schema and data from an existing database to the new one. You might encounter this process when there is a need for creating a RDS instance for different environments (System Testing, Integration Testing or Performance Testing) in your infrastructure or during database migration from one version to another. Since most of the organizations are moving into You Build You Own model relying on DBA team for each change request will not be required if we know few simple steps to manage database changes on our own. I am just writing this article as it might save some time on getting things done faster for what is needed to spin up your RDS instance with necessary schema and data in it.

In the said perspective this page will show you on “how to” part of it with PostgreSQL RDS using the utility tools psql and pgdump.

1. Setup PATH for psql & pgdump

psql and pgdump are utility tools that comes with PostgreSQL installation If you have already installed, the said executables can be found at a similar location in your system as below.

/Library/PostgreSQL/9.6/bin

Add the location to your existing path as below.

export PATH=/Library/PostgreSQL/9.6/bin/:$PATH;

2. Extract DDL & DML commands from Schema

Before performing any instructions given below make sure the port 5432 (default port for Postgres) from TCP is enabled in your RDS Instance Security Group for a specific IP range that will allow access to the RDS instance from your laptop. This check is required as you will perform all your create and insert statements from your machine.

The below command helps in generating DDL scripts based on existing schema available in your RDS instance that includes all configurations (like primary key, foreign key constraints)that exist in your old database will be made available in the generated file.

pg_dump -h <AWS Instance Name> -p <Port> -d <Database Name> -U <UserName> -s -F p -n <Schema Name> > <Give a File Name>

Second command below helps in generating the data dump from your old database by providing appropriate insert scripts.

pg_dump -h <AWS Instance Name> -p <Port> -d <Database Name> -U <UserName> -a -F p -n <Schema Name> — — inserts > <Give a File Name>

Executing Insert Scripts

You will be prompted for a password every time when you try to execute SQL scripts from your machine.

psql -f <SQL Script> -h <AWS DB Name> -p <Port> -d <Database Name> -U <UserName>

Once the SQL script execution completes you are all set to use your database.

If you just have one super user you can add new users using pgadmin tool under the “Login/Group Roles” start adding new users to your database with limited privileges and use it in your application.

The above method can be used during database migration from one version to other (say 9.6 to 9.8). Only changes those scripts generated might require is if any data types are not supported as part of the new version will require additional review to make it work before running it on the new version of DB.