Debugging Postgresl and Rails PG::ConnectionBad: could not connect to server: Connection refused

WHY??? It's your home! Go to your home!!

me@jaykilleen.com wrote this over 3 years ago and it was last updated over 3 years ago.


← Back to the Posts

Here is a series of commands/thoughts/hates I went through when debugging WHY on earth rails could not connect to Postgresql. I thought I had a fresh install and everything.

In the end... the issue was postgresql was running on port 5433 instead of 5432. 33 Is my lucky number so something tells me I made this change for some stupid reason. That reason could have been because I stupidly had postgres running on both windows and the windows subsystem for linux and both trying to accept traffic on localhost:5432.

I ended up deleting postgres on Windows. Sticking with the one on ubuntu in the WSL.

It took about an hour to nail down the issue... which sucks when you are on a deadline.

pg_hba.conf can be set to trust on all connections to bypass requiring a password at all. Ok when on a dev/test machine. NOT ok in production lol!

sudo service postgresql restart restarts the postgresql server
sudo -u postgres psql signs into psql as the default postgres user
\dt lists the database tables from within psql
\l lists the databases installed from within psql
\du lists all the users. Check for super user. Run from with psql
\q quit psql XD
SELECT versions() prints the postgresql version from within psql
\conninfo prints the connection and port that the postgresql service is running on. Run from psql.
ps -fHC postgres prints all the postgres jobs and the file locations etc. Run from terminal
sudo vim /etc/postgresql/9.5/main/postgresql.conf where you go to change the port. Run from terminal.
sudo -u postgres psql -c "SHOW data_directory"; show the data directory. Like command above.
sudo vim /etc/postgresql/9.5/main/pg_hba.conf set permissions etc to trust, peer or md5. Run from terminal.