HOW TO INSTALL POSTGRESQL9.1 ON UBUNTU

First install python-software-properties:

$sudo apt-get install python-software-properties

Add PPA repository and update apt:

$sudo add-apt-repository ppa:pitti/postgresql

Now install postgresql:

$sudo apt-get install postgresql-9.1 libpq-dev

Check the installation:

$locate postgresql 

$psql -V             # check psql version

psql (PostgreSQL) 9.1

$finger postgres     # check if user postgres is created 

Login: postgres          Name: PostgreSQL administrator
Directory: /var/lib/postgresql       Shell: /bin/bash
Never logged in.
No mail.
No Plan.

$ su postgres          # switch user to postgres
$ psql                 # launch psql as postgres and check the server version
psql (9.1)

postgres=# select version();

Setup Root User 'posrgres':

The installer has created a unix user postgres without a password. So first I give a (unix) password to this special user. The user postgress is a root user (database administrator) of PostgreSQL Server but without a (PostgreSQL) password. So I give it one here as well.

$ sudo passwd postgres # give the postgres user a (unix) password
$ su postgres           # switch to the user postgres
$ psql                   # launch psql to give postgres PostgreSQL passord
postgres=# alter user postgres with password 'secret';  # new password for postgres 
ALTER ROLE
postgres=# \q            # quit psql
$ exit                   # exit from user 'postgres'
exit

Configure PostgreSQL Server:

$ su postgres    # switch to the user postgres
$ cd /etc/postgresql/9.1/main
$ ls -la 

postgres@ubuntu-pc:/etc/postgresql/9.1/main$ cp pg_hba.conf pg_hba.conf.bak.original 
postgres@ubuntu-pc:/etc/postgresql/9.1/main$ cp postgresql.conf postgresql.conf.bak.original 

postgres@ubuntu-pc:/etc/postgresql/9.1/main$ ls -la

Make changes to pg_hba.config (authetification methods).

host    all         all       127.0.0.1/32       trust          # md5 -> trust

Make changes to postgresql.conf for error log.

#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------
log_destination = 'stderr'  # 2011.07.04 - enabled
logging_collector = on   # 2011.07.04 - enabled and turned on
log_directory = 'pg_log'  # 2011.07.04 - enabled. I will create this folder (see below).
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # 2011.07.04 - enabled
log_truncate_on_rotation = off  # 2011.07.04 - enabled and keep it off
log_rotation_age = 1d   # 2011.07.04 - enabled
log_rotation_size = 10MB  # 2011.07.04 - enabled

Create a log directory as specified in the config above:

$ su postgres         # switch to postgres user
$ cd ~/9.1/main/      # /var/postgresql/9.0/main
$ mkdir pg_log        # create a new log directory as specified in the config file above
$ ls -F               # confirm the new directory 'pg_log'
PG_VERSION  pg_log/ ...
$ exit

 

Restart the server:

$ sudo /etc/init.d/postgresql restart
 * Restarting PostgreSQL 9.1 database server  
$ sudo /etc/init.d/postgresql status
Running clusters: 9.1/main 

Check the new log:

$ sudo ls /var/lib/postgresql/9.1/main/pg_log
postgresql-2011-07-23_135126.log  

 Finally Install pgAdmin III (GUI tool for PostgreSQL) :

$ sudo apt-get install pgadmin3   # install the latest pgAdminIII
$ pgadmin3                        # launch it