How to do ALTER EXTENSION with Heroku's Postgres DB?

Question

I'm using the apartment gem for my SaaS (built with Ruby on Rails) and one of its requirements is we need to install the hstore PostgreSQL extension via a dedicated schema (ie: shared_extensions), not the default public schema.

Heroku enables hstore by default; however it is installed in the public schema. When I tried to do:

ActiveRecord::Base.connection.execute("ALTER EXTENSION hstore SET SCHEMA hstore;")

the console returned:

(3.6ms) ALTER EXTENSION hstore SET SCHEMA hstore; PG::Error: ERROR: must be owner of extension hstore

Currently it's impossible on the code side to make do with hstore extension being installed on public, so I definitely have to find a way to put hstore extension on shared_extensions schema.

Will there be a way to do this on Heroku? Thanks.

Answer 1

Turns out I can't do ALTER EXTENSION hstore SET SCHEMA hstore; on Heroku. I've documented the workaround here

1. Append ?schema_search_path=public,hstore to your DATABASE_URL environment variable, by this you don't have to revise the database.yml file (which is impossible since Heroku regenerates a completely different and immutable database.yml of its own on each deploy)
2. Run heroku pg:psql from your command line
3. And then DROP EXTENSION hstore; (Note: This will drop all columns that use hstore type, so proceed with caution; only do this with a fresh PostgreSQL instance)
4. Next: CREATE SCHEMA IF NOT EXISTS hstore;
5. Finally: CREATE EXTENSION IF NOT EXISTS hstore SCHEMA hstore; and hit enter (\q to exit)