Migrating PostgreSQL from Heroku to Render

Over the past couple of weeks we tested Render as a candidate to replace Heroku. We have been very happy so far and want to migrate as soon as possible. We are now wondering what the recommended way to migrate is, as the Heroku database runs on PostgreSQL 12.5 and the Render one onPostgreSQL 11.7. Obviously we want to keep the downtime as low as possible.

Thank you for your help.

Hi Lucas,

Thank you for the kind words about Render.

I’ve outlined the steps to migrate your postgres (12.5) db from heroku to Render’s (11.7) below:

Download database backup from Heroku

# (Optional) Turn maintenence mode on to prevent new things from being written to the database during migration
heroku maintenance:on --app APP 

# To download a backup from heroku. Will be saved as latest.dump
heroku pg:backups:capture --app APP
heroku pg:backups:download --app APP

Restore on Render

Pre-requisite Create a database on Render

# Restore if you have the same version (PG 12) locally
# substitute USER and DATABASE_NAME with values from the Render dashboard
pg_restore --verbose --clean --no-acl --no-owner -h postgres.render.com -U USER -d DATABASE_NAME latest.dump

###### OR if you don't have Postgres 12 locally ######

# This step uses a Docker image if you don't have Postgres 12 installed locally
# You will likely need to replace (pwd) with $(pwd) if you're using bash
# instead of fish
docker run -v `pwd`:/root -it postgres:12 bash

# Now go to the right directory and run the upload command

cd /root && pg_restore --verbose --clean --no-acl --no-owner -h postgres.render.com -U USER -d DATABASE_NAME latest.dump

I was able to use the above procedure to migrate a test database running on heroku (postgres 12.5) to Render ( postgres 11.7). However, migrating across major versions can have some unique corner cases so I’d recommend doing a test walkthrough of the process before you turn maintenance mode on ( and incur downtime).

Support for postgres 12 is in the works. If you’d like to be notified about our progress on that feature you can upvote it here

2 Likes

Thank you! Just ran some tests and it looks like it works great.

1 Like