Migrating DigitalOcean database to AWS

Francesco Ciulla - Oct 14 '21 - - Dev Community

Video Version: https://youtu.be/3zLWCNn0Vqk

In this article, we’ll look at how to migrate an existing Postgres database on DigitalOcean created through their "managed database" function to a Relational Database Service (RDS) instance on AWS.

For an introduction to RDS, you can read my previous article on migrating a local database to RDS, or watch the video.

Prerequisites:

  • Managed database on DigitalOcean with some data in it
  • AWS account
  • (optional) TablePlus or any other tool to manage a Postgresql DB

Steps:

  • Check DigitalOcean and download the connection certificate
  • Install TablePlus (GUI tool for managing relational databases)
  • Check DigitalOcean database
  • Create RDS instance
  • Test empty RDS instance
  • Backup DigitalOcean DB
  • Restore RDS database
  • Final Test

Check DigitalOcean and Download CA certificate

First, let's visit our DigitalOcean account’s Database page. We should see something like this:

image.png

Download the CA certificate locally. We need this because managed databases on DigitalOcean don't allow insecure connections.

image.png

TablePlus

To access the DB, we can use whatever tool we want (command line interface, Pgadmin, etc.). In this demo, we will use TablePlus (available on Mac/Windows), so if you want to follow along exactly I suggest you download it. We’ll use the free version.

image.png

Check DigitalOcean Database

Let's create a new connection on TablePlus:

image.png

Add the details for your DigitalOcean database:

  • Host
  • Port
  • Username
  • Password
  • Database name
  • SSL mode: REQUIRED

Remember also to add the certificate we just downloaded.

image.png

Click Connect and you will see the database with your data. In this case, we have just 2 tables and 3 inserts.

image.png

Create RDS Instance

Go on AWS Console and search "RDS"

image.png

Click Create Database.

image.png

Select Postgres and version 12, so we will have access to a free Tier (read the conditions before accepting).

image.png

Choose a name for the database, as well as a username and password to access the DB.

image.png

Make the instance accessible from the Internet:

image.png

Double-check that this is a Free Tier (with limitations- please read them)Then, click Create Database.

This will take a few minutes to complete.

image.png

Let's check that our security group is configured correctly. Our machine should have access to the instance:

image.png

Specifically, check if the inbound rules are set properly. In our case, they’re as follows:

image.png

Test Empty RDS Instance

Now let's test connecting to the RDS instance using TablePlus:

image.png

Click Connect. As you can see, the DB is empty for now:

image.png

Backup DigitalOcean DB

Now let’s use Tableplus to make a backup of the DigitalOcean database:

image.png

Choose a folder and save the file called defaultdb.dump (it will have the name of your database):

image.png

If you see this, it worked:

image.png

Restore RDS Database

To restore the database, click Restore.

image.png

Select the aws database. Then, select the postgres database and click Start restore:

image.png

Select the dump file, in our case defaultdb.dump:

image.png

Final Test

As a final test, let's access the RDS database again:

image.png

And here we can see our tables and inserts again:

image.png

And we’re done!

Video Version: https://youtu.be/3zLWCNn0Vqk

. . . . . . . . . . . . . . . . .