Heroku -

Database Upgrades

Upgrading the database plan: This also upgrades PostgreSQL version.

heroku addons:create heroku-postgresql:standard-0   # create a new database
heroku pg:wait

heroku maintenance:on                               # prohibit writing
# heroku ps:scale worker=0                          # if you have workers

heroku pg:copy DATABASE_URL HEROKU_POSTGRESQL_ROSE  # copy data from the old
heroku pg:promote HEROKU_POSTGRESQL_ROSE            # assign as primary

# heroku ps:scale worker=<PREVIOUS_NUMBER>          # if you had workers
heroku maintenance:off

heroku addons:destroy HEROKU_POSTGRESQL_LAVENDER    # remove the old DB

heroku pg:copy \
    source-application::OLIVE \
    HEROKU_POSTGRESQL_PINK -a target-application

Upgrading a big database: If you have hundreds of GBs of data and can't go to maintenance mode for the duration of the copy command, you can use followers.

# create follower
heroku addons:create heroku-postgresql:standard-2 \
    --follow HEROKU_POSTGRESQL_LAVENDER_URL \
    --app sushi
heroku pg:wait
heroku pg:info                      # check when follower is behind 300 commits

heroku maintenance:on               # prohibit writing
# heroku ps:scale worker=0          # if you have workers
heroku pg:info                      # check when follower is behind by 0 commits

heroku pg:unfollow HEROKU_POSTGRESQL_WHITE_URL   # allow writing and don't copy
heroku pg:promote HEROKU_POSTGRESQL_WHITE        # make primary database
heroku pg:info                                   # shows primary DB

# heroku ps:scale worker=<PREVIOUS_NUMBER>       # if you had workers
heroku maintenance:off

heroku addons:destroy HEROKU_POSTGRESQL_LAVENDER # remove the old DB

Follower changeover does not upgrade PostgreSQL version, but you can do it manually before promote.

heroku pg:upgrade HEROKU_POSTGRESQL_WHITE
heroku pg:wait