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
