Setup postgreSQL in Ubuntu

How to install and set up a PostgreSQL 8.1 server in ubuntu.

Introduction

PostgreSQL is a powerful object-relational database management system, provided under a flexible BSD-style license.^[1] PostgreSQL contains many advanced features, is very fast and standards compliant.

PostgreSQL has bindings for many programming languages such as C, C++, Python, Java, PHP, Ruby... It can be used to power anything from simple web applications to massive databases with millions of records.

Installation

Dapper

To install PostgreSQL 8.1 you may use the command line and type :

 sudo apt-get install postgresql-8.1

pgAdmin III is a handy GUI for PostgreSQL, it is essential to beginners. To install type at the command line :

 sudo apt-get install pgadmin3

You may also use the Synaptic package manager from the System>Administration menu to install these packages.

Breezy

The PostgreSQL version included in this release is version 8.0, which has some significant differences with 8.1. You will want to use ubuntu Backports in order to get version 8.1. You may than install PostgreSQL the same way you would under Dapper.

Basic Server Setup

Set password

To start off, we need to change the PostgreSQL postgres user password, we will not be able to access the server otherwise. As the “postgres” Linux user, we will execute the psql command, in a terminal type :

 sudo -u postgres psql template1

Then at the new prompt, type these two commands, replacing <***password***> with the new password (keep this safe ) :

 ALTER USER postgres WITH PASSWORD ' <***password***> ';
\q

Create database

To create the first database, which we will call "mydb", simply type :

 sudo -u postgres createdb mydb

Using pgAdmin III GUI

To get an idea of what PostgreSQL can do, you may start by firing up a graphical client. In a terminal type :

 pgadmin3

You will be presented with the pgAdmin III interface. Click on the "Add a connection to a server" button (top left). In the new dialog, enter the address 127.0.0.1, a description of the server, the default database ("mydb" in the example above), your username ("postgres") and your password.

With this GUI you may start creating and managing databases, query the database, execute SQl etc.

Managing the Server

Managing users and rights

PostgreSQL has a not really easy way to manage users. To manage users, you first have to edit /etc/postgresql/8.1/main/pg_hba.conf and modify the default configuration which is very protective. For example, if you want postgres to manage its own users (not linked with system users), you will add the following line:

8<-------------------------------------------
# TYPE  DATABASE    USER        IP-ADDRESS        IP-MASK           METHOD
host    all         all         10.0.0.0       255.255.255.0    password
8<-------------------------------------------

Which means that on your local network (10.0.0.0/24 - replace with your own local network !), postgres users can connect through the network to the database providing a classical couple user / password.

To create a database with a user that have full rights on the database, use the following command:

sudo -u postgres createuser -D -A -P myuser
sudo -u postgres createdb -O myuser mydb

The first command line creates the user the no database creation rights (-D) with no add user rights -A) and will prompt you for entering a password (-P). The second command line create the database 'mydb with 'myuser' as owner.

This little example will probably suit most of your needs. For more details, please refer to the corresponding man pages or the online documentation.

Further reading

If you are not familiar with SQL you may want to look into this powerful language, although some simple uses of PostgreSQL may not require this knowledge (such as a simple Django project).

The PostgreSQL website contains a wealth of information on using this database.

---

^[1] You do not have to pay in order to use PostgreSQL for some applications, such as commercial closed source software, contrary to other well known open source databases.