Transferring a Postgresql Database from AWS RDS to Heroku

I recently had the need to move a database from one service to another. After quite a bit of trial and error, I ended up discovering this answer:

1. Create a SQL file with pg_dump

Collect information on your existing database in RDS. You'll need your host, name, user, and password. Then from a command line interface use

$ PGPASSWORD=<database_password> pg_dump -Fc --no-acl --no-owner -h <database_host> -U <database_user> -d <database_name> -f <output_file_name>

Be sure to replace the <> values with your data.

• <database_password>: The password to your database. For me, this was a randomly generated 64-digit hexadecimal code.
• <database_host>: this looks like a subdomain URL. On RDS it usually takes the pattern:
  [database-name].[random_hash].[region].rds.amazonaws.com
• <database_user>: The username for the database. For me, this was a randomly generated string of 14 letters.
• <database_name>: The name of the database on the host. I named mine postgres at the time.
• <output_file_name>: The .sql file you'll be generating. Name it something you'll remember, you'll need to use it later. Mine was database_dump.sql

You'll want the -Fc, --no-acl, and --no-owner flags to make sure the export can be imported into another database. Otherwise, the import will look for the same host, username, password, and database name and prevent uploads.

The -Fc, --no-acl, and --no-owner flags are typically used to ensure that the dump is in a format that's suitable for creating backups and transferring them between databases. Let's clarify their purposes:

• -Fc: This flag specifies the custom format for the dump. It's a binary format that includes the necessary metadata to be recognized as a valid PostgreSQL backup.
• --no-acl: This flag tells pg_dump to exclude access privileges (ACLs) from the dump. This can be important when restoring to a different database to avoid conflicts with existing user access.
• --no-owner: This flag instructs pg_dump not to include ownership information for objects in the dump. It's often used to make the dump more portable, so it can be restored to databases with different users and roles.

These flags are useful when creating backups that you intend to transfer between different PostgreSQL databases or when you want to create a backup that doesn't carry over specific access rights. However, whether you need these flags depends on your specific use case and requirements.

ChatGPT 3.5

2. Move the SQL file to a web-accessible URL

I used the AWS CLI interface and an existing S3 bucket I use to temporarily store the SQL file in a way that can be accessed from a URL.

$ aws s3 cp <output_file_name> s3://<bucket_name>/[directories/]

• <output_file_name>: The name of your SQL file created in step 1.
• <bucket_name>: The name of your S3 bucket.
• [directories/]: Optional. A path of directories to put the file. (I didn't do this.)

The upload will take time relative to the size of the database.

3. Create temporary read permissions

If you used S3 in step 2, you may need to create temporary credentials to access that file from the web.

$ aws s3 presign s3://<bucket_name>/[directories/]<output_file_name>

This will output a very long URL with permissions that only last a short while. We'll call that output <signed_url>

4. Provision a new Database on Heroku

If you haven't yet, you'll need to provision a new PostgreSQL database on Heroku. Here's the CLI for doing that.

$ heroku addons:create heroku-postgresql:mini [--app <app_name>]

If you have more than one Heroku app, like a staging and production server, you may need to specify which app you want to provision your new database on.

You can see all the options for Heroku Postgres database plans & pricing here.

Either way, you'll need to get the name of your new Heroku database. Once the provisioning is complete, you'll see your CLI output has a new name:

HEROKU_POSTGRESQL_<new_name>_URL

Keep an eye out for that <new_name>. You'll need it for step 5. It's usually a color, like RED or COPPER.

5. Restore the backup

This step is considered destructive. You will be asked to type in the app name as a confirmation tool unless you add the --confirm flag.

$ heroku pg:backups:restore “<signed_url>” <new_name> [--app <app_name>] [—confirm <app_name>]

• <signed_url>: The URL from step 3. It needs to be in either ' ' or " " depending on the operating system. The URL uses & and ? symbols which can interfere with the CLI.
• <new_name>: The name of the Database that was created in step 4.
• [--app <app_name>] This flag is necessary if your Heroku CLI has more than one app.
• This step is considered destructive. You will be asked to type in the app name as a confirmation tool unless you add the --confirm flag.

6. Cleanup

Depending on your setup, you'll have to do a few things to make sure everything is in place and back to normal.

Maybe you need to delete the old AWS RDB database.

Maybe you need to update Heroku Config Vars to promote the new database as default.

Whatever your system setup, it's ready for you to do the final step.