Skip Navigation

Installing PostgreSQL on Mac OS X 10.4

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.

Preparing for Installation

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:

  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 \ ;\
    bunzip2 postgresql-8.1.4.tar.bz2 ; tar xvf postgresql-8.1.4.tar ;\
    cd postgresql-8.1.4
  2. 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.

  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
    $ echo 'export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/pgsql/lib' >> ~/.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 niload passwd . <<EOF
    postgres:*:499:499::0:0:PostgreSQL Server:/var/empty:/usr/bin/false
    $ sudo niload group . <<EOF

    Users with GUIDs less than 500 are not shown in the Accounts control panel.

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

  4. Start PostgreSQL:

    $ sudo -u postgres /usr/local/pgsql/bin/pg_ctl -D /Library/PostgreSQL/data start
  5. Set the postgres user's password:

    $ sudo -u postgres /usr/local/pgsql/bin/psql -U postgres -c \
    "ALTER USER postgres PASSWORD 'password';"
  6. Tighten security. Edit /Library/PostgreSQL/data/pg_hba.conf so it looks as shown:

    $ sudo -u postgres emacs /Library/PostgreSQL/data/pg_hba.conf
    # "local" is for Unix domain socket connections only
    local   all         postgres                          md5
    local   sameuser    all                               trust
    # IPv4 local connections:
    host    all         postgres          md5
    host    sameuser    all          trust
    # IPv6 local connections:
    host    all         postgres    ::1/128               md5
    host    sameuser    all         ::1/128               trust
  7. 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 = on             # enable autovacuum subprocess?
  8. Load your new settings:

    $ sudo -u postgres /usr/local/pgsql/bin/pg_ctl -D /Library/PostgreSQL/data reload
  9. 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';"
  10. 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 \
$ 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.

local   databasename   +dbgroupname                   md5
host    databasename   +dbgroupname    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/, specifying a nondefault port number (5431, for example), then restarting Apple Remote Desktop. More information is available.

Adding a PostgreSQL Startup Item

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:

  1. Create the PostgreSQL startup item directory:

    $ sudo mkdir /Library/StartupItems/PostgreSQL
  2. Create the PostgreSQL startup script, /Library/StartupItems/PostgreSQL/PostgreSQL:

    # 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
    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"
    RunService "$1"
  3. Make the PostgreSQL script executable:

    sudo chmod 755 /Library/StartupItems/PostgreSQL/PostgreSQL
  4. 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"
    <plist version="1.0">
  5. Append a PostgreSQL entry to /etc/hostconfig:

    $ sudo perl -e 'open H,">>/etc/hostconfig";print H "POSTGRESQL=-YES-\n";'

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 ;\

Here's source for a simple test program. Copy the following code to, compile with javac, and run with java.

java HelloPostgresql database username password
 * Class HelloPostgresql tests JDBC connectivity to a PostgreSQL database.
 * Adapted from code at

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);
    sql = "INSERT INTO jdbc_demo VALUES (1, 'One')";
    System.out.println("Executing: " + sql);

    sql = "INSERT INTO jdbc_demo VALUES (3, 'Four')";
    System.out.println("Executing twice: " + sql);

    sql = "UPDATE jdbc_demo SET text = 'Three' WHERE code = 3";
    System.out.println("Executing: " + 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

    System.out.println("Executing: " + "SELECT * FROM jdbc_demo");
    ResultSet results = stmt.executeQuery("SELECT * FROM jdbc_demo");
    if (results != null) {
      while ( {
        System.out.println("code = " + results.getInt("code") + "; " +
          "text = " + results.getString(2));

    sql = "DROP TABLE jdbc_demo";
    System.out.println("Executing: " + sql);


  public static void correctUse() {
    System.out.print("Use: java HelloPostgresql ");
    System.out.println("<database> <username> <password>");

  public static void main (String args[]) {
    if (args.length != 3) correctUse();
    try {
      HelloPostgresql demo = new HelloPostgresql(args);
    catch (Exception e) {

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 \ ;\
    tar xvfz DBD-Pg-1.47.tar.gz ;\
    cd DBD-Pg-1.47
  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 and run with perl.

perl database username password
# Tests DBI connectivity to a PostgreSQL database.
use DBI;

if (scalar(@ARGV) != 3) {
    print "Use: perl ";
    print "<database> <username> <password>\n";

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);

$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");

print "Executing: SELECT * FROM dbi_demo\n";
$sth = $dbh->prepare("SELECT * FROM dbi_demo");
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);