Install PostgreSQL 9.0 on Ubuntu 10.10

 PostgreSQL 9.0 final was released on 2010/09/20 which I’ve been waiting for to begin playing with it and evaluating it. The mainline Ubuntu Lucid apt repositories only have 8.4. According to the package maintainer, Martin Pitt, the postgresql-9.0 package won’t be added until Ubuntu Natty. Thankfully, he setup a backports repo for Ubuntu that we can pull from.

This is a very quick and painless process. If you have not done so already, make your life easier by installing python-software-properties:

sudo apt-get install python-software-properties



Next up is adding the backports repo and updating apt:

sudo add-apt-repository ppa:pitti/postgresql
sudo apt-get update


UPDATE: In the comments, Jason Froebe points out that if you already have an earlier version of PostgreSQL installed, you can just upgrade it:

sudo apt-get upgrade

Finally install postgresql-9.0:


sudo apt-get install postgresql-9.0 libpq-dev


The libpq-dev package is for compiling wrappers/clients against libpq.
There you’re done!

NOTE:
Default config directory is: /etc/postgresql/9.0/main
Default data directory is: /var/lib/postgresql/9.0/main


This installs the engine and pgadmin3 client, a graphical tool to manage databases.
The next thing is to change the administrator password system PostgreSQL:

sudo passwd postgres

Now we must change the password of "user postgres" on the engine, it first entered with the following line:

psql -h localhost -U postgres -W template1

or 

sudo su postgres -c psql template1

and then enter the following command:

ALTER USER postgres WITH PASSWORD 'password';

Finally out of postgres, enter the following command:

\q

With this we have installed PostgreSQL. Settings
We set up remote access to our motor. This step is optional, only if you want to allow access to our server from other hosts.
First we edit the file postgresql.conf:

sudo gedit /etc/postgresql/9.0/main/postgresql.conf

Look for the line:

# Listen_addresses = 'localhost'

and change it to:

listen_addresses = '*'

We also look for the following line:

# Password_encryption = on

and change it to:

password_encryption = on

Now we edit the file "pg_hba.conf"

sudo gedit /etc/postgresql/9.0/main/pg_hba.conf

Modify the lines:

# Database administrative login by UNIX sockets

local   all         postgres                          ident

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only

local   all         all                               ident

# IPv4 remote connections:

host    all         all         194.116.73.46/0          md5

# IPv4 local connections:

host    all         all         127.0.0.1/32          md5

# IPv6 local connections:

host    all         all         ::1/128               md5

We do this to use MD5 authentication with the user postgres.

Finally sent to restart:

sudo /etc/init.d/postgresql restart

More information about the configuration: http://www.linux-es.org/node/660

pgAdmin 1.12 for Ubuntu 10.04, Postgres 9.0

Download and install!

pgadmin3-data_1.12.0-1_all.deb
pgadmin3_1.12.0-1_i386.deb
pgadmin3_1.12.0-1_amd64.deb