Published on Working Software, LLC (http://www.working-software.com)

Home > Blogs > Fred's blog > Building and Installing PostgreSQL on Mac OS X

Building and Installing PostgreSQL on Mac OS X

Submitted by Fred on Mon, 12/31/2007 - 14:41

Here are instructions for building and installing the PostgreSQL database server on a Mac OS X system. This includes instructions for using dscl to create a special user account to manage the database processes and data, and a sample launchd script to automatically launch the database server on system startup.

I assume that you are comfortable working from the Unix shell (i.e., a Terminal window), and that you have system administrator privileges for your Mac OS X account. I also assume that you have the Apple Developer Tools installed on your system. These are distributed with Mac OS X, or you can download them from the Apple Developer Website [3].

You can download the latest PostgreSQL source from the PostgreSQL Download Page [4]. These instructions were written based on my experience installing PostgreSQL 8.2.5 on a Mac OS X 10.5.1 system.

Creating the PostgreSQL Administrator Account

Before you install PostgreSQL on your system you should create a special user account for it. This account will run the database server process and own all the files used by it. To create this account we use the dscl command. But first we need to select a user ID and group ID for the account that will not conflict with existing accounts. Use dscl to list the user IDs (UniqueID) and group IDs (PrimaryGroupID) of all existing accounts:

$ dscl . -list /Users UniqueID
_amavisd            83
_appowner           87
_appserver          79
...
_mysql              74
...
first_user_acct     501
$ dscl . -list /Groups PrimaryGroupID
_amavisd            83
_appowner           87
_appserveradm       81
_appserverusr       79
...
_mysql              74
...
first_user_acct     501
$

For the following discussion I will use ID values of 174, the _mysql ID values plus 100. Whatever values you use they should be less than 500 since values greater than 500 are assigned to standard users.

$ sudo dscl . -create /Users/postgres UniqueID 174
$ sudo dscl . -create /Users/postgres PrimaryGroupID 174
$ sudo dscl . -create /Users/postgres HomeDirectory /usr/local/pgsql
$ sudo dscl . -create /Users/postgres UserShell /usr/bin/false
$ sudo dscl . -create /Users/postgres RealName "PostgreSQL Administrator"
$ sudo dscl . -create /Users/postgres Password \*
$ dscl . -read /Users/postgres
AppleMetaNodeLocation: /Local/Default
GeneratedUID: 28CFEA0C-93D8-9454-A375-C112DB88ECFD
NFSHomeDirectory: /usr/local/pgsql
Password: *
PrimaryGroupID: 174
RealName:
 PostgreSQL Database
 RecordName: postgres
 RecordType: dsRecTypeStandard:Users
 UniqueID: 174
$ sudo dscl . -create /Groups/postgres PrimaryGroupID 174
$ sudo dscl . -create /Groups/postgres Password \*
$ dscl . -read /Groups/postgres
GeneratedUID: DC4E5FFC-9358-479F-875F-178A614CE252
Password: *
PrimaryGroupID: 174
RecordName: postgres
RecordType: dsRecTypeStandard:Groups
$

Note that by setting the password for the account to "*" and the UserShell to /usr/bin/false I make the account essentially unusable as a standard user account, which is what I want. Note also that I have set the HomeDirectory for the account to the eventual location of the installed PostgreSQL files. This helps me keep track of where everything is.

Building, Installing and Configuring

To build PostgreSQL from the source, and install it in the default location, run:

$ cd postgresql-8.2.5
$ ./configure --with-bonjour
$ make
$ sudo make install

This will put all the PostgreSQL content in /usr/local/pgsql. Run configure --help to see how to change the install location and a number of other PostgreSQL build options.

Next create a directory to hold the PostgreSQL database. This directory, referred to as the PGDATA directory, will be owned by the postgres user:

$ sudo mkdir /usr/local/pgsql/data
$ sudo chown postgres:postgres /usr/local/pgsql/data

I put the database directory inside the PostgreSQL install directory for simplicity. Wherever you decide to put it, make sure that it is a path that you back up or that you dump the database regularly to a path that is backed up.

Create the initial database structure by running initdb as user postgres:

$ sudo -u postgres /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
The files belonging to this database system will be owned by user “postgres”.
This user must also own the server process.

...	    

Success. You can now start the database server using:

    /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data
or
    /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start

$

Before starting the database server you may want to change some of the access privileges and logging behaviors. To do this you need to edit the PostgreSQL Host-based Authentication configuration file, pg_hba.conf, in the PGDATA directory just created, and you need to edit the PostgreSQL configuration file, postgresql.conf, in the same directory. Remember that both of these are writable only by user postgres.

The default pg_hba.conf file is fairly liberal, allowing any user on the local system to connect to any database. The following example illustrates how to restrict users to connecting only to their own database. See the PostgreSQL documentation [5] for details about how to control client authentication.

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
 
# Only user postgres can connect to the administrative database
local    template1   postgres                         ident sameuser
 
# Everyone else can connect to the other database, but only if
# they use the same database name as the Unix identd process
# reports for their Unix identity.	
local   all         all                               ident sameuser
host    all         all         127.0.0.1/32          ident sameuser
host    all         all         ::1/128               ident sameuser

The default behavior of the PostgreSQL database server is to write error and log messages to stderr. I will change that by editing the postgresql.conf file in the PGDATA directory so that the database server writes to files in the pg_log directory within the PGDATA directory. From postgresql.conf:

...
#----------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#----------------------------------------------------------------
 
# - Where to Log -
 
#log_destination = 'stderr'         # Valid values are combinations of
                                    # stderr, syslog and eventlog,
                                    # depending on platform.
 
# This is used when logging to stderr:
redirect_stderr = on                # Enable capturing of stderr into log files
                                    # (change requires restart)
 
# These are only used if redirect_stderr is on:
#log_directory = 'pg_log'           # Directory where log files are written
                                    # Can be absolute or relative to PGDATA
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # Log file name pattern.
                                    # Can include strftime() escapes
 
...

Again, see the PostgreSQL documentation for a complete description of the configuration settings for information and error logging.

At this point you can start the PostgreSQL database server with the pg_ctl command:

$ sudo -u postgres /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data start

You should see a log file created in /usr/local/pgsql/data/pg_log. If you edit the postgresql.conf (or pg_hba.conf) configuration file you can use pg_ctl to notify the running server to load the changes:

$ sudo -u postgres /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data reload
postmaster signaled

Finally, you can stop the database server with pg_ctl:

$ sudo -u postgres /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data stop

Launching PostgreSQL On Startup

To have Mac OS X launch the PostgreSQL database server automatically whenever I start my system I wrote the following launchd script, called org.postgresql.dbms.plist and put it in /Library/LaunchDaemons:

<?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>Label</key>
    <string>org.postgresql.dbms</string>
    <key>UserName</key>
    <string>postgres</string>
    <key>GroupName</key>
    <string>postgres</string>
    <key>ProgramArguments</key>
    <array>
      <string>/usr/local/pgsql/bin/postmaster</string>
      <string>-D</string>
      <string>/usr/local/pgsql/data</string>
    </array>
    <key>RunAtLoad</key>
    <true/>
</dict>
</plist>

If I had not already told PostgreSQL to redirect information and error messages from stderr to a file, launchd would catch that output and direct it to the system.log. I could redirect the PostgreSQL output to a file by adding the following to the above .plist settings:

    <key>StandardErrorPath</key>
    <string>/usr/local/pgsql/logs/postgresql.log</string>

but I prefer to keep my PostgreSQL configuration in the PostgreSQL configuration file. For an introduction to the launchd system refer to Apple's Getting Started with launchd [6] article on their website.

The above launchd script will start the PostgreSQL database server when the script is loaded. E.g.,:

$ sudo launchctl load /Library/LaunchDaemons/org.postgresql.dbms.plist

You can then use launchctl to stop and restart the database server:

$ sudo launchctl stop org.postgresql.dbms
$ sudo launchctl start org.postgresql.dbms

By installing org.postgresql.dbms.plist in your Mac OS X system's /Library/LaunchDaemons you assure that it will be loaded, and the PostgreSQL database server launched, at system startup time.

Using PostgreSQL

Once you have built, installed and launched the PostgreSQL database server you are ready to use it. First add the PostgreSQL executable and man-page directories to your PATHs by adding the following to your ~/.bash_profile:

PATH=${PATH}:/usr/local/pgsql/bin
MANPATH=${MANPATH}:/usr/local/pgsql/man
 
export PATH MANPATH

Next I typically create a database account for myself, giving it full database-administrator privileges:

$ sudo -u postgres createuser fred
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) y
CREATE USER
$

