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

Question

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

Phrasing

try adding 'host: localhost' in your database.yml, got this from https://mcmap.net/q/48938/-postgres-permission-denied-on-mac-os-x – Jeminah

@trajan I did that already. I even used that thread when I first started researching for a solution. I've posted my database.yml file. You can take a look at it. Maybe it might help. – Phrasing

show your pg_hba.conf – Greta

have you taken a look at this? – Lianaliane

@Lianaliane I tried what it said and no luck. – Phrasing

@Greta where do I find my pg_hba.conf? – Phrasing

Based on the error message, pg_hba.conf is not the problem. The error basically indicates that postgres is not running at all. Which leads to the question: how are you launching it? And when launched, what does it say on the screen and in the log? – Blackstone

See also #19777961 – Languet

Don't forget to turn the postgres app on if you're using that. – Gpo

You probably restarted your computer and forgot to launch the Postgres app. – Ingleside

Thanks for reminding me turn on the app @PavanKatepalli! Good call ;) – Magus

If you're here because of the OSX Security Update 2017-004 here's your answer: gist.github.com/giannisp/ebaca117ac9e44231421f04e7796d5ca – Karolynkaron

after upgrading to postgresql 11 on Ubuntu 19.04 make sure that your postgresql server is running on 127.0.0.1:5432 with sudo ss -tunelp | grep 5432. Start the server with either sudo systemctl restart postgresql or sudo service restart postgresql. I also had to purge and delete the whole database with sudo apt purge postgresql before installing postgresql 11 – Lyublin

I tend to get this when I've run a brew upgrade that replaces the version of postgresql version my DB needs. I validate the error's source by checking that the end of my postgres.log has errors about about version number incompatibility and then reinstall the version number that the error says I need. – Pember

Answer 1

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

Relational

#2 is a great suggestion: "Check server.log". My error was actually caused by upgrading to Yosemite, the server log said: FATAL: could not open directory "pg_tblspc": No such file or directory. This answer helped me with that problem #25970632 – Ripping

I just upvoted this because it gave me enough to fix the problem scorched earth style. I uninstalled pg (gem uninstall pg), uninstalled postgres (brew uninstall postgres), then nuked the postgres folder which was lingering with a bunch of stale stuff it in (rm -rf /usr/local/var/postgres). After a reboot brew install postgres, ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents, launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist, and ARCHFLAGS="-arch x86_64" gem install pg. I don't know how many other solutions I tried before getting this to work. – Blau

is the postmaster.opts file the same as the postmaster.pid file? – Slashing

vim /usr/local/var/postgres/server.log then type GG to go to the last line, look at it, and press :q to quit. – Essex

The log might show that FATAL: database files are incompatible with server DETAIL: The data directory was initialized by PostgreSQL version 9.3, which is not compatible with this version 9.4.1. where you need to select the old postgres version by using brew switch postgres 9.3.0 like suggested here: https://mcmap.net/q/40449/-homebrew-install-specific-version-of-formula – Essex

@Blau have you considered adding this as a separate answer? I'm going through it now, hoping it works. – Steelman

Yeah, I probably should have because it's buried below the fold now and hard to find. I added a separate answer now, and it uses brew services to simplify things. – Blau

Worked for Django too. For step 4, didn't need to run those launchctl commands, just restarted using ./manage.py runserver again – Oleate

For newer Brew users, the command for #4 is brew services restart postgresql – Geoffrey

Mac OS Sierra - I can't locate a server.log - is it now system.log? – Retainer

May I know why I don't have a server.log file – Dissyllable

For me (MAC, homebrew) the log file was /usr/local/var/log/postgres.log – Pharisee

For anyone on macOS that removed the postmaster.pid file, but it didn't resolve the issue, I had to open Activity Monitor and remove the postgres process from there. Removing the pid file meant that restarting postgres using homebrew wasn't actually shutting down the process itself. – Horbal

I find myself here every time my power goes out hahahaha – Rosenfeld

I checked /usr/local/var/postgres/server.logand it appeared that I had wrong permissions on /usr/local/var/postgres. Changed it to 0700 and it worked – Repairman

I have multiple postgres versions installed, so in my var folder I had to search for the specific postgres version and it's .pid file to remove it. – Ashla

