Skip to content

Instantly share code, notes, and snippets.

@simonw
Last active April 29, 2024 05:07
Show Gist options
  • Save simonw/50e14b9a3e829355d6d43f0f12f91e74 to your computer and use it in GitHub Desktop.
Save simonw/50e14b9a3e829355d6d43f0f12f91e74 to your computer and use it in GitHub Desktop.
How to upgrade a Heroku PostgreSQL database to a new plan

How to upgrade a Heroku PostgreSQL database to a new plan

I started a project on a Hobby Dev plan (free, limit 10,000 rows), and then later needed to upgrade it to Hobby Basic ($9/month, limit 10,000,000 rows).

After assigning the new database, I had two databases attached to the application. They looked something like this:

  • HEROKU_POSTGRESQL_OLIVE (postgresql-dimensional-3321) Old, free-tier (Hobby Dev) database
  • HEROKU_POSTGRESQL_COPPER (postgresql-perpendicular-6628) New Hobby Basic, $9/month database

Here's how I ran the upgrade:

heroku maintenance:on
heroku pg:copy DATABASE_URL HEROKU_POSTGRESQL_COPPER_URL
heroku pg:promote HEROKU_POSTGRESQL_COPPER
heroku maintenance:off

The pg:promote command set the DATABASE_URL environment variable to the new database.

More instructions: https://devcenter.heroku.com/articles/upgrading-heroku-postgres-databases#upgrade-with-pg-copy-default

Setting up backups

I set up nightly backups of the database using the following command:

heroku pg:backups schedule DATABASE_URL --at '02:00 America/Los_Angeles'

To list available backus, use:

heroku pg:backups

To fetch the most recent backup and restore it into a local Postgres:

curl -o latest.dump `heroku pg:backups public-url`
createdb mynewdb
pg_restore --verbose --clean --no-acl --no-owner \
    -h localhost -U simonw -d mynewdb latest.dump
@a1iraxa
Copy link

a1iraxa commented Jun 16, 2021

Heroku should learn amazing skills from you.

@skyforest
Copy link

🤗 for this

@peternixey
Copy link

Thanks Simon - very helpful. One thing to add is that if you have multiple apps you will also need to include the app name:

heroku maintenance:on -a yourappname
heroku pg:copy DATABASE_URL HEROKU_POSTGRESQL_COPPER_URL -a yourappname
heroku pg:promote HEROKU_POSTGRESQL_COPPER -a yourappname
heroku maintenance:off -a yourappname

@amman20
Copy link

amman20 commented Jun 28, 2022

Many Thanks @simonw & @peternixey for sharing this

@lastnamehurt
Copy link

Thanks all. Heroku was great until I needed to rely on their docs for a damn db upgrade! How in the hell do they send an email stating I need to upgrade asap without comprehensive docs on how to do so!?

@Innarticles
Copy link

Still my go-to place for PG upgrades!

@jdowning
Copy link

jdowning commented Nov 7, 2022

As of today, you can upgrade these databases with one command:

heroku addons:upgrade DATABASE_URL heroku-postgresql:mini --app myapp

This will change the plan but not require any maintenance or data migration. This only works for "in kind" Essential-tier database plan changes (hobby-dev, hobby-basic, mini, basic). If you want to change plans to another, larger plan, you'll need to use Simon's instructions. 💜

@ranmax123
Copy link

HEROKU_POSTGRESQL_COPPER_URL -> what is this for? I created a Python django heroku app and a postgre db addon in another app. But this HEROKU_POSTGRESQL_COPPER_URL came? Can I delete this? Otherwise, I have to pay for two databases

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment