Migrate local database on Docker container to AWS
Published: 2021-09-07 12:56:29

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](https://www.postgresql.org/) (PostgreSQL)**: a free open-source relational database very popular and stable. 

[Amazon Relational Database Service (RDS)](https://aws.amazon.com/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):

1. Clone the repository
2. Run the local instance of Postgres using docker compose
3. Make some inserts in the database using [Postman](https://www.postman.com/)
4. Make a backup of the database using Docker and [psql](https://www.postgresql.org/docs/13/app-psql.html)
5. Create an instance of RDS Postgres in AWS 
6. Restore the Postgres database using the AWS CLI 
7. Final check

## Clone the repository

To do this, we will use [a project that we used in a previous article](https://blog.tinystacks.com/how-to-create-an-express-app-with-a-local-postgres-database-and-docker-compose). ([Video version](https://www.youtube.com/watch?v=qczbRQtmCDo&t=1s))





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:

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

And then change folders:

```console
cd crud-api-node-postgres
```
Open this folder with your favorite IDE. If you have Visual Studio Code you can  use the command:

```console
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](https://www.youtube.com/watch?v=qczbRQtmCDo&t=1s).) 

```
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:

```console
docker compose up -d db
```

And then the node backend service:

```console
docker compose up -d node_backend
```

Let's check the running containers with this command:

```console
docker ps -a
```

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1630322706637/ym6cmxEQp.png)

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:

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1630322238642/pRcecMnco.png)

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: 

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1630322689159/IB9V2MR_f.png)

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

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1630323151927/LAEMCARpn.png)

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1630323475825/_Sdi8cB8Q.png)

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1630323413514/-JHdDLlr9.png)

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

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1630323570682/rozB-f2_N.png)

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:

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1630608373520/vXGTQ6Mum.png)

Click on **Create Database**:

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1630608396732/WA7JcYguL.png)

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**.

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1630611598254/mVWYoIsbe.png)


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

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1630611118985/pKxQwL9uG.png)


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](https://docs.aws.amazon.com/vpc/latest/userguide/VPC_SecurityGroups.html).


![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1630611507045/gdysvJ9fY.png)

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.)

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1630611787567/Bj4uIFfmY.png)



Database create will usually take a couple of minutes.

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1630611923218/g1Yq9dqNP.png)

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

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1630612160446/-7FWUPfTE.png)

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
```

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1630612354323/Zc-u6BDwb.png)

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

```console
select 1;
```

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1630612401475/iCfEF11Oz.png)

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:

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1630615314447/OlZW2ifRd.png)

### 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
```

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1630615612908/c61ta1tYK.png)

### Final Test

Now let's get back to our remote instance:

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1630615763163/kXSx012kP.png)

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

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

If you want to know more and be updated on TinyStacks news, [check out our Web site](https://www.tinystacks.com)!