Published on MacDevCenter (
 See this if you're having trouble printing code examples

PostgreSQL for Mac OS X

by Michael Brewer

Editor's note -- Developers of all types are testing Mac OS X as their new platform of choice. For example, this article on how to install a PostgreSQL database on Mac OS X was written by Michael Brewer, who has just switched from Windows 2000 to Mac OS X on an iBook.

In case you haven't been introduced to PostgreSQL, itís a robust open source database. For a little history take a look at How PostgreSQL Rose to Fame. Then read this article. Chances are you'll join the ranks of enthusiastic PostgreSQL fans. And the best part is you get to run it on Mac OS X.

Applying Existing Skills to a New Platform

Are you a long-time Mac user curious about working with databases in the past but scared away by prohibitive prices? Are you new to Apple and would like to put your prior database experience to use on the Mac? If you're either, read on. I'm going to walk you through installing PostgreSQL on Mac OS X 10.1.4, as well as using SQL tool with it and connecting to PostgreSQL with Java. The Java bit is at the tail end of this piece so that if programming (or Java itself) isn't your thing you can easily skip it.

I'm a new Mac user. I recently made the switch from Windows 2000 to Mac OS X, and have become comfortable enough in my new environment to flex some of my database skills on this exciting new platform -- having worked with everything from MS Access to Oracle in the past. I've kept up with some of the high level news about what is going on in the open source DBMS realm. So when I pondered what DBMS I should install on my Mac, PostgreSQL was the rather quick answer. It has good third party support from developers, and it passes the ACID (Atomicity, Consistency, Isolation, and Durability) test.


Related Reading

Practical PostgreSQL
By John C. Worsley, Joshua D. Drake

If you don't already have a current version of Apple's Developer Tools, now is the time to get them. We'll be using the compiler during installation of PostgreSQL.

All right, now let's get PostgreSQL. You can download PostgreSQL 7.2.1 via FTP or HTTP. I saved the file to my desktop, but you can save it wherever you wish. Use StuffIt Expander to extract the files.

To install PostgreSQL, open Terminal from Applications/Utilities. Inside of Terminal, go to the folder where you extracted the download to (an easy way to accomplish this is to type cd, a space, and then drag the folder onto Terminal to paste the path in place). In this folder you'll want to run the configure script.

Configure will sniff out the proper system variables to set for Mac OS X (10.1 or later is required). Several files will be created in the build tree to record these customizations. To run the configure script you must type ./configure at the command line. However, we are not ready to run the script just yet. First, let's discuss a few switches we can append to customize our configuration.

PostgreSQL will install into /usr/local/pgsql by default. Since we're installing to Mac OS, we want to change this directory to something more appropriate. You can do this by specifying the command line option --prefix=/Users/Shared/PostgreSQL.

Because I realize the audience for this article is international, we're also going to include locale support. We do this by passing the argument --enable-locale. Additionally, if you plan to use your database with an interface that expects all strings to be in Unicode, you need to add the --enable-multibyte=UNICODE option. I'm turning on multibyte support because I plan to use PostgreSQL as the DBMS for some Java applications I'll be developing. If you're going to skip the Java portion of this article, you can choose not to include this argument.

Some other interesting options are --with-openssl, --with-perl, --enable-syslog, and --enable-debug. I won't be covering these options in order to be brief, but I do recommend you look into them.

Now is the time to run configure. I typed the following (the underscore is merely a line continuation character for the purpose of fitting commands within the constraints of this Web page -- in other words, you don't want to type it):

./configure --prefix=/users/shared/PostgreSQL _
--enable-locale --enable-multibyte=UNICODE 

You'll know configure is complete when you receive a command prompt.

In order to actually begin building PostgreSQL we need to execute GNU Make from the command line by typing make (Apple has installed GNU Make as the default make mechanism).

The build process can last anywhere between 5 minutes and half an hour, according to documentation provided by PostgreSQL. The build on my 600Mhz iBook lasted 7 minutes. Once the build is completed, type make install to begin installing PostgreSQL. If you plan to do server-side program development such as creating custom functions, replace install with install-all-headers. If your install was successful, you'll see the following output:

Thank you for choosing PostgreSQL, the most advanced open source database engine.

Since we installed PostgreSQL to a location that is not searched for applications, we must add /Users/Shared/PostgreSQL/bin to our path. This can be handled by entering the following at the command prompt:

setenv PATH ${PATH}:/Users/Shared/PostgreSQL/bin

You should type the following to reference the manual pages for PostgreSQL:

setenv MANPATH ${MANPATH}:/Users/Shared/PostgreSQL/man

Setting Up a Database

In order to simplify things for the purpose of this article, we're going to create the database as your own user, which should be set up as an administrator. However, for production systems, it is recommended that you create the database as a dedicated PostgreSQL user.

Initialize the database cluster by typing the following:

initdb -D /Users/Shared/PostgreSQL/data

PostgreSQL will begin this task by displaying feedback similar to what you see below.

The files belonging to this database system will be owned by user "steve".

This user must also own the server process.

It will then display the feedback below to indicate that it is finished:

Success. You can now start the database server using:

/Users/Shared/PostgreSQL/bin/postmaster -D /Users/Shared/PostgreSQL/data


/Users/Shared/PostgreSQL/bin/pg_ctl -D /Users/Shared/PostgreSQL/data -l logfile start

Which is just what we want to do. The first command will start the database server in the foreground. The second command uses a shell script that automates the commands needed to start the database server in the background. Start the database with the second command.

Let's create our first database. Actually, PostgreSQL's first database (template1) was created when we initialized the database cluster. However, we're now going to create the first real database.

createdb foo

Congratulations, you just created your first database! Its name is "foo". That wasn't so hard.

Accessing the Database

Let's try accessing the database we just created. There are a couple of SQL tools for the Mac I'll touch on: ViennaSQL, and SQL4X Manager J. I'll walk you through setting up ViennaSQL and connecting to foo with it. But, I've also got a screenshot of the key connection setup screen if you wish to use SQL4X Manager J instead.

First, download the Java Archive (JAR) file for ViennaSQL. Then download the JDBC driver for PostgreSQL. I renamed pgjdbc2.jar to postgresql.jar and placed it in /Library/Java/Extensions. Now add this JAR and the current directory to the classpath environment variable.

setenv CLASSPATH ${CLASSPATH}:/Library/Java/Extensions/postgresql.jar:.

You will need to edit your postgresql.conf file in order to activate TCP/IP sockets for PostgreSQL. The file is located in /Users/Shared/PostgreSQL/data/. You will see the following line near the top of the file under the connection parameters section:

#tcpip_socket = false

Uncomment the line by removing the pound symbol (#) and replace "false" with "true." Open /Users/Shared/PostgreSQL/data/pg_hba.conf in a text editor. Scroll to the bottom of the host-based access file and enter the following to allow access to all databases from any computer on the local network:

host    all        trust

Restart the postmaster by issuing the following command:

/Users/Shared/PostgreSQL/bin/pg_ctl _
-D /Users/Shared/PostgreSQL/data _
-l logfile -o -i restart

Be sure to specify the -o and -i arguments to turn on IP sockets.

Learning Unix for Mac OS X

Related Reading

Learning Unix for Mac OS X
By Dave Taylor, Jerry Peek

OK, now start up ViennaSQL via its JAR file. Under File in the menu bar select Configure. This will bring up the ViennaSQL options window. Click on the Connection tab and create a new connection. Fill in the connection name with PostgreSQL. Type org.postgresql.Driver in the driver class text box. The URL should be jdbc:postgresql:foo in our case. Then fill in the appropriate information for your current Mac OS user.

Screen shot.
Setting up a connection in ViennaSQL.

Click the test button to verify that your connection is working, dismiss the connection test window, then click on the OK button in the new connection window and the ViennaSQL options window. There is a combo box in the top right-hand corner of ViennaSQL's main window -- select PostgreSQL from that box. This will connect you to the database. Paste the following SQL into the lower pane of ViennaSQL's window:

  price	DECIMAL(4,2),

Select Query and Execute from the menu bar. Then select Query and Commit from the menu bar. You've now created your first table, congratulations again! Now, let's populate it with some sample data. Paste the following six lines of code in the lower pane, replacing what is already there:

INSERT INTO books (code, title, author, price, kind)
VALUES ('1-56592-846-6', 'Practical PostgreSQL', 'John C. Worsley, Joshua D. Drake', 44.95, 'database');
INSERT INTO books (code, title, author, price, kind)
VALUES ('1-56592-616-1', 'Database Programming with JDBC and Java', 'George Reese', 34.95, 'java');
INSERT INTO books (code, title, author, price, kind)
VALUES ('0-596-00160-6', 'Learning Cocoa', 'Apple Computer', 34.95, 'macintosh');

You'll need to execute and commit these statements just like you did the last one.

Setting up a new connection in SQL4X.

Now, in order to view the data we just inserted, let's check out some Java code! I've written some Java that will connect to our database and return some values from each row of the books table. Download to your desktop. Now, in Terminal switch to your desktop folder and compile the source. Then run the resulting Java class. Here's what I had to enter into Terminal to accomplish this:

cd ~/Desktop
java connectPostgreSQL

After hitting enter on the third command line, you should see output similar to what you see below:

Practical PostgreSQL costs  44.95
Database Programming with JDBC and Java costs 34.95
Learning Cocoa costs 34.95

And that's all I wrote. Well, almost. I've included some links below for more information on PostgreSQL and some of the technologies that were touched on in this article.

More Information

PostgreSQL SQL Commands at PostgreSQL's Interactive Documentation site has more information on what SQL is supported by PostgreSQL.

The sample chapter for Learning Unix for Mac OS X by Dave Taylor and Jerry Peek has information on creating a .tcshrc file to make environment variable changes permanent. You may also want to look into Sun's JDBC pages.

Michael Brewer is a developer based near Charlotte, North Carolina. His interests include web development of various flavors, databases, and Java. One of the off-shoots of these activities is his website Brewed Thoughts.

Return to the

Copyright © 2009 O'Reilly Media, Inc.