Setting up Postgres on Ubuntu 10.04

We've just migrated our Django build server to a new box, and I'm currently installing all the necessary dependencies. Even though I've done this many times, I still keep forgetting the steps to do it properly, so this blog post will document the steps for future reference.

PostgreSQL

Installing postgres is a major pain, because there are so many annoying small things to do to get it working properly.

First, before you do anything you want to ensure that you set up your locale to UTF-8.

locale-gen en_US.UTF-8

update-locale LANG=en_US.UTF-8

If you forget to do this and its not setup by default, then postgres will use ASCII which is probably not what you want. If you sill forget this (yes, I forgot), and you haven't put in any data yet, then you can drop and recreate your postgres cluster.

pg_dropcluster --stop 8.4 main

pg_createcluster --start -e UTF-8 8.4 main

Okay, with that out of the way..

Installing Postgres

The latest version of Postgres on Ubuntu 10.04 is Postgres 8.4

sudo apt-get install postgresql-8.4

You'll also need the dev package to compile psycopg2 later

sudo apt-get install libpq-dev

And don't forget the python dev packages to compile psycopg2

sudo apt-get install python-dev

Setting up Postgres

By default postgres is configured to use your system users for authentication. If you want to use a specific user/password combination, you'll need to change this.

Open up /etc/postgresql/8.4/main/ph_hba.conf and change

local all all ident

to

local all all md5

Then restart postgres.

Create a postgres user and database

These are the two commands for creating a user and database. If you intend to run the Django unit tests, then dont forget to give CREATEDB permission for the user.

CREATE USER username WITH PASSWORD 'password' CREATEDB;

CREATE DATABASE db_name OWNER username ENCODING 'UTF-8';

Compiling psycopg2

You should be able to pip install psycopg2. Note that it needs to be compiled, so you should have build-essential package installed beforehand.

sudo apt-get install build-essential

pip install psycopg2

There is a gotcha here: psycopg2 version 2.4.2 is NOT compatible with Django 1.3. Either use the trunk version of Django or use psycopg2 version 2.4.1.

pip install psycopg2==2.4.1

Troubleshooting

1. make or gcc is not found: install the build-essential package
2. postgres gives the error "ERROR: new encoding (UTF8) is incompatible with the encoding of the template database (SQL_ASCII)": You haven't set the locale properly before installing postgres. See the top of the post for setting the locale
3. Django gives the error "Got an error creating the test database: permission denied to create database": If you want to run unit tests, the user must have the CREATEDB permission
4. postgres gives the error FATAL: Ident authentication failed for user "username": You need to edit the pg_hba.conf file to turn off ident authentication and set it to md5 instead
5. psycopg2 gives the error 'PyType_GenericAlloc' undeclared (first use in this function): Install the python-dev package
6. psycopg2 gives the error pg_config: command not found: Install the libpg-dev package