6

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

Andy Harvey
  • 12,333
  • 17
  • 93
  • 185

1 Answers1

4

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.

Neil Middleton
  • 22,105
  • 18
  • 80
  • 134
  • 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...? – Andy Harvey May 18 '12 at 12:19
  • 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. – Neil Middleton May 18 '12 at 16:39
  • 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`. – Andy Harvey May 20 '12 at 10:32
  • 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` – Neil Middleton May 21 '12 at 08:40
  • 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? – Noach Magedman Jan 15 '13 at 11:38
  • 2
    @Noach (1) Full list [is here](https://devcenter.heroku.com/articles/heroku-postgres-extensions-postgis-full-text-search); (2) You can't (afaik). – davetapley Jan 22 '13 at 23:18
  • 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 https://github.com/dimitri/pgextwlist) – Noach Magedman Jan 23 '13 at 14:35