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/
Add, if it does not exist, this line at the bottom of the .profile file:
and reload the $PATH in this way:
To verify that our $PATH contains the paths set above, type the following command:
Step 2: Download PostgreSQL
Create a new folder to download the sources and compile them:
cd ~/src
Download the latest version available at the time of writing this tutorial:
Step 3: Compile and Install
Build and install PostgreSQL with the following commands:
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
mkdir /usr/local/pgsql/data/
Create now a postgres user, owner of the server:
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:
In a new console, log in as postgres and start the server:
/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:
psql test
Now delete unnecessary folders created when creating the user:
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:
touch pgsqlscript
chmod +x pgsqlscript
Edit the file you’ve just created by inserting the following body:
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.
Step 5: Install Python drivers for PostgreSQL
Download the psycopg2 package
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:
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:
