Software > Mac OS X Packages > PostgreSQL

PostgreSQL Database Server

• Introduction
• Installation Instructions
• Update Instructions
• Examples
• Additional Information
• Build Instructions

Introduction

A package of the PostgreSQL database server. This is a very robust and reliable database.
The official homepage is at http://www.postgresql.org.

This particular package was built with support for:

• GNU Readline library (means you have a command history in the interactive psql interpreter, use the up and down keys)
• JDBC drivers (in /usr/local/pgsql/share/java)
• ODBC
• C++
• Multibyte / Unicode
• OpenSSL integration
• tcl

Installation Instructions

Versions 8.1.3 and up have been tested and will work on Mac OS X version 10.4/Tiger only. The software is built as Universal Binary and will run natively on PPC and Intel Macs.

NOTE: On Mac OS X 10.3 (and possibly later), it seems you have to adjust some system configuration parameters first.

You must have administrator rights to perform this installation.

Do this to install for the first time (see below for update instructions):

1. On Mac OS X version 10.4: Download and install the package pgsql-8.2.3.pkg.tar.gz
   (Note: If you get the message You cannot install this software on this disk. (null), try to click the Go Back and then the Continue buttons.
2. Create a new user in the Users System Prefs pane:
   1. Name: PostgreSQL User
      
   2. Short Name: postgres
      
   3. Password: whatever you want
      
3. Open a terminal window and type in the following commands:
4. type sudo chown -R postgres /usr/local/pgsql/, enter administrator password when asked
5. type su - postgres, enter the password of the newly created postgres user when asked
6. type /usr/local/bin/initdb -E UTF8 -D /usr/local/pgsql/data and wait for the operation to complete
7. type /usr/local/bin/pg_ctl -D /usr/local/pgsql/data -l postgres.log start
8. Now you can create a test database with /usr/local/bin/createdb test
9. Use it with /usr/local/bin/psql test

If you would like PostgreSQL to start up automatically when the system comes up, then you can install this package which installs a StartupItem into /Library/StartupItems:
pgsql-startupitem-1.2.pkg.tar.gz

Here is a little image that you assign to the new postgres user account in the "Users" system control panel so it looks a bit nicer in the login dialog: postgres.tif

Update Instructions

If you previously installed an older version of this package, here is what you have to do to update to the most recent version:

1. Before you install the new package, you have to dump the old database contents:
   1. Open a Terminal window
   2. Change to the postgres user using su - postgres
   3. Dump the database using /usr/local/bin/pg_dumpall > /tmp/postgresql.dump
2. Shut down the DB server with /usr/local/bin/pg_ctl stop -D /usr/local/pgsql/data/
3. Download and unpack the new package
4. Install the package by double-clicking on the package icon and going through the installer screens
5. In a new terminal window, change permissions:
   1. sudo chown -R postgres /usr/local/pgsql/
6. Back in the first terminal window, move the old data directory and create a new one
   1. mv /usr/local/pgsql/data/ /tmp/
   2. mkdir /usr/local/pgsql/data/
7. Initialize the new data directory
   1. /usr/local/bin/initdb -D /usr/local/pgsql/data
8. Start the DB server using /usr/local/bin/pg_ctl -D /usr/local/pgsql/data -l postgres.log start
9. Reload the previously dumped data
   1. /usr/local/bin/psql -f /tmp/postgresql.dump template1

Examples

• TestPostgreSQL.java is a short Java example that shows how to access the DB server using the JDBC interface.

Additional Information

• Mac OS X Technical documentation at postgresql.org
• PostgreSQLX is another distribution of PostgreSQL for Mac OS X that looks very promising. It seems to embed the database server into a regular double-clickable Mac OS X application, complete with GUI for the server settings. This might be ideal if you don't need a faceless database server process running in the background all the time.
• pgsqlformac at sourceforge.net is a distribution of postgresql for Mac OS X similar to mine here, except that it seems to install into /Library, so if you prefer that to installation into /usr/local this might be worth looking at.
• pgAdmin III is, according to a fan who wrote me about it, “quite possibly the SEXIEST GUI admin software out there for postgresql”.
• Paul Naro has written PostMan Query, a GUI application for querying PostgreSQL databases.
• Psyex has an article about installing PostgreSQL from source, with lots of explanations.
• DbVisualizer is a commercial graphical front end for various databases.
• pgSQL4RB PostgreSQL Classes for REALbasic
• phpPgAdmin is a web-based (PHP) administration tool for PostgreSQL
• SQLBoss is a commercial graphical front end for various databases, among them MySQL...
• MacSQL is a commercial graphical front-end to PostgreSQL and MySQL
• Here is an introduction to SQL
• EOAdaptor, Enterprise Objects Adaptor for PostgreSQL.
• Jeroen Clarysse suggests the program Cyclone for converting Mac-Roman encoded text to the ISO character set, which is what these programs expect as input. For example, this might be useful if you move your data from FileMaker to one of these databases.
• pgreplicator looks very interesting if you need to create a replicated set of servers
• www.msys.ch has a package with only the client-side libraries for developers

Build Instructions

Here are the general steps needed to create the above package from source.

• Download the source from www.postgresql.org and unpack it.
• Download, build and install the software / libraries for which you would like to include support in your module, e.g. libreadline etc. You will need Jakarta Ant to build the Java interface.
• configure the source, my configure line reads
  ./configure --bindir=/usr/local/bin --mandir=/usr/local/share/man/ --enable-recode --with-CXX --enable-odbc --with-java --enable-syslog --enable-unicode-conversion --enable-multibyte
• Build using make, wait until finished, then do a sudo make install.