Pengenalan Postgress

Internet Cafe

1. Pendahuluan

• class
• type
• inheritence
• function

• trigger
• constraint
• rules
• transaction integrity

2. Instalasi

2.1 Paket
Install semua paket postgres yang berbentuk rpm. Berikut daftar paket Postgres di RedHat 5.2 :

• postgresql-clients-6.3.2-10-i386.rpm
• postgresql-6.3.2-10-i386.rpm
• postgresql-devel-6.3.2-10-i386.rpm
• postgresql-data-6.3.2-10-i386.rpm

• postgres
• pg_datab
• pg_ifa
• pg_iface
• pgaccess

2.2 Compile
Cara lain untuk menginstal Postgres adalah dengan mengkompilasi source code-nya. Anda bisa mendapatkannya di http://www.postgresql.org/.
Tulisan ini akan membahas source Postgres versi 6.5.1.
Login sebagai root.([footnote] Ok, kita buat kesepakatan : prompt # berarti root, sedangkan $ berarti user biasa.) 
# cd /usr/src
# mkdir pgsql
# chown postgres:postgres pgsql
# cd /usr/local
# mkdir pgsql
# chown postgres:postgres pgsql
Login dengan user postgres.
$ cd /usr/src/pgsql
$ tar xfzv postgresql-6.5.1.tar.gz
$ mv postgresql-6.5.1/* .
$ rmdir postgresql-6.5.1
$ cd /usr/src/pgsql/src
$ ./configure —with-tcl —with-perl
$ cd /usr/src/pgsql/doc
$ make install
$ cd /usr/src/pgsql/src
$ make all > make.log &
Untuk melihat proses kompilasi :
$ tail -f make.log
Selanjutnya install source yang telah dikompilasi, dan anda harus sebagai root dalam hal ini.
# cd /usr/src/pgsql/src
# make install > make.install.log &
Untuk melihat proses instalasi :
$ tail -f make.install.log
Library yang terbentuk harus “diperkenalkan” ke seluruh user dengan menambahkan baris berikut ke file
 /etc/ld.so.conf.
/usr/local/pgsql/lib
Jalankan ldconfig agar perubahannya “terasa”.
# /sbin/ldconfig
Tambahkan baris ini ke file /etc/profile.
PATH=$PATH:/usr/local/pgsql/bin
LD_LIBRARY_PATH=/usr/local/pgsql/lib
MANPATH=$MANPATH:/usr/local/pgsql/man
PGLIB=/usr/local/pgsql/lib
PGDATA=/usr/local/pgsql/data
export PATH LD_LIBRARY_PATH MANPATH PGLIB PGDATA
Tahap berikutnya adalah membuat database sistem bernama template1.
Loginlah sebagai user postgres.
$ initdb
Saatnya untuk menjalankan postgres.
$ cd
$ nohup postmaster -i > pgserver.log 2>&1 &
Saatnya untuk mencoba.
$ createdb
$ psql
postgres=> select datetime ‘now’;
?column?
——————————————
Mon Sep 27 11:40:53 1999 GMT
(1 row)
postgres=> \q
$ destroydb
Konfigurasi untuk autostartup. Loginlah sebagai root.
# cp /usr/src/pgsql/contrib/linux/postgres.init.sh \
/etc/rc.d/init.d/postgres
# cd /etc/rc.d/rc5.d
# ln -s ../init.d/postgres S98postgres
Tambahkan option -i ke /etc/rc.d/init.d/postgres pada bagian ‘start’ agar bisa diakses remotely.
if [ ${USE_SYSLOG} = “yes” ]; then
  su - ${PGACCOUNT} -c “(${POSTMASTER} ${PGOPTS} -i \
2>&1 | logger -p ${FACILITY}.notice) &” > /dev/null \ 
2>&1 &
else
   su - ${PGACCOUNT} -c “${POSTMASTER} ${PGOPTS} -i \
2>>&1 ${PGLOGFILE} &” > /dev/null 2>&1 &
Dengan berbagai kemungkinan dan alasan, bisa jadi server Postgres down (entah di-kill, crash, dsb). Untuk itu tambahkan baris berikut ke file /etc/inittab dalam SATU BARIS.
pg:2345:respawn:/bin/su - postgres -c \
“/usr/local/pgsql/bin/postmaster \
-D/usr/local/pgsql/data \
>> /usr/local/pgsql/server.log 2>&1 \
</dev/null”
Menurut pembuatnya baris di atas dapat membuat Postgres hidup kembali manakala dia down. Namun demikian pembuatnya tidak tahu-menahu kalau script di atas menimbulkan efek samping lainnya. Jangan lupa menjalankan init q agar perubahannya terasa.
# init q
Sebelumnya anda telah melakukan uji sederhana dengan membuat database, melakukan select, dan menghapus database. Dalam source Postgres telah tersedia juga untuk test secara menyeluruh. Loginlah sebagai postgres.
$ cd /usr/src/pgsql/src/test/regress
$ make all runtest
Pesan kegagalan yang anda peroleh pada saat test tipe data, bukan melulu ada masalah dengan Postgres-nya. Sebagai contoh untuk platform yang menggunakan prosesor Intel semacam Pentium II tidak akan diperoleh pesan kegagalan ini, karena sejak Postgres versi 6.5 program regress ini juga melakukan uji platform.
Kasus ini bisa terjadi pada uji tipe data int8 (integer 8 byte). Anda akan mendapatkan pesan kegagalan manakala prosesor dan C compiler-nya tidak mendukung integer 64-bit (8 byte). Atau bisa jadi keduanya (prosesor dan C compiler) mampu, namun tidak dikonfigurasi untuk itu. Hal ini tidaklah perlu dirisaukan, kecuali anda berniat untuk menggunakan tipe data int8.
Setelah test selesai dilakukan hapuslah database
 $ destroydb regression
 $ cd /usr/src/pgsql/src/test/regress
 $ make clean

2.3 Setting-Up
Bagi yang tidak memperoleh paket postgresql-data (RedHat 6.0 tidak menyediakan), anda bisa membuatnya dengan cara :
$ initdb
Anda harus sebagai user postgres untuk melakukannya. ([footnote] Superuser Postgres bukanlah root melainkan postgres yang dibuat secara otomatis pada saat instalasi. Jika anda belum mengetahui password yang dimiliki user postgres, silahkan ubah password tersebut dan untuk hal ini anda harus sebagai root.)  Proses di atas akan membuat database template1. Agar template1 dapat diakses dari remote host anda perlu menambahkan baris berikut ini pada pg_hba.conf : host template1 192.168.1.0 255.255.255.0 ident sameuser
Baris di atas berarti client manapun dengan IP Address 192.168.1.x dapat mengakses template1 tanpa proses otorisasi (option sameuser).
Untuk SuSE 6.0 file tersebut terletak di /var/lib/pgsql/data/pg_hba.conf.
Sedangkan bagi anda yang mengikuti proses compile di atas, maka file tersebut ada di /usr/local/pgsql/data/pg_hba.conf.([footnote] Baik /var/lib/pgsql/data atau /usr/local/pgsql/data sering dianggap sebagai home directory user Postgres. Namun sebenarnya Postgres melihat variabel PGDATA dalam PATH. Anda bisa mengetahui nilai dari variabel ini dengan perintah export pada shell. Untuk selanjutnya penulis menggunakan /usr/local/pgsql/data.) 

2.4 Password
Postgres meletakkan kebijaksanaan security-nya di file pg_hba.conf.
Formatnya adalah:
host DBNAME IP_ADDRESS ADDRESS_MASK USERAUTH [AUTH_ARGUMENT]
Secara default, Postgres membiarkan setiap user untuk login tanpa diharuskan mengisikan password:
host all 127.0.0.1 255.255.255.255 trust 
Contoh:
host all 192.168.1.0 255.255.255.0 password kunci
Contoh di atas menunjukkan bahwa user dari host dengan alamat IP 192.168.1.x boleh terkoneksi ke server Postgres dengan kewajiban mengisikan username dan password yang terdapat dalam file kunci yang terletak di home directory user Postgres.
$ cd /usr/local/pgsql/data
$ pg_passwd kunci
Username: owo 
New password: 
Re-enter new password: 

2.5 User
Seperti disebutkan sebelumnya bahwa superuser Postgres adalah postgres dan bukan root, maka postgres-lah yang berhak membuat user yang dapat mengakses Postgres server.
$ createuser sugiana
Enter user’s postgres ID or RETURN to use unix user ID: 503 ->
Is user “sugiana” allowed to create databases (y/n) y
Is user “sugiana” allowed to add users? (y/n) n
createuser: sugiana was successfully added
Postgres memiliki sendiri manajemen usernya dan tidak menerima koneksi dari user yang belum terdaftar meski user tersebut terdaftar di Linux.
Hal ini bisa dibuktikan jika kita mencoba login dengan user yang tak terdaftar, misalnya owo :
$ psql template1
Connection to database ‘template1’ failed.
FATAL 1: SetUserId: user “owo” is not in “pg_shadow”

3. Client Applications

3.1 Utility
Utility biasanya dipakai untuk manajemen database dan user. Antarmukanya tidak saja untuk console (text mode), untuk XWindows juga tersedia.

3.1.1 createdb & destroydb
createdb digunakan untuk membuat database. Jika anda tidak menyebutkan nama databasenya secara otomatis createdb menamakannya dengan nama user.
$ createdb
Perintah di atas akan membuat database bernama sugiana manakala anda login sebagai user sugiana. Untuk menggunakan nama database yang berbeda maka anda perlu mencantumkannya.
$ createdb perpustakaan
Jika Postgres ada di remote server anda harus menyebutkan nama server tersebut.
$ createdb -h warnet.rab.co.id
Jangan lupa untuk memberikan izin agar remote client dapat mengakses database anda.([footnote] Lihat pembahasan Setting-Up)  Secara umum option -h tersebut bisa diterapkan pada berbagai utility lain seperti createdb, destroydb, createuser, destroyuser, atau psql.
Sedangkan destroydb kebalikan dari createdb, berfungsi untuk menghapus suatu database.
$ destroydb
Sebagaimana createdb, jika nama database tidak disebutkan maka nama databasenya diambil dari nama user. 

3.1.2 createuser & destroyuser
createuser untuk menambah user Postgres dan tidak harus sebagai user Linux.
$ createuser sugiana 
destroyuser kebalikan dari createuser, menghapus user Postgres.
$ destroyuser sugiana

3.1.3 psql
psql adalah utility yang interaktif untuk query. Antarmukanya (user interface) berupa command line. Anda kirim kalimat query ke Postgres, dan langsung lihat hasilnya.
$ psql
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
type \? for help on slash commands
type \q to quit
type \g or terminate with semicolon to execute query
You are currently connected to the database: sugiana
sugiana=>
Versi untuk XWindows-nya bernama mpsql, telah tersedia di SuSE 6.0.
\resizebox*{0.75\columnwidth}{0.5\ textheight}{\includegraphics{mpsql.ps}} 

3.2 Development Tools
Seperti yang sudah disebutkan bahwa ada begitu banyak cara (baca: interface) untuk mengakses Postgres, mulai dari yang textmode sampai gaya visual dengan kemampuan drag & drop.

3.2.1 PgAccess
PgAccess sangat mudah penggunaannya. Pemakaian mouse sangat berperan dan bahkan teknik drag & drop diterapkan dalam pembuatan query.
\resizebox*{0.75\columnwidth}{0.5\ textheight}{\rotatebox{90}{\includegraphics {pgaccess.ps}}} 

3.2.2 PHP
PHP merupakan bahasa yang disertakan dalam dokumen HTML, bekerja di sisi server sehingga script-nya tak tampak di sisi client. PHP dirancang untuk dapat bekerja sama dengan database server dan dibuat sedemikian rupa sehingga pembuatan dokumen HTML yang dapat mengakses database menjadi begitu mudah. 
Postgres sangat didukung oleh pengembang PHP. Dengannya kita dapat membuat aplikasi yang bisa dijalankan melalui browser manapun di berbagai sistem operasi seperti Netscape Navigator atau HotJava.
Konektivitas ke server cukup dengan sebuah procedure pg_connect. Misalkan database yang digunakan bernama perpus, anda login dengan user postgres dan passwordnya kunci.
$conn = pg_connect(“dbname=perpus user=postgres password=kunci”);
Berikut ini penulis berikan contoh lengkap menampilkan suatu tabel katalog yang ada dalam database perpus. Adapun struktur fieldnya bebas karena secara otomatis seluruh nama field dan record akan ditampilkan.
<?
$conn = pg_connect(“dbname=perpus user=postgres password=kunci”);
$result = pg_exec($conn, “select * from katalog”);
$jml_field = count(pg_fetch_row($result, 0));
echo “<table>”;
echo “<tr>”;
for ($i=0; $i < $jml_field; $i++) {
  echo “<td>” . pg_fieldname($result, $i) . “</td>”;
}
echo “</tr>”;
for ($i=0; $i<pg_numrows($result); $i++) {
  $row=pg_fetch_row($result, $i);
  echo “<tr>”;
  for ($k=0; $k < $jml_field; $k++) {
    echo “<td> $row[$k] </td>”;
  }
  echo “</tr>”;
}
echo “</table>”;
?>

3.2.3 Perl
Perl library kini tersedia untuk mengakses Postgres. Perl merupakan bahasa tingkat tinggi yang menawarkan kemudahan, strukturnya sangat bagus dan fungsi-fungsi manipulasi string sangat banyak, cocok untuk modul report.
$username=$ENV{USER};
use Pg;
my $dbname = ‘warnet’;
my ($conn, $result, $i);
$conn = Pg::connectdb(“dbname=$dbname user=dvt password=a”);
$result = $conn->exec(“INSERT INTO log (username) VALUES (‘$username’)”);
$result = $conn->exec(“select nama from pelanggan where username=’$username”’);
my @row;
@row = $result->fetchrow;
$nama = @row[0];
if ( $nama ) {
  print “Halo $nama\n”;
} else {
  print “Anda belum terdaftar. Hubungi admin.\n”;
}

3.2.4 C
C merupakan bahasa yang sangat mendasar untuk mengembangkan aplikasi Postgres. Dari strukturnya bahasa ini bisa digunakan untuk aplikasi yang menuntut kecepatan dan akses hardware langsung.

3.2.5 JDBC([footnote] Java DataBase Connectivity) 
JDBC merupakan interface Java untuk mengakses Postgres. Aplikasi yang dibangun dengan Java dapat lebih portabel, dan mudah diperluas karena didukung konsep pemrograman berorientasi objek, sehingga cocok diterapkan pada aplikasi yang besar.

3.2.6 Python
Cobalah python ! Anda akan mendapatkan bahasa pemrograman yang lebih manusiawi. Style-nya mirip Pascal. Bisa digabungkan dengan Tk dan bekerja baik dengan Postgres.

3.2.7 Tcl/Tk
Tcl (Tool Command Language) merupakan bahasa yang seringkali dikaitkan dengan Tk (Tool Kit). Tcl merupakan language sedangkan Tk merupakan widget atau bisa juga disebut visual component.([footnote] Contoh visual component adalah button, listbox, textbox, dsb. Biasanya dikaitkan dengan environment XWindow) 
proc konek {qry} { 
global brs jmlrec
set conn [ pg_connect warnet -host localhost]
set res  [ pg_exec $conn  $qry]
pg_result  $res -assign brs 
set jmlrec [ pg_result $res -numTuples ]
}
global brs jmlrec
load /usr/local/pgsql/lib/libpgtcl.so 
entry .e -textvariable query
listbox .l
pack .e
pack .l
bind .e <KeyPress-Return> {
  konek $query
  for  { set i 0 } { $i < $jmlrec } { incr i } { 
   .l insert end $brs($i,nama)
  }
}

4. Perawatan

4.1 Backup
Seluruh objek (tabel, isi tabel, view, stored procedures, dll) dalam suatu database dapat “dikeluarkan” dalam bentuk SQL script.
$ pg_dump -d -f sugiana.sql sugiana
Perpindahan database dapat juga dilakukan dengan operasi file dan direktori.
Hal seperti ini kadang dipakai manakala sistem crash dan kita belum sempat melakukan “dummy” SQL. Postgres menyimpan setiap database ke dalam suatu direktori. Setiap objeknya (seperti tabel) disimpan dalam sebuah file. Secara default seluruh direktori tersebut tersimpan dalam /var/lib/pgsql/base untuk RedHat dan /var/lib/pgsql/data untuk SuSE. Jadi jika anda ingin membackup seluruh database anda juga dapat melakukannya dengan utility kompresi biasa seperti tar.
$ cd /var/lib/pgsql
$ tar cfz backup.tgz base
Setelah server Postgres pulih, anda tinggal meng-ekstrak-nya ke tempat semula :
$ cd /var/lib/pgsql
$ tar xfz backup.tgz
Perlu anda ketahui bahwa Postgres menyimpan “registry” sistem dalam database template1, salah satunya adalah nama-nama database yang diletakkan dalam tabel pg_database. Jadi meski anda telah meng-copy direktori database dalam /var/lib/pgsql/base namun belum mendaftarkannya maka database tersebut tetap saja belum bisa diakses. Perlu sedikit tindakan pengakalan untuk mendaftarkannya.
$ psql template1
template1=> insert into pg_database values(‘sugiana’,100,’sugiana’);
Tentu saja tindakan di atas tidak perlu dilakukan jika direktori yang anda restore termasuk direktori template1 dari sistem sebelumnya.

4.2 Cleaning-up
Di samping itu telah tersedia perintah vacuum untuk membuang “garbage” yang tak perlu yang terdapat dalam suatu database.
template1=> vacuum

4.3 Upgrading
Sebelum Postgres diupgrade, backup-lah terlebih dahulu database yang lama.
$ pg_dumpall > db.out
Setelah diupgrade, lakukan restore.
$ cd
$ psql -e template1 < db.out

5. Referensi

• The PostgreSQL Development Group, The PostgreSQL Tutorial Introduction
• Sugiana, Owo, Web Database dengan PHP3, Seminar KPLI Jawa Tengah, Semarang, 1999
• http://looking-glass.usask.ca:82/comp-comparison.html