PostgreSQL (pronounced “postgress cue ell”) is an open-source database system that runs on nearly every flavor of Unix, including Mac OS X. Although it has yet to become as popular as its main competitor, MySQL, it is fully ACID compliant, and able to handle both lightweight and complex database applications. For more about PostgreSQL’s features, check out this comprehensive list, or its documentation.
In this article, I’ll show you how to install and configure PostgreSQL on Mac OS X, and then how to use it with Perl, Java, and PHP. Finally, I’ll demonstrate how you can use the web-based phpPgAdmin to administer your databases.
Installing PostgreSQL
Before installing PostgreSQL, you’ll need to install a package called readline. The easiest way to get it is through the Fink project. The Fink project’s goal is to port open-source Unix applications to Darwin and Mac OS X, and to make them available through an easy-to-use package manager. To install Fink, follow these directions. Make sure you’ve got the appropriate Fink installer for your version of OS X.
Once Fink is installed, open a terminal window and type the following to install readline:
liz@mail:~> sudo /sw/bin/fink install readline
You can install the Fink port of PostgreSQL, but a newer and more stable version of PostgreSQL is available if you install from the source files. Since the compilation process is simple, I’ve chosen to install from source, using the curl command. The newest stable version (7.4.1, at the time of this writing) can be found at PostgreSQL.org. As usual, you’ll need to use the sudo command to gain root access before installing system software:
liz@mail:~> cd /usr/local/src
liz@mail:src> sudo sh
Password:
root@mail:src> mkdir postgres
root@mail:src> cd postgres
root@mail:postgres>
curl -O ftp://ftp5.us.postgresql.org/pub/PostgreSQL/source/v7.4.1/postgresql-7.4.1.tar.gz
root@mail:postgres> tar -xzvf postgresql-7.4.1.tar.gz
root@mail:postgres> cd postgresql-7.4.1
(Note that I prefer to put source code in /usr/local/src.)
Now that I have the source files, I can run the configure script, compile, and install. Since I just installed readline via Fink, I need to add the Fink directories as arguments to the configure script:
root@mail:postgresql-7.4.1>
./configure --with-includes=/sw/include/ --with-libraries=/sw/lib
root@mail:postgresql-7.4.1> make
root@mail:postgresql-7.4.1> make install
Next, I need to add an administrative user called “postgres” who effectively owns the databases. To add a user in Mac OS X, open System Preferences and select Accounts, then click New User. Finally, enter the values shown below and click Save:
For further details on adding new users to Mac OS X, see the Internet Developer MySQL article, which covers adding users through the NetInfo Manager and niutil command-line utility.
Next, I return to the terminal to create a directory to hold my PostgreSQL data files. This directory will be owned by the new “postgres” user:
root@mail:~> mkdir /usr/local/pgsql/data
root@mail:~> chown postgres /usr/local/pgsql/data
I need to log in as “postgres” in order to complete the next few steps. To do this, I type the following into a terminal window, followed by the password I just created:
liz@mail:~> su -l postgres
Now I can initialize the databases:
postgres@mail:~> /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
As you can see, the initdb command, along with other PostgreSQL executables, can be found in /usr/local/pgsql/bin. To simplify things later, you’ll probably want to add this directory to your system path (the list of directories that are searched when you type the name of a program at the command line). If you’re using the csh or tcsh shells, this command will let you change your path:
setenv PATH ${PATH}:/usr/local/pgsql/bin
If you’re using sh or bash, this will do the trick:
export PATH=$PATH:/usr/local/pgsql/bin
If you want the path change to be permanent, store the command somewhere where it will be re-run whenever you start a new terminal session. If you’re using the tcsh shell (the default for Mac OS X), you can create a file called .tcsh in any user’s home directory. Commands in a .tcsh file are run by the system whenever a new terminal session begins. Below is the .tcsh file I created while logged in as “postgres.” It contains the command to add /usr/local/pgsql/bin to that user’s path, and a line to store the location of the PostgreSQL data files into a variable called PGDATA (which I’ll need later):
setenv PGDATA /usr/local/pgsql/data
setenv PATH ${PATH}:/usr/local/pgsql/bin
I can run the commands in my .tcsh file by typing source ~/.tcsh.
Now I’m ready to start the PostgreSQL server:
postgres@mail> /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
Once it’s up and running, I can create my first database using the createdb command:
postgres@mail> createdb test
And that’s it. To enter the PostgreSQL command-line utility and run SQL commands in the test database, type the following:
postgres@mail> psql test
The example code in the rest of this article assumes the existence of a table called “foo” in the database “test.” If you’d like to run any of the examples, go to the PostgreSQL command line and execute the commands below. This will create the table “foo” and insert two rows of data into it:
test=> create table foo (name varchar primary key, foo_id serial);
NOTICE: CREATE TABLE will create implicit sequence 'foo_foo_id_seq' for
SERIAL column 'foo.foo_id' CREATE TABLE
test=> insert into foo (name) values ('Liz');
INSERT 16985 1
test=> insert into foo (name) values ('Jason');
INSERT 16986 1
User Access and Authentication
PostgreSQL ships with fairly lax access rules. If you’ll be using your database in a production environment, or on any kind of shared server, you should add at least a basic level of security. User authentication rules are defined in the file /usr/local/pgsql/data/pg_hba.conf. As explained in the PostgreSQL documentation, you can edit this file to password-protect user accounts, change authentication methods for local and remote connections, and enable or disable user access to various parts of the system.
I’ve chosen to require passwords for everyone by setting METHOD to md5 for all users. Further, while I have given full access to all databases to the “postgres” user, I’ve set up group-based access for other users by setting DATABASE to be samegroup for everyone else. For example, in order for a user to have write permission to a database called something, that user will have to be a member of the something group. I’ve also enabled socket connections from localhost (with the same authentication rules) so I’ll be able to use JDBC to connect to PostgreSQL later. Here are the relevant lines from my pg_hba.conf file:
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
host all postgres 127.0.0.1 255.255.255.255 md5
host samegroup all 127.0.0.1 255.255.255.255 md5
local all postgres md5
local samegroup all md5
After editing the file, I need to create a password for the “postgres” superuser, and restart the database server so the changes will take effect:
postgres@mail:~> psql test
test=# alter user postgres with password 'foo';
test=# \q
postgres@mail:~> pg_ctl reload
Next, to demonstrate group-based access, I’ll create two new databases, called shared and private, along with a group called shared. Then I’ll create a user called “liz” and add that user to the shared group, but not the private group:
postgres@mail:~> psql test
test=# create database shared;
test=# create database private;
test=# create group shared;
test=# create user liz with password 'liz';
test=# alter group shared add user liz;
Now I can acccess the shared database as user “liz”:
liz@mail:~> psql shared liz
Password:
Welcome to psql 7.4.1, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
shared=> \q
liz@mail:~>
When I try to access the private database as “liz,” however, I am denied entry:
liz@mail:~> psql private liz
psql: FATAL: No pg_hba.conf entry for host localhost, user liz, database private
Of course, the database administrator can get to the private database without being a member of any particular group:
liz@mail:~> psql private postgres
Password:
Welcome to psql 7.4.1, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
private=#
Before moving on, create a group called test and add the user “liz” to that group:
test=# create group test;
test=# alter group shared add user liz;
This will allow the code examples below to run as they are. (Unless, of course, you’d like to create your own user account and edit the example code to match. In that case, add your account to the test group and you’ll be good to go.)
PostgreSQL and Perl
Your Perl scripts can interact with PostgreSQL via DBI.pm, the popular database-independent programming interface. If you’ve written Perl scripts for MySQL or Oracle in the past, you’re probably already familiar with it. You can get DBI for Mac OS X via CPAN or Fink:
liz@mail:~> sudo /sw/bin/fink install dbi-pm
or
liz@mail:~> sudo perl -MCPAN -e 'install DBI'
After installing DBI, you’ll need a Postgres-specific driver called DBD::Pg. The driver is also available from CPAN:
liz@mail:~> export POSTGRES_INCLUDE="/usr/local/pgsql/include"
liz@mail:~> export POSTGRES_LIB="/usr/local/pgsql/lib -lssl -lcrypto"
liz@mail:~> sudo perl -MCPAN -e 'install DBD::Pg'
The Fink port of DBD::Pg isn’t stable yet, but should be soon. If you’ve already decided that you prefer Fink, you can always try typing the command below. You’ll see a “package not found” error if it doesn’t work:
liz@mail:~> sudo /sw/bin/fink install dbd-pg-pg
Note: Depending on your system configuration and the way in which you installed PostgreSQL, you may have to run the ranlib command on your libpq.a file before you can install DBD::Pg. If you see error messages relating to ranlib or libpq.a, don’t worry. Just run the command below and you should be fine:
liz@mail:~> sudo ranlib /usr/local/pgsql/lib/libpq.a
At this point, both DBI and DBD::Pg should be installed. To test it out, I install the Perl code below into a file called pg.cgi in my cgi-bin directory (/Library/WebServer/CGI-Executables), and make sure the file is both readable and executable by entering: chmod ugo+rx /Library/WebServer/CGI-Executables/pg.cgi.
#!/usr/bin/perl
use DBI;
use CGI;
use CGI::Carp qw(fatalsToBrowser);
use strict;
my $c = new CGI();
my $dbname = 'test';
my $user = 'liz';
my $pass = 'liz';
print $c->header();
print $c->start_html("Reading names from table 'foo' in db 'test'");
my $dbh = DBI->connect("dbi:Pg:dbname=$dbname", $user, $pass) or die DBI::errstr;
my $res = $dbh->selectall_arrayref("select name from foo order by foo_id");
for my $row (@$res)
{
print 'Name: ' , @$row[0] , '<br>';
}
$dbh->disconnect();
print $c->end_html();
Here is the output I saw:
To learn more about using PostgreSQL and Perl together, you can type perldoc DBI or perldoc DBD::Pg into a terminal window, or read about DBI at CPAN.org.
PostgreSQL and Java
The folks at PostgreAQL.org have made a JDBC driver for PostgreSQL available, which means you can easily use PostgreSQL as the database behind your Java applications. Download a JDBC driver and copy it into a shared library location, like /Library/Java/Extensions/. Next, make sure your CLASSPATH environment variable references that directory. For this example, I created a new CLASSPATH in tcsh, like so:
liz@mail:~> setenv CLASSPATH /Library/Java/Extensions/pg73jdbc2ee.jar:.
Bash or sh users should use this instead:
liz@mail:~> export CLASSPATH="/Library/Java/Extensions/pg73jdbc2ee.jar:."
Before connecting via JDBC, edit your pg_hba.conf file as shown above. You’ll also need to edit your /usr/local/pgsql/data/postgresql.conf file to allow socket connections. As “postgres,” open postgresql.conf and change this line:
#tcpip_socket = false
to this:
tcpip_socket = true
And then restart the server:
postgres@mail:~> pg_ctl relaod
Now we’re ready to test the JDBC connection. This small Java class contains code that connects to PostgreSQL and retrieves a few rows from a small table, just like the Perl and PHP examples. If you’d like to set up a web server–based Java environment, I recommend reading the Apple Internet Developer articles on Java and OS X. In the meantime, though, create a file called PgTest.java with these contents:
import java.sql.*;
import java.text.*;
import java.io.*;
public class PgTest
{
Connection db; // connection object
Statement sql; // statement to run queries with
// the constructor does all the work in this simple example
public PgTest(String argv[])
throws ClassNotFoundException, SQLException
{
String database = argv[0];
String username = argv[1];
String password = argv[2];
// load the JDBC driver for PostgreSQL
Class.forName("org.postgresql.Driver");
// connect to the datbase server over TCP/IP
// (requires that you edit pg_hba.conf
// as shown in the "Authentication" section of this article)
db = DriverManager.getConnection("jdbc:postgresql:"+database,
username,
password);
// create a statement for later use
sql = db.createStatement();
String theQuery = "select name from foo order by foo_id";
System.out.println("Now executing query: \""+ theQuery + "\"\n");
ResultSet results = sql.executeQuery(theQuery);
if (results != null)
{
while (results.next())
{
System.out.println("Name: "+results.getString("name")+"\n");
}
}
else
{
System.out.println("No rows found");
}
results.close();
db.close();
}
public static void showUsage()
{
System.out.println("\nUsage:\n "+
"java PgTest <database> <username> <password> \n");
System.exit(1);
}
public static void main (String args[])
{
if (args.length != 3) showUsage();
try
{
PgTest showMe = new PgTest(args);
}
catch (Exception ex)
{
System.out.println("Caught Exception:\n"+ex);
ex.printStackTrace();
}
}
}
Now we can compile and run the Java test application.
PostgreSQL and PHP
In order to use PostgreSQL with PHP, you’ll have to recompile PHP. If you’re like most OS X users in early 2003, this will give you a chance to upgrade to the new major PHP version, 4.3.4, which has been eight months in the making. I chose to compile PHP as an apxs module. This is not the only solution, but it allows you to use the Apache server that is pre-installed on Mac OS X. If you want to learn about your other installation options, visit PHP.net. The important thing is to configure PHP with the — with-pgsql option before compiling.
First, get the latest stable version of PHP, available from PHP.net. Extract the archive, then run configure and make. You may want to add other arguments to ./configure if you’ve got other modules to add:
root@mail:src> tar -xzvf php-4.3.4.tar.gz
root@mail:src> cd php-4.3.4/
root@mail:php-4.3.4> ./configure --with-pgsql --with-apxs
root@mail:php-4.3.4> make
root@mail:php-4.3.4> make install
root@mail:php-4.3.4> cp php.ini-dist /usr/local/lib/php.ini
Now edit the /etc/httpd/httpd.conf file and tell Apache to recognize the .php extension:
AddType application/x-httpd-php .php
Finally, start the web server with apachectl start or through the Sharing option in System Preferences.
Documentation for the many PHP functions for accessing PostgreSQL can be found in this section of the PHP manual. You can get started, however, by running a test script. Create a file in your document root (usually /Library/Webserver/Documents/) called “pg.php”:
<html>
<head>
<title>Reading names from table 'foo' in db 'test'</title>
<head>
<body>
<?php
$conn = pg_connect("dbname=test user=liz password=liz");
if (!$conn)
{
print "Unable to Connect to DB";
exit;
}
$result = pg_query("select name from foo");
if (!$result)
{
print "Unable to retrieve data";
exit;
}
while ($arr = pg_fetch_array($result))
{
print 'Name: ' . $arr['name'] . '<br>';
}
?>
</body>
</html>
Here’s what it’ll look like:
Administering Your Databases with phpPgAdmin
Once you’ve installed PHP and PostgreSQL support, you can take advantage of phpPgAdmin, a web-based application that makes it easy to manage your PostgreSQL databases and users. Installing phpPgAdmin is simple: simply download the application into your web server’s document root, change a few configuration parameters, and you’re set:
root@mail:~> cd /Library/WebServer/Documents/
root@mail:Documents> curl -O http://prdownloads.sourceforge.net/phppgadmin/phpPgAdmin-3.2.1.tar.gz
root@mail:/usr/local/src/phppgadmin> tar -xzvf phpPgAdmin-3.2.1.tar.gz
If your server is visible to anyone besides trusted users, you should add access protection to the phpPgAdmin directory. If you don’t know how to add authentication to a directory, these instructions on the Apache.org site will tell you.
Before you can run phpPgAdmin, you’ll need to create and edit the application’s configuration file, called config.inc.php:
root@mail:src> cd /Library/WebServer/Documents/phpPgAdmin
root@mail:phpPgAdmin> cp config.inc.php-dist config.inc.php
Open config.inc.php and edit it to match the code below:
// The $cfgServers array starts with $cfgServers[1]. Do not use $cfgServers[0].
// You can disable a server config entry by setting host to ''.
$cfgServers[1]['local'] = false;
$cfgServers[1]['host'] = 'localhost';
$cfgServers[1]['port'] = '5432';
$cfgServers[1]['adv_auth'] = true;
$cfgServers[1]['user'] = '';
// if you are not using adv_auth, enter the username to connect all the time
$cfgServers[1]['password'] = '';
// if you are not using adv_auth and a password is required enter a password
$cfgServers[1]['only_db'] = '';
// if set to a db-name, only this db is accessible
This tells the application that there’s a PostgreSQL server on the local machine (localhost), and instructs it to prompt web users to enter a valid PostgreSQL username and password for access.
You can now view phpPgAdmin in your favorite browser. Enter “postgres” as your username, and “foo” (or the administrative password you chose earlier) as your password. Note that this is not the “postgres” user’s OS X password but the password you created by typing alter user postgresql with password ‘foo’ at the psql prompt. Once you’ve logged in, you’ll be able to view and edit databases, tables, users, and groups—all through a nifty browser interface:
Conclusion
PostgreSQL is a robust open-source database that’s ideal for web development on Mac OS X. If you’re interested in learning more about what PostgreSQL can do, I recommend the developers’ documentation, or one of the many fine books available.
|
