PostgreSQL is my preferred database on a Linux environment.This is the sequence I used to install it on my development machine. It is running Ubuntu 8.10 (Intrepid Ibex) beta on a VMware Workstation virtual machine.

I referred to https://help.ubuntu.com/community/PostgreSQL, http://bioinformaticsonline.co.uk/2007/02/26/postgresql_on_ubuntu_linux_how_to and  http://hocuspokus.net/2007/11/05/install-postgresql-on-ubuntu-710/ and adapted the instructions a little bit.

Installing the server and basic tools

sudo apt-get install postgresql postgresql-client postgresql-contrib

That got me PostgreSQL 8.3.

Basic Server Setup

First, changing the password of the postgres user:

sudo -u postgres psql postgres
ALTER USER postgres WITH ENCRYPTED PASSWORD '<***password***>';
\q

Creating the initial databases

sudo -u postgres createdb dev
sudo -u postgres createdb test

That creates a database named “dev”, for development, and another named “test”, for testing.

I use schemas to have multiple applications using a single database.

Setting Up the Admin Pack

The admin pack is said enable better logging and monitoring within pgAdmin.

sudo -u postgres psql < /usr/share/postgresql/8.3/contrib/adminpack.sql

Installing pgAdmin III

pgAdmin III is a database design and management application for use with PostgreSQL.

sudo aptitude install pgadmin3

Using pgAdmin

pgadmin3

I clicked on the “Add a connection to a server” button (top left). I filled in the host (localhost) and password of the postgres user (from the previous ALTER USER command). Then I clicked OK and was connected to the local PostgreSQL server.

Incidentally, I noticed that the databases I created were set to UTF-8 encoding. Great, exactly what I wanted.

Restarting the server

If there is any need, the server can be restarted this way:

sudo /etc/init.d/postgresql-8.3 restart

Other tasks

Some times I might want to open up the server for network access and/or install phpPgAdmin to control it with a Web interface. The articles mentioned in the introduction can help with that. I don’t think I’ll have to do either thing on this development environment, though.

Conclusion

That’s it. Quick, easy and powerful. I really like Ubuntu.