Home » Odeon Blogs » Calvin, Weaver »

OS X Lion PostgreSQL

OS X Lion PostgreSQL

So, Apple has decided to bundle PostgreSQL with their stock Lion install.

And should you also install PostgreSQL via MacPorts (or even Homebrew), this is the little hiccup you will run into when you try connecting to your local postgresql db server.

  1. $ psql -d postgres -U postgres

  2. psql: could not connect to server: Permission denied 

  3. Is the server running locally and accepting 

  4. connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

To solve this problem, we will need to unload the current postgresql server and load up the new server.

So, simply:
  1. $ DIR=/opt/local/var/db/postgresql90/defaultdb

  2. $ sudo launchctl unload -w /Library/LaunchDaemons/org.macports.postgresql90-server.plist

  3. $ sudo mkdir -p $DIR

  4. $ sudo chown postgres:postgres $DIR

  5. $ sudo su postgres -c "/opt/local/lib/postgresql90/bin/initdb -D $DIR"

  6. $ sudo launchctl load -w /Library/LaunchDaemons/org.macports.postgresql90-server.plist

  7. $ psql -h localhost -U postgres
And you should happily see your postgresql connection work perfectly fine on your terminal -
  1. psql (9.0.4)

  2. Type "help" for help.

  3. 

  4. postgres=#

Say "Hip Hip Hooray".


Category: postgresql


Tagged as: lion mac postgresql

Discussion

  1. Terry on Sep 05, 2011 - 14:18 said:

    Thanks for posting this. It helped me resolve my installation issue.


  2. Chris Jackson on Sep 05, 2011 - 15:15 said:

    Is there any reason why one could NOT use the postgres installation already bundled with osx lion? Would there be any reason not to? I haven't tried using it yet but plan on doing so unless I hear otherwise.


  3. Stefan Talpalaru on Sep 05, 2011 - 18:40 said:

    The first reason that comes to mind is that the bundled postgres is an old version that cannot be upgraded because OS X lacks a package manager.


  4. Calvin Cheng on Sep 07, 2011 - 2:59 said:

    @Chris: @Stefan's right. Lion bundles 8.4 - reference http://www.jonathandean.com/2011/08/postgresql-8-4-on-mac-os-x-10-7-lion/

    I prefer 9.0 or 9.1 hence the installation via MacPorts.

    Regards.


  5. mikegt on Sep 20, 2011 - 19:39 said:

    Can anyone suggest how the fix would work for homebrew installed Postgres.

    I'm converting from Win to OSx, so I'm a little clueless.

    Thanks,

    Mike G.


  6. Calvin Cheng on Sep 21, 2011 - 1:02 said:

    Hi Mike,

    Here's a solution by a homebrew user on Lion:-

    http://nextmarvel.net/blog/2011/09/brew-install-postgresql-on-os-x-lion/

    Hope that helps :-)

    Cheers!


  7. mikegt on Sep 21, 2011 - 15:50 said:

    Thanks Calvin, but that link's solution didn't seem to do anything (I probably did something wrong since I'm new to OS X).

    But I finally figured out how to resolve the conflict between the Brew installed Postgres and the Postgres that comes with Lion:
    1. I had to edit /etc/paths to put /usr/local/bin at the top of the list.
    2. Then uninstall & reinstall pg gem
    3. Then follow instructions in >brew info postgres to configure Postgres to automatically load/start

    I had to read these two posts:
    http://tammersaleh.com/posts/installing-postgresql-for-rails-3-1-on-lion
    http://stackoverflow.com/questions/6770649/repairing-postgresql-after-upgrading-to-osx-10-7-lion
    But I couldn’t find a .bash_profile, so went with Greg’s instructions on that stack overflow question to “edit etc/paths and make sure /user/local/bin is on the top” and the run
    >gem uninstall pg
    >gem install pg

    This link does a good job explaining how to modify the /etc/paths file which was locked on my iMac: http://hathology.com/2008/06/how-to-edit-your-path-environment-variables-on-mac-os-x/

    Lastly, figured out that the Brew installed version of Postgres just wasn’t starting and executed
    >brew info postgres
    to read the configuration commands below which finally did the trick:
    >mkdir -p ~/Library/LaunchAgents
    >cp /usr/local/Cellar/postgresql/9.0.4/org.postgresql.postgres.plist ~/Library/LaunchAgents/
    >launchctl load -w ~/Library/LaunchAgents/org.postgresql.postgres.plist

    Good luck to everyone. This was one tricky thing to figure out.


  8. Calvin Cheng on Sep 21, 2011 - 19:19 said:

    Fantastic notes, Mike! Appreciate you sharing the details - a lot!


  9. mikegt on Sep 21, 2011 - 21:20 said:

    You're welcome.
    I should clarify my long comment: Steps 1-3 above is all it took to solve the problem.
    Then I simply go into more detail on how/why I did steps 1-3 (ie: everything I wrote after “I had to read these two posts…)

    Do you guys do Rails development? I'm trying to set up a Rails & Solr based site.


  10. Calvin Cheng on Sep 21, 2011 - 21:35 said:

    Hello Mike,

    We do use a rails application in-house for project management and issue tracking purposes but our main focus is python/django for web apps.

    You should really consider django over rails! :-)

    So here's some helpful apps you might consider for your full-text-search backend:
    * https://github.com/sophilabs/django-solr
    * https://github.com/toastdriven/django-haystack

    And our own postgresql-specific full-text-search implementation written by @Stefan:-
    * https://github.com/odeoncg/django-fts-odeon

    Cheers!
    Calvin


  11. mikegt on Sep 21, 2011 - 23:08 said:

    Cool, thanks. I've heard django has a great pre-built admin interface. I'll check out those links and be in touch.


  12. emil tin on Jan 31, 2012 - 13:04 said:

    when i looked in the log file i saw:
    FATAL: data directory "/usr/local/var/postgres" has group or world access
    DETAIL: Permissions should be u=rwx (0700).

    so i simply ran:
    sudo chmod 700 /usr/local/var/postgres




Leave a Comment :

(required)


(required)




(required)




(required)






Leave a Comment


Page generated in: 0.23s