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
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
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.
That’s it. Quick, easy and powerful. I really like Ubuntu.