Skip to content

Instantly share code, notes, and snippets.

@simonw

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

Last active May 30, 2025 04:49
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.
Download ZIP

Revisions

  1. Simon Willison revised this gist Apr 11, 2018. 1 changed file with 17 additions and 0 deletions.
    17 changes: 17 additions & 0 deletions how-to-upgrade-heroku-postgresql.md
    View file
    Original file line number Diff line number Diff line change
    @@ -22,3 +22,20 @@ 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
  2. Simon Willison created this gist Apr 11, 2018.
    24 changes: 24 additions & 0 deletions how-to-upgrade-heroku-postgresql.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,24 @@
    # 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