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

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 got the same problem after updating my mac on Osx Movaje.

i found this solution :

Try first the bellow command line in your terminal :

<!-- language: lang-html -->

brew services restart postgresql
<!-- end snippet -->

If nothing change :

<!-- language: lang-html -->

ps aux | grep postgres
<!-- end snippet -->

If still nothing change :
<!-- language: lang-html -->

ls -ls | grep post
<!-- end snippet -->

Last command to fix it, removed the postgres lock file by executing from root :
<!-- language: lang-html -->

rm /usr/local/var/postgres/postmaster.pid
<!-- end snippet -->

and then :
<!-- language: lang-html -->

brew services restart postgresql
<!-- end snippet -->

From berziiii :

[To see links please register here]

Hope that will help :)

Regards !!

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.

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`

I had to reinstall my postgres, great instructions outlined here:

[To see links please register here]

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.

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.

As described by @Magne, the error `PG::ConnectionBad - could not connect to server: Connection refused` can be presented following a [major/minor version](

[To see links please register here]

) 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](

[To see links please register here]

) 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

[To see links please register here]

with some additions)*

You would need to **restart the Postgresql Server**.

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

sudo service postgresql restart

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

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

[To see links please register here]

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

[To see links please register here]

---

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`