What's with Postgres on OS X?

Question

My goal is to have a Postgres setup that I can use to develop my rails app and then push it to Heroku without having to change the database config file. Seems simple enough, right?

I've seen plenty of things on the internet about PostgreSQL 9.2.x not running on OS X 10.8, and I'm still struggling to get it working with Rails. Not realizing that a version of PSQL shipped with OS X, I set out to install it. (NOTE: I'm sort of debugging in circles, so things may not have worked as I expected.)

I've tried downloading and installing using the official graphical installer listed here, and I rebooted my Mac. I saw an extra user account was created, and I promptly deleted it. (I know, I know, probably what caused some heartache down the line, but it didn't seem reasonable to have an extra user account sitting around.)

Upon running my rails app, it couldn't connect to postgres. I tried running psql from Terminal.

psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?`

I then proceeded to install Postgres with homebrew. I installed homebrew, then postgres. Same thing. I googled a bit and ran into a thread on the Postgres forum. What I got from it was that something changed between OS X 10.7 and 10.8, but that wasn't the cause of my problem.

Next, I tried using self-contained the Postgres.app. Now, I was able to create users and connect to the database. Rails was able to connect to Postgres, but complained that the database that I defined in config.yaml was absent. So I created it - or so I thought.

Running CREATE DATABASE my_db in the "self-contained" version of PSQL didn't work - even though without the self contained app, PSQL would have that socket error. I then tried creating a new user with write access. Nope. The CREATE was silently failing.

I tried adding localhost to my environment variable and it seemed to have solved the port error - but now Rails was having trouble finding Postgres again.

I uninstalled the brew version. I removed the enterprise tools et al. Still no luck. Reboot. Still no luck.

I just uninstalled brew and my config looks like this:

development:
  adapter: postgresql
  database: my_db
  pool: 5
  timeout: 5000
  host: localhost
  port: 5432

Rails says the database doesn't exist.

What do I need to do to get Postgres working with Rails 3.2.6 on Mac OS X 10.8.4 Mountain Lion?

Answer 1

There are a lot of ways of getting to the point you want, but this should work:

First - get rid of any existing copies of PostgreSql as follows:

brew update
brew uninstall postgresql

(assuming no probs with brew update)

I found this removed the instance that ships with osx 10.8

If you still have the Postgres App, drag it out of Applications and into Trash

I would then do a restart, just for the hell of it... (my old Windows experience :-)

Then install the latest postgres (or u can prob specify a version here)

brew install postgres

If it throws up an error relating to something like ossd-uuid, then just do a

brew uninstall ossd-uuid && brew install ossd-uuid

and that should clear it

The homebrew lists some useful commands, eg set postgres to launch on startup etc

Create an initial postgres db by

initdb /usr/local/var/postgres -E utf8

the start postgres by

postgres -D /usr/local/var/postgres

Now at your terminal prompt, type

psql postgres

using your Mac admin password, u should get a prompt thus (\q exits):

postgres=#

A user with your username will have been created (but, from memory, not a password for it) so set that now

postgres=# alter user your-name with password 'anything';

It will return with ALTER ROLE if successful (& just the prompt if not)

Then, if your Rails database is eg dev_db, u can create a user for that thus:

postgres=# create role your_user with login createdb createrole password 'your_pass';

your_user and your_pass will appear in your rails database.yaml file (not sure if createdb & createrole NEED to be there, but thats what I did)

Finally, your database.yaml needs to have development like:

development:
  adapter: postgresql
  encoding: unicode
  database: dev_db
  pool: 5
  timeout: 5000
  host: localhost
  username: your_user
  password: your_pass

Then

rake db:create

And that should create an empty database that your Rails migrations will populate

I hope :-)