PostgreSQL on Ubuntu Linux

PostgreSQL is a powerful object-relational database management system, provided under a flexible BSD-style license. PostgreSQL contains many advanced features, is very fast and standards compliant. It supports a large part of the SQL standard and is designed to be extensible by users in many aspects.

Some of the features are: ACID transactions, foreign keys, views, sequences, sub queries, triggers, user-defined types and functions, outer joins, multi version concurrency control. Graphical user interfaces and bindings for many programming languages are available as well.

Installing The Database Server

To install PostgreSQL 8.2 you may use the command line and type:

sudo apt-get install postgresql-8.2

GUI for PostgreSQL

To install pgAdmin III, a handy GUI for PostgreSQL, you may use the command line and type:

sudo apt-get install pgadmin3

Basic Server Setup

Set Password

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:

sudo -u postgres psql template1

Then at the new prompt, type these two commands, replacing secret with the new password (up to you ;))

ALTER USER postgres WITH PASSWORD 'secret';
\q




To create the first database, which we will call “mydatabase”, simply type :

sudo -u postgres createdb mydatabase

Managing The Server

Change Authentication Method

We need to edit file pg_hba.conf to change authentification method for accessing PostgreSQL database.

sudo vim /etc/postgresql/8.2/main/pg_hba.conf

host    all         all         127.0.0.1/32          password



hostname  all         all         192.168.1.52         password 

Which means postgres allow the local and the network host 192.168.1.52. Here the authentication method we using user password. Also
we have to edit the following line in /etc/postgresql/8.2/main/postgresql.conf

1. - Connection Settings -

listen_addresses = '192.168.1.52' # what IP address(es) to listen on;

Create a Database

To create a database with a user that have full rights on the database, use the following command:

sudo -u postgres createuser -D -A -P mynewuser
sudo -u postgres createdb -O mynewuser mydatabase

To Access a Remotehost Database

sudo psql -U username -d databasename -h hostname
eg)
psql -U dhanya -d mydatabase -h 192.168.1.52




That’s it, now all you have to do is restart the server and all should be working!

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

....

Sahab