Five time winner of The Linux Journal Editors' Choice Award for best DBM, PostgreSQL  has garnered praise both from the industry and the users for being an exceptional database management system. Here is how to install PostgreSQL on Ubuntu..

PostgreSQL is a object –relational database management system with a flexible BSD-style license. It boasts of a lot of advanced features, has excellent speed and conforms to the standards.

Many of the programming languages like C, C++, Java, PHP are bound with PostgreSQL. Simple web applications to huge databases are supported by it.

Client Installation

If only PostgreSQL server connection is required, install the PostgreSQL client instead of the whole package. That can be done by the following command

sudo apt-get install postgresql-client

After that connect to the server using the command given below

psql -h server.domain.org database user

Then enter the password which will let you access PostgreSQL with the help of line commands like the one given below

SELECT * FROM table WHERE 1;

The connection can be terminated with the following command

\q

Installation

Install PostgreSql 8.1 in Dapper(Ubuntu 6.06 LTS)

To install PostgreSQL 8.1 in Dapper, type the following in the command line :

sudo apt-get install postgresql-8.1

Install PostgreSql 8.3 in Hardy(Ubuntu 8.04)

To install the latest Postgresql 8.3, type the following in the command line :

sudo apt-get install postgresql

Install PostgreSql 8.4 in Intrepid(Ubuntu 8.10), Karmic(Ubuntu 9.10), Lucid(Ubuntu 10.04) and Maverick(Ubuntu 10..10)

To install Postgresql 8.4 in Ubuntu versions 8.10,9.10,10.04 and 10.10, type the following in the command line

sudo apt-get install postgresql

Administration

pgAdmin III is a useful GUI for PostgreSQL that will come in handy especially to beginners. Install it with the following command:

sudo apt-get install pgadmin3

These packages may be installed also by using the Synaptic package manager from the System -> Administration menu.

Basic Server Setup

First of all, we need to change the PostgreSQL postgres user password without which we will not be able to access the server. As the “postgres” Linux user, we will execute the psql command.

Type the commands given below each of the following Ubuntu versions to set up the server

To install server in Dapper(Ubuntu 6.06 LTS)

sudo -u postgres psql template1

To install server in Intrepid(Ubuntu 8.10), Karmic(Ubuntu 9.10), Lucid(Ubuntu 10.04) and Maverick(Ubuntu 10.10)

sudo -u postgres psql postgres

You can set the password for the "postgres" database role with the following command and give the password when asked for it. This password will be hidden from the console to ensure privacy:

\password postgres

To exit the posgreSQL prompt, type Control+D

Create database

To create the first database, simply type(here mydb is our database name):

sudo -u postgres createdb mydb

Install Server Instrumentation for Postgresql 8.4

To install Server Instrumentation for Postgresql 8.4 , type the following command:

sudo apt-get install postgresql-contrib

Run the adminpack.sql script by the following command:

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

Alternative Server Setup

This can be used if you don't want to connect to the database from other machines.Postgresql is configured to use 'ident sameuser' authentication for any connections from the same machine by default. You can refer to the Postgresql documentation for more information. This basically implies  that if your Ubuntu username is 'foo' and you add 'foo' as a Postgresql user then you can connect to the database without using a password.

This is how you create a database account (which is also a database superuser in this particular case) with the same name as your login name and a generate a password for it. This is necessary as the only user who can connect to a fresh install is the postgres user.

sudo -u postgres createuser --superuser $USER
sudo -u postgres psql
postgres=# \password $USER

By default, Client programs connect to the local host using the Ubuntu login name and expect to find a database with the same name. So we suggest that you use your new superuser privileges granted above to create a database with the same name as your login name:

createdb $USER

Now you can connect to your own database easily with:

psql

If you want to create an additional database, type:

create database amarokdb;

Now you can  go  ahead and instruct Amarok to use postgresql to store its music catalog. The database would be called amarokdb, your own login name would be the user name, and absolutely no password is required thanks to 'ident sameuser'.

Using pgAdmin III GUI

Start by  firing up a graphical client by typing  :

pgadmin3

This will take you to the pgAdmin III interface. Here click on the "Add a connection to a server" button (top left). In the new dialog, enter the address 127.0.0.1, a description of the server, the default database ("mydb" in the example above), your username ("postgres") and your password.

Now you can start creating and managing databases, query the database, execute SQl  and do much more with this GUI.

Managing the Server

Managing users and rights

First  edit /etc/postgresql/8.1/main/pg_hba.conf and modify the existing default configuration to manage users. For instance, if postgres should be able to manage its own users (who are not linked with system users), add the following :

# TYPE  DATABASE    USER        IP-ADDRESS        IP-MASK           METHOD
host    all         all         10.0.0.0       255.255.255.0    md5

Thus  on your local network (10.0.0.0/24 - replace with your own local network !), postgres users can connect to the database through the network by just providing a normal user / password.

In addition to allowing a user to connect over the network to the to a database on the server, postgre must be enabled to listen across different networks. In order to do that, type

/etc/postgresql/8.3/main/postgresql.conf

in the editor of your choice and change the listen_addresses to that given below:

listen_addresses = '10.0.0.1,localhost'

Here 10.0.0.1 is the server's IP address on the local network. Please note that ,by default,this setting is commented out.

If you want to create a database where a user has full rights over the database, type  the following in the command line:

sudo -u postgres createuser -D -A -P myuser
sudo -u postgres createdb -O myuser mydb

Here,the first command  creates the user with no database creation rights (-D)  and no add user rights (-A) and will prompt you to enter a password (-P). The second command  creates the database 'mydb with 'myuser' as the owner.

Hope this small example satisfies your needs. If you would like a more detailed interpretation, do check out the online documentation.

Restarting the Server

You may need to restart the server after configuring the networking / users. This is how we suggest you do it :

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

Further reading

We suggest that you read up on SQL if you are not familiar with it since it is very powerful and come in useful all the time.But some simple uses of PostgreSQL like simple Django project  do not require this knowledge.You can also refer the official PostgreSQL site for more information.The PostgreSQL website contains a wealth of information on using this database.

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