Postgresql.app vs Homebrew

Question

I am pretty new to RoR and I am looking to deploy my next app to heroku. I want my development & test environment to match my production environment for the smoothest transition. As such, I went about installing postgresql on my system. This process has been more than frustrating and I am beyond confused. I followed countless tutorials to no avail and it seems like many have conflicting information. Here is what I know:

There are many ways to install postgresql. Common options are macports, homebrew, fink, Postgresql.app, or enterpriseDB. Once you install one of the previous, you have to create your rails app and either run:

rails new <app_name> -d postgresql

or run the standard 'rails new ' and then change 'sqlite3' to 'pg' in your gemfile. Then, if I am correct, you actually have to create your own database in the command-line by doing this:

$ psql
$ CREATE DATABASE your_database_name;

Then, edit your database.yml to follow something close to this:

development:
  adapter: postgresql
  encoding: unicode
  database: <your_database_name>
  host: localhost
  pool: 5
  username: <username>
  password:

Once that is done, all should be well. However, I am having problems. I actually have it working using the above process, but I am confused as to how it is running. During hours of trying to get all this to work, I installed the macports, homebrew, and postgresql.app. However, whenever I try to interact with the database (such as a 'rake db:migrate') without postgresql.app running, I get this error:

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?

If I fire it back up, everything is fine. Ok, so that leads me to assume my system is using postgresql.app to run postresql. With that information, I confidently uninstall macports and hombrew installations of postgresql. However, upon doing so, I get this error when trying to interact with the database:

Library not loaded: /usr/local/lib/libpq.5.4.dylib (LoadError)

I reinstall macports and still get the same error. Then I reinstall homebrew and the error goes away. I then uninstall macports again and all is still fine. It appears that postgresql.app and my homebrew installation are somehow dependent upon each other. If I am correct, they should be able to run independently of each other because each is a full install of postgreql. I am pretty much out of ideas at this point. Any and all input on how this process is done what what is going on would be VERY APPRECIATED.

EDIT

$ which psql

shows:

/usr/local/bin/psql

and

ls -l /usr/local/bin/psql

shows:

lrwxr-xr-x  1 robertquinn  admin  35 Jul 29 17:32 /usr/local/bin/psql -> ../Cellar/postgresql/9.1.4/bin/psql 

Answer 1

Homebrew is definitely the way to go. After years of using Macports, I find Homebrew much easier to use, upgrade and maintain.

If you are new to Homebrew, sometimes when you install something, there will be output printed at the end of the install which gives you additional tasks. Sometimes the install asks you to manually create new links, sometimes it asks you to redo links with the new cellar-located executables. Make sure you read that output and follow those after-install instructions.

Once those instructions are followed, open a new terminal window to make sure your settings are respected.

Also, if you use rails new app_name -D postgresql, your database.yml file should already be configured correctly, unless of course you've configured your postgres instance to use a username and password.

To make starting and stopping postgres easier, i added these commands to my .profile file

# postgres
alias pg_start="pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start"
alias pg_stop="pg_ctl -D /usr/local/var/postgres stop -s -m fast"