Shady Project

PostgreSQL on OS X

Posted by Shady Thu, 25 Jan 2007 05:19:45 GMT

Yesterday I rambled on about the benefits of RDBMS and spatial data. Today, we'll actually take the first step towards getting all that fun stuff working. Setting up Postgres is not really a difficult task, especially if you have ever built any unix software before. Let's get started.

A few notes before we get started: You will need to keep all this source somewhere on your machine while it's being built. Where you put this is up to you. I usually download everything to ~/Downloads, and extract the files into their own directories inside downloads, then cd to those directories and build the software from there. It makes it easier for me to see what I have downloaded, what I'm working on, and makes clean up of the sources after the build procedure easier. You can, of course, put the source wherever you want to. /usr/local/src is a popular place, so is ~/scratch. Whatever works for you.

Additionally, I will be installing all this software into /usr/local. This is the traditional location for add on software in a unix environment. The build scripts for postgres default to this location. If you wish to be more "mac-like" you could install postgres into, for example, /Library/PostgreSQL if you chose to. It's up to you. I'll show you how to change the location used for the installations. Note that it is important you remember where you install libraries and other software, because if you put things into a non standard location, you will have to tell the compiler where to look for and dependencies.

Postgres doesn't have a whole bunch of dependencies to worry about, unlike a lot of other complex unix-y type software. In fact, there really is only one prerequisite: readline.

Readline is a GPL library for doing fancy things with command histories, line editing, and other things. A lot of other software uses this library(ruby uses it as part of irb, for example), so installing it now will acutally help you in the future should you need to install any other software that relies on it. Steps for installing readline:

1. Download the source distribution from ftp.gnu.org/gnu/readline/readline-5.1.tar.gz using curl, wget, transmit, or you downloading program of choice.
2. Fire up Terminal.app (found in /Applications/Utilities) and change to the directory where you downloaded the file.
3. type in 'tar xvzf readline-5.1.tar.gz' sans quotes and hit return. This will extrat the soruce files for readline and put them into a directory
4. cd readline-5.1
5. Run the following command './configure --prefix=/usr/local' to run the configure script. The --prefix flag allows us to set the base installation directory for this software. If you wanted to put it somewhere else (not reccomended) you could specify that here.
6. Once configure is done running, run 'make' to actually build the software.
7. When all the output from make stops, run 'sudo make install' to install the newly built software.

And you're done! With readline, that is. The next step is to actually build postgres. The steps for this are pretty much the same as they are for readline; namely download, extract, configure, make, install. Instructions for postgres are as follows:

1. Download the current version of the postgres source (8.2.1 as of this writing). Note that you have two options for downloading the source: gzip and bzip. These instructions assume you get the tar.gzip version.
2. Open the terminal, navigate to the directory where you downloaded the source and run 'tar xvzf postgresql-8.2.1.tar.gz' to extract the postgres source to its own directory
3. cd into the postgresql-8.2.1 directory and run 'configure --with-includes=/usr/local/includes --with-libraries=/usr/local/lib' the --with flags tell the compiler where to look for the libraries postgre needs (like readline). Note that if you changed the --prefix when installing readline, you will need to put the appropriate directory in both --with-includes and --with-libraries. Similar to readline, the configure script for postgres will accept the --prefix flag, so if you wish to pust postgres somewhere different, you can specify that here.
4. Now that we have configured everything, we can run 'make'. When make is finished running, run 'sudo make install' to install postgre into /usr/local or other directory you specified in the configure step.

And thats pretty much it as far as compiling and installing postgres is concerned. However if you try to run postgres right now, you will be sorely dissapointed, for a variety of reasons. We still have a few more tasks we must complete before postgres is really up and running.

Our first priority is to set up a postgres user. The postgres daemon expects to be run as an unprivilged user. It also expects your data directory to be owned by this single user, and only accesible by this same user. So lets go ahead and create that user now. We do this in the same fashion as adding any other OS X user: System Preferences -> Accounts and click the little + to add a new one. You can name it whatever you wish, I chose postgres. Enter a good password for the user. Do not check 'Allow user to administer this computer'box. We want this user to have the minimum amount of privileges on our machine for security reasons. Note that this user will show up in the login screen like a regular old user. I'll tell you how to remove this account later.

Now that we have our postgres user set up, we can set up and initialize the data directory postgres will use to store our databases. Steps for initializing our data directory:

