Pg::Connectionbad - Could Not Connect to Server: Connection Refused

PG::ConnectionBad - could not connect to server: Connection refused

It could be as simple as a stale PID file. It could be failing silently because your computer didn't complete the shutdown process completely which means postgres didn't delete the PID (process id) file.

The PID file is used by postgres to make sure only one instance of the server is running at a time. So when it goes to start again, it fails because there is already a PID file which tells postgres that another instance of the server was started (even though it isn't running, it just didn't get to shutdown and delete the PID).

1. To fix it remove/rename the PID file. Find the postgres data directory. On macOS using homebrew it is in /usr/local/var/postgres/,
   or /usr/local/var/log/ other systems it might be /usr/var/postgres/. On M1, it might be /opt/homebrew/var/postgresql.
2. To make sure this is the problem, look at the log file (server.log). On the last lines you will see:

FATAL: lock file "postmaster.pid" already exists

HINT: Is another postmaster (PID 347) running in data directory "/usr/local/var/postgres"?

3. If so, rm postmaster.pid

4. Restart your server. On a mac using launchctl (with homebrew) the following commands will restart the server.

   brew services restart postgresql
   

OR on older versions of Brew

    launchctl unload homebrew.mxcl.postgresql.plist  
    launchctl load -w homebrew.mxcl.postgresql.plist

rake aborted! PG::ConnectionBad: could not connect to server: Connection refused

This means that Postgresql isn't running so it cannot make the database.

Have you installed Postgres? If so, you can likely start the service by using:

sudo service postgresql restart

If Postgres isn't installed, you first need to install it. How to do so differs depending on your system:

https://www.postgresqltutorial.com/install-postgresql/

PG ConnectionBad Could not connect to server

I will send you some steps that help me with this issue:

1. CHECKING POSTGRES STATUS AND RUNNING PORT (if it is on 5432):

Ubuntu/Linux Distros (Terminal):

$ pg_lsclusters

or

$ systemctl status postgresql.service

or

$ service postgresql status

Windows:

Ctrl+R type services.msc

You should find the postgres status and the port which it is running

2. STARTING POSTGRES SERVICE:

Ubuntu/Linux Distros (Terminal):

$ systemctl start postgresql.service

or

$ sudo service postgresql start

Windows

Ctrl + R type services.msc find your postgres service and just start it.

3. CHECKING AND CREATING USER ROLE ON POSTGRES

   First connect to the database:

   $ sudo su postgres

   $ psql

Then check if your role is created with the command:

$ \du

If it's not created yet you can create by using:

$ CREATE role name_of_your_role_here WITH createdb login password 'your_password_here';

After that you could try again to connect to the database:

don't forget to use

rake db:create
rake db:migrate

and configure database.yml file on your application

4. Change Client Authentication Method on PG_HBA.CONF

You can see more details of it right here: pg_hba.conf

You would probably use md5 password method or no auth at all for tests purpose

To do that, you could change the METHOD column of your localhost from peer(default) to md5

You can find this file on:

LINUX DISTROS:

cd /etc/postgresql/your postgres version here/main/pg_hba.conf

WINDOWS:

C:/Porgram Files/PostgresSQL/your postgres version
her/data/pg_hba.conf

Option method with simple user/password:

TYPE  DATABASE        USER            ADDRESS                 METHOD

IPv4 local connections:

host  all             all             127.0.0.1/32            md5 <= CHANGE HERE

Option method with no Password:

TYPE  DATABASE        USER            ADDRESS                 METHOD

IPv4 local connections:

host  all             all             127.0.0.1/32            trust <= CHANGE HERE

PG::ConnectionBad: could not connect to server: Connection refused (Ubuntu 16.04, Rails 5, Capistrano)

As it turns out, I had the wrong ip address in the database.yml file. I had it set to my host ip (138.68.6.26) when it should have been set to the local ip (127.0.0.1).

PG::ConnectionBad - could not connect to server: Connection refused on Mac OS X

It appeared that I needed to create a new PostgreSQL database cluster.
First you need to remove the postgres dir in the /usr/local/var/

