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
@levarberry
Copy link

You should teach Heroku how to write documents. Their own documentation made no sense. This is straight to the point.

@armenarmen
Copy link

I'm with @levarberry on that

@skalibog
Copy link

Agree with @levarberry

@rakib06
Copy link

rakib06 commented Jun 21, 2020

Thank you so much

@anirudhsama
Copy link

Very useful, thanks!

@Antoine-lb
Copy link

+1, @levarberry agree

@fakefarm
Copy link

LIKE.A.GLOVE 💯

@galbrecht18
Copy link

Mucho thanks for this. Agreed with all on this thread, Heroku's documentation sucked on this. You'd think they'd want to make it very clear and visible for something that makes them more money.

@sgino209
Copy link

Thank you very much, fully agree with levarberry...

@JoeyBodnar
Copy link

The hero we need.

@jonsinclair1
Copy link

Amazing. Thank you

@takaria0
Copy link

takaria0 commented Apr 5, 2021

Super nice, thank you so much.

@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