Ultimate Snow Leopard Web Development machine (part 3) – Databases

27 Oct
2009

Depending on the webapp project I’m working on, I flip back and forth between MySQL and PostgresSQL, generally using Postgres for anything that needs spatial or mapping related functionality.

One pain point I encountered with Snow Leopard is getting everything compiled for true 64bit, and then getting the appropriate bindings for MySQL or Postgres to work with frameworks like Rails (Ruby) or Django (Python). I work with both, so by the end of the post you’ll be prepared to use both DB servers for either framework.

Step 1: Install MySQL from source

We refer here, again to the excellent tutorial from HiveLogic:

http://hivelogic.com/articles/compiling-mysql-on-snow-leopard/

After completing the install, set up your root password:

[cci lang="bash"]

$ mysqladmin -u root password NEWPASSWORD

[/cci]

Step 2: Install PostgreSQL from source

As for Postgres, I found these instructions found on InvisionPower as a good basis to start my install from.

http://community.invisionpower.com/topic/292849-installing-postgresql-server-on-mac-os-x-snow-leopard/

Couple of note regarding Postgres.

1) the instructions in the link above seem to provide a dead link for the Postgres source. You can try this curl command instead of theirs:

[cci lang="bash"]
$ curl http://ftp9.us.postgresql.org/pub/mirrors/postgresql/source/v8.4.1/postgresql-8.4.1.tar.gz -O
[/cci]

2) Make sure to compile as a 64 bit application, using the “ARCH=x86_64″ and other flags specified during your “make” step.

3) when creating and setting user/group info, I found an error in the 8th command line instructions. The original command references a group “_postgres” before it has been created. Do this instead:

Enough changes were required to make things compile and work on my new system, that I’ll simply transcribe my exact steps here:

[cci lang="bash"]
$ sudo mkdir /usr/local/src
$ cd /usr/local/src

$ sudo su

$ curl http://ftp9.us.postgresql.org/pub/mirrors/postgresql/source/v8.4.1/postgresql-8.4.1.tar.gz -O
$ tar -zvxf postgresql-8.4.1.tar.gz
$ rm postgresql-8.4.1.tar.gz
$ cd postgresql-8.4.1

$ ./configure –prefix=/usr/local/postgresql-8.4.1

$ ARCH=x86_64 CFLAGS=”-arch x86_64″ LDFLAGS=”-arch x86_64″ make
$ make install

$ ln -s /usr/local/postgresql-8.4.1 /usr/local/pgsql
$ echo “PATH=/usr/local/pgsql/bin:$PATH” >> ~/.profile
$ source ~/.profile
$ mkdir /usr/local/pgsql/data/

$ dscl . list /Users UniqueID
[/cci]

I use ” dscl . list /Users UniqueID | grep <##>” to find the first available number past 75. For me it was 80.

[cci lang="bash"]
$ dscl localhost create /Local/Default/Users/_postgres
$ dscl localhost create /Local/Default/Users/_postgres PrimaryGroupID 0
$ dscl localhost create /Local/Default/Users/_postgres UniqueID 80
$ dscl localhost create /Local/Default/Users/_postgres UserShell /bin/bash
$ dscl localhost passwd /Local/Default/Users/_postgres
$ dscl localhost create /Local/Default/Users/_postgres NFSHomeDirectory /var/home/_postgres
$ mkdir -p /var/home/_postgres
$ chown -Rf _postgres /var/home/_postgres
$ dscl localhost create /Local/Default/Groups/_postgres
$ dscl localhost create /Local/Default/Groups/_postgres UniqueID 80
$ dscl localhost append /Local/Default/Groups/_postgres GroupMembership _postgres
$ chown -Rf _postgres:_postgres /var/home/_postgres

$ defaults write /Library/Preferences/com.apple.loginwindow.plist Hide500Users -bool TRUE
$ chown -R _postgres /usr/local/postgresql-8.4.1/
[/cci]

I did not exit su at this point, as suggested in the original InvisionPower article. I needed to be su to change to user “_postgres”.

[cci lang="bash"]
$ su – _postgres
$ /usr/local/pgsql/bin/initdb -E UTF8 -D /usr/local/pgsql/data/
$ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data/ -l /usr/local/pgsql/data/postgresql.log start
[/cci]

Now test creating a new DB:

[cci lang="bash"]
$ /usr/local/pgsql/bin/createdb test
$ /usr/local/pgsql/bin/psql test
[/cci]

Type \q to quit.

[cci lang="bash"]
$ exit
$ sudo su
$ rm -rf /var/home
$ dscl localhost delete /Local/Default/Users/_postgres NFSHomeDirectory
$ dscl localhost passwd /Local/Default/Users/_postgres
$ exit
[/cci]

At this point your server is installed and tested. The InvisionPower post provides a great script that you can use to start/stop/restart the server from the command line. See the bottom of their article.

3 Responses to Ultimate Snow Leopard Web Development machine (part 3) – Databases

Avatar

Scott Mead

October 28th, 2009 at 7:44 am

PostgresPlus Standard edition is much easier:

http://www.enterprisedb.com/downloads.do

It has a next -> next finish style installer that does all of this for you.
–Scott Mead
EnterpriseDB

Avatar

Sandeep

October 28th, 2009 at 12:04 pm

Thanks for the suggestion Scott! Does PostgresPlus Standard edition install natively as a 64 bit app on Snow Leopard?

Avatar

Paul Beach

December 2nd, 2009 at 8:43 am

Have you tried looking at Firebird for MacOSX?
http://www.firebirdsql.org/
Paul

Comment Form

top