Installing PostgreSQL on Snow Leopard 10.6

Good news! Whether you are using PostgreSQL for general development, or need a version to match your setup on Heroku, getting PostgreSQL 8.3 installed on Snow Leopard is fairly straight forward. However, you’ll want to make some changes so that it works right for you.

Installing PostgreSQL 8.3

First, you’ll need to install Xcode if you haven’t already. This is available on the Snow Leopard DVD in the Optional Installs directory.

Second, if you aren’t already using it, download Mac Ports for Snow Leopard and install it. Mac Ports has come a long way in the last few years and will make your life much easier.

Once those are installed, run the following command:
sudo port install postgresql83 postgresql83-server

Setup Your First Database

At the very end of the install it tells you how to setup your first database:

sudo mkdir -p /opt/local/var/db/postgresql83/defaultdb
sudo chown postgres:postgres /opt/local/var/db/postgresql83/defaultdb
sudo su postgres -c '/opt/local/lib/postgresql83/bin/initdb -D /opt/local/var/db/postgresql83/defaultdb'

You’ll also want to setup Postgres to auto-run as a server on start up.

sudo launchctl load -w /Library/LaunchDaemons/org.macports.postgresql83-server.plist

If you want to start it right now, you can either reboot or do the following:

sudo su postgres -c '/opt/local/lib/postgresql83/bin/postgres -D /opt/local/var/db/postgresql83/defaultdb'

Make psql Available from the Command Line

The executable files for PostgreSQL get shoved into a non-standard place (just like MySQL), so you’ll need to edit the default profile.

sudo vi /etc/profile

You can also do this using sudo mate /etc/profile if you aren’t comfortable in VI.

The PATH= line needs to be changed to include the PostgreSQL bin directory.

Mine was PATH="/opt/local/bin:$PATH" and is now:

PATH="/opt/local/bin:/opt/local/sbin:/opt/local/lib/postgresql83/bin:$PATH"

If you open a new terminal window you can now type psql and it will find it.

Create a New User and Database

By default, PostgreSQL creates a postgres user for you. However, it’s not good practice to use the default and it’s a pain in the ass. Let’s just create a new database user to make it easier.

createuser --superuser macusername -U postgres

You need to change macusername to your mac username. This will make your life ALOT easier. Trust me here.

Next, create your database:

createdb my_database

Installing the PostgreSQL Ruby Gem

Unlike the MySQL driver, we don’t need to pass the ARCHFLAGS variable as 64 bit. PostgreSQL comes with both 32 and 64-bit versions. Yeah!

sudo gem install postgres-pr

Per Tom’s comment below, we should be using the native driver for better performance.

sudo env ARCHFLAGS="-arch x86_64" gem install pg

Configuring your Rails Application

Inside your Ruby on Rails application, open up config/database.yml and change your development adapter to be similar to the following:

development:
adapter: postgresql
database: defaultdb
username: defaultdb

You can change defaultdb to the name you need for your application.

