postgresql not accepting tcp/ip on port 5432 for rails on linux

Question

I've tried installing psql on linux but ran into some issues. I downloaded from yaourt and the psql command is running without any issues. It all seems to work fine until I try to run rails db:setup, at which point I'm thrown the error:

could not connect to server: Connection timed out
    Is the server running on host "localhost" (92.242.132.16) and accepting
    TCP/IP connections on port 5432?
Couldn't create database for {"adapter"=>"postgresql", "encoding"=>"unicode", "pool"=>5, "username"=>"revisionhubadmin", "password"=>"password", "host"=>"localhost", "database"=>"revisionHub_development"}
rails aborted!
PG::ConnectionBad: could not connect to server: Connection timed out
    Is the server running on host "localhost" (92.242.132.16) and accepting
    TCP/IP connections on port 5432?
Traceback (most recent call last):
bin/rails: undefined method `reject' for nil:NilClass (NoMethodError)

This is despite the psql command running okay and the following response from systemctl status postgresql.service

● postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
   Active: active (running) since Tue 2018-02-13 16:05:40 GMT; 2h 31min ago
  Process: 1961 ExecStop=/usr/bin/pg_ctl -s -D ${PGROOT}/data stop -m fast (code=exited, status=1/FAILURE)
  Process: 2084 ExecStart=/usr/bin/pg_ctl -s -D ${PGROOT}/data start -w -t 120 (code=exited, status=0/SUCCESS)
  Process: 2082 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGROOT}/data (code=exited, status=0/SUCCESS)
 Main PID: 2086 (postgres)
    Tasks: 5 (limit: 4915)
   CGroup: /system.slice/postgresql.service
           ├─2086 /usr/bin/postgres -D /var/lib/postgres/data
           ├─2088 postgres: checkpointer process   
           ├─2089 postgres: writer process   
           ├─2090 postgres: wal writer process   
           └─2091 postgres: bgworker: logical replication launcher   

Feb 13 16:05:40 bens-xps postgres[2084]: 2018-02-13 16:05:40.524 GMT [2086] LOG:  listening on IPv4 address "0.0.0.0", port 5432
Feb 13 16:05:40 bens-xps postgres[2084]: 2018-02-13 16:05:40.524 GMT [2086] LOG:  listening on IPv6 address "::", port 5432
Feb 13 16:05:40 bens-xps postgres[2084]: 2018-02-13 16:05:40.526 GMT [2086] LOG:  listening on Unix socket "/run/postgresql/.s.PGSQL.5432"
Feb 13 16:05:40 bens-xps postgres[2084]: 2018-02-13 16:05:40.554 GMT [2086] LOG:  could not bind socket for statistics collector: Cannot assign requested address
Feb 13 16:05:40 bens-xps postgres[2084]: 2018-02-13 16:05:40.554 GMT [2086] LOG:  disabling statistics collector for lack of working socket
Feb 13 16:05:40 bens-xps postgres[2084]: 2018-02-13 16:05:40.554 GMT [2086] WARNING:  autovacuum not started because of misconfiguration
Feb 13 16:05:40 bens-xps postgres[2084]: 2018-02-13 16:05:40.554 GMT [2086] HINT:  Enable the "track_counts" option.
Feb 13 16:05:40 bens-xps postgres[2084]: 2018-02-13 16:05:40.556 GMT [2087] LOG:  database system was shut down at 2018-02-13 16:05:17 GMT
Feb 13 16:05:40 bens-xps postgres[2084]: 2018-02-13 16:05:40.561 GMT [2086] LOG:  database system is ready to accept connections
Feb 13 16:05:40 bens-xps systemd[1]: Started PostgreSQL database server.

I've already tried changing my pg_hba.config to

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             0.0.0.0/0            trust
# IPv6 local connections:
host    all             all             ::0/0                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

and changed local addresses to * with listen_addresses = '*' and uncommenting port = 5432 in postgresql.conf.

These files were from /var/lib/postgres/data, which I thought was odd because which psql returns /usr/bin/psql but systemctl confirms that I edited the right ones because the logs mention 0.0.0.0/0

I downloaded postgres with yaourt on antergos, which is an arch based linux distro. PG is added to my gemfile on version 0.18.4 and postgres is installed as version 10.1.

My heroku server which runs the app I'm trying to run db:setup on in production works fine without any issues and the app has also been fine running on a previous install of linux (Ubuntu) on the same laptop and on a mac, suggesting that the issue probably isn't with this specific rails app. Regardless, here's the default and dev part of my database.yml file:

default: &default
  adapter: postgresql
  encoding: unicode
  pool: 5
  username: revisionhubadmin
  password: **********
  host: localhost

development:
  <<: *default
  database: revisionHub_development

And yes, I have made a revisionhubadmin psql account. Running \du in psql gives:

ben              | Superuser, Create DB

postgres         | Superuser, Create role, Create DB, Replication, Bypass RLS

revisionhubadmin | Superuser, Create DB

So yeah, I'm a little bit confused. Does anyone know how to fix this?

Answer 1

A few steps - first, on the database server, make sure that the socket is open and listening. Something like ss or netstat will help with that:

$ ss -ltn
State   Recv-Q  Send-Q  Local Address:Port  Peer Address:Port              
LISTEN       0     128  0.0.0.0:5432        0.0.0.0:*

If it's confirmed as listening, as I suspect it is as psql works for you and as this log clearly shows:

LOG:  listening on IPv4 address "0.0.0.0", port 5432

Does it work from the rails system to the database itself, or are they hosted on the same system? A note, if it's on the same system, psql will use UNIX domain sockets instead of the actual networked loopback device, so there is a marked difference in how the two communicate wherein the first example will strictly use the file-socket and the second will use the loopback device:

$ psql -U myuser -d mydb

versus:

$ psql -U myuser -d mydb -h 127.0.0.1

If the second one fails, you genuinely have network connectivity issues, and you'd have to look at firewall rules, connectivity, etc.

If they're on different systems, and psql runs fine from the rails client host to the database host but we still can't do migrations, my only other thought is that you could test connectivity to the socket with telnet or some other simple socket tool to see if it really works:

$ telnet mydbhost 5432

EDIT: I noticed from your configuration that the database is local - I bet that psql in this case worked using the UNIX socket, not the network socket like your rails command is trying to do. Verify the socket is listening and can be connected to with network clients please.

EDIT 2: If you’d rather not troubleshoot it, you can simply drop ‘host: localhost’ from your database.yml and that lets you use postgres’s UNIX socket it seems.