By Robert Crews
PostgreSQL is an object-relational database management system descended from the database project Ingres (named after the famous painter), developed at UC Berkeley from 1977 to 1985. In 1985, the Ingres Corporation used the code to produce one of the first commercially successful relational database servers. Development continued at Berkeley from 1986 to 1994 under the project name "Postgres." In 1994, Illustra Information Technologies used the Postgres code to develop their successful multimedia database. During the financial upheavals of the late 1990s, Illustra was bought by Informix, which was in turn bought by IBM. The Berkeley Postgres code was resurrected in 1995 as "Postgres95," and renamed "PostgreSQL" in 1996. Development left Berkeley in that year to be managed by a world-wide group of open-source developers.
PostgreSQL is widely regarded as the most advanced open-source database server with high compliance with SQL:2003, inner and outer joins, views, subselects, foreign keys, triggers, secure connections, and having advanced transactional safety. PostgreSQL is an excellent relational database management system for professional deployment and is also a sophisticated, inexpensive (free!) database for learning SQL, database administration, and database access from a variety of programming languages.
Everything you need to compile and deploy PostgreSQL is already installed on Mac OS X 10.5 (assuming you've installed the optional Developer Tools). For XML support, you'll need to add a newer version of the libxml2 libraries to your system.
To prepare for installation:
Set the TZ timezone environment variable. The environ man page describes TZ and other common environment variables. One way to do this is to edit your .bash_profile file, then source it:
$ echo 'export TZ=:/etc/localtime' >> ~/.bash_profile $ source ~/.bash_profile
The hardest part about building and installing PostgreSQL on Mac OS X is deciding what configuration options to use. The configuration in this section builds in support for every option available or easily obtainable on a typical Mac computer. Expect the procedure to take about 20 minutes on newish hardware.
To build and install PostgreSQL:
Get PostgreSQL source, unpack it, and cd to the top level of the distribution directory.
$ curl -O \ ftp://ftp9.us.postgresql.org/pub/mirrors/postgresql/source/v8.3.3/postgresql-8.3.3.tar.bz2 ;\ bunzip2 postgresql-8.3.3.tar.bz2 ; tar xvf postgresql-8.3.3.tar ;\ cd postgresql-8.3.3
Run configure:
$ ./configure --with-perl --with-python --with-tcl --with-krb5 \ --with-openssl --with-pam --with-ldap --with-bonjour --with-libxml \ --with-libxslt --enable-thread-safety
If you didn't install the updated libxml2 libraries, omit the --with-libxml and --with-libxslt flags.
Run make, make check, and make install:
$ make $ make check $ sudo make install
Add the PostgreSQL utilities and documentation to your system paths:
$ echo 'export PATH=$PATH:/usr/local/pgsql/bin' >> ~/.bash_profile $ echo 'export MANPATH=$MANPATH:/usr/local/pgsql/man' >> ~/.bash_profile $ source ~/.bash_profile
The PostgreSQL documentation dedicates hundreds of pages to database management. This section describes a fast and direct way to get to where you can start entering and retrieving data.
To initialize and configure PostgreSQL:
Create the Mac OS X postgres user and group:
$ sudo dscl . > create /users/postgres > create /users/postgres Password * > create /users/postgres UniqueID 499 > create /users/postgres PrimaryGroupID 499 > create /users/postgres RealName "PostgreSQL Server" > create /users/postgres NFSHomeDirectory /var/empty > create /users/postgres UserShell /usr/bin/false > quit $ sudo dscl . > create /groups/postgres > create /groups/postgres Password * > create /groups/postgres PrimaryGroupID 499 > create /groups/postgres RealName "PostgreSQL Users" > quit
After entering sudo dscl ., the command line returns a > prompt for interactive input. Alternately, you can run each statement as an isolated command:
$ sudo dscl . -create /groups/postgres $ sudo dscl . -create /groups/postgres Password "*" $ sudo dscl . -create /groups/postgres PrimaryGroupID 499 $ sudo dscl . -create /groups/postgres RealName "PostgreSQL Users"
See the dscl man page for more information.
Hide the postgres user (and all other users with a UniqueID less than 500) from the Mac OS X GUI:
$ sudo defaults write /Library/Preferences/com.apple.loginwindow Hide500Users -bool true
Create the PostgreSQL data directory, and give ownership to the postgres user:
$ sudo mkdir /Library/PostgreSQL $ sudo mkdir /Library/PostgreSQL/data $ sudo chown postgres:postgres /Library/PostgreSQL/data
Create a file containing the following data at /Library/LaunchDaemons/org.postgresql.postmaster.plist:
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd"> <plist version="1.0"> <dict> <key>Label</key> <string>org.postgresql.postmaster</string> <key>RunAtLoad</key> <true/> <key>UserName</key> <string>postgres</string> <key>ProgramArguments</key> <array> <string>/usr/local/pgsql/bin/postgres</string> <string>-D</string> <string>/Library/PostgreSQL/data</string> </array> <key>KeepAlive</key> <dict> <key>SuccessfulExit</key> <true/> </dict> <key>Disabled</key> <true/> </dict> </plist>
The Disabled key—set to "true" above—controls whether or not PostgreSQL will automatically start when your computer boots. Disabled true means it will not start automatically. The launchctl command provides a -w command flag to set or unset the Disabled flag without requiring you to open the file in a text or XML editor.
Initialize your installation:
$ sudo -u postgres /usr/local/pgsql/bin/initdb -D /Library/PostgreSQL/data
The initdb command creates a database and database user with the same name as the user running the program, in this case, postgres.
Start PostgreSQL:
$ sudo launchctl load -w /Library/LaunchDaemons/org.postgresql.postmaster.plist
Note the presence of the -w flag that changes the org.postgresql.postmaster.plist Disabled flag to false, configuring PostgreSQL to automatically launch when your computer boots.
Set the postgres user's password:
$ sudo -u postgres /usr/local/pgsql/bin/psql -U postgres -c \ "ALTER USER postgres PASSWORD 'password';"
Tighten security. Edit /Library/PostgreSQL/data/pg_hba.conf so it looks as shown:
$ sudo -u postgres emacs /Library/PostgreSQL/data/pg_hba.conf
# TYPE DATABASE USER CIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only local all postgres md5 local sameuser all trust # IPv4 local connections: host all postgres 127.0.0.1/32 md5 host sameuser all 127.0.0.1/32 trust # IPv6 local connections: host all postgres ::1/128 md5 host sameuser all ::1/128 trust
Stop then start PostgreSQL to load your new settings:
$ sudo launchctl unload /Library/LaunchDaemons/org.postgresql.postmaster.plist $ sudo launchctl load /Library/LaunchDaemons/org.postgresql.postmaster.plist
Create your PostgreSQL user account using the same user name as your Mac OS X login name:
$ sudo -u postgres /usr/local/pgsql/bin/psql -U postgres -c \ "CREATE USER username PASSWORD 'password';"
Create your personal user database:
$ sudo -u postgres /usr/local/pgsql/bin/psql -U postgres -c \ "CREATE DATABASE username OWNER username ENCODING 'UTF8';"
To allow multiple users to access the same database, create a group with the same name as the database, then add users—PostgreSQL users you've already created with CREATE USER (or createuser)—to the group:
$ sudo -u postgres /usr/local/pgsql/bin/psql -U postgres -c \ "CREATE DATABASE databasename OWNER username ENCODING 'UTF8';" $ sudo -u postgres /usr/local/pgsql/bin/psql -U postgres -c \ "CREATE GROUP dbgroupname;" $ sudo -u postgres /usr/local/pgsql/bin/psql -U postgres -c \ "GRANT ALL PRIVILEGES ON DATABASE databasename TO GROUP dbgroupname;" $ sudo -u postgres /usr/local/pgsql/bin/psql -U postgres -c \ "ALTER GROUP dbgroupname ADD USER otherusername;"
Then add one or more host-based authentication lines to /Library/PostgreSQL/data/pg_hba.conf to give the group you created permission to access the database.
# TYPE DATABASE USER CIDR-ADDRESS METHOD local databasename +dbgroupname md5 host databasename +dbgroupname 127.0.0.1/32 md5 host databasename +dbgroupname ::1/128 md5
Apple Remote Desktop includes a copy of PostgreSQL that listens on the default port of 5432. If you use Apple Remote Desktop and want to run the PostgreSQL you're currently installing on the default port, change the port of the instance Apple Remote Desktop uses by uncommenting the "port=" line from /var/db/RemoteManagement/RMDB/rmdb.data/postgresql.conf, specifying a nondefault port number (5431, for example), then restarting Apple Remote Desktop. More information is available.
All you need to do is copy the PostgreSQL JDBC driver to your Java extensions folder.
To install the PostgreSQL JDBC driver:
Copy the latest PostgreSQL JDBC driver to your Java extensions folder:
$ pushd . ;\ cd /Library/Java/Extensions ;\ curl -O http://jdbc.postgresql.org/download/postgresql-8.3-603.jdbc3.jar ;\ popd
Here's source for a simple test program. Copy the following code to HelloPostgresql.java, compile with javac, and run with java.
emacs HelloPostgresql.java javac HelloPostgresql.java java HelloPostgresql database username password
/*
* Class HelloPostgresql tests JDBC connectivity to a PostgreSQL database.
* Adapted from code at http://www.fankhausers.com/postgresql/jdbc/
*/
import java.sql.*;
public class HelloPostgresql {
Connection conn; // A database connection
Statement stmt; // Reusable statement variable
DatabaseMetaData meta; // Connection data
public HelloPostgresql (String argv[])
throws ClassNotFoundException, SQLException {
String database = argv[0];
String username = argv[1];
String password = argv[2];
Class.forName("org.postgresql.Driver"); // Load the driver
conn = DriverManager.getConnection("jdbc:postgresql:" +
database, username, password); // Connect to the database
meta = conn.getMetaData(); // Get connection data
System.out.println("Connection to " + meta.getDatabaseProductName() +
" " + meta.getDatabaseProductVersion() + " successful.");
Statement stmt = conn.createStatement(); // Create a reusable statement
String sql = "CREATE TABLE jdbc_demo (code INT, text VARCHAR(20))";
System.out.println("Executing: " + sql);
stmt.executeUpdate(sql);
sql = "INSERT INTO jdbc_demo VALUES (1, 'One')";
System.out.println("Executing: " + sql);
stmt.executeUpdate(sql);
sql = "INSERT INTO jdbc_demo VALUES (3, 'Four')";
System.out.println("Executing twice: " + sql);
stmt.executeUpdate(sql);
stmt.executeUpdate(sql);
sql = "UPDATE jdbc_demo SET text = 'Three' WHERE code = 3";
System.out.println("Executing: " + sql);
stmt.executeUpdate(sql);
System.out.println (stmt.getUpdateCount() + " rows were updated.");
System.out.println("Demonstrating a prepared statement...");
sql = "INSERT INTO jdbc_demo VALUES (?, ?)";
System.out.println("Statement form: " + sql);
System.out.println("Looping three times, filling in the fields...");
PreparedStatement ps = conn.prepareStatement(sql);
for (int i=10; i < 13; i++) {
System.out.println(i + "...");
ps.setInt(1, i); // Set column one (code) to i
ps.setString(2, "HiHo"); // Column two gets a string
ps.executeUpdate();
}
ps.close();
System.out.println("Executing: " + "SELECT * FROM jdbc_demo");
ResultSet results = stmt.executeQuery("SELECT * FROM jdbc_demo");
if (results != null) {
while (results.next()) {
System.out.println("code = " + results.getInt("code") + "; " +
"text = " + results.getString(2));
}
}
results.close();
sql = "DROP TABLE jdbc_demo";
System.out.println("Executing: " + sql);
stmt.executeUpdate(sql);
conn.close();
}
public static void correctUse() {
System.out.print("Use: java HelloPostgresql ");
System.out.println("<database> <username> <password>");
System.exit(1);
}
public static void main (String args[]) {
if (args.length != 3) correctUse();
try {
HelloPostgresql demo = new HelloPostgresql(args);
}
catch (Exception e) {
e.printStackTrace(System.err);
}
}
}
Database access from Perl consists of two parts: the database-independent part and the database-dependent part. You can install the DBI into any Perl installation, but you should have a PostgreSQL database installed and configured before installing DBD::Pg. Installing the DBD::Pg module doesn't prevent you from later installing DBD modules for other database servers.
To install the Perl DBI and the PostgreSQL Database Driver:
Start the CPAN module interactive shell:
$ sudo perl -MCPAN -e shell
Install the DBI bundle:
cpan> force install Net::Daemon cpan> install Test::Simple Bundle::DBI
Stop the CPAN module interactive shell:
cpan> quit
Get the latest DBD::Pg. Unpack it, and cd to the top level of the distribution directory.
$ curl -OL \ http://search.cpan.org/CPAN/authors/id/T/TU/TURNSTEP/DBD-Pg-2.9.0.tar.gz ;\ tar xvfz DBD-Pg-2.9.0.tar.gz ;\ cd DBD-Pg-2.9.0
Set your testing environment variables:
$ export POSTGRES_LIB="/usr/local/pgsql/lib -lssl -lcrypto" $ export DBI_DSN=dbi:Pg:dbname=database $ export DBI_USER=username $ export DBI_PASS=password
Sing along:
$ perl Makefile.PL $ make $ make test $ sudo make install
Here's source for a test program. This one does the same as the Java sample in "Installing the PostgreSQL JDBC Driver." Copy the code to hello_postgresql.pl and run with perl.
emacs hello_postgresql.pl perl hello_postgresql.pl database username password
# Tests DBI connectivity to a PostgreSQL database.
use DBI;
if (scalar(@ARGV) != 3) {
print "Use: perl hello_postgresql.pl ";
print "<database> <username> <password>\n";
exit(1);
}
my $dbh = DBI->connect("dbi:Pg:dbname=$ARGV[0]", $ARGV[1],
$ARGV[2], {RaiseError => 1});
print "Connection to " .
$dbh->selectall_arrayref("SELECT VERSION();")->[0][0] .
" successful.\n";
my $sql = "CREATE TABLE dbi_demo (code INT, text VARCHAR(20))";
print "Executing: $sql\n";
my $sth = $dbh->do($sql);
$sql = "INSERT INTO dbi_demo VALUES (1, 'One')";
print "Executing: $sql\n";
$sth = $dbh->do($sql);
$sql = "INSERT INTO dbi_demo VALUES (3, 'Four')";
print "Executing twice: $sql\n";
$sth = $dbh->prepare($sql);
$sth->execute();
$sth->execute();
$sql = "UPDATE dbi_demo SET text = 'Three' WHERE code = 3";
print "Executing: $sql\n";
print $dbh->do($sql) . " rows were updated.\n";
print "Demonstrating a prepared statement...\n";
$sql = "INSERT INTO dbi_demo VALUES (?, ?)";
$sth = $dbh->prepare($sql);
print "Statement form: $sql\n";
print "Looping three times, filling in the fields...\n";
for (10..12) {
print "$_...\n";
$sth->bind_param(1, $_);
$sth->bind_param(2, "HiHo");
$sth->execute();
}
print "Executing: SELECT * FROM dbi_demo\n";
$sth = $dbh->prepare("SELECT * FROM dbi_demo");
$sth->execute();
while (my $r = $sth->fetchrow_hashref()) {
print "code = $$r{'code'}; text = $$r{'text'}\n";
}
$sql = "DROP TABLE dbi_demo";
print "Executing: $sql\n";
$sth = $dbh->do($sql);
$dbh->disconnect();