xezzaa
37.87K
· August 2012 ·
B61ce134adf2d6eb1d1f1d11122d6142

Install PostgreSQL 9.2 on OS X Mountain Lion

The PostgreSQL database keeps getting better with each release. With 9.2 now at beta2, it's a great time to jump in and get up to speed.

Prerequisites

This guide assumes the following:

  • OS X Mountain Lion
  • The latest XCode
  • XCode Command Line Tools
  • The latest MacPorts

Installation

Make sure MacPorts is up to date, then install the postgresql92-server port:

$ sudo port -v selfupdate
$ sudo port -v install postgresql92-server

Setup the Initial Database

Once the server has been installed, you will need to create the initial database:

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

Starting the Server

Once the initial database has been created, you can start the server.

To run the server manually, execute the following command:

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

If you want the server to automatically start at boot time, execute the following command:

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

Setup your PATH

The executable files for PostgreSQL are in a non-standard location, so you you'll want to update your PATH to make things easier. Most likely, you'll want to edit your ~/.bashrc or ~/.zshrc (or similar) profile, though you can set apply the changes for all users system-wide by editing /etc/profile.

Make sure you set the new path before /usr/bin to ensure that you are using the latest versions and not the default apple-supplied tools:

export PATH=/opt/local/lib/postgresql92/bin:$PATH

You can verify this works by running which psql and you should see /opt/local/lib/postgresql92/bin/psql as the output.

Create a New User

You will be setup with a postgres user by default, but it is good practice to create a different user account.

To make things easy, create a new database user to match your OS X username:

$ createuser --superuser <my username> -U postgres

You should now be able to create a new database:

$ createdb my_app

Do please consider setting a password for your newly-created user ;)

Configuring a Rails Application

To setup your Rails application with PostgreSQL, you will need to do the following:

  • Add the pg gem to your Gemfile and run the bundle command Gist
  • Configure your database.yml to use PostgreSQL: Gist

Even if you have set the min_messages option, you may still see console output like the following:

WARNING:  there is already a transaction in progress

Edit the file /opt/local/var/db/postgresql92/defaultdb/postgresql.conf and set the following:

client_min_messages = error

Everything should now be running smoothly.

Sign in or sign up to add your response.

16 Responses

382
Userpic

Macports in 2012? Really? And I thought everyone switched to homebrew back in 2011.

over 1 year ago ·
388
B61ce134adf2d6eb1d1f1d11122d6142

@rwz, yep, really. MacPorts works, well and reliably. It also supports a lot of functionality which I use that homebrew doesn't support (activating/deactivating multiple versions for testing libraries, for example). Homebrew is great, and I recommend it often for many folks, but I need a better reason than "everyone else is doing it" to make the move myself.

Thanks for the input!

over 1 year ago ·
774
U1b6bd8ad071161fb5bd43535867f4590

Thanks bro! You saved my day.

over 1 year ago ·
788
B61ce134adf2d6eb1d1f1d11122d6142

@jfsagasti I'm glad you found it useful, thanks!

over 1 year ago ·
1945
Ae97ad0da5c7887be291561eb1720093

A handy command is

sudo port select --set postgresql postgresql92

That way you you can type 'psql' instead of 'psql92', and so on for the other CLI tools.

over 1 year ago ·
2010
B61ce134adf2d6eb1d1f1d11122d6142

@apsoto, very nice addition, thank you!

over 1 year ago ·
5868
B22db59bb266a4d7624db92bfad9894b

@phlipper afaik brew also allows to use different versions of a package, after all it's a git wrapper. It may not be intuitive, but there's a way to get to all branches and commits in a package repo.
Still it's a shame macports and homebrew don't work together.

over 1 year ago ·
5887
B61ce134adf2d6eb1d1f1d11122d6142

@ellmo sure it's technically possible, but have you seen this done in practice? I don't think this would account for related dependencies. Can you link to some documentation on how to do this?

Once brew is as easy as port activate name @version I'll definitely take a closer look.

over 1 year ago ·
6350

NIce post thanks for the help!!

over 1 year ago ·
6351
B61ce134adf2d6eb1d1f1d11122d6142

@chrisdilbert I'm glad you found it useful, thanks!

over 1 year ago ·
6429
15eb507f7b3472041a35544c151ff2fa

Or you can try this: http://postgresapp.com

over 1 year ago ·
7080
D16c557ae30b90ed51ede778248020b0

this post saved my day. thanks.

over 1 year ago ·
10132
B66ea780b005be62bb244ca451f6d162

As written, your initdb command sets the default encoding to SQL-ANSI. To set the default as UTF8, you need to do this, instead:

sudo su postgres -c '/opt/local/lib/postgresql92/bin/initdb -E UTF8 -D /opt/local/var/db/postgresql92/defaultdb'

Note: The above command is supposed to be on one line. Also, the order of the parameters is important. The encoding must come before the location of the database files. This is also true for postgreSQL93, which is what I actually installed.

over 1 year ago ·
13016
4de5df8cd2477351f6eee149ba2f1824

I have a serious question I can't seem to find an answer to. Everywhere I go people just use "port (command input)" but on the website they say to do "sudo port (command input)" I haven't been able to execute any port commands without doing sudo first. Are you doing sudo before everything and assuming we do that or do you have a special configuration for it?

over 1 year ago ·
13022
B61ce134adf2d6eb1d1f1d11122d6142

@to-mos, FWIW I've generally used sudo when working with the port command.

over 1 year ago ·
14550
Cfa71089e69037dd7e7eab858e7c4f69

Generally, you can just do a port command by itself when you're doing something that reads - like "port list active". But you probably want /opt to be owned by your admin user, so any command that modifies it would need to be run with sudo. People don't always put sudo in front of every command when writing because it's like saying "Simon says" every time. And some people log in as the admin user, or set up ports with /opt owned by a non-admin user so sudo isn't required to modify it.

Also, instead of the long launchctl command, you can just do

sudo port load postgresql93-server

which does the same thing.

over 1 year ago ·