OS X Lion 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
    

    
    
13. priordan on Mar 05, 2012 - 12:23 said:

    Thanks, saved me hours of work.

    Much appreciated.

    
    
14. Gabe Garza on Apr 03, 2012 - 7:45 said:

    As hackers grow in numbers, the standard default settings in all software installers have changed.

    What "use to be" meant easy access for hackers, now tighter security measures are put in place.

    With that said and to show that there's nothing wrong with the postgres that comes installed on Lion Server, the way to access how you use to access psql has now changed.

    -- Start Of Test
    lionserver:~ testuser$ sudo psql -d postgres -U postgres

    WARNING: Improper use of the sudo command could lead to data loss
    or the deletion of important system files. Please double-check your
    typing when using sudo. Type "man sudo" for more information.

    To proceed, enter your password, or type Ctrl-C to abort.

    Password:
    psql: FATAL: role "postgres" does not exist
    lionserver:~ testuser$ sudo psql -d postgres -U _postgres
    psql (9.0.5)
    Type "help" for help.

    postgres=#
    -- End of Test

    Two things...

    I don't recommend using sudo but I'm just showing that there's nothing wrong with the installed postgres on Lion Server.
    You need to create users with the correct permissons as to not let them compromise your database.

    Second thing, it's not 'postgres' but '_postgres' now.
    

    
    
15. Kurt Battisti on Apr 15, 2012 - 0:51 said:

    Thank you very much. Was a great help.

    
    
16. Sinan Karasu on May 30, 2012 - 21:09 said:

    I decided to fix the one bundled in Lion Server. I have a laptop and a Mac Pro with Lion Server. The one on the laptop installed and started postgres without any problems. On the Mac Pro, I had to actually create a /etc/sysctl.conf from /etc/sysctl.conf.default, and added the following:

    # the following from http://forums.enterprisedb.com/posts/list/3090.page
    kern.sysv.shmmax=33554432
    kern.sysv.shmmin=1
    kern.sysv.shmmni=256
    kern.sysv.shmseg=64
    kern.sysv.shmall=8192

    kern.sysv.shmmax: 4194304
    kern.sysv.shmmin: 1
    kern.sysv.shmmni: 32
    kern.sysv.shmseg: 8
    kern.sysv.shmall: 1024
    kern.sysv.semmni: 87381
    kern.sysv.semmns: 87381
    kern.sysv.semmnu: 87381
    kern.sysv.semmsl: 87381
    kern.sysv.semume: 10
    kern.maxproc: 1064
    kern.maxprocperuid: 709
    # end end end------------

    Reboot

    And then I was able to execute
    % sudo -u _postgres initdb -D /var/pgsql

    And everything worked.

    The only difference I can see between the 2 machines is laptop has 8GB Mac Pro has 32GB Ram.

    I don't know what fixed it.

    
    
17. Ivano Ravanetti on Sep 05, 2012 - 3:59 said:

    Hi,

    After upgrade from Lion to Mountain Lion, postgresql didn't start .

    By the following commands , posgresql was able to start :

    cp -pr /var/pgsql_socket_alt /var/pgsql_socket
    chown -R postgres:staff /var/pgsql_socket