AddThis Social Bookmark Button

Listen Print Discuss
Mac OS X > Excerpts >

PostgreSQL for Mac OS X
Pages: 1, 2

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

or

/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        192.168.0.0   255.255.0.0        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:

CREATE TABLE books (
  code	CHARACTER(13) CONSTRAINT firstkey PRIMARY KEY,
  title	CHARACTER VARYING(40) NOT NULL,
  author	CHARACTER VARYING(40) NOT NULL,
  price	DECIMAL(4,2),
  kind	CHARACTER VARYING(10)
);

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 connectPostgreSQL.java 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
javac connectPostgreSQL.java
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 MacDevCenter.com.