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.
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”…
Thanks Tom. I’ve updated the post with the proper install code.
I’m also looking forward to reading more of railsonpostgresql.com
@greg, no problem, thanks for the link! There’s a lot of good material out there… just need to kind of gather it up and sift through it…
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.
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.
@Alex – /etc/profile is global for all users instead of ~/.bash_profile for the current user.
Did you know postgresql 8.4 was out on macports ?
@jerome – Yes, but 8.3 is currently supported by Heroku and it was important for me to match versions.
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.
@Paul – Are you running the command with sudo?
@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.
@Paul – I think you are on the right track. Run
which createdb. That will tell you which binary it is using.@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!
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*
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.
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 !
Failed to get Postgres running but found the answer on http://blog.blackwhale.at/ – sudo env PATH=/Library/PostgreSQL8/bin:$PATH gem install pg – need to substitute your own Postgres path.
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?
@omnivore – My only experience is installing it through Mac ports. It has been great with no issues for anyone in our office.
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…
Answering my own question:
env ARCHFLAGS=”-arch x86_64″ gem install pg
did the trick…
Thanks for a great article…
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
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.
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.
How do I get my database to startup automatically? It’s hard to keep looking up what the start commands are.
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.
Check out the pg_dumpall command.
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?
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.)
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?
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.
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.)
Thanks for your post! Finally got me going. Your advice about createuser –superuser macusername -U postgres is really greate, saves you alot of hustle.