Setting Data Base Postgresql ON External HDD

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=>

Auto Mount eksternal HDD on Linux

lihat:
===========
ls -l /dev/disk/by-uuid/
sudo vol_id /dev/sda1

/etc/fstab
============
# <file system> <mount point>   <type>  <options>       <dump>  <pass>
proc            /proc           proc    defaults        0       0
# /dev/sda1
UUID=92105cd3-3970-4940-b8c7-5cea8e12bb51 /               ext3    defaults,errors=remount-ro 0       1
# /dev/sda7
UUID=0ca8de01-4322-471b-92b4-7b37006420e3 /home           ext3    defaults        0       2
# /dev/sda6
UUID=7a7cf203-a8bb-456f-9908-007dd611966c /var            ext3    defaults        0       2
# /dev/sda5
UUID=c5a8145e-f9ce-4004-adce-e223eaa08076 none            swap    sw              0       0
/dev/scd0       /media/cdrom0   udf,iso9660 user,noauto     0       0

c959dc26-cbfa-457f-8bf1-7ea084154954

Postgresql setting

/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 ‘9876’;
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) ‘muki’
==================================================
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 muki createdb login encrypted password ‘9876’;
CREATE ROLE
postgres=#

serching isi tulisan dalam suatu file

Kalo kita mau cari isi tulisan pada suatu file tanpa buka file tersebut kita bisa pake console

caranya gene :

mukimini@lion:~/remaja_autopart/dbf$ grep -i -r fbrmerk *

maksudnya gene…

Sekarang gw kan ada di dalam folder dbf dan didalam folder ini ada banyak bgt file yg ekstensinya .dbf, kira-kira sekitar 40 file. Dan gw kan cari mau tulisan fbrmerk dari puluhan file tersebut. Banyangin coba kalo gw harus buka satu demi satu dari semua file dan nyari kata demi kata tersebut……..arrggghhhh……. 😦 bisa-bisa copot dah mata gw…….akhirnya gw tanya tm gw cara yg paling mudah terus aku di kasih tau cara pake console.ternyata gak panjang singkat banget. “grep -i -r (nama yang mau di cari) * ”

Ex: mukimini@lion:~/remaja_autopart/dbf$ grep -i -r fbrmerk *