How to install and configure PostgreSQL server on Ubuntu Linux 9.04

Installation

sudo apt-get install postgresql

Create database

sudo su postgres
psql
postgres=# CREATE DATABASE dsbis_db WITH ENCODING 'utf-8';

where dsbis_db is database name.

Create database user

postgres=# CREATE ROLE dsbis_db_user LOGIN PASSWORD 'ojweod30';
postgres=# ALTER DATABASE dsbis_db OWNER TO dsbis_db_user;

Configure host-based authorization

If you need e.g. allow remote access to this database, you need to edit this configuration file: /etc/postgresql/8.3/main/pg_hba.conf.

To be able to connect locally with your newly added database user, add this line:
local all all md5
possibly as replacement of this line:
local all all ident sameuser
if you do not want to allow connecting to database to all local users of your Linux system.

Restart database server

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

NOTE: Now, you can set your newly created database login/password in your application settings and it should work. But remember, that if you application resides on another host, probably you will need to allow connections from it in pg_hba.conf (see section above, about host baset authorization).