1. Open up Terminal.app and run the following command 'mkdir /usr/local/pgsql/data' This will create the actual directory in which our data will be stored. Note that if you installed postgres in a different location, such as /Library/PostgreSQL you can put the data directory in that location. In fact, you can really put this directory anywhere you want; /pgdata would work, or even ~/MyPostgreData.
2. run the command 'chown postgres /usr/local/pgsql/data'. This changes the ownership of the data directory we just created to the postgres user. Again, if you changed the directory, or the name of the postgres user, be sure to replace the username and path above with the values you used.

Now that we have created the user and the data directory, we need to initialize the directory. Postgres expects certain files and other things to be in this data directory, so we have to prepare it for use. In order to do this, we will have to login as the postgres user. However, we won't be logging out and logging back in, we will do this all from the command line:

1. Open up Terminal.app (you really probably should just be keeping it open)
2. Run the command 'su -l postgres' making sure to replace postgres with the username you chose. You will be prompted for the password. Enter the password you created when you set up the postgre user.
3. You are now logged in as the user postgres. Run the following command '/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data', again making sure to supply your prefix if you changed it from /usr/local during configuration.

Whether or not this succeeds will depend on which version of OS X you are running. Prior to 10.4, this will succeed and you will have postgres ready to run. However, if you have 10.4, this will fail with some cryptic error message about shared memory request failing due to due max size being smaller then the requested amount. This may seem daunting at first, be we can fix it.

It turns out that sometime in 10.4, Apple change the maximum amount of shared memory that an application could request. They also added some requirements as to how shared memory requests have to be configured. It all sounds complex, but the fix itself is relativley simple. Just follow these simple steps:

1. Open up Terminal.app
2. Run the command 'cd /etc' to change into the etc directory
3. Run the command 'sudo touch sysctl.conf'. This will create the file sysctl.conf and it will be owned by the root user.
4. We now have to edit the file we just created. Any plain text editor will do, i used command line vim, but you could use TextMate, TextWrangler, or even BBEdit. Add the following lines to this file:
   • kern.sysv.shmmax=4194304
   • kern.sysv.shmmin=1
   • kern.sysv.shmmni=32
   • kern.sysv.shmseg=8
   • kern.sysv.shmall=1024
   Each of these lines should appear on their own line
5. Now you will need to restart your computer to make these changes take effect
6. Once the computer has restarted, redo the steps above, starting with 'su -l postgres' and following through to '/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data'. This time, you should receive no error messages.

Okay, all thats left is to start up the postgres server and try to create some databases. Let's do that, shall we?

1. Do I even need to say it? Open up Terminal.app
2. Login as the postgres user 'su -l postgres' again if you used a different name be sure to use that one. Enter the password.
3. Run the command '/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start'. This command will start the database server. The -D option will tell postgres where to find the data directory. The -l option will tell postgres where to put its logfile. We can set this to any location we desire, so long as postgres has write access. The above line will put the logfile in the current directory, and will name it logfile.
4. You should see the message 'server starting' and now, finally, postgres is up and running. Pat yourself on the back as necessary

Now that we have our server up and running, lets give it a test run shall we? Make sure that you have terminal up and running, and that you are logged in as the postgres user.

1. Run '/usr/local/pgsql/bin/createdb test'. This will, naturally, create a database. Name it whatever you want.
2. Run '/usr/local/pgsql/bin/psql test'. This connects us to our newly created database. The psql utilitiy allows us to execute SQL statements against the database we specify when we run the tool. Lets try to add some tables now.
3. Enter 'create table the_table (the_name varchar primary key, the_id serial);' into the pgsql window, sans quotes. This will create a table named the_table with two fields. You will get some NOTICE: output about the explicit sequence for the serial, and explicit index for primary key. Don't worry about those, it's just a notification that some extra database objects will be created, not a warning or failure.
4. Enter 'insert into the_table (name) values ('Bob');' into the psql prompt sans quotes. This will insert a new row into the table.
5. Now lets see if that insert actually worked. Enter 'select * from the_table;' without quotes, and we should get back a nice little table with our one row.

So it looks like everything is working as intended! Of course, we still have a few issues. Starting up and stopping the server is a pain, some postgres user is hanging around our login window, and we have to be logged in as that user to do anything useful. And we don't have a fancy GUI for managing our database. Not to worry. We'll cover all that stuff (and more) nex time. This post is already long enough, don't you think? Tune in next time for some postgres housekeeping tips and tricks.