Postgresql 101

This is to help people start with postgresql in matter of minutes. 

Following are some simple assumptions made here.

* The OS is unix like
* Postgresql is already installed on the system


Starting postgresql from scratch

1) Log in as any OS user. This user will be database super user and it 
can not be OS user root. Typically this user is called as 'postgres' or 'pgsql'. 
But it can be any other user as well

2) Export environment variable PGDATA to a directory where database is to be located and
call initdb to create initial database template.

e.g.
$ export PGDATA=/mnt1/dbs/postgresql
$ initdb

This will create a template database in /mnt1/dbs/postgresql. For all practical purposes, 
consider this directory opeque unless you know what you are doing.

When initdb is finished, it will tell you how to start postgresql server as last part of 
initdb mesages

--------------------------
Success. You can now start the database server using:

    /usr/bin/postmaster -D /mnt1/dbs/postgresql
or
    /usr/bin/pg_ctl -D /mnt1/dbs/postgresql -l logfile start
--------------------------

So you should now start the database serevr as indicated by last message. Using pg_ctl is 
the simplest way of doing that.


4) Create a database to start with.

$ createdb test
CREATE DATABASE
$

5) Start using it with postgresql terminal sql client.

$psql test
Welcome to psql, the PostgreSQL interactive terminal.

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

test=#

At this point, you can use all the commands shown in postgresql manual. Enjoy!


Some useful tips

1) Shutting down the database

* Log in as database super user. It's the OS user who ran initdb etc. in above steps
* Use pg_ctl to stop the database

$ pg_ctl -D mnt1/dbs/postgresql stop

2) Start postgresql with network option.

Postgresql by default does not listen on network. To make it do so, you need to pass '-i' option to it. 
The pg_ctl command will be modified to look like

$ /usr/bin/pg_ctl -D /mnt1/dbs/postgresql -l logfile -oi start

3)Tuning postgresql

Many paramters that postgresql uses can be modified using configuration file $PGDATA/postgresql.conf
The configuration file is well documented. Read the administrators guide as well.

If you are looking for any heavy duty work, keep in mind that postgresql defaults are very conservative 
and are not meant for any heavy duty work. Please tune the system before you put load.

4) Users in postgresql

You can create databases in postgresql and users. But unlike some other RDBMS, users in postgresql are 
global i.e. same username/password can be used to connect to any database in system. Rights granted to 
any users are of course can be tuned but there is nothing like a user in a particular database