Rails / Postgres connection issue (psql and pg working fine)

Question

My rails app connects to the database on my development machine, and in production just fine. I've cloned the project to a coworker's machine, and recreated the database locally from a pgdump. The same process works fine on my machine. But on his,

psql at the command line is having no trouble:

$ psql ROL2
psql (9.5.2)
Type "help" for help.

ROL2=#

And to test the pg gem (pg-0.18.4), I've written a simple ruby script to output our users' names:

#!/usr/bin/env ruby

require 'pg'
conn = PG.connect( dbname: 'ROL2' )
conn.exec( "SELECT * FROM users" ) do |result|
  result.each do |row|
    puts "#{row['first_name']} #{row['last_name']}"
  end
end

No problem; works fine. But when I try connecting via the rails console or a task in the app:

$ rails db 
  psql: server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.

Here's the database.yml:

# LOCAL POSTGRES
development:
  adapter:  postgresql
  encoding: utf8
  host:     localhost
  port:     5432
  database: ROL2
  pool:     5

Ditto when I try to connect with a client (Postico). I've tried with both the postgres user and his username / password.

ROL2=# \du
                                    List of roles
  Role name  |                         Attributes                         | Member of
-------------+------------------------------------------------------------+-----------
 joeschmo    | Superuser, Create role, Create DB                          | {}
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Why might Rails have trouble connecting to the database, even though psql and the pg gem are working fine?

Added: the error as it appears in the Rails development logs:

