How to move your Postgres database from Heroku to AWS RDS [Detailed Guide]

This blog post will show you how to move your existing Postgres database on Heroku to AWS. At DealDrop we began moving our application from Heroku to AWS recently and couldn't find anywhere with all of this information in one place, so I'm hoping this article will make the process more straight forward for someone else.

This post will assume that you've already have a Postgres database setup on Heroku. Alright, let's get started.

We'll start by getting some details from Heroku first, then we can create your Postgres RDS database in AWS. One very important thing to note is where your current postgres database in Heroku is located because we want to create the database in the same region on AWS to reduce the latency.

Open up your terminal and run: heroku pg:info --app appname
Note: Your app name is the name of your application, not the name of your postgres add-on. Also, it will only show the region if you're on the Standard0 plan or above. The default region is US.

=== DATABASE_URL
Plan: Standard 0
Status: Available
Data Size: 8.07 MB
Tables: 0
PG Version: 12.2
Connections: 7/120
Connection Pooling: Available
Credentials: 1
Fork/Follow: Available
Rollback: earliest from 2020-02-14 04:10 UTC
Created: 2020-02-14 04:05 UTC
Region: us
Data Encryption: In Use
Continuous Protection: On
Maintenance: not required
Maintenance window: Mondays 19:00 to 23:00 UTC
Add-on: postgresql-horizontal-20822

Heroku & underlying AWS Regions

| ID        | Location                | Runtime        | AWS            |
|-----------|-------------------------|----------------|----------------|
| eu        | Europe                  | Common Runtime | eu-west-1      |
| us        | United States           | Common Runtime | us-east-1      |
| dublin    | Dublin, Ireland         | Private Spaces | eu-west-1      |
| frankfurt | Frankfurt, Germany      | Private Spaces | eu-central-1   |
| oregon    | Oregon, United States   | Private Spaces | us-west-2      |
| sydney    | Sydney, Australia       | Private Spaces | ap-southeast-2 |
| tokyo     | Tokyo, Japan            | Private Spaces | ap-northeast-1 |
| virginia  | Virginia, United States | Private Spaces | us-east-1      |

AWS Configurations

Now over to AWS.

1. On the top right, select the region that matches your Heroku region.
2. Now head over to the RDS page
3. Click on Create Database and fill out the form
   1. Creation Method - Standard Create
   2. Engine Options - PostgreSQL
   3. Template - Production
   4. Settings:
      1. DB Identifier - Can be anything you want
      2. Username - Can be anything you want
      3. Password - Set a password as we'll use this in the restore process later
   5. DB instance size - This defaults to db.m5.xlarge - which is really big, so make sure you select the correct size for your needs
   6. Storage - Use the defaults
   7. Availability & durability - Use defaults
   8. Connectivity - Let's create a new VPC
      1. We'll also create a new DB subnet group as well
      2. Make sure you mark 'publicly accessible' to Yes
      3. Create a new VPC Security Group as well
   9. Database Authentication - Select Password Authentication
   10. Additional Configuration:
       1. Initial Database Name - Make sure you give it a name, otherwise RDS does not create a database
       2. The rest of this section can be left to the default selections
   11. Click "Create database" and wait for it to create!

Let's test it now

-> brew install telnet
-> telnet database-1.ceyahmuzqcx4.us-east-1.rds.amazonaws.com 5432
=> Connected to ec2-3-90-123-123.compute-1.amazonaws.com

// It's working!!

This will ping your database to see if it can be publicly accessible. You can get this endpoint by clicking into your database instance.

Updating your Security Group settings:

1. Head over to your VPC dashboard and on the left hand side, click into Security Groups and select the group you created during the database creation process
2. Click into Inbound Rules - You should see 1 rule there for PostgreSQL - TCP - 5432 - Your IP address. This means only your IP address can connect to this any instances with this security group on port 5432, which is why our telnet command worked)
3. Let's add in another rule:
   • Type: PostgreSQL
   • Protocol: TCP
   • Port: 5432
   • Source: Anywhere - 0.0.0.0/0, ::/0
     • Yes, I know what you're thinking... unsafe much? Well this is actually the method recommended by Heroku. More on the later.

Alright, we're done with the AWS side, let's jump over to Heroku.

Backup old DB & restore to new DB (estimated downtime: 15-60 minutes - depends on how large your DB is)

1. If you're running an application that gets a lot of writes, its best to turn on maintenance mode in Heroku (add image here) to avoid losing data during the transfer
   • If you cannot afford to have any down time, it might be best to use AWS DMS (Database Migration Service) - https://aws.amazon.com/dms/
2. Create a manual back up (in durability tab) and download the file
   • Rename this file to lastest.dump (Makes the next step easier)
3. pg_restore --verbose --clean --no-acl --no-owner -h database-identifier.abcdefghi.us-east-1.rds.amazonaws.com -U username -d databasename latest.dump
4. Once the restore is complete, you can point your application to this new database, and it should be good to go!

One last thing...

Since our database is accepting requests from anywhere, we'll need to make sure its secure. Heroku recommends forcing your postgres database to only accept SSL connections. Let's quickly head back to your AWS RDS dashboard to do this.

1. On the left hand side, select "Parameter groups"
2. Parameter group family: postgres11
   • Group name: postgres11-SSL (Can call it whatever you want)
   • Description: "Force SSL connections only"
3. Click into the postgres11-SSL group you just created. We'll need to make sure that RDS only accepts SSL-encrypted connections. By default, the rds.force_ssl parameter is set to 0. You'll need to set this to 1.
4. Head back into your database settings and update the DB parameter group to use the new group you just created. And you're done!

Using your new database

You'll need to download and use the Amazon RDS CA certificate when make the connection to your database.

curl https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem > amazon-rds-ca-cert.pem

Depending on what application framework you're using, your configurations will be different. Here's an example of how we're using it with Django.

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql_psycopg2",
        "NAME": "databasename",
        "USER": "postgres",
        "PASSWORD": "mypassword",
        "HOST": "database-1.cey4hmu1234.us-east-1.rds.amazonaws.com",
        "PORT": "5432",
        "OPTIONS": {
            "sslmode": "verify-ca",
            "sslrootcert": os.path.join(BASE_DIR, "amazon-rds-ca-cert.pem")
        }
    }
}

Read more on this here:

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.SSL

https://devcenter.heroku.com/articles/amazon-rds

Thanks for reading! If you have any questions, issues, feedback or corrections, please let me know!

Comments

[Andrew May]

The idea of a database being publicly accessible sends shivers down my spine.

If the application is also moving to AWS then I would recommend putting the database in private subnets so that it can never be public (and also locking down your security group so that it's only accessible to the security group of the application that will be using it). If you're dumping the data to a file you can scp it up to a bastion host in your VPC to load it to the private cluster (obviously this assumes downtime).

If you must have the database public for some time, you can always snapshot it after the data load and use the snapshot to create a new private instance in different subnets after the load is completed.

Setting SSL does certainly help, but I'm paranoid enough to not trust that by itself.

[Adam Mcquistan]

Unfortunately when doing a Heroku to RDS migration you cannot use DMS because it needs to have elevated permissions that Heroku does not give you