Options for testing PostgreSQL PostGIS on Heroku?

Question

I am in the process of creating an application which makes heavy use of the PostGIS extensions to PostgreSQL. I found this:

https://devcenter.heroku.com/articles/is-postgis-available

Which suggests to me that I can only get PostGIS on Ronin and above DBs. Which is totally fine by me for production, my plan was to use that level of DB for my production system anyway.

However, I also have a stage and development environment on Heroku which means I need to have a PostGIS enabled database for those environments too as I cannot run tests out of a production DB. I need an option to support these environments and as such I'm wondering the following:

Can you connect to a DB on Heroku except through the addons process?

I tried manually adding a config var for a test DB but it doesn't just magically work through pg:promote--there is some other thing which controls how Heroku considers a valid database.

Any other options people could think of would be appreciated. I know there is a closed beta of SpacialDB that might work (already looking into that) but anything else anyone can think of? I'm just not willing to pay $400 extra a month for dedicated DBs for test/stage.

user664833 user664833 · Accepted Answer · 2014-01-13T01:20:32

You can create (provision) and delete a production-tier database on a per-need basis. The entry level, production-tier database will cost you $0.07/hour (or $1.67/day); it's keeping the database provisioned full-time that will cost you $50/month. See Heroku Postgres Production Tier Technical Characterization.

provision a database

1. This can be done in one or two lines.

-bash> heroku addons:add heroku-postgresql:crane --remote staging
Adding heroku-postgresql:crane on cool-app-0007... done, v36 ($50/mo)
Attached as HEROKU_POSTGRESQL_GOLD_URL
The database should be available in 3-5 minutes.
 ! The database will be empty. If upgrading, you can transfer
 ! data from another database with pgbackups:restore.
Use `heroku pg:wait` to track status..
Use `heroku addons:docs heroku-postgresql` to view documentation.

Despite the provisioning process taking 3-5 minutes, the command prompt will quickly return. As per the message, you can track the provisioning status:

-bash> heroku pg:wait --remote staging
Waiting for database HEROKU_POSTGRESQL_GOLD_URL... available

Note that you can do the above two commands in one shot (&& ensures that the second command executes only after the first returns successfully):

-bash> heroku addons:add heroku-postgresql:crane --remote staging && heroku pg:wait --remote staging

2. Once complete ("available"), set DATABASE_URL by using the above mentioned name (i.e. HEROKU_POSTGRESQL_GOLD_URL). I do it in one shot in order to avoid copy/pasting:

-bash> heroku config:set DATABASE_URL=`heroku config:get HEROKU_POSTGRESQL_GOLD_URL --remote staging` --remote staging
Setting config vars and restarting cool-app-0007... done, v37 
DATABASE_URL: postgres://i28asd172a3k2:bd3k2s05sls1a03b8c4efi0b33a@ec2-12-345-678-90.compute-1.amazonaws.com:5562/eexf3mwha92jk6

3. Migrate or Restore.

The first time you do the above, you will need to set up the database (e.g. by running the migrations: heroku run rake db:migrate --remote staging and possibly followed by heroku restart --remote staging).

Subsequently you will restore from a backup, and thus be able to continue where you left off:

-bash> heroku pgbackups:restore HEROKU_POSTGRESQL_GOLD_URL b001 --confirm cool-app-0007

HEROKU_POSTGRESQL_GOLD_URL (DATABASE_URL)  <---restore---  b001
                                                             HEROKU_POSTGRESQL_ROSE_URL (DATABASE_URL)
                                                             2014/01/12 03:19.29
                                                             16.3KB

Retrieving... done
Restoring... done

create a backup

-bash> heroku pgbackups:capture HEROKU_POSTGRESQL_ROSE_URL

HEROKU_POSTGRESQL_ROSE_URL (DATABASE_URL)  ----backup--->  b001

Capturing... done
Storing... done

remove the database

-bash> heroku addons:remove HEROKU_POSTGRESQL_ROSE_URL
Removing HEROKU_POSTGRESQL_ROSE_URL on cool-app-0007... done, v35 ($50/mo)

For more info, see

Options for testing PostgreSQL PostGIS on Heroku?

3 Answers

provision a database

create a backup

remove the database