Zac Bowling’s Mind

This is one of those things that is under-documented. I’m not going into specifics of how to use Postgres, so this is really for anyone that knows a little about it or is trying to follow some generic platform inspecific instructions on setting up something with it and is stumped by the setup in Debian.

My hope is to help people searching Google for answers like I did (which currently sucks) and which I had to go read the code to figure out. So here is a few tips on using Postgres on Debian using the distribution packages. (I hope this information is indexed well by Google for all so you like it, then digg it or share it or what ever social networking service type link it so this posts index will go up).

In Debian, you are allowed to install multiple versions of Postgres side by side thanks to some magic package called “postgresql-common” (link is for `sid’ as of November 13, 2008 so ask these guys on the Debian postgres packaging project if things have changed much by the time you read this blog entry).

They do this by installing Postgres into multiple directories. As you can see from listing the configuration folder in /etc/.


polystimulus:~/# ls /etc/postgresql/
7.4 8.0 8.1 8.3


One directory for each install basically. They go on step below that even and create this concept called “clusters”. By default each install has a built in “cluster” called main. You can create your own, possibly for each DB, if you want.

Clusters are made to make upgrading from each Postgres version up, easier. Since different packages require different versions of Postgres, and also because end users use Postgres and may not want to be forced to update their code if the distribution wants to upgrade, this works out great.

The problem is that most things assume that normally only have one postgres install, and that is always at one specific port, port 5432, and that at the shell, first “psql” executable in your path is the Postgres client you want to use. Unfortunately with multiple versions running around that assumption doesn’t quite work out right.

In this case we have to do a bit of configuring, tinkering, and setting up our environments.

For the command line tools for postgres, each of them is wrapped by a shell script. This shell script detects the version and cluster you want to use and directs you to the appropriate command line client. It can easily configured using the PGCLUSTER enviroment variable.

I’m not sure which Postgres install gets the port for 5432 by default (on mine it was the 8.1 install for some reason). You can change the port each postgres instance is running under by editing /etc/postgresql/<version>/<cluster>/postgresql.conf

Be sure to read up on clusters, because they are will be a time saver in the future. Read the man file on pg_createcluster, pg_lscluster, pg_ctlcluster, and pg_upgradecluster especially.

As frustrating as having to edit every script I get now to give it a port or new Unix socket address, I still love these tools and I wish I had these tools a few years ago.

What is supprising is that mysql doesn’t have something like this in Debian.

I post this because LeadSwami.com is powered by Postgres and have had my head deep in PostGIS the last few days, geocoding all the addresses in our 19 million records. So much data!

Tags: Code, clusters, Debian, databases, debian postgres, mysql, Postgres, postgres version, psql, shell, sql, ubuntu, Work