Connection refused (PGError) (postgresql and rails)

This may resolve the issue :-

• Get the hba.conf file address by using command SHOW config_file; and SHOW hba_file; in sql prompt

• Now open hba.conf and add this host all all 0.0.0.0/0 trust

• Now open postgresql.conf and add listen_address = '*'

The error message is instrumental:

could not connect to server: Connection refused
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 5433?

port

You may be trying to connect to the wrong port.
Standard port is 5432. Check how (and whether at all) you started your postgres server:

postgres@db:~$ ps -auxww | grep ^postgres
... <stripped more lines>
postgres  1274  0.0  0.3 1437240 57308 ?       S    May27   5:01 /usr/lib/postgresql/9.1/bin/postgres -D /var/lib/postgresql/9.1/main -c config_file=/etc/postgresql/9.1/main/postgresql.conf

The manual has related information here.

In my example, settings from /etc/postgresql/9.1/main/postgresql.conf got used, which says (among many other settings):

port = 5432

Or run:

netstat -nlp | grep postgres

Or just look here (at least in Debian or Ubuntu):

ls -lA /var/run/postgresql/

PostgreSQL picks the next free port if you create a new database cluster. Since you installed repeatedly, you may have confused port numbers.

listen_addresses

Or you just forgot to allow TCP/IP connections. Related answers:

• Run batch file with psql command without password
• What's the difference between "local" and "localhost" connection types in pg_hba.conf?
• no pg_hba.conf entry for host

In my case, the problem was caused by the upgrade of postgresql-9.4 to postgresql-9.5 in Ubuntu 16.04. Since, There were two versions installed at some point in time, the later installed version i.e. postgresql-9.5's config changed to default port of 5433 (instead of standard default 5432).

The problem occurred when my Rails project tried to connect to the default port of postgresql (since the port was not defined explicitly defined in database.yml file) and kept failing. Even after removal of postgresql-9.4 later.

Solution changing postgresql's config file

The solution is changing the port in the configuration of updated postgresql (9.5). To do this open the file /etc/postgresql/9.5/main/postgresql.conf and change the line

port = 5433                             # (change requires restart)

to

port = 5432                             # (change requires restart)

and then restart the server with sudo service postgresql restart.

Solution with changing Rails' `database.yml' file

Alternatively, you can change rails' database.yml file by explicitly mention the new port (that ie 5433) without changing the postgresql's config file. To do this, simply add a line like this

port: 5433

and restart the rails server.

sudo -u postgres pg_ctlcluster 9.3 main stop

followed by

sudo -u postgres pg_ctlcluster 9.3 main restart

worked for me