installing postgres on mac os x

You want to run or develop database applications using Postgres on Apple Mac OS X.

Postgres don't provide binaries for Mac OS X. Thankfully, compiling and setting up the database engine yourself is straightforward. This procedure shows you have to install and configure Postgres in your own account: root access is not required. This procedure has been tested with Postgres 8 on Mac OS X 10.5 (Leopard) and Mac OS X 10.4 (Tiger). I'd be interested in hearing reports about the success of this procedure with different versions.

NB. The $ shown at the start of commands should not be typed, it represents a shell prompt.

You need:

Installing Postgres

Start by uncompressing the downloaded Postgres source code. Open a Terminal window, then if you've downloaded v8.2.5 you run (and similarly for other versions):

$ tar xvjf postgresql-8.2.5.tar.bz2
$ cd postgresql-8.2.5

You now need to run the configure script with the install location you wish to use. For example, if you want to install Postgres within a directory called postgres in your Applications directory (/Users/$USER/Applications) you would use:

$ ./configure --prefix=/Users/$USER/Applications/postgres

Configure takes a little while to run on older machines. When it's finished you're ready to compile and install:

$ make
$ make install

Configuring the Environment

In order to find the postgres binaries and man pages easily you should add their paths to your profile. If you're using bash (the default shell in Mac OS X since 10.3) you can run the following:

$ echo "PATH=$PATH:/Users/$USER/Applications/postgres/bin" >> ~/.bash_profile
$ echo "MANPATH=$MANPATH:/Users/$USER/Applications/postgres/man" >> ~/.bash_profile

This change will take effect next time you log in to a shell. To make the change immediately you can source the profile file:

$ source ~/.bash_profile

To check this worked try running the following commands:

$ psql --version
psql (PostgreSQL) 8.2.5
contains support for command-line editing

$ man psql
  <man page should load...>

If you get an error that the command or man page hasn't been found, open your ~/.bash_profile file in an editor and check it.

Creating a Database Data Directory

You are now ready to create and configure directory to store your databases in. If you want to store your database data in a directory called 'postgres' in your home directory you would use:

$ mkdir ~/postgres
$ chmod 0700 ~/postgres
$ initdb -D ~/postgres

Controlling Postgres

Basic Start and Stop

You control the database engine with the pg_ctl command. To start the database engine run (-D specifies the location of your database data directory):

$ pg_ctl start -D ~/postgres

LOG:  database system was shut down at 2007-11-20 17:24:12 GMT
LOG:  checkpoint record is at 0/42BF00
LOG:  redo record is at 0/42BF00; undo record is at 0/0; shutdown TRUE
LOG:  next transaction ID: 0/593; next OID: 10820
LOG:  next MultiXactId: 1; next MultiXactOffset: 0
LOG:  database system is ready

Similarly, you stop the engine with:

$ pg_ctl stop -D ~/postgres
	
waiting for server to shut down....LOG:  received smart shutdown request
LOG:  shutting down
LOG:  database system is shut down
 done
server stopped

Stopping and Starting with a Script

Using the pg_ctl command you'll see that log output is written to the screen (stdout) and that you need to specify the data directory every time. To simplify running the server you can use a shell script:

Once the script is installed you can start Postgres with the following simple command (stop and restart work in a similar way):

$ pg.sh start

The script directs log output to postgres.log in ~/postgres (or whatever you set PGDATA to).

Creating and using a Database

Now you have the Postgres server up and running it's time to use it. The first step is creating a new database.

Create Database

Creating a database is easy with the createdb command. To create a database called 'herring' run:

$ createdb herring
CREATE DATABASE

Connecting to the Database

Postgres comes with a command line interface called psql. To connect to a database called 'herring' run:

$ psql herring 
Welcome to psql 8.2.5, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

herring=#

You are now at the database prompt and can issue commands, for example to select data or create tables.

Running Commands on the Database

Let's create a table called fish (you'll get a message about a sequence being created, this is normal):

herring=# CREATE TABLE FISH (id SERIAL, name VARCHAR, number INT);

To see a list of all the tables in our database:

herring=# \dt
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 public | fish | table | flux
(1 row)

To get help on a specific command use \h. For example:

herring=# \h CREATE TABLE

Finally we'll insert a few rows of data and perform a query:

herring=# INSERT INTO fish (name, number) values('plaice', 600);
INSERT 0 1
herring=# INSERT INTO fish (name, number) values('cod', 450);
INSERT 0 1
herring=# INSERT INTO fish (name, number) values('halibut', 78);
INSERT 0 1

herring=# SELECT * FROM fish;
 id |  name   | number 
----+---------+--------
  1 | plaice  |    600
  2 | cod     |    450
  3 | halibut |     78
(3 rows)

To quit the Postgres command line interface use \q.

That wraps up this introduction to using Postgres on Mac OS X. Postgres has excellent online documentation, be sure to check it out.