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:
# (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
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
Thank you! Just ran some tests and it looks like it works great.
We are frustrated by the lack of postgres v12 or v13. We know you are shooting for June or July but we can’t wait that long. Is there another path to v12? (We don’t want to fall back to Heroku but…)
Hi @Will_Kessler, I understand your frustration. We will let you know as soon as we have newer versions of postgres available in early access.
The only expedited path to v12 would be to self-manage postgres on Render as a private Docker service with an attached persistent disk. It’s not nearly as convenient as our managed postgres offering, but you would have more freedom in the way you configure and operate your database.
For those who are on Postgres v11, will there be a path to migrate to v12 once it’s available?
@MikeWilson Yes, there will be a migration path. We’re still working out the specifics, but our goal is to make it as easy as possible to upgrade.
I’m trying to do the same thing but coming from PG v10.16 on Heroku. Does that change the instructions at all?
I run PG 11 in dev and have it installed locally so I think my app’s already compatible with 11.
In this setup should I go with the Docker approach using v10 or its it okay to use my local PG v11?
I would have considered upgrading in place to v11 on Heroku first Upgrading the Version of a Heroku Postgres Database | Heroku Dev Center
but I don’t have a great way to shut off writes (especially since Render doesn’t have a heroku maintenance:on) and I’d like to avoid a 2nd downtime if the upgrade step seems unnecessary.
Also I got 100 errors in my first restore attempt using pg_restore. The errors may all be harmless. Some related to dropping DEFAULT ids.
When I tried with --if-exists then it dropped down to 6. Stuff related to plpgsql, pg_stat_statements, etc. I think also harmless.
No reason not to use --if-exists, right?
Hi Richard,
I run PG 11 in dev and have it installed locally so I think my app’s already compatible with 11.
In this setup should I go with the Docker approach using v10 or its it okay to use my local PG v11?
Using pg v11 should be fine since it seems like your app is already working on another pg11 database. Though, it may be worth doing a dry run with v11 to see if there are some corner cases that show up only during a migration.
When I tried with --if-exists then it dropped down to 6. Stuff related to plpgsql, pg_stat_statements, etc. I think also harmless.
Those should be fine to ignore.