Monday, February 27, 2012

Install PostgreSQL 9 on OS X


November 25, 2010

Install PostgreSQL 9 on OS X

Tested on OS X 10.6.5.

Homebrew

You’re not still using MacPorts, are you? Please go get Homebrew immediately, the new-hotness package manager for OS X written in Ruby. It amazing how simple Homebrew makes this process in comparison to my old instructions on this topic. It’s also nice to be able to peer into friendly-Ruby Homebrew Formulae to see the exact build commands.

Uninstall Old Versions

Initially, I tried a brew install postgresql over the top of the existing version that was installed via non-Homebrew means. When it was complete, psql --version was still showing 8.4.x. I found it is best to uninstall any old PostgreSQL versions first. Of course,back up your data with pgAdmin or pg_dump.
First, uninstall any Homebrew versions. The --force option makes it uninstall all versions.
brew rm postgresql --force

Via Uninstaller

Second, uninstall any versions installed by other means.
If you installed using EnterpriseDB, you likely have an uninstall-postgresql.app file in your install directory. Double-click that to uninstall their Postgres install.

Manual

I went the manual route. Change paths accordingly for your version.
sudo /sbin/SystemStarter stop postgresql-8.4
sudo rm -rf /Applications/PostgreSQL\ 8.4
sudo rm -rf /etc/postgres-reg.ini
sudo rm -rf /Library/StartupItems/postgresql-8.4
sudo rm -rf /Library/PostgreSQL/8.4
sudo dscl . delete /users/postgres
Edit /etc/profile and delete any lines that reference “postgres”. For me, there was just one line that had to do with loading Postgres’ Environment file.
nano /etc/profile
I rebooted here, to make sure the Postgres startup items were truly gone. Open “Activity Monitor” and ensure there are no “postgres” processes.

Install PostgreSQL

brew update
brew install postgresql
Follow the instructions at the end of the install to initialize the DB, add startup items, and start Postgres. For me, it was:
initdb /usr/local/var/postgres
cp /usr/local/Cellar/postgresql/9.0.1/org.postgresql.postgres.plist ~/Library/LaunchAgents
launchctl load -w ~/Library/LaunchAgents/org.postgresql.postgres.plist
pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

Important Note About Authentication

The Homebrew formula does not create a “postgres” user, as was common up to this point. Out of the box, you authenticate against the DB using the same user account under which you ran brew install. It’s perfectly acceptable to leave it that way on your Dev machine. Don’t forget to change that if this is a Production environment.

Instrumentation

Install Instrumentation so pgAdmin doesn’t yell at you. Run this as the same user under which you installed Postgres.
psql -d postgres < /usr/local/Cellar/postgresql/9.0.1/share/contrib/adminpack.sql

Test

Launch pgAdmin, add a DB for “localhost”, and use your username and password.

Install the PG Gem

This installs the “pg” gem that Matz himself wrote. It is recommended over the old “postgres” gem.
env ARCHFLAGS="-arch x86_64" gem install pg
Filed under Mac

Showing 16 comments

  • Steven Chanin, Ruby on Rails Developer, Entrepreneur, Creator of First Kid Bank (http://firstkidbank.com) and How I Work (http://how-i-work.com), freelancer.
    Thanks for the advice.

    With 9.1.2, it looks like they moved the adminpack to:
    /usr/local/Cellar/postgresql/9.1.2/share/postgresql/extension/adminpack--1.0.sql
  • Dalibor Filus
    It will say that you have to run CREATE EXTENSION adminpack; in psql console (run it with "psql -d postgres"). I did that and it worked seamlessly.
  • Lion users are pretty likely to encounter permission errors like:
    could not connect to database postgres: could not connect to server: Permission denied Is the server running locally and accepting connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

    Fix this by: export PATH=/usr/local/bin:$PATH
  • Guest
    Also make sure that you have added host: localhost to your database.yml file for local development.
  • Jonathan Greenleaf
    thanks - version 9.04 puts the adminpack.sql here: psql -d postgres < /usr/local/Cellar/postgresql/9.0.4/share/postgresql/contrib/adminpack.sql
  • wiseleyb
    Thanks - this was very helpful. One tip for newbs like me: when you're done with these steps and type "psql" - you'll get ' datatase "your-user-name" does not exist". Just do a quick "createdb your-user-name" and you'll be in business
  • just3ws
    If you're really lazy. :)

    createdb $(whoami)
  • thank you, very useful
  • Newb question: Since homebrew doesn't create a 'postgres' user how do I create one? Thanks.
  • iq9
    I don't believe you need to, Jim. Homebrew makes it so that Postgres runs under your user account (in other words, whatever user you were logged in as while installing Postgres with Homebrew).
  • But I am trying to work on a project with others on github and the username in the database.yml file is postgres. Sorry, should have said that in the first comment.
  • Trev21mx
    i have problems with shutting down could someone help me i use the command pg_ctl -D /usr/local/var/postgres stop -s -m fast and show me pg_ctl: server does not shut down
  • Thanks! This was exactly what I needed. I used it to install Postgresql on a MacBook Air running OS X Lion. Worked great. For others, be sure to delete ALL leftover files from previous installs.
  • Michael Wheeler
    Ran into an error while following your instructions on Mac OS X 10.7:

    FATAL: Could not allocate memory

    This post helped me fix it: http://willbryant.net/software...
  • Do you know how to uninstall PostgreSQL 9 for OS X?
  • Sven Svensson, Jag är en pigg och glad gubbe från Sverige.
    If you can't create the database, see https://github.com/mxcl/homebr...

Add New Comment

  • Image

Reactions

Trackback URL 
Archives