This worked great for me, just wanted to make one note, it does take some time for the server to boot up (~5-10 minutes) in my case, so be patient if you think it isn't working. You can check the status by viewing the contents of postmaster.pid (I used "cat postmaster.pid"), it will go through three different stages in the boot up process if I remember correctly (booting, starting, and finally ready). I was also able to use pg_isready to view the status and once it was accepting connections I was able to connect and boot my rails server again. – Ramonaramonda

Combination worked for me: first rm /usr/local/var/postgres/postmaster.pid then brew services restart postgresql 👍🏻 (I also find myself here everytime my mac panics!) – Billie

On M1 the postmaster.pid is in /opt/homebrew/var/postgresql instead of /usr/local/var/postgres/. Otherwise, everything works fine! – Wille

Answer 2

You would need to restart the Postgresql Server.

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

sudo service postgresql restart

Cage

Thank you 1000 times. I've just upgraded my ubuntu version and this fixed for me. – Cayuse

Althought the question does refer to OSX Mavericks, this slution is handy for Ubuntu installations when something quirky happen in migration or server startup. – Gyration

Answer 3

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)

Nassir

Answer 4

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.

Blau

Be careful with this method... this is a scorched earth approach and will most likely destroy any local dbs you currently have. Have a look at github.com/copiousfreetime/launchy where you might be able to do lunchy stop postgres followed by lunchy start postgres – Verticillate

on ubuntu I believe sudo apt purge postgresql or any other postgresql pachage should be enough and it will destroy everything. – Lyublin

I did this and now get the following error : postgresql_adapter.rb:709:in `rescue in connect': FATAL: database "project3_development" does not exist (ActiveRecord::NoDatabaseError). What should I do ? I can actually see the database in rubymine. – Propagandize

Answer 5

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

Glauce

I have it installed but the location is at /usr/local/bin/psql and when I run sudo -u postgres it says sudo: unknown user: postgres – Phrasing

psql should work if you want to use pg in your app. Try type in terminal psql metals-directory_development. Any results? If not, then try to reinstall postgres try this for example – Glauce

When I run psql metals-directory_development I get psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"? I'm a beginner rails programmer and I have no idea what that means. I've tried reinstalling postgres numerous times but no luck. – Phrasing

Answer 6

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 :)

Leman

Answer 7

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!

Whoso

This was my problem as well. I suspect it happened because I already had 9.3 running when I tried to "upgrade" (i.e. install 9.5). I'm guessing it was trying to be polite and configure itself to run in parallel on a different port rather than take over the existing install/process. – Dolor

Answer 8

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)

Asarum

Answer 9

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 !!

Rodrich

Answer 10

This is what really helped me.

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

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

Vanzant

Answer 11

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/

Frostwork

Answer 12

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.

Helton

Answer 13

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

rake db:create db:migrate  

Hasson

Answer 14

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: How to upgrade PostgreSQL from version 9.6 to version 10.1 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

Essex

Answer 15

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

Ishii

Answer 16

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.

Delainedelainey

This worked but I lost all of my local databases – Latini

Answer 17

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.

Bowlder

This solved it for me. Thanks so much for the solution! It was a process! – Khichabia

Answer 18

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.

Proliferate

Answer 19

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

Alfons

I did the opposite, changed from localhost to 127.0.0.1 and worked – Carlenecarleton

Answer 20

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

Profitsharing

Similar situation here -- the key for me also was to look at server.log. Found my answer here: #25970632 – Rev

Answer 21

@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
}

Mensch

Answer 22

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.

Bibelot

Answer 23

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

Heti

Answer 24

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.

Lianne

Answer 25

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.

Criminal

Answer 26

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.

Jitterbug

Answer 27

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

Hillery

Answer 28

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.

Abjuration

Answer 29

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

Cyanide

Answer 30

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

Toffeenosed

Answer 31

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.

Damian

Answer 32

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

Strophanthus

Answer 33

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.

Colombo

Answer 34

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.

Achaea

Answer 35

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'

Lavella

Answer 36

After spending a lot of time I figured out that there was something wrong with my loop back interface and it wasn't listening on localhost. I passed the following commands to get it working

sudo ifconfig lo0 down

then

sudo ifconfig lo0 up

Crowder

Answer 37

A lot many really thorough answers already, but I'll add what I tried and worked for me as it isn't mentioned here so far (and perhaps it internally does what a lot of the answers suggested).

I simply fired up pgAdmin (it took a while to load at first), entered the local db password, and it connected successfully. Then, I returned to the migration command I was trying to run and voila - all is restored in the universe.

All the best!

Coachandfour