Thursday, August 30, 2012

Installing PostgreSQL 9.1 on Ubuntu 12.04, using Synaptic

Installed PostgreSQL 9.1 on my  Ubuntu 12.04 (Server), using Synaptic.

1. Resources

My Previous Installation: Installing PostgreSQL 9.0 on Ubuntu (11.04) using PPA

2. Install using Synaptic

Search and install "postgresql" in Synaptic.
It installed the following packages:

  postgresql-9.1           object-relational SQL database, version 9.1 server
  postgresql-client-9.1    front-end programs for PostgreSQL 9.1
  libpq5                   PostgreSQL C client library
  postgresql-client-common manager for multiple PostgreSQL client versions
  postgresql-common        PostgreSQL database-cluster manager

3. Check Installation

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

4. Setup Root User 'postgres'

Installer creates a new unix user "postgres". It also creates PostgreSQL user with the same name. Neither has a password. So I give password for both.

$ finger postgres            # check if installer created a new user 'postgres'
Login: postgres          Name: PostgreSQL administrator
Directory: /var/lib/postgresql       Shell: /bin/bash
Never logged in.
No mail.
No Plan.
$ sudo passwd postgres       # give this new user 'postgres' a unix password
$ su postgres                # switch to this user 'postgres'
$ psql                       # launch psql as root user 'postgres'
postgres=# alter user postgres with password 'secret';  # give a postgresql password
postgres=# \q                # quit psql
$ exit                       # exit from user 'postgres'

5. Make Myself a PostgresSQL User

$ su postgres                # switch to root user 'postgres'
$ psql                       # launch psql
psql (9.1.5)
postgres=# CREATE ROLE socrateos WITH CREATEDB CREATEROLE LOGIN PASSWORD 'secret';   # add myself as a PostgreSQL user with these rights
postgres=# CREATE DATABASE testdb; # create a new database
postgres=# \q                # quit psql
$ exit                       # exit from user 'postgres'
$ psql testdb                # login to testdb as myself (not root user)
psql (9.1.5)
testdb=> CREATE DATABASE mydb; # see if I can create a database
testdb=> \c mydb;            # connect to a newly created database
You are now connected to database "mydb" as user "socrateos".
mydb=> CREATE TABLE table1(id SERIAL PRIMARY KEY, name VARCHAR(30));
NOTICE:  CREATE TABLE will create implicit sequence "table1_id_seq" for serial column ""
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "table1_pkey" for table "table1"
mydb=> INSERT INTO table1(name) VALUES('adam');
mydb=> INSERT INTO table1(name) VALUES('eve');
mydb=> SELECT * FROM table1;
 id | name 
  1 | adam
  2 | eve
(2 rows)
mydb=> \q

6. Configure PostgreSQL Server

I want to configure the server for its error log file.

$ su postgres                 # switch to user postgres
$ cd /etc/postgresql/9.1/main # go to config directory
$ cp postgresql.conf postgresql.conf.bak.original  # make a backup
$ exit                        # exit from user 'postgres'

I edited postgresql.conf to enable the following.

log_destination = 'stderr' # 2012-09-05 enabled.
logging_collector = on     # 2012-09-05 enabled and turned on.
log_directory = 'pg_log'   # 2012-09-05 enabled. I will create this folder.
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # 2012-09-05 enabled.
log_truncate_on_rotation = off  # 2012-09-05 enabled and keep it off
log_rotation_age = 1d      # 2012-09-05 enabled.
log_rotation_size = 10MB   # 2012-09-05 enabled.

Now I create a log directory that I specified in the config.

$ su postgres              # switch to user postgres
$ cd ~                     # go to the home directory of postgres
$ cd 9.1/main/             # go to the directory where to create a log directory
$ mkdir pg_log             # create a log directory
$ exit                     # exit from user 'postgres'

7. Restart the Server

I need to restart the server to activate the new configuration.

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

8. Check the New Error Log

Yes, it created a new log where I wanted.

$ sudo ls /var/lib/postgresql/9.1/main/pg_log

9. Finally Install PGADMIN3, a GUI Admin app

I used Software Center to find and install pgadmin3.
It can be launched from the "Programming" menu group.

10. How to uninstall PostgreSQL installed as above.

Use the Synaptic and specify and delete the items it install in the  step above (#2).


  1. You are the best!

  2. I dont care when you do this... THANK YOU!!!

  3. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in TECHNOLOGY , kindly contact us
    MaxMunus Offer World Class Virtual Instructor led training on TECHNOLOGY. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
    For Demo Contact us.
    Sangita Mohanty
    Skype id: training_maxmunus
    Ph:(0) 9738075708 / 080 - 41103383