Flickr Badge

Wednesday, August 31, 2011

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

5 comments:

JERALD JAMERSON said...

I'm running 11.02 and it is slow. I would like to invite you to my blog http://jerald-jamerson.blogspot.com

Musthafa Ullal said...

I used Ubuntu for 1 month, everything was fine for me except the file sharing differences in LAN networks made me to uninstall on all systems. However, nice tutorial

esther said...
This comment has been removed by a blog administrator.
Anonymous said...

thank you!

i had this locale problem with a Vagrant VM (Ubuntu 10.04).


- lucas pottersky.

Anonymous said...

Not so far I have found new cool tool to work with PostgreSQL on ubuntu — Valentina Studio. Its free edition can do things more than many commercial tools!!

I very recommend check it. http://www.valentina-db.com/en/valentina-studio-overview