How to setup PostgreSQL

https://help.ubuntu.com/community/PostgreSQL

Basic Server Setup
To start off, we need to change the PostgreSQL postgres user password; we will not be able to access the server otherwise. As the “postgres” Linux user, we will execute the psql command.

In a terminal, type:

Dapper

sudo -u postgres psql template1
Karmic, Lucid, Intrepid and Maverick

sudo -u postgres psql postgres
Set a password for the "postgres" database role using the command:

\password postgres
and give your password when prompted. The password text will be hidden from the console for security purposes.

Type Control+D to exit the posgreSQL prompt.

Create database
To create the first database, which we will call "mydb", simply type:


sudo -u postgres createdb mydb
Install Server Instrumentation for Postgresql 8.4
To install Server Instrumentation for Postgresql 8.4 you may use the command line and type:


sudo apt-get install postgresql-contrib
Run the adminpack.sql script, simply type:

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