Heroku CI w/ Postgresql Extensions

Question

I'm attempting to use Heroku's CI to run my Rails application's tests but it's running into a problem when attempting to load my structure.sql file.

-----> Preparing test database
       Running: rake db:schema:load_if_ruby
       db:schema:load_if_ruby completed (3.24s)
       Running: rake db:structure:load_if_sql
       psql:/app/db/structure.sql:28: ERROR:  must be owner of extension plpgsql
       rake aborted!
       failed to execute:
       psql -v ON_ERROR_STOP=1 -q -f /app/db/structure.sql d767koa0m1kne1
       Please check the output above for any errors and make sure that `psql` is installed in your PATH and has proper permissions.
       /app/vendor/bundle/ruby/2.4.0/gems/activerecord-5.1.1/lib/active_record/tasks/postgresql_database_tasks.rb:108:in `run_cmd'
       /app/vendor/bundle/ruby/2.4.0/gems/activerecord-5.1.1/lib/active_record/tasks/postgresql_database_tasks.rb:80:in `structure_load'
       /app/vendor/bundle/ruby/2.4.0/gems/activerecord-5.1.1/lib/active_record/tasks/database_tasks.rb:223:in `structure_load'
       /app/vendor/bundle/ruby/2.4.0/gems/activerecord-5.1.1/lib/active_record/tasks/database_tasks.rb:236:in `load_schema'
       /app/vendor/bundle/ruby/2.4.0/gems/activerecord-5.1.1/lib/active_record/tasks/database_tasks.rb:255:in `block in load_schema_current'
       /app/vendor/bundle/ruby/2.4.0/gems/activerecord-5.1.1/lib/active_record/tasks/database_tasks.rb:304:in `block in each_current_configuration'
       /app/vendor/bundle/ruby/2.4.0/gems/activerecord-5.1.1/lib/active_record/tasks/database_tasks.rb:303:in `each'
       /app/vendor/bundle/ruby/2.4.0/gems/activerecord-5.1.1/lib/active_record/tasks/database_tasks.rb:303:in `each_current_configuration'
       /app/vendor/bundle/ruby/2.4.0/gems/activerecord-5.1.1/lib/active_record/tasks/database_tasks.rb:254:in `load_schema_current'
       /app/vendor/bundle/ruby/2.4.0/gems/activerecord-5.1.1/lib/active_record/railties/databases.rake:290:in `block (3 levels) in <top (required)>'
       /app/vendor/bundle/ruby/2.4.0/gems/activerecord-5.1.1/lib/active_record/railties/databases.rake:294:in `block (3 levels) in <top (required)>'
       /app/vendor/bundle/ruby/2.4.0/gems/rake-12.0.0/exe/rake:27:in `<top (required)>'
       Tasks: TOP => db:structure:load
       (See full trace by running task with --trace)
 !
 !     Could not prepare database for test
 !

The relevant line here is:

psql:/app/db/structure.sql:28: ERROR: must be owner of extension plpgsql rake aborted!

Structure.sql contains this line:

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

Any ideas on how to get this working on Heroku's CI?

score 12 · Accepted Answer · answered May 24 '17 at 23:55

12

Ended up overriding db:structure:dump to remove the COMMENT ON ... statements:

namespace :db do
  namespace :structure do
    task dump: [:environment, :load_config] do
      filename = ENV["SCHEMA"] || File.join(ActiveRecord::Tasks::DatabaseTasks.db_dir, "structure.sql")
      sql = File.read(filename).each_line.grep_v(/\ACOMMENT ON EXTENSION.+/).join

      File.write(filename, sql)
    end
  end
end

answered May 24 '17 at 23:55

Kyle Decot

20,715
39
142
263

2

What file do you put this task in? `project.rake`? – user2954587 Feb 14 '18 at 14:18
Rails 7 has now `ActiveRecord::Tasks::DatabaseTasks.structure_dump_flags`, which can be used to disable comments using `["--no-comment"]`. Note that it will disable all comments (on extensions, but also on tables and columns). Also, you must override the `db:schema:dump` instead of `db:structure:load` which is deprecated – tight Jun 15 '22 at 12:18
Another workaround is to remove comments (`COMMENT ON EXTENSION ... IS NULL;`) in a migration with a condition on `Rails.env.development?`. This will fix the schema file, and prevent other developers to add comments in again. – tight Jun 15 '22 at 12:25

score 5 · Answer 2 · answered Jun 18 '18 at 18:50

Another workaround would be to add something like

if Rails.env.development?
  ActiveRecord::Tasks::DatabaseTasks.structure_load_flags = ["-v", "ON_ERROR_STOP=0"]
end

anywhere in the initialisation / tasks pipeline before the db:structure:load is executed.

score 2 · Answer 3 · answered Sep 27 '17 at 04:42

If Kyle's solution isn't enough and the errors aren't caused only by comments on extensions, but actual extensions installations, you can still go the hard way and add this to an initializer:

# This is a temporary workaround for the Rails issue #29049.
# It could be safely removed when the PR #29110 got merged and released
# to use instead IGNORE_PG_LOAD_ERRORS=1.

module ActiveRecord
   module Tasks
     class PostgreSQLDatabaseTasks
       ON_ERROR_STOP_1 = 'ON_ERROR_STOP=0'.freeze
     end
   end
end

Note: This isn't specific to Heroku but a broader Rails 5.1 issue

score 1 · Answer 4 · answered Dec 20 '18 at 09:59

There are two solutions to this problem. First, as it was previously noted, is disabling the ON_ERROR_STOP feature. It'd help regardless of the environment. Custom rake task:

namespace :db do
  namespace :structure do
      # This little task is a workaround for a problem introduced in Rails5. Most specificaly here
      # https://github.com/rails/rails/blob/5-1-stable/activerecord/lib/active_record/tasks/postgresql_database_tasks.rb#L77
      # When psql encounters an error during loading of the structure it exits at once with error code 1.
      # And this happens on heroku. It renders review apps and heroku CI unusable if you use a structure instead of a schema.
      # Why?
      # Our `db/structure.sql` contains entries like `CREATE EXTENSION` or `COMMENT ON EXTENSION`.
      # Zylion of extensions on heroku are loaded in template0, so "our" db also has them, but because of that
      # only a superuser (or owner of template0) has access to them - not our heroku db user. For that reason
      # we can neither create an extension (it already exists, but that is not a problem, because dump contains IF NOT EXIST)
      # nor comment on it (and comments don't have IF NOT EXIST directive). And that's an error which could be safely ignored
      # but which stops loading of the rest of the structure.
      desc "Disable exit-on-error behaviour when loading db structure in postgresql"
      task disable_errors: :environment do
        ActiveRecord::Tasks::DatabaseTasks.structure_load_flags = ["-v", "ON_ERROR_STOP=0"]
      end
    end
  end


# And use it like so:
bin/rails db:structure:disable_errors db:structure:load

Another option, in my opinion, superior if it comes only to Heroku, would be using PostgreSQL in in-dyno plan (https://devcenter.heroku.com/articles/heroku-ci-in-dyno-databases), which is basically a DB instance sitting within dyno, thus we have full access to it. Also, the test suite should be significantly faster because we use a localhost connection, not over the wire. To enable it, change your app.json content to have entries like so:

{
  "environments": {
    "test": {
      "addons": [
        "heroku-postgresql:in-dyno"
      ]
    }
  }
}

Heroku CI w/ Postgresql Extensions

4 Answers4