you can use this code - cd /usr/local/var/ && rm -rf postgres

after that - create the new postgres folder mkdir postgres (inside /usr/local/var)

and initialise the creation of the new PostgreSQL database cluster with -

cd && initdb --locale=C -E UTF-8 /usr/local/var/postgres

The last thing, restart the postgres - brew services restart postgresql

PG::ConnectionBad at /sign_in could not connect to server: Connection but I can connect with psql

First, I'm assuming this is a WSL2 instance, not WSL1.

WSL2 instances really do run in a VM. The "VirtualMachinePlatform" component is a subset of Hyper-V. The WSL2 instances are running NAT'd (not bridged) behind their own virtual NIC vEthernet (WSL).

localhost to the WSL2 instance points to the virtual interface, not to the Windows host. On the flip side, Windows does attempt to detect ports bound inside a WSL2 instance and allow Windows applications (such as a web-browser) access to the services on those ports via localhost. However, this seems to break down for some people every so often, requiring a wsl --shutdown of the VM in order to restore the functionality.

So yes, you can access the Rails app (running in WSL) using your web browser (running in Windows) at localhost. However, you can't access the Postgres server running in Windows from the Rails server running in WSL2 via localhost.

Or at least you shouldn't. I'm surprised that the psql command seems to be working. Is it that:

• psql is running under WSL? (shouldn't work via localhost)
• Or perhaps you mean that you are running psql from PowerShell or cmd? That would of course work via localhost
• Or perhaps the psql command is in the Windows path that gets propagated to WSL? That would also allow it to work via localhost.

But ultimately, what you should need for accessing the Windows Postgres server from within WSL is to simply use a Windows IP or address pointing to it. I've detailed some options in this answer a few days ago. But short answer:

• Use mDNS, the ".local" domain. If your hostname is stevesdesktop, then try replacing localhost in your database.yml with stevesdesktop.local.
• If that doesn't work, use the Windows IP directly.
• Or edit /etc/hosts with the IP and a name to assign.

PG::ConnectionBad - could not connect to server: Connection refused Sinatra server on Heroku

You don't need the other lines if you have the db url, it can be a 1 liner, and also let postgres timeout it's default, no need to set it. You probably want a larger pool size in prod. Go to the postgres database you should have added to your app in Heroku. Click on the View Credentials button. Copy the data from there to your local .env file and also add .env to your .gitignore file because you don't want to commit your private credentials to your public repo.

Then on heroku

database.yml:

development:
  adapter: sqlite3
  database: db/madlibs.sqlite
  host: localhost
  pool: 5
  timeout: 5000

production: 
  adapter: postgresql
  encoding: unicode
  host: <%= ENV['DB_HOST'] %>
  username: <%= ENV['DB_USERNAME'] %>
  password: <%= ENV['DB_PASSWORD'] %>
  port: <%= ENV['DB_PORT'] %>
  database: <%= ENV['DB_NAME'] %>
  pool: 25

Alternatively, you could just use 1 environment variable which concatenates all of this into a URI which would look something like

#postgresql://username:password@db-shared-us-east-1-or-something.blah:1234/db
#If you use this format you can use just a single environment variable somthinglike

production: 
  adapter: postgresql
  encoding: unicode
  url: <%= ENV['DATABASE_URI'] %>
  pool: 25

In either case you will need to set whatever environment variables you'll use in your database.yml file set in your Heroku dashboard at: https://dashboard.heroku.com/apps/yourappname/settings and click on Reveal Config Vars. This is where you will set the needed vars as this is where your app will load them from. Set either the vars in my first example, or a single one as in my 2nd example. Either way should work.

Make sure you have the pg gem in your production group in Gemfile. And make sure you've set your environment variables on your heroku server.

Rails db:create using PostgreSQL return PG::ConnectionBad: could not connect to server: Connection refused

You need to start the Postgres server for it to start accepting connections. Currently, you've probably downloaded Postgres, but not started it.

You could follow instructions here to learn how to start it on Windows: https://tableplus.com/blog/2018/10/how-to-start-stop-restart-postgresql-server.html

---

Related Topics