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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: