Setting postgreSQL

==============================================================
biasa binggung jika sudah di Customer, dan lupa..... :D
makannya saya postingakan disini :), biar kalo lupa ada yang bisa di bacem :D ...............

/etc/postgresql/8.1/main/pg_hba.conf
=====================================================================
…
…
…
# Database administrative login by UNIX sockets
#local all postgres ident sameuser
local all postgres trust

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# “local” is for Unix domain socket connections only
#local all all ident sameuser
local all all trust

# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all 192.168.1.0/24 md5
# IPv6 local connections:
host all all ::1/128 md5
=====================================================================

/etc/postgresql/8.1/main/postgresql.conf
=====================================================================
…
…
#listen_addresses = ‘localhost’ # what IP address(es) to listen on;
listen_addresses = ‘*’
# comma-separated list of addresses;
# defaults to ‘localhost’, ‘*’ = all
port = 5432
max_connections = 100

=====================================================================

set Password ‘postgres’ pertama
=====================================================================
root@server-desktop:/home/repo# /etc/init.d/postgresql-8.1 restart
* Restarting PostgreSQL 8.1 database server [ OK ]

root@server-desktop:/home/repo# psql -U postgres postgres
Welcome to psql 8.1.8, 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

postgres=# alter role postgres encrypted password ‘asdf’;
ALTER ROLE
postgres=# \q
root@server-desktop:/home/repo#

install PL (PLPGSQL) di template1
karena template1 adalah database template untuk di-clone/duplikat ketika kita membuat database baru. Jadi kalau kita buat language disitu otomatis dataabase baru yg kita buat selanjutnya akan sudah termasuk language tsb. =====================================================================
root@server-desktop:/home/repo# createlang -U postgres plpgsql template1
root@server-desktop:/home/repo#

Install DBLink di template1
root@server-desktop:/home/repo# psql -U postgres template1 < /usr/share/postgresql/8.1/contrib/dblink.sql

create role (user) ‘bits’
=====================================================================
root@server-desktop:/home/repo# psql -U postgres postgres
Welcome to psql 8.1.8, 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

postgres=# \h create role
Command: CREATE ROLE
Description: define a new database role
Syntax:
CREATE ROLE name [ [ WITH ] option [ … ] ]

where option can be:

SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| CREATEUSER | NOCREATEUSER
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| CONNECTION LIMIT connlimit
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD ‘password’
| VALID UNTIL ‘timestamp’
| IN ROLE rolename [, …]
| IN GROUP rolename [, …]
| ROLE rolename [, …]
| ADMIN rolename [, …]
| USER rolename [, …]
| SYSID uid

postgres=# create role bits createdb login encrypted password ‘asdf’;
CREATE ROLE
postgres=#

ganti user di psql
===========================
postgres=# \c - bits

ganti database & user di psql
===========================
postgres=> \c template1 bits
You are now connected to database “template1″ as user “bits”.
template1=>

Auto mount usb harddisk ke /media/data
=========================================
root@server-desktop:/etc# vi /etc/fstab
# /etc/fstab: static file system information.
#
#
/dev/sdb1 /media/data ext3 defaults 0 0

Buat TableSpace (untuk separate database ke external-disk/usbdisk)
==========================================
1. buat folder db di /media/extdisk
root@server-desktop:/media/extdisk# ls -al
total 24
drwxr-xr-x 3 root root 4096 2007-10-12 14:47 .
drwxr-xr-x 6 root root 4096 2007-10-12 16:01 ..
drwx—— 2 root root 16384 2007-10-12 14:47 lost+found
root@server-desktop:/media/extdisk# mkdir db
root@server-desktop:/media/extdisk# mkdir bak
root@server-desktop:/media/extdisk#
2. chown ke postgres.postgres
root@server-desktop:/media/extdisk# chown postgres.postgres db
root@server-desktop:/media/extdisk# chown postgres.postgres bak
root@server-desktop:/media/extdisk# chmod 770 db
root@server-desktop:/media/extdisk# chmod 770 bak
root@server-desktop:/media/extdisk# ls -al
total 32
drwxr-xr-x 5 root root 4096 2007-10-12 16:04 .
drwxr-xr-x 6 root root 4096 2007-10-12 16:07 ..
drwxrwx— 2 postgres postgres 4096 2007-10-12 16:04 bak
drwxrwx— 2 postgres postgres 4096 2007-10-12 16:04 db
drwx—— 2 root root 16384 2007-10-12 14:47 lost+found

3. buat tablespace di postgres , set ke /media/extdisk
root@server-desktop:/media/extdisk# psql -U postgres postgres
Welcome to psql 8.1.8, 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

postgres=# \h create tablespace
Command: CREATE TABLESPACE
Description: define a new tablespace
Syntax:
CREATE TABLESPACE tablespacename [ OWNER username ] LOCATION ‘directory’

postgres=# create tablespace extdisk owner bits location ‘/media/extdisk/db’;
CREATE TABLESPACE
postgres=#

Buat database baru dengan owner bits TABLESPACE=extdisk
===========================
template1=> \h create database
Command: CREATE DATABASE
Description: create a new database
Syntax:
CREATE DATABASE name
[ [ WITH ] [ OWNER [=] dbowner ]
[ TEMPLATE [=] template ]
[ ENCODING [=] encoding ]
[ TABLESPACE [=] tablespace ]
[ CONNECTION LIMIT [=] connlimit ] ]

template1=>

postgres=> CREATE DATABASE mks OWNER bits TABLESPACE=extdisk;
CREATE DATABASE
postgres=> \q

root@server-desktop:/media/extdisk/db# ls -al
total 16
drwx—— 3 postgres postgres 4096 2007-10-12 16:13 .
drwxr-xr-x 5 root root 4096 2007-10-12 16:04 ..
drwx—— 2 postgres postgres 4096 2007-10-12 16:13 16390
-rw——- 1 postgres postgres 4 2007-10-12 16:11 PG_VERSION
root@server-desktop:/media/extdisk/db#

test koneksi:
—————
root@server-desktop:~# psql -U bits mks
Welcome to psql 8.1.8, 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

mks=>

INSTALL RETAILLUX
====================
root@server-desktop:~# cd /media/flashdisk/retaillux
root@server-desktop:~# psql -U bits mks
Welcome to psql 8.1.8, 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

mks=>