waqas bhatti / notes / setting up postgres and psycopg2 on rhel6

0. Installing Postgres from system packages

This guide is intended mainly for RHEL/CentOS/Scientific Linux since the Postgres packages available from the distribution are rather old. If you don't care about using the latest and greatest, or are using Fedora 16+ with a suitably up to date Postgres version, do the following as root:

$ yum install postgresql postgresql-server postgresql-contrib python-psycopg2

There, all done! Now jump to step 2 below for initial setup of the database server.

1. Installing Postgres 9.2 on RHEL and RHEL-derivatives

First, exclude the system postgresql RPMs from the installation sources, so that they don't overwrite our packages and configuration later on. See here for detailed instructions on how to do so. In brief, we add the following directive to the /etc/yum/repos.d/[rhel/sl/centos main/security].repo file: 
exclude=postgresql*

Now on to the actual installation. As root:

$ wget http://yum.postgresql.org/9.2/redhat/rhel-5-i386/pgdg-sl92-9.2-8.noarch.rpm
$ yum localinstall pgdg-sl92-9.2-8.noarch.rpm
$ yum install postgresql-server postgresql postgresql-contrib postgresql92-devel

For some reason, the RPMs for Postgres 9.2 available from this official repository don't fully update the system path, leaving out the pg_config library package config tool, and causing issues with the installation of psycopg2 later on. Furthermore, Postgres is installed to a nonstandard path (/usr/pgsql-9.2). We need to add the corresponding /usr/pgsql-9.2/bin directory to the system-wide or user path to fix this. To do this system-wide, make a file called postgres-9.2.sh (or whatever) and place it in /etc/profile.d:

#!/bin/sh

export POSTGRESQL_HOME=/usr/pgsql-9.2
pathmunge $POSTGRESQL_HOME/bin

Then issue a source /etc/profile to update the path.

2. Setting up the database server

Now start the server daemon and enable it to start on boot:

$ service postgresql-9.2 initdb
$ service postgresql-9.2 start
$ chkconfig postgresql-9.2 on

(If you're using the default Fedora/RHEL packages, the commands above obviously should not include the '-9.2' prefix.)

Become the postgres user for further setup:

$ su -l postgres

As the postgres user, create a normal user that will access a database, and the associated database:

$ createuser -d -l -r -P [username]
[type in db user password]
createdb -O [username] [dbname]

To allow this user to login securely, we need to edit the /var/lib/pgsql/data/pg-hba.conf file. To the TOP of this file, add the following lines:

local	 [dbname]	 [username]		 			 md5
host	 [dbname]	 [username]		 127.0.0.1/32		 md5

where [dbname] is the database you just created, and [username] is the user that will use this database. Adding the line to the top appears to be very important, since it looks like the rules are parsed in order by line. If you add it to the bottom, then Postgres will try peer authentication first, which will fail if your DB username is not the same as the currently logged in UNIX username.

Reload the configuration files for the Postgres daemon:

$ pg_ctrl reload

Check the log at /var/lib/pgsql/data/pg_log/postgresql-Mon.log to see if everything is going as expected.

Now as the database user you just created, try to connect to the Postgres server:

$ psql -U [username] -W [database]

Hopefully everything is working correctly. Now we should set a password for the postgres UNIX user to lock it down. As root:

$ passwd postgres
$ su - postgres
$ psql -d template1 -c "ALTER USER postgres WITH PASSWORD '[new postgres password]';"

3. Installing psycopg2

NOTE: if you installed psycopg2 using yum earlier, you can safely skip the step below and skip to checking if the whole thing worked.

We will now install psycopg2, the Python bindings for Postgres. It's generally agreed that most Python projects should use virtualenv and pip to be as system-independent as possible. We'll follow this prescription here.

First, we'll install virtualenv and pip:

$ su -c 'yum install python-virtualenv' (needs the EPEL repository enabled)

Now we'll activate a virtual environment and install psycopg2.

$ virtualenv [directory for postgres python project]
$ cd [directory]
$ source bin/activate
$ pip install psycopg2

4. See if it all works

Finally, we can check if the whole thing worked:

$ python
Python 2.6.6 (r266:84292, Jun 18 2012, 09:59:14)
[GCC 4.4.6 20110731 (Red Hat 4.4.6-3)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import psycopg2
>>> db = psycopg2.connect(user='[username]',database='[dbname]',password='[dbpassword]')
>>> cur = db.cursor()
>>> cur.execute('create table test (id int,name varchar)')
>>> cur.execute('insert into test values (%s,%s)', (1,'python'))
>>> cur.execute('insert into test values (%s,%s)', (2,'zugzug'))
>>> cur.execute('select * from test')
>>> rows = cur.fetchall()
>>> rows
[(1, 'python'), (2, 'zugzug')]

Useful references