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
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
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.