PostgreSQL

Revision as of 15:03, 4 December 2005

PostgreSQL is an open source object-relational database management system released under the terms of the BSD licence. It is based on postgres, developped at the University of California at Berkeley Computer Science Department (Just like BSD). It features many stuff MySQL lacks, such as transactional integrity or complex queries.

Installation

You can choose to install PostgreSQL from the port tree...

/usr/ports/databases/postgresql80-client
/usr/ports/databases/postgresql80-server

... or the packages ...

pkg_add -r postgresql80-client postgresql80-server

Additionally, you may want to install postgresql support to PHP. Depending on your PHP version, install :

/usr/ports/databases/php4-pgsql
# or
/usr/ports/databases/php5-pgsql

(You can also install it from packages)

Configuring PostgreSQL

Creating a cluster

What is called cluster in PostgreSQL is the database disk area. The first step is to enable PostgreSQL, witch is done in /etc/rc.conf : add the following line at the end :

postgresql_enable="YES"

Then, run :

/usr/local/etc/rc.d/010.pgsql.sh initdb

If everything is good, you can now start PostgreSQL :

/usr/local/etc/rc.d/010.pgsql.sh start

Listening to the network (optional)

By default, you will be able to use your database localy only. Edit the file ~pgsql/data/postgresql.conf and uncomment the following line :

listen_addresses = '*'

You can also change the max_connections value. Restart PostgreSQL in order to have the changes effective (But you can do the next step before ;-) ).

Configuring connections (optional but recommended !)

An other step is required in order to be ready to use PostgreSQL over a network : telling the system who can access what. This is the role of the ~/data/pg_hba.conf file. You may want to change some things as the default configuration is to accept all local connections and reject all others. Here we require authentification for a local connection or a connection from the network (192.168.0.xxx) :

#local   all         all                               trust
local   all         all                                md5
# IPv4 local connections:
#host    all         all         127.0.0.1/32          trust   
host    all         all         127.0.0.1/32           md5
host    all         all         192.168.0.1/24         md5

PostgreSQL needs to be restarted after that :

/usr/local/etc/rc.d/010.pgsql.sh restart

Creating databases

There are two ways to create databases :

• The createdb command (in a shell).
• The CREATE DATABASE query.

For example, let's create the foo database :

shell> psql template1 pgsql
Password: 
Welcome to psql 8.0.3, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

template1=# CREATE DATABASE foo ;
CREATE DATABASE
template1=#

Note that the connection is done into the template1 database: This first database is created by the initdb command and is used to log-in a database for the creation of a new one (Problem of the first database creation).

Creating users

There are two ways to create users :

• The createuser command (in a shell).
• The CREATE USER query.

Let's create a bar user :

shell> psql template1 pgsql
Password: 
Welcome to psql 8.0.3, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

template1=# CREATE USER bar PASSWORD 'B4R' ;
CREATE USER
template1=#

You can change the password of an user like this  :

template1=# ALTER USER bar PASSWORD 'f0O}b4R' ;
ALTER USER
template1=#

Using PostgreSQL

Soon ! --Smortex 04:45, 3 Aug 2005 (EDT)

PostgreSQL Website

http://www.postgresql.org