In this article, we’ll see how to migrate your local Postgres database to AWS RDS.

Video Version:https://youtu.be/87G3iUl-tj0

Just a couple of definitions before we start:

Postgres (PostgreSQL): a free open-source relational database very popular and stable.

Amazon Relational Database Service (RDS): service to set up, operate, and scale a relational database in the cloud. Amazon RDS supports several database instance types:

Amazon Aurora
PostgreSQL
MySQL
MariaDB
Oracle Database
SQL Server

In this article, we’ll see how to migrate our local Postgres database using the AWS Database Migration Service to easily migrate or replicate your existing databases to Amazon RDS.

Video version:

Usually what we want is a local database to test in the initial phase. But then, after a while, we want to bring it to the cloud - for example, onto AWS.

This is the full procedure and it's not strictly related to Docker (though it’ll help to have Docker installed locally on your machine):

Clone the repository
Run the local instance of Postgres using docker compose
Make some inserts in the database using Postman
Make a backup of the database using Docker and psql
Create an instance of RDS Postgres in AWS
Restore the Postgres database using the AWS CLI
Final check

Clone the repository

To do this, we will use a project that we used in a previous article. (Video version)

This repository is based on the official TinyStacks AWS Docker Express application repository on GitHub https://github.com/tinystacks/aws-docker-templates-express.

To clone the repository, open a command prompt and type:



git clone https://github.com/FrancescoXX/crud-api-node-postgres

And then change folders:



cd crud-api-node-postgres

Open this folder with your favorite IDE. If you have Visual Studio Code you can use the command:



code .

Run the services using docker compose

Before starting our services, let's take a look at the docker-compose.yml file we used in the previous article. (If you want a more detailed explanation about these commands, you can check out my previous video.)



version: '3.8'

services: 
  node_backend:
    container_name: node_backend
    image: node-test:1.0.0
    build: 
      context: .
    ports: 
      - '8080:80'
    environment:
      - PGUSER=francesco
      - PGPASSWORD=12345
      - PGDATABASE=defaultdb
      - PGHOST=db
    depends_on: 
      - db  
  db:
    container_name: db
    image: 'postgres:12'
    ports: 
      - 5432:5432
    environment:
      - POSTGRES_USER=francesco
      - POSTGRES_PASSWORD=12345
      - POSTGRES_DB=defaultdb
    volumes: 
      - pgdata1:/var/lib/postgresql/data

volumes:
  pgdata1: {}

We have 2 services, and the volume called pgdata1, where we will store our data.

Let's run the database service with this command:



docker compose up -d db

And then the node backend service:



docker compose up -d node_backend

Let's check the running containers with this command:



docker ps -a

Now we’re ready to make our inserts into the database.

Database Inserts Using Postman

You can make the inserts in the database in different ways. In this article, we will use Postman, a tool to test API.

Check if the backend is up and running by creating a new [GET] request at the /ping endpoint, port 8080:

Check the current users bycreating a new [GET] request at the /users endpoint, port 8080. The response will be a blank response body ([]) with status 200, which is what we expect:

Create three new users on this database witha new [POST] request at the /users endpoint, port 8080:

If we check the users again via the /users, we will see the three new users.

Now we are ready to create the backup of the database using the pg_dump command.

Database Dump with pg_dump

pg_dump is a utility for backing up a Postgres database. It makes consistent backups even if the database is being used concurrently.

pg_dump does not block other users accessing the database.

The good news is that we can use docker exec to dump the database:

Run this command:



docker exec db pg_dump -U francesco defaultdb > backup.sql

That was easy!

We are now ready to move on the AWS UI to create an RDS instance.

Create RDS Instance on AWS (UI)

Let's create the AWS RDS instance.

On the AWS UI console, search for RDS:

Click on Create Database:

Here, unsurprisingly, you can choose a database. Choose Postgres, version 12.5 (the same used locally and it has also a Free tier on AWS, which is nice!) and Free Tier.

In the Settings section, you can choose a name for the database, a username, and a password:

Under Connectivity, select Yes for Public Access (it’s set to No by default) so we can access to it from our local AWS CLI.

You also need a VPC with Security group open to the public. To learn more about security groups, check out the AWS documentation.

Leave everything else as it is, and click Create Database. (Please notice that since we are on a free tier, we will not be charged.)

Database create will usually take a couple of minutes.

if you click on the database name, you will have a summary of it:

Now that we have the running postgres instance running on AWS, we can use the psql command to restore the database.

Database Migration using Docker and psql

First of all, let's make a connection test to the aws Postgres database:



docker run -it --rm postgres psql --host database-2.c9nq6suhmqou.us-east-1.rds.amazonaws.com --port 5432 --username postgres

To check if it's working we can type the following command command (don't forget the ; at the end!):



select 1;

This means that the remote postgres instance is accessible from our local machine!

Let's exit the current psql process with the command:

\q

Now, for ease of use, navigate into the folder where the backup.sql file is located. In our case, it was the same folder where we installed our service:

Database Migration

Now comes the interesting and most important part - the migration to AWS:

to do this,



psql -f backup.sql --host database-2.c9nq6suhmqou.us-east-1.rds.amazonaws.com --port 5432 --username postgres

Final Test

Now let's get back to our remote instance:

There it is! We have migrated our local database onto an RDS instance!