Installing PostgreSQL 9.0 on Ubuntu (11.04) using PPA

The version of default PostgreSQL in the latest Ubuntu (11.04) is still 8.4.8.
So I decided to install 9.0 version when I found there is a PPA (Personal Package Archives) for it. (I have no previous version of PostgreSQL in this Ubuntu PC.)

1. Resources.
PostgreSQL PPA: https://launchpad.net/~pitti/+archive/postgresql

2. Add PPA repository to my Ubuntu.

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


3. Install PostgreSQL 9.0

$ sudo apt-get update
$ sudo apt-get install postgresql


4. Check the installation [updated 2011-07-24]

$ locate postgresql # check the locations of postgresql files
...
/etc/init.d/postgresql # server daemon
/etc/logrotate.d/postgresql-common
/etc/postgresql/9.0
/etc/postgresql/9.0/main # directory for config files
/etc/postgresql/9.0/main/environment
/etc/postgresql/9.0/main/pg_ctl.conf
/etc/postgresql/9.0/main/pg_hba.conf
/etc/postgresql/9.0/main/pg_ident.conf
/etc/postgresql/9.0/main/postgresql.conf
/etc/postgresql/9.0/main/start.conf
...
/usr/lib/postgresql # server apps
/usr/lib/postgresql/9.0
/usr/lib/postgresql/9.0/bin
/usr/lib/postgresql/9.0/lib
...
/var/lib/postgresql # home directory for root user postgres
/var/lib/postgresql/9.0
/var/lib/postgresql/9.0/main # I will create error log directory (pg_log) here
/var/lib/update-rc.d/postgresql
/var/log/postgresql
/var/log/postgresql/postgresql-9.0-main.log
/var/log/postgresql/postgresql-9.0-main.log.1
...

$ psql -V # check psql version
psql (PostgreSQL) 9.0.4

$ 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.0.4)

postgres=# select version();
-----------------------------------------------------------------------------------------------------
PostgreSQL 9.0.4 on i686-pc-linux-gnu, compiled by GCC gcc-4.5.real (Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 32-bit


5. 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


6.Configure PostgreSQL Server
I want to make some changes to server config.

Make backup copies of config files.

$ su postgres # switch to the user postgres
$ cd /etc/postgresql/9.0/main
$ ls -la
...
-rw-r----- 1 postgres postgres 4108 2011-07-02 19:54 pg_hba.conf
-rw-r--r-- 1 postgres postgres 18064 2011-07-02 19:54 postgresql.conf
...

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

postgres@ubuntu-pc:/etc/postgresql/9.0/main$ ls -la
...
-rw-r----- 1 postgres postgres 4108 2011-07-02 19:54 pg_hba.conf
-r-r----- 1 postgres postgres 4108 2011-07-03 18:33 pg_hba.conf.bak.original
-rw-r--r-- 1 postgres postgres 18064 2011-07-02 19:54 postgresql.conf
-rw-r--r-- 1 postgres postgres 18064 2011-07-03 18:33 postgresql.conf.bak.original
...


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.[updated on 2011-07-24]

$ su postgres # switch to postgres user
$ cd ~/9.0/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


7. Restart the server [new]
I have to restart the server to use new configurations.

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


8. Check the new log. [new]
Now I should have a new log generated.

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


9. Finally Install pgAdmin III (GUI tool for PostgreSQL) [new]
Since the same PPA repository (ppa:pitti/postgresql) also contains the latest pgAdmin III, I can install it with a simple apt-get command.

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


It is version 1.12.2.

Reference To:http://socrateos.blogspot.com/2011/07/installing-postgresql-90-on-ubuntu-1104.html