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

Every time I run my rails 4.0 server, I get this output.

Started GET "/" for 127.0.0.1 at 2013-11-06 23:56:36 -0500

PG::ConnectionBad - could not connect to server: Connection refused
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 5432?
could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 5432?
could not connect to server: Connection refused
Is the server running on host "localhost" (fe80::1) and accepting
TCP/IP connections on port 5432?
:
 activerecord (4.0.0) lib/active_record/connection_adapters/postgresql_adapter.rb:825:in `connect'
 activerecord (4.0.0) lib/active_record/connection_adapters/postgresql_adapter.rb:542:in `initialize'
 activerecord (4.0.0) lib/active_record/connection_adapters/postgresql_adapter.rb:41:in `postgresql_connection'
 activerecord (4.0.0) lib/active_record/connection_adapters/abstract/connection_pool.rb:440:in `new_connection'
 activerecord (4.0.0) lib/active_record/connection_adapters/abstract/connection_pool.rb:450:in `checkout_new_connection'
 activerecord (4.0.0) lib/active_record/connection_adapters/abstract/connection_pool.rb:421:in `acquire_connection'
 activerecord (4.0.0) lib/active_record/connection_adapters/abstract/connection_pool.rb:356:in `block in checkout'
 /System/Library/Frameworks/Ruby.framework/Versions/2.0/usr/lib/ruby/2.0.0/monitor.rb:211:in `mon_synchronize'
 activerecord (4.0.0) lib/active_record/connection_adapters/abstract/connection_pool.rb:355:in `checkout'
 activerecord (4.0.0) lib/active_record/connection_adapters/abstract/connection_pool.rb:265:in `block in connection'
 /System/Library/Frameworks/Ruby.framework/Versions/2.0/usr/lib/ruby/2.0.0/monitor.rb:211:in `mon_synchronize'
 activerecord (4.0.0) lib/active_record/connection_adapters/abstract/connection_pool.rb:264:in `connection'
 activerecord (4.0.0) lib/active_record/connection_adapters/abstract/connection_pool.rb:546:in `retrieve_connection'
 activerecord (4.0.0) lib/active_record/connection_handling.rb:79:in `retrieve_connection'
 activerecord (4.0.0) lib/active_record/connection_handling.rb:53:in `connection'
 activerecord (4.0.0) lib/active_record/migration.rb:792:in `current_version'
 activerecord (4.0.0) lib/active_record/migration.rb:800:in `needs_migration?'
 activerecord (4.0.0) lib/active_record/migration.rb:379:in `check_pending!'
 activerecord (4.0.0) lib/active_record/migration.rb:366:in `call'
 actionpack (4.0.0) lib/action_dispatch/middleware/callbacks.rb:29:in `block in call'
 activesupport (4.0.0) lib/active_support/callbacks.rb:373:in `_run__1613334440513032208__call__callbacks'
 activesupport (4.0.0) lib/active_support/callbacks.rb:80:in `run_callbacks'
 actionpack (4.0.0) lib/action_dispatch/middleware/callbacks.rb:27:in `call'
 actionpack (4.0.0) lib/action_dispatch/middleware/reloader.rb:64:in `call'
 actionpack (4.0.0) lib/action_dispatch/middleware/remote_ip.rb:76:in `call'
 better_errors (0.9.0) lib/better_errors/middleware.rb:84:in `protected_app_call'
 better_errors (0.9.0) lib/better_errors/middleware.rb:79:in `better_errors_call'
 better_errors (0.9.0) lib/better_errors/middleware.rb:56:in `call'
 actionpack (4.0.0) lib/action_dispatch/middleware/debug_exceptions.rb:17:in `call'
 actionpack (4.0.0) lib/action_dispatch/middleware/show_exceptions.rb:30:in `call'
 railties (4.0.0) lib/rails/rack/logger.rb:38:in `call_app'
 railties (4.0.0) lib/rails/rack/logger.rb:21:in `block in call'
 activesupport (4.0.0) lib/active_support/tagged_logging.rb:67:in `block in tagged'
 activesupport (4.0.0) lib/active_support/tagged_logging.rb:25:in `tagged'
 activesupport (4.0.0) lib/active_support/tagged_logging.rb:67:in `tagged'
 railties (4.0.0) lib/rails/rack/logger.rb:21:in `call'
 quiet_assets (1.0.2) lib/quiet_assets.rb:18:in `call_with_quiet_assets'
 actionpack (4.0.0) lib/action_dispatch/middleware/request_id.rb:21:in `call'
 rack (1.5.2) lib/rack/methodoverride.rb:21:in `call'
 rack (1.5.2) lib/rack/runtime.rb:17:in `call'
 activesupport (4.0.0) lib/active_support/cache/strategy/local_cache.rb:83:in `call'
 rack (1.5.2) lib/rack/lock.rb:17:in `call'
 actionpack (4.0.0) lib/action_dispatch/middleware/static.rb:64:in `call'
 railties (4.0.0) lib/rails/engine.rb:511:in `call'
 railties (4.0.0) lib/rails/application.rb:97:in `call'
 rack (1.5.2) lib/rack/content_length.rb:14:in `call'
 thin (1.5.1) lib/thin/connection.rb:81:in `block in pre_process'
 thin (1.5.1) lib/thin/connection.rb:79:in `pre_process'
 thin (1.5.1) lib/thin/connection.rb:54:in `process'
 thin (1.5.1) lib/thin/connection.rb:39:in `receive_data'
 eventmachine (1.0.3) lib/eventmachine.rb:187:in `run'
 thin (1.5.1) lib/thin/backends/base.rb:63:in `start'
 thin (1.5.1) lib/thin/server.rb:159:in `start'
 rack (1.5.2) lib/rack/handler/thin.rb:16:in `run'
 rack (1.5.2) lib/rack/server.rb:264:in `start'
 railties (4.0.0) lib/rails/commands/server.rb:84:in `start'
 railties (4.0.0) lib/rails/commands.rb:78:in `block in <top (required)>'
 railties (4.0.0) lib/rails/commands.rb:73:in `<top (required)>'
 bin/rails:4:in `<main>'

I'm running Mavericks OS X 10.9 so I don't know if that's the problem. I've tried everything I could but nothing seems to work. I've uninstalled and install both postgres and the pg gem multiple times now.

This is my database.yml file

development:
  adapter: postgresql
  encoding: unicode
  database: metals-directory_development
  pool: 5
  username: 
  password: 
  template: template0
  host: localhost
  port: 5432

test: &test
  adapter: postgresql
  encoding: unicode
  database: metals-directory_test
  pool: 5
  username: 
  password: 
  template: template0
  host: localhost
  port: 5432

staging:
  adapter: postgresql
  encoding: unicode
  database: metals-directory_production
  pool: 5
  username:
  password:
  template: template0
  host: localhost

production:
  adapter: postgresql
  encoding: unicode
  database: metals-directory_production
  pool: 5
  username:
  password:
  template: template0
  host: localhost

cucumber:
  <<: *test

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

You would need to restart the Postgresql Server.

If you are using ubuntu you can restart Postgresql by following command

sudo service postgresql restart

I have managed to solve the problem by following the Chris Slade's answer, but to restart the server, I had to use the following commands:

launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

that I found here (pjammer's answer down at the bottom)

Do you have postgresql installed within your system? If not, then watch Install postgresql. After you successfully integrate postgresql into your system you can type something like that in your system terminal:

which psql
#=> /usr/bin/psql

After that you need to create a user and database in postgresql like this:

sudo su - postgres
psql

Then you can see the following within your terminal

postgres=#

Type there:

CREATE USER yourname WITH PASSWORD 'passwordhere';
CREATE DATABASE metals-directory_production  WITH OWNER yourname;
GRANT ALL PRIVILEGES ON DATABASE metals-directory_production TO yourname;

After you do this, then you need to correct your database.yml. Probably you need something like that:

development:
  adapter: postgresql
  encoding: unicode
  database: metals-directory_development
  pool: 5
  username: yourname
  password: passwordhere   ### password you have specified within psql
  host: localhost
  port: 5432               ### you can configure it in file postgresql.conf

Also if you have problems with postgresql it is good idea to check pg_hba.conf

1. Uninstall pg:

   gem uninstall pg

2. Uninstall postgres:

   brew uninstall postgres

3. Nuke the postgres folder which might be lingering with a bunch of stale stuff it in:

   rm -rf /usr/local/var/postgres

4. Reboot (maybe unnecessary)

5. Reinstall pg:

   brew install postgres

6. My comment in Chris Slade's answer starts pg the hard way, now I use brew services which has simplified my life in so many ways:

   brew install services

7. And start pg with it:

   brew services start postgresql

8. Reinstall the gem:

   gem install pg

And bobsyouruncle.

To fix these type of issues with Postgres and to work with Postgres on Mac OSX, this is probably the BEST and the EASIEST solution that I have found so far:

http://postgresapp.com/

Just download, install and be happy :)

check the file postgresql.conf (on ubuntu is in /etc/postgresql/X.X/main/postgresql.conf ) and look for the line that says:

listen_addresses="localhost"

try change it to:

listen_addresses="*"

it would be accepting every IP's, next check the line that says:

port=5432

and check if is the same port of your database.yml, by default on my postgresql-9.2 use 5433 instead 5432, don't forget to restart the postgres server,

Good Luck!

As described by @Magne, the error PG::ConnectionBad - could not connect to server: Connection refused can be presented following a major/minor version upgrade (e.g. 9.5 -> 9.6 or 9 -> 10) of PostgreSQL.

I got this error after having run brew upgrade postgresql after the release of PostgreSQL version 9.6. The problem is that major/minor version upgrades require additional steps to migrate old date to the new version.

How to check if this is your problem

You can check if this is the problem by checking the latest brew formula PostgreSQL version installed with homebrew...

$ brew info postgresql

/usr/local/Cellar/postgresql/9.5.4_1 (3,147 files, 35M)
Poured from bottle on 2016-10-14 at 13:33:28
/usr/local/Cellar/postgresql/9.6.1 (3,242 files, 36.4M) *
Poured from bottle on 2017-02-06 at 12:41:00

...and then comparing it to the current PG_VERSION

$ cat /usr/local/var/postgres/PG_VERSION
9.5

If the PG_VERSION is less than the latest brew formula and the difference is a major/minor version change, then this is probably your problem.

How to fix (i.e. how to upgrade the data)

Instructions below are for an upgrade from 9.5 to 9.6. Change the version numbers as appropriate for your own upgrade

Step 1. Make sure PostgreSQL is switched off:

$ launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
# or, with Homebrew...
$ brew services stop postgresql

Step 2. Make a new pristine database:

$ initdb /usr/local/var/postgres9.6 -E utf8

Step 3. Check what the old and new binary versions are:

$ ls /usr/local/Cellar/postgresql/
9.5.3	9.5.4	9.6.1

Note that in this example I am upgrading from 9.5.4 binary to 9.6.1 binary

Step 4. Migrate the current data to the new database using the pg_upgrade utility.

$ pg_upgrade \
  -d /usr/local/var/postgres \
  -D /usr/local/var/postgres9.6 \
  -b /usr/local/Cellar/postgresql/9.5.4/bin/ \
  -B /usr/local/Cellar/postgresql/9.6.1/bin/ \
  -v

• -d flag specifies the current data directory
• -D flag specifies the new data directory to be created
• -b specifies the old binary
• -B specifies the new binary we're upgrading to

Step 5. Move the old data directory out of the way

$ mv /usr/local/var/postgres /usr/local/var/postgres9.5

Step 6. Move newly created data directory to where PostgreSQL expects it to be

$ mv /usr/local/var/postgres9.6 /usr/local/var/postgres

Step 7. Start PostgreSQL again

$ launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
# or, if you're running a current version of Homebrew
$ brew services start postgresql

Step 8. If you’re using the pg gem for Rails, you should recompile by uninstalling and reinstalling the gem (skip this step if you're not using the pg gem)

$ gem uninstall pg
$ gem install pg

Step 9.(optional) After you've reassured yourself that everything is working OK, you can run regain some disk space with the following command:

brew cleanup postgresql

...and if you're feeling really brave you can delete the old PostgreSQL data directory with the following command

rm -rf /usr/local/var/postgres9.5/

(This answer is based on an excellent blog post https://keita.blog/2016/01/09/homebrew-and-postgresql-9-5/ with some additions)

I got the same problem after updating my mac on Osx Movaje.

i found this solution :

Try first the bellow command line in your terminal :

brew services restart postgresql

If nothing change :

ps aux | grep postgres

If still nothing change :

ls -ls | grep post

Last command to fix it, removed the postgres lock file by executing from root :

rm /usr/local/var/postgres/postmaster.pid

and then :

brew services restart postgresql

From berziiii : https://github.com/ga-wdi-boston/capstone-project/issues/325

Hope that will help :)

Regards !!

As suggested above, I just opened up the Postgres App on my Mac, clicked Open Psql, closed the psql window, restarted my rails server in my terminal, and it was working again, no more error.

Trust the elephant: http://postgresapp.com/

This is what really helped me.

$ cd /usr/local/var/postgres/
$ rm postmaster.pid

Reference: http://alumni.lewagon.org/questions/60

put host: localhost in database.yml file and run this command:

rake db:create db:migrate  

If you hit this problem after doing a brew upgrade which upgraded postgres to a new major version (f.ex 9.3.0 to 9.4.0 or higher), then do this:

>@dmitrygusev's fix from https://github.com/Homebrew/homebrew/issues/35240 > >Following official [Postgresql] migration guide helped: > > brew switch postgres 9.3.5 # presuming you already installed 9.4.1 > pg_dumpall > outputfile > launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist > mv /usr/local/var/postgres /usr/local/var/postgres.old > brew switch postgres 9.4.1 > initdb -D /usr/local/var/postgres > psql -d postgres -f outputfile > > That's all. Check if import went well, then delete backups: > > rm outputfile > rm -Rf /usr/local/var/postgres.old

The issue here is that on a major version upgrade of postgres, it's necessary to recreate/migrate your database. And possibly chown directories or manually call initdb.

See also: https://stackoverflow.com/questions/24379373/how-to-upgrade-postgres-from-9-3-to-9-4-without-losing-data

---

Some other tips, that might come in handy, in case you're not using Homebrew:

Stop PG server manually:

pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log stop

Start PG server manually:

pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

Locate your postgres file it could be in /usr/local/var/postgres/ or in /usr/var/postgres/ and then delete the postmaster.pid file present in that folder.

This occurred for me after upgrade Postgres (through Homebrew). I upgraded from version 12 to 13.

If this is the case for you try running postgres -D /usr/local/var/postgres and check the output. In my case it stated database files are incompatible with server.

You might get an error message that postgres.old already exists

Error: /usr/local/var/postgres.old already exists!

To fix that, just remove that file

rm -rf /usr/local/var/postgres.old

Fixing the database was as simple as running:

brew postgresql-upgrade-database

If removing postmaster.pid does not resolve the issue, this worked for me:

cd /usr/local/var/
rm -rf postgres/
mkdir postgres
initdb --locale=C -E UTF-8 postgres/
brew services restart postgresql

Credit to @spirito_libero 's solution on this thread.

Mac users with the Postgres app may want to open the application (spotlight search Postgres or find the elephant icon in your menu bar). Therein you may see a red X with the message: "Stale postmaster.pid file". Unfortunately a spotlight search won't show the location of this file. Click "Server Settings...", and in the dialog box that opens, click the "Show" button to open the Data Directory. Navigate one folder in (for me it was "var-10"), and delete the postmaster.pid file.

Go back to the Postgres app and click the Start button. That red X should turn into a green check mark with the message "Running". Now you should be able to successfully run Rails commands like rails server in the terminal.

I just had this problem tonight, working on a rails application I've been working on for a while. My problem simply came down to the fact that my postgresql server was not running.

I went to the top of the screen (I'm on a Mac) and clicked the little elephant icon and clicked 'Start'.

Turns our the server wasn't on.

Hopefully this provides a simple solution for someone.

I have tried all of the answers above and it didn't work for me.

In my case when I chekced the log on /usr/local/var/log/postgres.log. It was fine no error. But I could see that it was listening my local IPV6 address which is "::1"

In my database.yml It was like this

host:     <%= ENV['POSTGRESQL_ADDON_HOST'] || '127.0.0.1' %>

I changed it by

host:     <%= ENV['POSTGRESQL_ADDON_HOST'] || 'localhost' %>

and then it worked

I had the same problem, this explanation solved it for me: http://blog.55minutes.com/2013/09/postgresql-93-brew-upgrade/

key step was looking at the tail of my /usr/local/var/postgres/server.log , that let me see what the real problem was, which was that i hadn't fully completed the process of upgrading PostgreSQL

@Chris Slade's answer helped me.

I wrote a little script to kill those remaining processes if usefull:

kill_postgres() {
  if [[ $* -eq "" ]]; then
    echo "Usage: 'kill_postgres <db_name>' to kill remaining instances (Eg. 'kill_postgres my_app_development')"
  else
    gksudo echo "Granted sudo"
    pids="$(ps xa | grep postgres | grep $* | awk '{print $1}' | xargs)"
    if [[ $pids -eq "" ]]; then
      echo "Nothing to kill"
    else
      for pid in "${pids[@]}"
      do
        echo "Killing ${pid}"
        sudo kill $pid
        echo "Killed ${pid}"
      done
      kill_postgres $*
    fi
  fi
}

My problem was in my application.yml file. My database url on heroku was not using port 5342. Check your heroku config var DATABASE_URL. Make sure that matches exactly with was in your application.yml for the applicable database.

I just run this command sudo service postgresql restart and everything worked again.

I had the same problem in production (development everything worked), in my case the DB server is not on the same machine as the app, so finally what worked is just to migrate by writing:

bundle exec rake db:migrate RAILS_ENV=production

and then restart the server and everything worked.

I know this is late but might help somebody. I was having the same issue. Turns out that I'd two versions of postgres 9.1 and 9.5. I uninstalled 9.1 and 9.5 and installed 9.5 again and it worked for me.

I had the same problem. I Check the last line of PostgreSQL log files in /var/log/postgresql. There was an unrecognized configuration parameter in file /etc/postgresql/9.5/main/postgresql.conf. Commenting the error line in postgresql.conf resolved my problem.

The Homebrew package manager includes launchctl plists to start automatically. For more information run brew info postgres.

Start manually:

pg_ctl -D /usr/local/var/postgres start

Stop manually:

pg_ctl -D /usr/local/var/postgres stop

Start automatically:

"To have launchd start postgresql now and restart at login:"

brew services start postgresql

In my case there was permissions issue.

When I see logs I found out the issue, Run

cat /usr/local/var/log/postgres.log 

I found out

2020-07-17 15:08:47.495 PKT [16282] FATAL:  data directory "/usr/local/var/postgres" has invalid permissions
2020-07-17 15:08:47.495 PKT [16282] DETAIL:  Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).

I just ran

sudo chmod -R 700 /usr/local/var/postgres

It worked.

I stopped the rails server, ran rake db:migrate and started my rails s.

I ran into this error after following a brew upgrade in which postgresql was updated. I found exactly how to fix my problem from this great post. I was able to get postgres back up and running and even migrated over all my existing databases. https://coderwall.com/p/ti4amw/how-to-launch-postgresql-after-upgrade

I just had this problem and none of the suggested solutions worked for me. After a lot of googling, I did find a solution. This is what worked for me.

First, I had to run this command to start the server and I am guessing set the location of config file.

pg_ctl -D /usr/local/var/postgres start && brew services start postgresql

Then I ran this command to access postgres

psql postgres

And at the postgres prompt then I typed "\du" to list the roles

postgres=# \du

The postgres role was missing so I had to create it with this command

CREATE ROLE POSTGRES WITH SUPERUSER CREATEDB CREATEUSER CREATEROLE REPLICATION BYPASSRLS ;

That solved my problem and I hope this helps someone else.

You don't have to delete the postmaster.pid file, as this might be inviting data corruption.

Option? Simply kill the process (do not use kill -9, just an ordinary kill will do).

Then just restart the postgres server and you're good to go!

Here are the steps to achieve that:

1. Locate and open the postmaster.pid file (mine is on Mac Sierra)

   vi ~/Library/Application\ Support/Postgres/var-10/postmaster.pid

2. Copy the PID - it's the number on the first line of the postmaster.pid file

3. Kill the process with kill PID, eg, if my PID is 381, I will do kill 381

4. Restart Postres - if using brew, do brew services start postgresql. Or if using postgresapp, just simply click the start button

I had to reinstall my postgres, great instructions outlined here: https://medium.com/@zowoodward/effectively-uninstall-and-reinstall-psql-with-homebrew-on-osx-fabbc45c5d9d

Then I had to create postgres user: /usr/local/opt/postgres/bin/createuser -s postgres

This approach will clobber all of your local data so please back up your data if needed.

For MacOS I use this:

brew info postgres

And in output in the most end, I see:

...
To have launchd start postgresql now and restart at login:
  brew services start postgresql
Or, if you don't want/need a background service you can just run:
  pg_ctl -D /usr/local/var/postgres start

So I just use this command pg_ctl -D /usr/local/var/postgres start and psql begin work.

For me, it was really simple: I had a stale postmaster.pid file.

Simply deleting and restarting postgres fixed it for me:

rm '/Users/<username>/Library/Application Support/Postgres/var-10/postmaster.pid'