What You Did Is What You Get

Menu

PostgreSQL 9 installation for UBuntu 11.04

I’ve remembered that several weeks ago my laptop couldn’t boot the OS while I was using Ubuntu 10.04 for about 1 year. I was trying to boot with my Ubuntu 10.04’s bootable CD. But it always gave me the same result, I saw blank screen with blinking cursor :-(.
I have a thought that the problem could be one of the:

memory card,

harddisk, or even worse

the motherboard (I was hoping not the motherboard X_X).

First, I checked the harddisk by removed it from my laptop, then restart again with the same bootable CD. Yupi, It was working (ye…ye..ye..ye..ye..:-D). I was glad, because at least i knew that the motherboard is still working :-).
So, the problem was on the harddisk. I thought that maybe the harddisk had some bad sector in it. So, without thinking too much, I’ve decided to backup all of the data to my PC’s harddisk, then performed low level format from my PC.
After finished, I had an idea to gave that harddisk new OS. So, I was choosing Ubuntu 11.04 (Natty Narwhal) instead of Ubuntu 10.04 (Lucid Lynx).
It’s all working now :-). But, I have to install again all of my development tools from the beginning. One of them is PostgreSQL 9.

Unfortunately, at the time of this note, the default version of PostgreSQL in Ubuntu 11.04 (Natty Narwhal) is still 8.4.8.
The solution for this problem is PPA (Personal Package Archives).

PPAs Repository

PPA is repository provided by Canonical (the company behind Ubuntu), that allows developers and enthusiasts to offer up-to-date versions of software to all Ubuntu users. Originally PPAs were limited to programmers and testers, but Canonical has opened PPAs to everyone in late 2007.
Installing software on Ubuntu is different than on Mac or Windows; I’d rather say it’s better :-). Rather than going to the web to download a package, it’s usually better bet to check the Ubuntu Software Center for any program we might want to install. This software is stored in a repository, which is a collection of Ubuntu’s software to be downloaded quickly and easily :bd.
But, users typically have to wait for a new version of Ubuntu to try out new software. For example on this case, I want to install postgreSQL 9 in my new Ubuntu 11.04, but by default Ubuntu 11.04 repository use PostgreSQL 8, so typically I have to wait for the next Ubuntu version for postgreSQL 9 (it is wasting my time, right ?).
Don’t worry, this is where PPAs come in. A PPA (Personal Package Archive) is a collection of software not included in Ubuntu by default. Whatever the case, PPAs provide update for our favourite software at a much quicker rate than Ubuntu itself. So, I think you’ve got the idea :-). Sure, I’ll use this PPA to install what I need, i.e. PostgreSQL 9 for my Ubuntu 11.04 :-).

Using PPA to install PostgreSQL 9

The step is easy as follows:

add PPA repository for postgreSQL to my Ubuntu. You can do this from the console with command add-apt-repository ppa:pitty/postgresql (usually you have to use the command with sudo).
See the picture below:

Update the package using command sudo apt-get update.
See the picture below:

Waiting for the process to be done by the system, just like the following picture:

Now, you can check the installation version and dependency package using the commands as you can see from this below picture:

PostgreSQL Administration

Setup root (database administrator) user ‘postgres’

By default, if you’ve passed the postgreSQL installation, the installer has created a unix user postgres for you. You can check this by using command finger like the following:
The unix user postgres is a root user (database administrator) for PostgreSQL. It has no password at all (unix password and postgresql password). So i give unix password and postgresql password just like the following:

you will see there 2 important files named pg_hba.conf and postgresql.conf. We’re going to change those 2 configuration files.
But, before you do that, I suggest you to make a copy (backup) of the original files such as follow:

The pg_hba.conf is used for authentication methods. We’re going to edit the authentication method by open this file using your own favourite editor (e.g. vi/vim, nano, gedit, etc.) and make sure the file contains this line below:

host all all 127.0.0.1/32 trust

second, we’re going to edit the postgresql.conf. This file is the main configuration file that handles many important part such as postgre file locations (e.g. data_directory, hba_file, etc), connections and authentication (e.g. postgresql port, unix_socket_directory, etc), query tuning, error reporting and logging, etc.
For simplicity, here I just need to set the error reporting and logging.
So open the postgresql with your favourite editor, then add/change these lines:

# -----------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
# -----------------------------------------------------------------------
# - Where to Log -
log_destination = 'stderr' # valid values are combinations of
# stderr, csvlog, syslog, and eventlog,
# depending on platform. csvlog
# requires logging_collector to be on.
# This is used when logging to stderr:
logging_collector = on # Enable capturing of stderr and csvlog
# into log files. Required to be on for
# csvlogs.
# These are only used if logging_collector is on:
log_directory = 'pg_log' # directory where log files are written,
# can be absolute or relative to PGDATA
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_file_mode = 0600 # creation mode for log files,
# begin with 0 to use octal notation
log_truncate_on_rotation = off # I'd like to keep this off to keep the space
log_rotation_age = 1d
log_rotation_size = 10MB

Yeah..seems like the link that i’ve given has not valid anymore. Thanks a lot lucan for your time (sorry, I don’t know what should i call you ^_^). It’s very useful for me and the others. Good job and nice to know you ^_^.