I create a default database, with the same name as my account name, to play around in:

fred$ createdb
CREATE DATABASE	
fred$

Finally, I connect to the database and start working:

fred$ psql -d fred
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
 
fred# \dt
No relations found.
fred# 
...

And so on. Refer to the PostgreSQL documentation [5], which you just installed on your system in /usr/local/pgsql/doc!

Upgrading or Removing PostgreSQL

To upgrade a PostgreSQL installation start by dumping all the existing databases. You should do this before stopping the current database server, in case the newer version of the dump utility is unable to process your older data files. Once you have dumped the data you can stop your current version and move it out of the way:

$ pg_dumpall > dump.sql # use -o if your data has large objects!
$ sudo launchctl stop org.postgresql.dbms
$ sudo mv /usr/local/pgsql /usr/local/pgsql.old

Build and install your new version according to the instructions above. Be sure to create a PGDATA directory that is owned by the postgres user. Initialize the PGDATA directory with initdb. E.g.:

$ configure --with-bonjour
$ make
$ sudo make install
$ sudo mkdir /usr/local/pgsql/data
$ sudo chown postgres:postgres /usr/local/pgsql/data
$ sudo -u postgres /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

Once you have created your new database, start the new database server and populate the new database with the data you dumped from your old system:

$ sudo -u postgres /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
$ sudo -u postgres /usr/local/pgsql/bin/psql -d postgres -f dump.sql

