Skip Navigation

Installing PostgreSQL on Mac OS X 10.5

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.

Preparing for Installation

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:

  1. 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
    

Building and Installing PostgreSQL

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:

  1. 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
    
  2. 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.

  3. Run make, make check, and make install:

    $ make
    $ make check
    $ sudo make install
    
  4. 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
    

Initializing and Configuring PostgreSQL

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:

  1. 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.

  2. 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
    
  3. 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
    
  4. 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.

  5. 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.

  6. 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.

  7. Set the postgres user's password:

    $ sudo -u postgres /usr/local/pgsql/bin/psql -U postgres -c \
    "ALTER USER postgres PASSWORD 'password';"
    
  8. 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
    
  9. 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
    
  10. 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';"
    
  11. 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.

Installing the PostgreSQL JDBC Driver

All you need to do is copy the PostgreSQL JDBC driver to your Java extensions folder.

To install the PostgreSQL JDBC driver:

  1. 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);
    }
  }
}

Installing the Perl DBI and the PostgreSQL Database Driver

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:

  1. Start the CPAN module interactive shell:

    $ sudo perl -MCPAN -e shell
    
  2. Install the DBI bundle:

    cpan> force install Net::Daemon
    cpan> install Test::Simple Bundle::DBI
    
  3. Stop the CPAN module interactive shell:

    cpan> quit
    
  4. 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
    
  5. 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
    
  6. 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();