PostgreSQL on Ubuntu Linux

PostgreSQL is a powerful object-relational database management system, provided under a flexible BSD-style license. PostgreSQL contains many advanced features, is very fast and standards compliant. It supports a large part of the SQL standard and is designed to be extensible by users in many aspects.

Some of the features are: ACID transactions, foreign keys, views, sequences, sub queries, triggers, user-defined types and functions, outer joins, multi version concurrency control. Graphical user interfaces and bindings for many programming languages are available as well.

This is a simple walk-through to install the PostgreSQL database server and the PgAdmin administration application on Ubuntu Linux.

Installing The Database Server

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

sudo apt-get install postgresql-8.1

GUI for PostgreSQL

To install pgAdmin III, a handy GUI for PostgreSQL, you may use the command line and type:

sudo apt-get install pgadmin3

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 secret with the new password (up to you ;))

ALTER USER postgres WITH PASSWORD 'secret';
 q

Create Database

To create the first database, which we will call “mydatabase”, simply type :

sudo -u postgres createdb mydatabase

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

To get a menu entry for pgAdmin do the following…

sudo gedit /usr/share/applications/pgadmin.desktop
[Desktop Entry] Comment= PostgreSQL Administrator III
Name=pgAdmin III
Encoding=UTF-8
Exec=pgadmin3
Terminal=false
Comment[en_GB]=PostgreSQL Administrator III
Icon=/usr/share/pixmaps/pgadmin3.xpm
Type=Application
Categories=GNOME;Application;Database;System;
Name[en_GB]=pgAdmin III

Then save the file and exit gedit. You should find the launcher in the System Tools section of the Applications menu.

Managing The Server

Change Authentication Method

We need to edit file pg_hba.conf to change authentification method for accessing PostgreSQL database.

sudo cp /etc/postgresql/pg_hba.conf /etc/postgresql/pg_hba.confbak
sudo gedit /etc/postgresql/pg_hba.conf

For example, if you want postgres to manage its own users (not linked with system users), you will add the following line:

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

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.

Create a Database

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 mynewuser
sudo -u postgres createdb -O mynewuser mydatabase

That’s it, now all you have to do is restart the server and all should be working!

sudo /etc/init.d/postgresql-8.1 restart
  • kalo mo ganti pgdata dari /var/lib/postgresql/data ke /home/user/data gimana??

  • Pingback: links for 2007-03-06 at MRPETERPETER()

  • Pingback: » How To Install Under Ubuntu 7.10 - Method 1 | Bugs Dashboard()

  • Hi,
    I want to know what to write in my .NET program which can connect to postgres database, i mean to say what is the odbc driver name.
    Thank

  • Deepak

    Hi,

    Thanks for your help.. i am new to both PGSQL and Linux… your site was quite useful..

  • It also checks the status of your disks at login time and provides fixes. ,

  • to milisdad on Feb 5th, 2007

    said:

    kalo mo ganti pgdata dari /var/lib/postgresql/data ke /home/user/data gimana??

    caranya :
    cut saja content folder “/var/lib” ke “/home/user/data”.
    lalu set variable pgdata ke folder tersebut. restart server, dan — seharusnya bekerja dengan normal.

    sudah dicoba di windows.