Upgrading a Heroku PostgreSQL database with pg#

Figured this out in https://github.com/simonw/simonwillisonblog/issues/132 - I was running PostgreSQL 9.x and I wanted 11.x in order to use the new search_type="websearch" option in Django 3.1.

I followed the steps in Upgrading with pg (I don’t have any database followers so the pg:upgrade route wasn’t open to me).

Because I’m the only user who writes to my database I skipped the maintenance mode bit.

Step one: provision a new DB#

1
$ heroku addons:create heroku-postgresql:hobby-basic -a simonwillisonblog
2
Creating heroku-postgresql:hobby-basic on ⬢ simonwillisonblog... $9/month
3
▸    Release command executing: config vars set by this add-on will not be
4
▸    available until the command succeeds. Use `heroku releases:output` to view
5
▸    the log.
6
Database has been created and is available
7
! This database is empty. If upgrading, you can transfer
8
! data from another database with pg:copy
9
Created postgresql-animate-15868 as HEROKU_POSTGRESQL_JADE_URL
10
Use heroku addons:docs heroku-postgresql to view documentation

Step 2 skipped - I didn’t bother with maintenance mode#

Step 3: copy the data to the new database#

I had to wait for it to finish provisioning first:

1
heroku pg:wait -a simonwillisonblog

Then I used this command to figure out which _URL it had:

1
heroku config -a simonwillisonblog | grep URL

Which meant I could run the copy:

1
$ heroku pg:copy DATABASE_URL HEROKU_POSTGRESQL_JADE_URL --app simonwillisonblog
2
▸    WARNING: Destructive action
3
▸    This command will remove all data from JADE
4
▸    Data from DATABASE will then be transferred to JADE
5
▸    To proceed, type simonwillisonblog or re-run this command with --confirm simonwillisonblog
6

7
> simonwillisonblog
8
Starting copy of DATABASE to JADE... done
9
Copying... done

Step 4: promote the new database#

1
$ heroku pg:promote HEROKU_POSTGRESQL_JADE -a simonwillisonblog
2
Ensuring an alternate alias for existing DATABASE_URL... HEROKU_POSTGRESQL_OLIVE_URL
3
Promoting postgresql-animate-15868 to DATABASE_URL on ⬢ simonwillisonblog... done
4
Checking release phase... pg:promote succeeded. It is safe to ignore the failed Detach DATABASE (@ref:postgresql-contoured-66343) release.

Step 5: deprovision the old database#

1
% heroku addons:destroy HEROKU_POSTGRESQL_OLIVE -a simonwillisonblog
2
 ▸    WARNING: Destructive Action
3
 ▸    This command will affect the app simonwillisonblog
4
 ▸    To proceed, type simonwillisonblog or re-run this command with --confirm simonwillisonblog
5

6
> simonwillisonblog
7
Destroying postgresql-contoured-66343 on ⬢ simonwillisonblog... done