How to install PostgreSQL and psycopg2 on Osx Snow Leopard

Hello everyone.

In my last post I showed how to install MySQL from source on Osx.
Among the many comments received, some of them suggested me to use homebrew

I recommend everyone to use this tool in order to easily install several unix packages on OSX.

Among the packages that can be installed there’s also PostgreSQL, the subject of today’s post.

If you install PostgreSQL via homebrew, maybe have a look at this post

This post is instead directed to those who are willing to install the database from source in /usr/local/postgresql-8.4.4

Step 1: Set the $PATH environment variable

Open a terminal and set the $PATH environment variable in order to link the correct folders in /usr/local/

1

mate ~/.profile

Add, if it does not exist, this line at the bottom of the .profile file:

1

export PATH="~/bin;/usr/local/bin:/usr/local/sbin:/usr/local/mysql/bin:/usr/local/pgsql/bin:$PATH"

and reload the $PATH in this way:

1

source ~/.profile

To verify that our $PATH contains the paths set above, type the following command:

1

echo $PATH

Step 2: Download PostgreSQL

Create a new folder to download the sources and compile them:

1
2

mkdir ~/src
cd ~/src

Download the latest version available at the time of writing this tutorial:

1

curl -O http://ftp2.it.postgresql.org/mirrors/postgres/source/v8.4.4/postgresql-8.4.4.tar.gz

Step 3: Compile and Install

Build and install PostgreSQL with the following commands:

1
2
3
4
5
6

tar -zvxf postgresql-8.4.4.tar.gz
rm postgresql-8.4.4.tar.gz
cd postgresql-8.4.4
./configure --prefix=/usr/local/postgresql-8.4.4
ARCH=x86_64 CFLAGS="-arch x86_64" LDFLAGS="-arch x86_64" make
make install

Create a symbilic link, used before in the $PATH

1
2

ln -s /usr/local/postgresql-8.4.4 /usr/local/pgsql
mkdir /usr/local/pgsql/data/

Create now a postgres user, owner of the server:

1
2
3
4
5
6
7
8
9
10
11

dscl localhost create /Local/Default/Users/postgres
dscl localhost create /Local/Default/Users/postgres PrimaryGroupID 0
dscl localhost create /Local/Default/Users/postgres UniqueID 75
dscl localhost create /Local/Default/Users/postgres UserShell /bin/bash
dscl localhost passwd /Local/Default/Users/postgres
dscl localhost create /Local/Default/Users/postgres NFSHomeDirectory /var/home/postgres
mkdir -p /var/home/postgres
chown -Rf postgres:postgres /var/home/postgres
dscl localhost create /Local/Default/Groups/postgres
dscl localhost create /Local/Default/Groups/postgres UniqueID 75
dscl localhost append /Local/Default/Groups/postgres GroupMembership postgres

And let’s give him read permissions over the installation directory:

1

chown -R postgres /usr/local/postgresql-8.4.4/

In a new console, log in as postgres and start the server:

1
2
3

su - postgres
/usr/local/pgsql/bin/initdb -E UTF8 -D /usr/local/pgsql/data/
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data/ -l /usr/local/pgsql/data/postgresql.log start

If the server is started correctly, we can create our test database:

1
2

createdb test
psql test

Now delete unnecessary folders created when creating the user:

1
2
3
4
5

sudo su
rm -rf /var/home
dscl localhost delete /Local/Default/Users/postgres NFSHomeDirectory
dscl localhost passwd /Local/Default/Users/postgres
exit

Then we can create scripts to start and stop the PostgreSQL server using the command line.

We create a bin folder in our home and the following file, also giving execute permissions:

1
2
3

mkdir bin
touch pgsqlscript
chmod +x pgsqlscript

Edit the file you’ve just created by inserting the following body:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

#!/bin/bash

start()
{
echo -n "Starting PostgreSQL server"
sudo su postgres -c '/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data/ -l /usr/local/pgsql/data/postgresql.log start'

return
}

stop()
{
echo -n "Stopping PostgreSQL server"
sudo su postgres -c '/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data/ stop'
return
}

case "$1" in
start)
start
;;
stop)
stop
;;
restart)
stop
start
;;
*)
echo "Usage: {start|stop|restart}"
exit 1
;;
esac
exit $?

At this point you can start the server by typing “pgsqlscript start” in the console. To stop the server just type “pgsqlscript stop”.

Step 4: Install Ruby PostgreSQL drivers

Just install the pg gem to have access to the database.

1

gem install pg

Step 5: Install Python drivers for PostgreSQL

Download the psycopg2 package

1
2
3
4

cd ~/src
curl -O http://initd.org/psycopg/tarballs/psycopg2-2.2.2.tar.gz
tar xzfv psycopg2-2.2.2.tar.gz
cd psycopg2-2.2.2

and modify the setup.cfg file with this one:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

[build_ext]
define=PSYCOPG_EXTENSIONS,PSYCOPG_NEW_BOOLEAN,HAVE_PQFREEMEM,HAVE_PQPROTOCOL3

# PSYCOPG_EXTENSIONS enables extensions to PEP-249 (you really want this)
# PSYCOPG_DISPLAY_SIZE enable display size calculation (a little slower)
# HAVE_PQFREEMEM should be defined on PostgreSQL >= 7.4
# HAVE_PQPROTOCOL3 should be defined on PostgreSQL >= 7.4
# PSYCOPG_DEBUG can be added to enable verbose debug information
# PSYCOPG_OWN_QUOTING can be added, but it is deprecated (will go away in 2.1)
# PSYCOPG_NEW_BOOLEAN to format booleans as true/false vs 't'/'f'

# Set to 1 to use Python datatime objects for default date/time representation.
use_pydatetime=1

# If the build system does not find the mx.DateTime headers, try
# uncommenting the following line and setting its value to the right path.
#mx_include_dir=

# For Windows only:
# Set to 1 if the PostgreSQL library was built with OpenSSL.
# Required to link in OpenSSL libraries and dependencies.
have_ssl=0

# Statically link against the postgresql client library.
static_libpq=0

# "pg_config" is the preferred method to locate PostgreSQL headers and
# libraries needed to build psycopg2. If pg_config is not in the path or
# is installed under a different name uncomment the following option and
# set it to the pg_config full path.
pg_config=/usr/local/pgsql/bin/pg_config

# If "pg_config" is not available, "include_dirs" can be used to locate
# postgresql headers and libraries. Some extra checks on sys.platform will
# still be done in setup.py.
# The next line is the default as used on psycopg author Debian laptop:
#include_dirs=/usr/local/lib

# Uncomment next line on Mandrake 10.x (and comment previous ones):
#include_dirs=/usr/include/pgsql/8.0:/usr/include/pgsql/8.0/server

# Uncomment next line on SUSE 9.3 (and comment previous ones):
#include_dirs=/usr/include/pgsql:/usr/include/pgsql/server

# If postgresql is installed somewhere weird (i.e., not in your runtime library
# path like /usr/lib), just add the right path in "library_dirs" and any extra
# libraries required to link in "libraries".
library_dirs=/usr/local/pgsql/lib
libraries=/usr/lib

At this point we can compile and install the package:

1

python setup.py install