CODEWITHSUNDEEP
ruby-on-railspostgresqlherokuschemahstore
Tu H.
Tu H.

Reputation: 496

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

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.

Upvotes: 1

Views: 1395

Answers (2)

Nate Yaconis
Nate Yaconis

Reputation: 41

A less destructive alternative work around:

1.) Put your application in maintenance mode: heroku maintenance:on -a [app-name] 2.) Backup up your database: ALWAYS BACK IT UP :) heroku pg:backups capture -a [app-name] 3.) Copy your database locally: heroku pg:pull [Database URL] mylocaldb -a [app-name] 4.) Now you have the db locally, you can move the extensions without any destruction of data or columns because you have local superuser priv.: ALTER EXTENSION "hstore" SET SCHEMA hstore; 5.) Now export this version back to production: heroku pg:push mylocaldb [Database URL] -a [app-name] 6.) Take your app out of maint. mode and you now have the extensions installed in the shared schema of hstore.

7.) You will still need to perform step 1 of Tu H.'s response if you are on rails < 4.1

OR if you are running rails 4.1+ you can just update your database.yml file: schema_search_path: "public,hstore"

This is a less destructive way to get what you want done without too much downtime.

Upvotes: 4

Tu H.
Tu H.

Reputation: 496

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)

Upvotes: 1

Related Questions