ijrodrigues/how-to-upgrade-heroku-postgresql.md

## how-to-upgrade-heroku-postgresql.md

      
    Raw
  

              how-to-upgrade-heroku-postgresql.md
            
          
    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