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 volunteer 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.4 (assuming you've installed the optional Developer Tools). If you're running an earlier version of Mac OS X, use Fink to install the readline and openssl libaries.
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://ftp3.us.postgresql.org/pub/postgresql/v8.1.4/postgresql-8.1.4.tar.bz2 ;\ bunzip2 postgresql-8.1.4.tar.bz2 ; tar xvf postgresql-8.1.4.tar ;\ cd postgresql-8.1.4
Run configure:
$ ./configure --with-perl --with-python --with-tcl --with-krb5 \ --with-openssl --with-pam --with-bonjour --enable-thread-safety
If you installed the readline and openssl libraries with Fink, you'll need to add the --with-includes=/sw/include and --with-libraries=/sw/lib flags to your your configure statement.
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 $ echo 'export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/pgsql/lib' >> ~/.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 niload passwd . <<EOF postgres:*:499:499::0:0:PostgreSQL Server:/var/empty:/usr/bin/false EOF $ sudo niload group . <<EOF postgres:*:499: EOF
Users with GUIDs less than 500 are not shown in the Accounts control panel.
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
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 -u postgres /usr/local/pgsql/bin/pg_ctl -D /Library/PostgreSQL/data start
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
Turn on stats_row_level and autovacuum to enable autovacuuming. Edit /Library/PostgreSQL/data/postgresql.conf so it looks as shown:
$ sudo -u postgres emacs /Library/PostgreSQL/data/postgresql.conf
stats_row_level = on #stats_reset_on_server_start = off #---------------------------------------------------------- # AUTOVACUUM PARAMETERS #---------------------------------------------------------- autovacuum = on # enable autovacuum subprocess?
Load your new settings:
$ sudo -u postgres /usr/local/pgsql/bin/pg_ctl -D /Library/PostgreSQL/data reload
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
You can stop a manually started PostgreSQL instance with the following command:
$ sudo -u postgres /usr/local/pgsql/bin/pg_ctl -D /Library/PostgreSQL/data stop
Don't be alarmed if you see "could not save history to file… .psql_history" after entering \q to exit the interactive mode of psql. This is a harmless (psql writes history despite the message) and known Mac OS X bug introduced in PostgreSQL 8.1. Change if (write_history(fname) == 0) to if (write_history(fname) > 0) in line 218 of src/bin/psql/input.c before running make if you want to remove the warning.
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.
A PostgreSQL startup item configures Mac OS X to start the PostgreSQL server when your computer starts. Stopping, starting, and restarting the server becomes then as simple as
$ sudo SystemStarter stop PostgreSQL $ sudo SystemStarter start PostgreSQL $ sudo SystemStarter restart PostgreSQL
See the SystemStarter man page for information. You can disable automatic startup at any time by editing /etc/hostconfig.
To add a PostgreSQL startup item:
Create the PostgreSQL startup item directory:
$ sudo mkdir /Library/StartupItems/PostgreSQL
Create the PostgreSQL startup script, /Library/StartupItems/PostgreSQL/PostgreSQL:
#!/bin/sh
# Source common setup, including /etc/hostconfig
. /etc/rc.common
StartService ( ) {
# Don't start unless PostgreSQL is enabled in /etc/hostconfig
if [ "${POSTGRESQL:-NO-}" = "-YES-" ]; then
ConsoleMessage "Starting PostgreSQL"
sudo -u postgres /usr/local/pgsql/bin/pg_ctl \
-D /Library/PostgreSQL/data \
-l /Library/PostgreSQL/data/logfile start
fi
}
StopService ( ) {
ConsoleMessage "Stopping PostgreSQL"
sudo -u postgres /usr/local/pgsql/bin/pg_ctl -D /Library/PostgreSQL/data stop
}
RestartService ( ) {
if [ "${POSTGRESQL:-NO-}" = "-YES-" ]; then
ConsoleMessage "Restarting PostgreSQL"
StopService
StartService
else
StopService
fi
}
RunService "$1"
Make the PostgreSQL script executable:
sudo chmod 755 /Library/StartupItems/PostgreSQL/PostgreSQL
Create the PostgreSQL startup parameters file, /Library/StartupItems/PostgreSQL/StartupParameters.plist:
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd"> <plist version="1.0"> <dict> <key>Description</key> <string>PostgreSQL</string> <key>OrderPreference</key> <string>Late</string> <key>Provides</key> <array> <string>PostgreSQL</string> </array> <key>Requires</key> <array> <string>Network</string> </array> </dict> </plist>
Append a PostgreSQL entry to /etc/hostconfig:
$ sudo perl -e 'open H,">>/etc/hostconfig";print H "POSTGRESQL=-YES-\n";'
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.1-407.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/D/DB/DBDPG/DBD-Pg-1.47.tar.gz ;\ tar xvfz DBD-Pg-1.47.tar.gz ;\ cd DBD-Pg-1.47
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();