Running JRuby/Rails app "off-premises" using Heroku Postgres

Question

I'm running a JRuby/Rails app on my own app server. I don't want to run my own Postgres boxes right now, and want to use Heroku. So, I spun up some Heroku Postgres instances and can connect to them fine via Sequel, but am having a hard time with the "native" ActiveRecord access.

I've set the connection URL in an ENV variable as

WAREHOUSE_PG_URL='jdbc:postgresql://<instance>:<port>/<dbname>?user=<username>&password=<password>&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory'

Then, in my database.yml, I have:

production:
  adapter: postgresql
  url: <%= ENV['RAILS_PG_URL'] %>

This seems very cumbersome. I tried just setting the DATABASE_URL, but that blew up with IndexError: string not matched, and if I just did

production:  <%= ENV['RAILS_PG_URL'] %>

That blew up with: ActiveRecord::ConnectionNotEstablished: jdbc adapter requires :driver and :url (got :driver = )

Migrations are giving me strange errors though:

% rake db:migrate
file:/Users/me/.rbenv/versions/jruby-1.7.15/lib/jruby.jar!/jruby/kernel19/kernel.rb:28 warning: unsupported exec option: close_others
rake aborted!
NoMethodError: undefined method `empty?' for nil:NilClass
/Users/me/code/Exchange/vendor/bundle/jruby/1.9/gems/activerecord-4.1.7/lib/active_record/tasks/postgresql_database_tasks.rb:54:in `structure_dump'
/Users/me/code/Exchange/vendor/bundle/jruby/1.9/gems/activerecord-4.1.7/lib/active_record/tasks/database_tasks.rb:150:in `structure_dump'
/Users/me/code/Exchange/vendor/bundle/jruby/1.9/gems/activerecord-4.1.7/lib/active_record/railties/databases.rake:270:in `(root)'
/Users/me/code/Exchange/vendor/bundle/jruby/1.9/gems/activerecord-4.1.7/lib/active_record/railties/databases.rake:43:in `(root)'
/Users/me/code/Exchange/vendor/bundle/jruby/1.9/gems/activerecord-4.1.7/lib/active_record/railties/databases.rake:37:in `(root)'
Tasks: TOP => db:structure:dump
(See full trace by running task with --trace)

Update: this is the actual code around that method that's blowing up:

  def structure_dump(filename)
    set_psql_env
    search_path = configuration['schema_search_path']
    unless search_path.blank?
      search_path = search_path.split(",").map{|search_path_part| "--schema=#{Shellwords.escape(search_path_part.strip)}" }.join(" ")
    end

    command = "pg_dump -i -s -x -O -f #{Shellwords.escape(filename)} #{search_path} #{Shellwords.escape(configuration['database'])}"
    raise 'Error dumping database' unless Kernel.system(command)

    File.open(filename, "a") { |f| f << "SET search_path TO #{ActiveRecord::Base.connection.schema_search_path};\n\n" }
  end

Line 54 is the command = "pg_dump..... one

Update2: I also want to just throw an "I'm annoyed I have to use 'don't bother checking if certs are good' in this setup" in there, also.

Update3: I just looked closely at this and realized how weird it was:

% rake db:migrate
file:/Users/me/.rbenv/versions/jruby-1.7.15/lib/jruby.jar!/jruby/kernel19/kernel.rb:28 warning: unsupported exec option: close_others
E, [2014-11-13T15:45:49.482000 #86686] ERROR -- : ActiveRecord::JDBCError: org.postgresql.util.PSQLException: ERROR: relation "users" does not exist
  Position: 293:         SELECT a.attname, format_type(a.atttypid, a.atttypmod),
               pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod
          FROM pg_attribute a LEFT JOIN pg_attrdef d
            ON a.attrelid = d.adrelid AND a.attnum = d.adnum
         WHERE a.attrelid = '"users"'::regclass
           AND a.attnum > 0 AND NOT a.attisdropped
         ORDER BY a.attnum

I, [2014-11-13T15:45:51.671000 #86686]  INFO -- : Migrating to CreateMarkets (20140312080145)

CreateMarkets is my first migration - I can't figure out where that code came from that rased the exception that happened right before it. Also, that exception didn't stop the rest of the migrations from going....

Answer 1

It looks like I was able to fix this by adding all of my database connection details to database.yml:

production:
  adapter: postgresql
  url: <%= ENV['RAILS_PG_URL'] %>
  database: <%= ENV['RAILS_PG_DATABASE'] %>
  port: <%= ENV['RAILS_PG_PORT'] %>
  host: <%= ENV['RAILS_PG_HOST'] %>
  username: <%= ENV['RAILS_PG_USER'] %>
  password: <%= ENV['RAILS_PG_PASSWORD'] %>
  ssl: true

So the ActiveRecord JDBC Adapter uses the url to connect (with all the ssl-cert-unchecking and all that), whereas the "regular" stuff that happens during the course of DB maintenance (pg_dump) will need to build their own set of ENV variables to control the execution of that command. Neat.

I'm not "in love" with this, but it seems ok.