How to use postgres extensions on Heroku? And how to handle their migrations?

Question

I have a Rails app that includes pg_search and queue_classic gems. Both of these make use of PostgreSQL extensions e.g., unaccent, pg_trgm and ps-something-something (sorry, not as my dev machine and can not remember).

I'm deploying to Heroku, and having trouble running my migrations that exceute these extensions

e.g.,

def up
  execute "create extension unaccent"
  execute "create extension pg_trgm"
end

I get the impression that Heroku supports these from here https://devcenter.heroku.com/articles/full-text-search and the fact that Heroku uses queue_classic https://github.com/ryandotsmith/queue_classic.

I've been unable to find any information that explains how to make use of these postgres extensions on Heroku. Or even if they are available for shared databases or only dedicated.

So my questions:

1. How to I make these extensions available to my app on Heroku?
2. How do I handle migrations so that these extensions are available to dev and test environments, but don't break migrations on staging or production or environments if Heroku is restricting this type of execution.

Really appreciate any ideas, especially those accompanied with pointers to relevant information/ instructions.

Thanks

Tristis

Answer 1

If you're using the old standard shared database plans then these probably won't work so you need to look at bumping up to the new shared plans or the production plans.

Satanic

Thanks Neil, makes sense that 9.1 is required. Do you know anything about specifics under Heroku? i.e. do I need to run these migrations to create the extensions? Are they simply available by default? Suppose I could just try, but I'm still surprised at the lack of documentation. This must be quite a common need...? – Tristis

This stuff is all still pretty new, and a lot of the PG9 stuff is still beta. I think you do have to run the migrations to enable extensions on the current plans. – Satanic

Thanks Neil, this worked great. I had to switch my app to the new database heroku addons:add heroku-postgresql:dev, and then enable 9.1 as the default heroku pg:promote HEROKU_POSTGRESQL_PURPLE. I'm not sure if the database name will be different for different cases. You can check the names with heroku pg:info. – Tristis

Postgres database URL's work a bit different to the old DATABASE_URL's that we had before - it's now built in a way that gives you a unique 'colour' on a database which you then pg:promote' to your DATABASE_URL` – Satanic

Could you elaborate on how to install the extensions? (1) How do we determine the list of extensions that are already installed in Heroku Postgres's share/extension folder? (2) How do we install arbitrary 3rd-party extensions, e.g. from pgxn.org? – Grapeshot

@Noach (1) Full list is here; (2) You can't (afaik). – Underline

Thanks, @dukedave. The devcenter article elaborates that a definitive list can be accessed at psql=> show extwlist.extensions; (Heroku apparently uses the "PostgreSQL Extension Whitelist" extension from github.com/dimitri/pgextwlist) – Grapeshot