34 thoughts on “Installing PostgreSQL on Snow Leopard 10.6

  1. Hi Greg – The only problem is that postgres-pr is the pure Ruby driver – no native code involved. That’s why you don’t have to pass in any compiler flags… but unfortunately you’ll also get the slower performance of that driver. Would be much faster to use “gem install pg”…

  2. Get this on the gem :

    Building native extensions. This could take a while…
    ERROR: Error installing pg:
    ERROR: Failed to build gem native extension.

    /opt/local/bin/ruby extconf.rb
    extconf.rb:1: command not found: pg_config –version
    ERROR: can’t find pg_config.
    HINT: Make sure pg_config is in your PATH
    *** extconf.rb failed ***
    Could not create Makefile due to some reason, probably lack of
    necessary libraries and/or headers. Check the mkmf.log file for more
    details. You may need configuration options.

  3. Ah, I solved my problem. Users may become confused if their system uses .bash_profile or .bash_rc instead of /etc/profile for exporting the correct PATH.

  4. Great article! I’ve ran into an issue though. I did the createuser command like you have above but when I try createdb it always prompts me for a password which I was never prompted to create when I ran createuser. I tried entering several different ones with no luck. Any ideas? Thanks again.

  5. @Greg. I’m not using sudo with the createdb command. But I think I have a better idea of what may be going on. Initially I had 8.4 installed on leopard and when I did a clean reinstall of snow leopard I migrated my user folder from Time Machine. I tried building postgres from source which resulted in a segmentation fault so that’s when I decided to try the mac ports install (I removed the directory containing the source build). When I did the mac ports install I tried to create a new user using the same command as you have listed and it said the user already existed. Sounds like I have some files or directories that stuck around.
    But even so I’m not sure why I would be prompted for a password when I never created one. What I really need to know is how can I completely remove anything associated with postgres so I can do a ‘clean’ install from mac ports. Thanks.

  6. @Greg – Finally got installed and working correctly this afternoon. I found a list of what to rm for a manual uninstall and afterwards I was able to install via mac ports. All is good!

  7. Ack! I get as far as the profile, but there is nothing in my profile with the PATH. Here is the profile text:

    # System-wide .profile for sh(1)

    if [ -x /usr/libexec/path_helper ]; then
    eval `/usr/libexec/path_helper -s`
    fi

    if [ "${BASH-no}" != "no" ]; then
    [ -r /etc/bashrc ] && . /etc/bashrc
    fi

    Where do I need to insert the PATH? Many thanks….. *susan*

  8. Yup. Me again. Turns out there are two profile documents. The PATH was in /.profile. Modified that string and I am up and running. Many thanks.

  9. Did not remove Leopard Xcode – stupid with hindsight but easy to think Xcode 3.1 would do – it won’t and gives a Macports error. After that everything went fine. Thanks for the help !

  10. I have to ask before I launch into this — are there any 64 bit/ 32 bit issues lurking here? This has screwed me up on mysql, and as I am moving to Heroku, I’ve decided to avoid mysql for now and only use pg. But I can’t find any indication if there’s a 32 bit or 64 bit version… anything you can tell me?

  11. all in all so far so good… but while I have psql running, in my app directory I try to migrate:

    rake aborted!
    Please install the postgresql adapter: `gem install activerecord-postgresql-adapter` (no such file to load — pg)

    I have installed the gem, no problems reported. Not sure what the issue is — any ideas?

    Thanks for getting me this far… feels like I’m close…

  12. Answering my own question:

    env ARCHFLAGS=”-arch x86_64″ gem install pg

    did the trick…

    Thanks for a great article…

  13. I’ll suggest if you think you have *any* 32 bit code running on an Xserve Snow Leopard machine, modify your macports configuration to force x86 (not x86_64) code building. Here’s a link to a problem I ran into with a database that was built and used under 32 bit postgres (under Leopard) and then I introduced a 64bit postgres (under Snow Leopoard). http://bit.ly/2avz9j

  14. When i type psql83 on the commend line i get an error message with my username ram database does not exist. So i type psql83 default and i get default db does not exist.

  15. Final got it working! I edited the .bash_profile file and included the path info there. Works great. Plus had to do a few other details listed above.

  16. How do I get my database to startup automatically? It’s hard to keep looking up what the start commands are.

  17. Hi Greg, I installed postgresql8.3 per your instructions above and didn’t have any problems. However, i need to move my database to another harddrive but can’t find where the actual database files are stored. do you what the best way to move the database is? thanks.

  18. I’m not able to find PATH= in the /etc/profile

    Like susan above, here is my profile text:

    # System-wide .profile for sh(1)

    if [ -x /usr/libexec/path_helper ]; then
    eval `/usr/libexec/path_helper -s`
    fi

    if [ "${BASH-no}" != "no" ]; then
    [ -r /etc/bashrc ] && . /etc/bashrc
    fi

    However, unlike her I do not have another profile folder that I can find.

    Any thoughts?

  19. By the way, you might want to mention that /etc/profile is only for bash. If you’re using Z shell, then it’s /etc/zprofile. (I’d guess that ksh, csh, and tcsh, all have their own files as well, though I don’t know if anyone uses those much anymore.)

  20. Greg, this was excellent. It worked “out of the box”. A million ‘thank you’s! In fact, I’ve written a simplified tutorial based on your work, with some extra explanations for novices, which I thought I would upload some time, giving full credit and a link to your page right at the top. Would that be alright or would that be internet plagiarism?

  21. Just wanted to let you know i couldn’t get the postgres one-click installer to work, then stumbled on this blog, and it really helped. I’m up and running now! You should also mention to people to download the pgadmin app, its brilliant. Thanks so much.

  22. By the way, you might want to mention that /etc/profile is only for bash. If you’re using Z shell, then it’s /etc/zprofile. (I’d guess that ksh, csh, and tcsh, all have their own files as well, though I don’t know if anyone uses those much anymore.)

  23. Thanks for your post! Finally got me going. Your advice about createuser –superuser macusername -U postgres is really greate, saves you alot of hustle.

Leave a Reply