Note that as shown above the command to launch PostgreSQL will not return (it runs in the foreground, not the background). You will therefore have to run the first and second commands in separate Terminals/shells. When the second command, psql, finishes you can stop the postgres (same as postmaster) process by typing Control-C in its Terminal window.

Next copy over any local configuration changes you have made to your previous version of PostgreSQL. These are the changes made primarily in postgresql.conf and pg_hba.conf in your data directory.

With your new system installed in the place of the old one, and with it configured and populated with your old data, you can restart the server.

$ sudo launchctl start org.postgresql.dbms

To remove PostgreSQL completely from your system you must remove:

  • The PostgreSQL executables (installed in /usr/local/pgsql above)
  • The PostgreSQL database data (installed in /usr/local/pgsql/data above)
  • The launchd script in /Library/LaunchDaemons
  • The postgres user account

The commands to do this, assuming you followed my example above, are:

$ sudo launchctl stop org.postgresql.dbms
$ sudo rm /Library/LaunchDaemons/org.postgresql.dbms.plist
$ sudo rm -rf /usr/local/pgsql
$ sudo dscl . -delete /Groups/postgres
$ sudo dscl . -delete /Users/postgres

You can also remove the PostgreSQL additions to your PATH and MANPATH environment variables.

Copyright © 2012, Working Software, LLC
Source URL: http://www.working-software.com/node/30

Links:
[1] http://www.working-software.com/taxonomy/term/9
[2] http://www.working-software.com/taxonomy/term/20
[3] http://developer.apple.com/tools/
[4] http://www.postgresql.org/download/
[5] http://www.postgresql.org/docs/manuals/
[6] http://developer.apple.com/macosx/launchd.html