PG::ConnectionBad (server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
):
activerecord (4.2.4) lib/active_record/connection_adapters/postgresql_adapter.rb:655:in `initialize'
activerecord (4.2.4) lib/active_record/connection_adapters/postgresql_adapter.rb:655:in `new'
activerecord (4.2.4) lib/active_record/connection_adapters/postgresql_adapter.rb:655:in `connect'
activerecord (4.2.4) lib/active_record/connection_adapters/postgresql_adapter.rb:242:in `initialize'
activerecord (4.2.4) lib/active_record/connection_adapters/postgresql_adapter.rb:44:in `new'
activerecord (4.2.4) lib/active_record/connection_adapters/postgresql_adapter.rb:44:in `postgresql_connection'
activerecord (4.2.4) lib/active_record/connection_adapters/abstract/connection_pool.rb:438:in `new_connection'
activerecord (4.2.4) lib/active_record/connection_adapters/abstract/connection_pool.rb:448:in `checkout_new_connection'
activerecord (4.2.4) lib/active_record/connection_adapters/abstract/connection_pool.rb:422:in `acquire_connection'
activerecord (4.2.4) lib/active_record/connection_adapters/abstract/connection_pool.rb:349:in `block in checkout'
/Users/mac_osx_username/.rvm/rubies/ruby-2.2.3/lib/ruby/2.2.0/monitor.rb:211:in `mon_synchronize'
activerecord (4.2.4) lib/active_record/connection_adapters/postgresql_adapter.rb:655:in `new'
activerecord (4.2.4) lib/active_record/connection_adapters/postgresql_adapter.rb:655:in `connect'
activerecord (4.2.4) lib/active_record/connection_adapters/postgresql_adapter.rb:242:in `initialize'
activerecord (4.2.4) lib/active_record/connection_adapters/postgresql_adapter.rb:44:in `new'
activerecord (4.2.4) lib/active_record/connection_adapters/postgresql_adapter.rb:44:in `postgresql_connection'
activerecord (4.2.4) lib/active_record/connection_adapters/abstract/connection_pool.rb:438:in `new_connection'
activerecord (4.2.4) lib/active_record/connection_adapters/abstract/connection_pool.rb:448:in `checkout_new_connection'
activerecord (4.2.4) lib/active_record/connection_adapters/abstract/connection_pool.rb:422:in `acquire_connection'
activerecord (4.2.4) lib/active_record/connection_adapters/abstract/connection_pool.rb:349:in `block in checkout'
/Users/mac_osx_username/.rvm/rubies/ruby-2.2.3/lib/ruby/2.2.0/monitor.rb:211:in `mon_synchronize'
activerecord (4.2.4) lib/active_record/connection_adapters/abstract/connection_pool.rb:348:in `checkout'
activerecord (4.2.4) lib/active_record/connection_adapters/abstract/connection_pool.rb:263:in `block in connection'
/Users/mac_osx_username/.rvm/rubies/ruby-2.2.3/lib/ruby/2.2.0/monitor.rb:211:in `mon_synchronize'
activerecord (4.2.4) lib/active_record/connection_adapters/abstract/connection_pool.rb:262:in `connection'
activerecord (4.2.4) lib/active_record/connection_adapters/abstract/connection_pool.rb:571:in `retrieve_connection'
activerecord (4.2.4) lib/active_record/connection_handling.rb:113:in `retrieve_connection'
activerecord (4.2.4) lib/active_record/connection_handling.rb:87:in `connection'
activerecord (4.2.4) lib/active_record/migration.rb:383:in `connection'
activerecord (4.2.4) lib/active_record/migration.rb:370:in `call'
actionpack (4.2.4) lib/action_dispatch/middleware/callbacks.rb:29:in `block in call'
activesupport (4.2.4) lib/active_support/callbacks.rb:88:in `__run_callbacks__'
activesupport (4.2.4) lib/active_support/callbacks.rb:778:in `_run_call_callbacks'
activesupport (4.2.4) lib/active_support/callbacks.rb:81:in `run_callbacks'
actionpack (4.2.4) lib/action_dispatch/middleware/callbacks.rb:27:in `call'
actionpack (4.2.4) lib/action_dispatch/middleware/reloader.rb:73:in `call'
actionpack (4.2.4) lib/action_dispatch/middleware/remote_ip.rb:78:in `call'
actionpack (4.2.4) lib/action_dispatch/middleware/debug_exceptions.rb:17:in `call'
web-console (2.2.1) lib/web_console/middleware.rb:39:in `call'
actionpack (4.2.4) lib/action_dispatch/middleware/show_exceptions.rb:30:in `call'
railties (4.2.4) lib/rails/rack/logger.rb:38:in `call_app'
railties (4.2.4) lib/rails/rack/logger.rb:20:in `block in call'
activesupport (4.2.4) lib/active_support/tagged_logging.rb:68:in `block in tagged'
activesupport (4.2.4) lib/active_support/tagged_logging.rb:26:in `tagged'
activesupport (4.2.4) lib/active_support/tagged_logging.rb:68:in `tagged'
railties (4.2.4) lib/rails/rack/logger.rb:20:in `call'
quiet_assets (1.1.0) lib/quiet_assets.rb:27:in `call_with_quiet_assets'
actionpack (4.2.4) lib/action_dispatch/middleware/request_id.rb:21:in `call'
rack (1.6.4) lib/rack/methodoverride.rb:22:in `call'
rack (1.6.4) lib/rack/runtime.rb:18:in `call'
activesupport (4.2.4) lib/active_support/cache/strategy/local_cache_middleware.rb:28:in `call'
rack (1.6.4) lib/rack/lock.rb:17:in `call'
actionpack (4.2.4) lib/action_dispatch/middleware/static.rb:116:in `call'
rack (1.6.4) lib/rack/sendfile.rb:113:in `call'
railties (4.2.4) lib/rails/engine.rb:518:in `call'
railties (4.2.4) lib/rails/application.rb:165:in `call'
rack (1.6.4) lib/rack/lock.rb:17:in `call'
rack (1.6.4) lib/rack/content_length.rb:15:in `call'
rack (1.6.4) lib/rack/handler/webrick.rb:88:in `service'

Added the dev logs. The failures don't show up in the postgres logs, which makes sense if it is failing to connect. — hoverbikes
– hoverbikes, Commented May 10, 2016 at 19:28

Michael Granger · Accepted Answer · 2016-05-11 00:05:19Z

2

Since the database.yml includes host and port, Rails is probably trying to connect to the TCP socket, whereas your connections via psql and pg (since they don't specify host or port) are using the UNIX socket. PostgreSQL comes configured out of the box to not listen to the TCP socket, so look at the postgresql.conf and ensure it has listen_addresses uncommented. If it doesn't, you can try either taking host and port out of the database.yml or modifying the config to enable TCP connections and restarting PostgreSQL.

answered May 11, 2016 at 0:05

Michael Granger

1,3689 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

hoverbikes Over a year ago

This answer will help people who encounter the same set of symptoms. The difference between connecting via UNIX socket (vs TCP) was indeed the reason psql and pg were working through the problem. (In my case, Postgres configuration wasn't the issue, though; my coworker had installed a thrid-party application that was blocking local connections on port 5432.)

Michael Granger Over a year ago

Ah, okay. Glad it at least got you on the right track. Thanks for accepting!

Collectives™ on Stack Overflow

Rails / Postgres connection issue (psql and pg working fine)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related