How to Reset PostgreSQL with a Fresh Installation

Encountering PostgreSQL errors concerning can be quite frustrating. This guide outlines a brute force method to resolve them.

Instructions

Are you encountering an error similar to this?

$ psql -v

psql: error: 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"?

Or this?

$ pg_ctl start

waiting for server to start.... stopped waiting
pg_ctl: could not start server
Examine the log output.

Or perhaps this?

Library not loaded: /usr/local/opt/openssl/lib/libssl.1.0.0.dylib (LoadError)
  Referenced from: /Users/brianbirdwell/dev/landcentral/lc-admin/vendor/ruby/2.1.0/extensions/x86_64-darwin-18/2.1.0-static/pg-0.18.4/pg/pg.bundle
  Reason: image not found - /Users/brianbirdwell/dev/landcentral/lc-admin/vendor/ruby/2.1.0/extensions/x86_64-darwin-18/2.1.0-static/pg-0.18.4/pg/pg.bundle

You likely need to uninstall your existing versions of PostgreSQL and reinstall them. Below are the steps to fully uninstall PostgreSQL and reinstall it:

1. Uninstall PostgreSQL from Homebrew

First, cease all running versions of PostgreSQL:

$ brew services stop postgresql

Next, unlink PostgreSQL:

$ brew unlink postgresql

If you encounter an error, try:

$ brew services unlink postgresql

Lastly, uninstall PostgreSQL:

$ brew uninstall postgresql

2. Delete the Configuration Files Related to PostgreSQL

Change directories to your root:

$ cd ~/

Run a search for PostgreSQL in your usr directory:

$ find /usr -name postgresql

Remove the directories appearing in that search (avoid removing results from rbenv, bundler, or your dev directory):

$ rm -rf /usr/local/var/postgres

To ensure everything is removed, conduct another search:

$ find /usr -name postgresql

3. Reinstall PostgreSQL, Recreate Your Databases, and Repopulate Them

You are now prepared to reinstall PostgreSQL:

$ brew install postgresql

$ brew link --force postgresql

$ brew services start postgresql

$ brew services list

If brew services list displays your chosen version of postgresql as initiated in green, everything is in order!

To further confirm, check your PostgreSQL version:

$ psql -V

This command should return the version number.

NOTE: This process will erase all the PostgreSQL databases on your system!

You must set up the databases again using rake db:create in your projects, and populate the database using rake db:seed or a local snapshot:

$ psql -U postgres -h localhost <your_database_name> < ~/dev/databases/<database_snapshot_name>.sql

Potential Issues:

You might see an error like:

Library not loaded: /usr/local/opt/postgresql/lib/libpq.5.dylib

This error typically arises when the PostgreSQL adapter gem cannot find the PostgreSQL version that was removed or is inactive. You can address this issue in two ways:

First, try forcibly reinstalling the gems locally, updating the PostgreSQL connection:

$ bundle install --force

If the above fails, locate the actual directory housing your libpq.dylib file and create a symlink (ln -s) from the failing file reference to the actual dylib file. This solution should be your last resort as it is substantially more fragile:

$ ln -s /usr/local/opt/postgresql/lib/libpq.5.dylib /usr/local/opt/postgresql/lib/libpq.5.dylib

IMPORTANT! Our projects may utilize differing PostgreSQL versions, potentially triggering errors when swapping between versions. If you know of a superior method to manage this, feel free to update this guide!