PG::ConnectionBad - could not connect to server: Connection refused
9.5 -> 9.6
9 -> 10
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.
You can check if this is the problem by checking the 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
$ cat /usr/local/var/postgres/PG_VERSION
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.
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/ \
-d flag specifies the current data directory
-b specifies the old binary
Step 5. Move the old data directory out of the way
$ mv /usr/local/var/postgres /usr/local/var/postgres9.5
$ mv /usr/local/var/postgres9.6 /usr/local/var/postgres
$ 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 youre 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 was exactly the problem I experienced. This solution worked perfectly. Thank you.