Why do I get Postgres extension errors when I run `rake db:structure:load` or `rake db:setup`?

Issue

You are seeing ERROR: must be owner of extension plpgsql or similar when you attempt to run rake db:structure:load.

Resolution

When Rails generates structure.sql, it includes commands to add database comments to database extensions which are not supported by Heroku Postgresql. In general, there are three options for resolving this issue depending on your exact needs.

If you do not need to store the schema in SQL format, and instead are okay with the ruby format, you change the schema format to ruby. For Example:

config.active_record.schema_format = :ruby

After this change your database schema will be stored in schema.rb file as opposed to structure.sql.

If you need to use structure.sql there are two ways of doing so. The most simple method is removing COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; from structure.sql.

Alternatively, to keep ActiveRecord from resetting this change during migrations, create a new migration that clears the comment from your extension(s). For example:

def up
  ActiveRecord::Base.connection.execute("COMMENT ON EXTENSION plpgsql IS NULL;") if Rails.env.development?
end

def down
  ActiveRecord::Base.connection.execute("COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';") if Rails.env.development?
end

Ask on Stack Overflow

Engage with a community of passionate experts to get the answers you need

Ask on Stack Overflow

Heroku Support

Create a support ticket and our support experts will get back to you

Contact Heroku Support