Install Postgresql 9.2 on Ubuntu 12.04 LTS

Some of the Servers I maintain are in Ubuntu 12.04 LTS and sometimes I misplace resources I need to maintain. So I keep here one of the important piece.

First sure about the language setup with

locale -a

Then us the following commands:

$ sudo apt-get update
$ sudo apt-get -y install python-software-properties
$ sudo add-apt-repository ppa:pitti/postgresql
$ sudo apt-get update

$ sudo apt-get -y install postgresql-9.2 postgresql-client-9.2 postgresql-contrib-9.2
$ sudo apt-get -y install postgresql-server-dev-9.2 libpq-dev

$ sudo pg_createcluster 9.2 main --start

First of all you have to set a new password for the superuser postgres. In order to do so, type the following command line into your terminal

$ sudo -u postgres psql postgres

Now you are in psql, the command interface for postgresql, and in the database postgres. In your terminal it looks like this at the moment

$ postgres=#

To change your password:

$ \password postgres

and type your new password and re type when asked for it.

Create a database (for testing)

If you want to create a db, posgresql has to know, which user you are. Therefore you have to type -u username in front of the command createdb. If you type the following, it means that you as the user postgres want to create a database wich is called mydb.

$ sudo -u postgres createdb mydb

System Kernel tuning

$ sudo nano /etc/sysctl.conf

Add the parameter

kernel.shmmax=8589934592 (8G * 1024 * 1024 * 1024)

/sbin/sysctl -p

Tune Postgres configuration.

$ sudo nano /etc/postgresql/9.2/main/postgresql.conf

Follow my other blog on tuning.