Tug a s Exp lora si Ba sis Da ta Postg reSQL (bagian 2) Oleh : M Firda Fauzan 13504127 Najib Baedlowi 13504131 Bayu Hendra Winata 13504138 Tri Aji Nugroho 13504141 Wisnu Manupraba 13504148 Jamal Saepul Aziz 13504156
Program Studi Teknik Informatika Sekolah Teknik Elektro dan Informatika Institut Teknologi Bandung 2007
Daftar Isi Daftar Isi.................................................................................................................................... 2 Daftar Gambar ....................................................................................................................... 3 Mekanisme Eksplorasi ............................................................................................................. 4 Manajemen Transaksi dan Protokol Konkurensi ................................................................. 4 Mekanisme Penanganan Transaksi .................................................................................. 4 Pengertian ........................................................................................................................ 4 Properti Transaksi ............................................................................................................. 4 Concurrency Control.......................................................................................................... 6 Perkenalan ....................................................................................................................... 6 Transaction Isolation........................................................................................................ 7 Penanganan Deadlock ................................................................................................... 12 Pemulihan Basis Data ........................................................................................................... 13 SQL Dump........................................................................................................................... 14 Mengembalikan Dump ................................................................................................ 16 Penggunaan Pg_dumpall............................................................................................ 17 Penanganan Basis Data Berukuran Besar ................................................................. 17 Menggunakan Backup Level File Sistem ....................................................................... 18 Pengarsipan berkelanjutan dan Point-In-Time Recovery (PITR) ................................. 19 Pengaturan Arsip WAL .................................................................................................. 22 Pembuatan Base Backup ............................................................................................ 23 Pemulihan Basis Data dengan menggunakan Online Backup.............................. 24 Pengaturan Recovery .................................................................................................. 25 Security ................................................................................................................................... 26 Authentication................................................................................................................... 27 trust .................................................................................................................................. 27 ident ................................................................................................................................ 28 password ........................................................................................................................ 28 crypt ................................................................................................................................ 28 md5 ................................................................................................................................. 28 pam (Pluggable Authentication Module) ................................................................. 28 reject ............................................................................................................................... 28 Privilege(Hak Istimewa) .................................................................................................... 29 grant................................................................................................................................ 29 Role.................................................................................................................................. 30 Pengaturan privilege dengan GUI pgAdmin III ........................................................ 31 Kesimpulan ............................................................................................................................ 33 Daftar Referensi..................................................................................................................... 34
Daftar Gambar Gambar 1 Transaksi 1 melakukan read pada transaksi konkuren keduanya read ...... 8 Gambar 2 Transaksi 2 melakukan read pada transaksi konkuren keduanya read...... 8 Gambar 3 Transaksi 1 melakukan write pada transaksi konkuren 1 write, 1 read ........ 9 Gambar 4 Transaksi 2 melakukan aksi read pada transaksi konkuren 1 write, 1 read . 9 Gambar 5 Transaksi 1 melakukan write pada transaksi konkuren keduanya write.... 10 Gambar 6 Transaksi 2 menunggu pada transaksi konkuren keduanya write ............. 10 Gambar 7 Mengubah isolation level menjadi serializable ............................................. 11 Gambar 8 Transaksi pada level serializable...................................................................... 12 Gambar 9 Transaksi mengakses data yang dilock oleh transaksi serializable level ... 12 Gambar 10 Melakukan dump database.......................................................................... 14 Gambar 11 File Dump dalam bentuk .sql ......................................................................... 15 Gambar 12 Membuat database "tes" ............................................................................... 16 Gambar 13 Me-restore dari file dump ............................................................................... 16 Gambar 14 Membuat dump untuk seluruh databse ...................................................... 17 Gambar 15 pembuatan dump untuk database berukuran besar............................... 18 Gambar 16 Restore untuk database berukuran besar ................................................... 18 Gambar 17 Terjadi crash sewaktu transaksi belum commit........................................... 20 Gambar 18 Kondisi tabel setelah terjadi crash sebelum commit ................................. 20 Gambar 19 Kondisi crash ketika transaksi sudah commit .............................................. 21 Gambar 20 Tabel mahasiswa setelah crash setelah commit........................................ 21 Gambar 21 Terjadi crash setelah checkpoint .................................................................. 22 Gambar 22 Kondisi tabel mahasiswa setelah sistem hidup kembali ............................ 22 Gambar 23 Pengaturan WAL pada file postgresql.conf ................................................ 23 Gambar 24 Pengaturan Recovery pada file recovery.conf.......................................... 26 Gambar 25 Login dengan mode trust............................................................................... 28 Gambar 26 Login dengan mode password atau crypt atau md5............................... 28 Gambar 27 Menambah role baru ..................................................................................... 31 Gambar 28 Menambah role baru ..................................................................................... 32 Gambar 29 Mengatur keanggotaan suatu role .............................................................. 32
Mekanisme Eksplorasi Eksplorasi PostgreSQL kali ini kelompok kami menggunakan metodologi uji coba dengan berdasar kepada literatur yang ada.
Manajemen Transaksi dan Protokol Konkurensi Mekanisme Penanganan Transaksi Pengertian Transaction merupakan konsep yang sangat penting pada sistem database. Poin penting dari hal ini adalah bagaimana sebuah transaksi menggabung beberapa langkah atau aksi menjadi sebuah atau banyak atau bahkan tidak ada operasi. Sesuai dengan konsep ACID, bahwa status sebuah transaksi itu hanya ada dua, gagal atau sukses, jika sebuah transaksi sukses, maka hasil terbaru akan di commit dan ditulis ke physical database, sedangkan jika gagal, transaksi tersebut akan di roll back. Operasi yang terjadi pada sebuah transaksi bisa berupa insert, update, delete atau bahkan kombinasinya. Secara default, Po stg reSQL m e ng a ng g a p setia p query a d a la h seb ua h tra nsa ksi. Untuk m e m b ua t b eb era p a query m enja d i se b ua h tra nsa ksi, m eng g una ka n command BEGIN dan COMMIT. BEGIN; . COMMIT ; Properti Transaksi Postgre menangani properti transaksi sebagai berikut: Atomicity Transaksi selesai secara keseluruhan atau tidak sama sekali. Tra nsa ksi d ia ng g a p seb a g a i sa tu query, jika p a d a sa a t eksekusi g a g a l d iteng a h, m a ka a ka n d ila kuka n rollback sehing g a se o la h-o la h tra nsa ksi tid a k dilakukan sama sekali. Misal ada tabel seperti berikut ta b ung a n :
Di ta b le terseb ut, sim p a na n m em p unya i constraint tidak boleh negatif. La lu a d a tra nsa ksi tra nsfer : BEGIN; UPDATE Tabungan SET Simpanan=Simpanan + 4000
WHERE Nama like 'Alpha'; UPDATE Tabungan SET Simpanan=Simpanan - 4000 WHERE Nama like 'Beta'; COMMIT; Pa d a tra nsa ksi terseb ut, up d a te p erta m a d a p a t d ila kuka n, ta p i up d a te ke d ua tid a k d a p a t d ila kuka n ka rena m ela ng g a r constraint. Ka re na b ersifa t atomicity maka transaksi di rollback dan tabel tidak berubah. Isolation Sela m a b elum com m it, p erub a ha n ya ng d ila kuka n o le h sua tu tra nsa ksi tid a k visible terhadap transaksi yang lain. PostgreSQL memberikan dua level isolation, yaitu: o Read commited Sebuah statement ha nya m eliha t tup p le ya ng commited se b elum statement tersebut mulai dieksekusi. o Serializable Semua statement d a la m tra nsa ksi ha nya m eliha t tup le ya ng commited sebelum query p e rta m a d a la m tra nsa ksi ya ng m eng ub a h database Da n sec a ra default level isolation ya ng d ig una ka n a d a la h re a d c o m m ited . Untuk mengganti menjadi Serializable: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN; UPDATE Tabungan SET Simpanan=Simpanan + 1000 WHERE Nama like 'Alpha'; UPDATE Tabungan SET Simpanan=Simpanan - 1000 WHERE Nama like 'Beta'; COMMIT; Pa d a sa a t tra nsa ksi se lesa i m ela kuka n up d a te d a n a ka n commit, kemudian a d a tra nsa ksi ya ng m em b a c a ta b el terse b ut, m a ka ta b el ya ng terliha t m a sih ta b el seb elum ta b el tra nsa ksi tra nsfer d ie ksekusi.
Durability Setela h terja d i commit, se m ua p erub a ha n ya ng d ib ua t o leh tra nsa ksi menjadi visible d a n d ip a stika n tersim p a n ke d a la m database m eskip un terjadi system crash. Untuk tra nsfer ya ng b erha sil: BEGIN; UPDATE Tabungan SET Simpanan=Simpanan + 1000 WHERE Nama like 'Alpha'; UPDATE Tabungan SET Simpanan=Simpanan - 1000 WHERE Nama like 'Beta'; COMMIT;
Maka tabel akan menjadi:
Consistency Eksekusi dari sebuah transaksi harus menjaga consistency pada database. Pa d a c o nto h atomicity d ia ta s, seb ua h tra nsa ksi m ela ng g a r sua tu constraint, untuk menja g a ko nsistensi p a d a d a ta b a se , m a ka tra nsa ksi d ia ta s d i-abort dan dilakukan rollback untuk menjaga agar database tetap konsisten. Co nto h la in m isa l a d a d ua tra nsa ksi tra nsfer ya ng d ila kuka n b ersa m a a n: BEGIN; UPDATE Tabungan SET Simpanan=Simpanan + 1000 WHERE Nama like 'Alpha'; UPDATE Tabungan SET Simpanan=Simpanan - 1000 WHERE Nama like 'Beta'; COMMIT; BEGIN; UPDATE Tabungan WHERE Nama UPDATE Tabungan WHERE Nama COMMIT;
SET Simpanan=Simpanan - 1500 like 'Alpha'; SET Simpanan=Simpanan + 1500 like 'Beta';
Ma ka ha sil ya ng d id a p a t a d a la h ko nsisten (jum la h sim p a na n a lp ha d a n b eta sesudah dan sesudah transaksi sama) Sebelum
Sesudah
Concurrency Control Bagian ini akan mencoba menjelaskan bagaimana postgreSQL menangani lebih dari satu transaksi yang mengakses data yang sama pada suatu waktu. Perkenalan PostgreSQL menyediakan banyak tool bagi developer untuk menangani akses konkuren terhadap data. Data dikelola dengan menggunakan multiversion model (Multiversioin Concurrency Control, MVCC) dimana ketika sebuah transaksi melakukan query terhadap database, seolah-olah mendapatkan data yang telah lama bukan data saat ini. Hal ini melindungi transaksi dari melihat data yang tidak konsisten yang diakibatkan oleh transaksi konkuren lain yang melakukan update terhadap tuple data yang sama atau dengna kata lain menyediakan transaction isolation untuk setiap sesi.
Keuntungan utama dengan menggunakan MVCC model untuk concurrency control daripada locking adalah pada MVCC lock untuk membaca data tidak konflik dengan lock untuk menulis data dengan kata lain tidak mungkin aksi read mem-block aksi write dan sebaliknya aksi write tidak akan mem-block aksi read.
Transaction Isolation SQL standard, mendefinisikan empat level transaction isolation untuk mencegah tiga fenomena, yaitu: Dirty read Sebuah transaksi membaca data yang ditulis oleh transaksi konkuren lainnya yang belum commit. Nonrepeatable read Sebuah transaksi membaca ulang sebuah data yang telah dibaca sebelumnya namun nilai data tersebut telah diubah oleh transaksi konkuren lain. Phantom read Sebuah transaksi melakukan eksekusi ulang terhadap sebuah query pencarian yang mengembalikan sekumpulan tuple namun hasil yang didapat berbeda dari hasil yang didapat pertama kali. Isolation level
Dirty Read
Nonrepeatable read
Phantom read
Read uncommitted
Possible
Possible
Possible
Read commited
Not possible
Possible
Possible
Repeatable read
Not possible
Not possible
Possible
Serializable
Not possible
Not possible
Not possible
Dari keempat keempat transaction isolation level yang disebutkan diatas, ada dua level yang perbedaannya bisa dilihat yaitu: Read Committed Isolation Level Level merupakan default level dari postgreSQL. Ketika ada aksi read (SELECT) data yang akan diambil adalah data yang di commit terakhir, sedangkan jika ada transaksi yang konkuren yang sebenarnya sedang memodifikasi data tersebut namun belum commit dianggap tidak ada. Begitu juga kepada aksi write (UPDATE, DELETE), dia akan mencari kepada target yang commit.
Berikut hasil eksplorasi untuk read commited. Transaksi konkuren keduanya read
Pada kasus ini, kedua transaksi akan berjalan semuanya karena aksi hanya read, sehingga tidak ada lock yang mencegah transaksi lain.
Gambar 1 Transaksi 1 melakukan read pada transaksi konkuren keduanya read
Gambar 2 Transaksi 2 melakukan read pada transaksi konkuren keduanya read Transaksi konkuren satu read, satu write Pada kasus ini ketika ada aksi write dia akan melakukan lock, namun aksi read tetap bisa membaca data namun data versi sebelum data di update. Seperti pada gambar di bawah ini, ketika ada dilakukan update nama diubah menjadi wisnu, namun karena belum commit,
maka transaksi kedua yang melakukan aksi read, mendapatkan hasil bahwa nama masih fauzan.
Gambar 3 Transaksi 1 melakukan write pada transaksi konkuren 1 write, 1 read
Gambar 4 Transaksi 2 melakukan aksi read pada transaksi konkuren 1 write, 1 read Transaksi konkuren keduanya write
Pada kasus ini, transaksi yang pertama kali datang, akan melock data yang bersangkutan. Misal pada gambar dibawah ini, transaksi 1 melakukan update terhadap data dengan id = 1, transaksi 2 juga melakukan hal yang sama. Maka transaksi 2, akan menunggu hingga transaksi 1 melepas locknya, entah dengan aksi commit atau membatalkan transaksi (roll back).
Gambar 5 Transaksi 1 melakukan write pada transaksi konkuren keduanya write
Gambar 6 Transaksi 2 menunggu pada transaksi konkuren keduanya write
Serializable Isolation Level Pada level ini, transaksi tidak akan dijalankan secara konkuren, melainkan selalu serial. Jadi ketika ada transaksi yang mengakses data atau target yang sama, maka akan menunggu hingga transaksi yang melock pertama kali melepas locknya. Berikut adalah hasil eksplorasi pada level serializeable: Mengeset isolation level transaction
Gambar 7 Mengubah isolation level menjadi serializable Sebuah transaksi konkuren mengakses data yang telah diset levelnya menjadi serializable
Gambar 8 Transaksi pada level serializable
Gambar 9 Transaksi mengakses data yang dilock oleh transaksi serializable level
Pada postgreSQL, lock dilakukan terhadap data yang dikenai aksi write, sedangkan jika untuk aksi read, maka data yang akan diberikan adalah data dengan version yang lama. Dengan kata lain bahwa aksi hanya akan membaca data yang commited, sedangkan data yang belum commit, tidak akan pernah dibaca (ketika transaksi itu dilaksanakan).
Penanganan Deadlock PosgreSQL memberikan dukungan terhadap deadlock detection.
Misal ada dua transaksi Transaksi A BEGIN; UPDATE Tabungan WHERE Nama UPDATE Tabungan WHERE Nama COMMIT;
SET Simpanan=Simpanan - 1000 like 'Beta'; SET Simpanan=Simpanan + 1000 like 'Alpha';
Transaksi B BEGIN; UPDATE Tabungan WHERE Nama UPDATE Tabungan WHERE Nama COMMIT;
SET Simpanan=Simpanan + 1000 like 'Alpha'; SET Simpanan=Simpanan - 1000 like 'Beta';
Ked ua tra nsa ksi terse b ut d ie kse kusi sec a ra ko nkuren, ketika tra nsa ksi A d a n B selesa i meng e kse kusi up d a te p erta m a , m a ka a ka n terja d i d e a d lo c k. Po stg reSQL a ka n m end eteksi a d a nya d ea d lo c k d a n sa la h sa tu tra nsa ksi d i abort a g a r tra nsa ksi ya ng lain bisa commit.
Tra nsa ksi ya ng d ia b o rt a d a la h tra nsa ksi d eng a n cost p a ling se d ikit jika d ila kuka n abort.
Pemulihan Basis Data Postg reSQL se b a g a i p enyim p a n d a ta ha rus d i b a c kup sec a ra reg ula r. Sa ng a t p enting untuk m eng e rti tenta ng p rinsip d a sa r teknik d a n a sum si d a la m b a c kup data. Ad a tig a p end e ka ta n ya ng d ila kuka n d a la m m ela kuka n p e m uliha n b a sis d a ta pada PostgreSQL yaitu dengan cara : 1. SQL dump 2. File system pada level backup 3. Pengarsipan berkelanjutan (Continous Archiving). Masing-m a sing d a ri ketig a c a ra itu m e m iliki kele b iha n d a n ke kura ng a n m a sing masing, berikut kita eksplorasi tiap teknik tersebut.
SQL Dump Ide dari pendekatan ini adalah membangkitkan sebuah file teks sederhana dengan m eng g una ka n p erinta h SQL, sehing g a a ka n m e m b ua t tirua n b a sis d a ta ya ng b ersta tus sa m a p a d a sa a t d ila kuka n d um p . Po stg reSQL m end ukung m eto d e ini dengan adanya program pg_dump dengan sintaks sebagai berikut: pg_dump dbname > outfile Contoh ekse kusi Eksp lora si
untuk
m ela kuka n
p e m b ua ta n
d um p
d a ta b a se
b erna m a
Gambar 10 Melakukan dump database Pa d a c o ntoh ekse kusi d ia ta s a ka n m eng ha silka n file d um p d e ng a n e kstensi *.sq l dengan nama db.sql Berikut seb a g ia n isi d a ri file d b .sq l. d a ta b a se Eksp lo ra si m e m iliki ta b le mahasiswa dan dosen, d eng a n ko lom NAMA d a n NIM p a d a mahasiswa d e ng a n p rim a ry ke y NIM, dan NAMA dan NIP pada dosen dengan primary key NIP.
Gambar 11 File Dump dalam bentuk .sql
p g _d um p m erup a ka n sua tu p ro g ra m sta nd a r p o stg reSQL p a d a sisi klie n a rtinya p ro g ra m b a c kup ini d a p a t d ija la nka n p a d a re m ote ho st m a na p un ya ng m e m iliki a kses ke sua tu b a sis d a ta . Na m un p g _d um p tid a k b ero p era si d eng a n izin khusus. Seb a liknya , p ro g ra m ini ha rus m em iliki a kse s terha d a p se mua ta b el ya ng a d a pada b a sis d a ta ya ng a ka n d ib ua t c a d a ng a nnya a rtinya p ro g ra m ini ha rus dijalankan seb a g a i sup eruser. p g _d um p se c a ra oto m a tis a ka n terko ne ksi kep a d a na m a b a sis data user yang sama dengan nama sistem operasi user yang dipergunakan. Semua d um p ya ng d ib ua t o leh p g d um p b ersifa t interna lly c o nsistent a rtinya seg a la p erub a ha n ya ng terja d i p a d a b a sis d a ta ya ng a sli tid a k a ka n m em p eng a uhi sta tus dari b a sis d a ta c a d a ng a nnya . Pro g ra m ini p un tid a k m e m p eng a ruhi o p e ra si la in yang ditujukan kepada basis data yang asli kecuali operasi yang menggunakan exclusive lock. Ha l p enting ya ng ha rus d ila kuka n a d a la h jika sua tu b a sis d a ta m em iliki OIDs, m isa lnya fo reig n keys, m a ka p ro g ra m p g _d um p jug a ha rus m e m b ua t d um p
terha d a p OIDs terseb ut, untuk m ela kuka n ini d a p a t m eng g una ka n command line.
-o p a d a
Mengembalikan Dump File teks ya ng d iha silka n o leh p g _d um p a ka n d ib a c a oleh p ro g ra m p sq l. Sinta ks dasar untuk menampilkan dump adalah: psql dbname < infile d eng a n infile a d a la h file teks ya ng d iha silka n o leh p ro g ra m p g _d um p . Co m m a nd d i a ta s tid a k la ng sung m e m b ua t d a ta b a se m a ka se b elum kita m ela kuka n resto re , la kuka n d ulu p em b ua ta n d a ta b a se. Na m a b a sis d a ta d b na m e ha rus d ib ua t d a ri tem p la te0 seb elum ekse kusi p ro g ra m d i a ta s. Sela in b a sis d a ta ya ng d ituju, seluruh o b jek ya ng d im iliki user ya ng terd a p a t p a d a d um p b eserta p enentua n a ksesnya juga harus terlebih dahulu dibuat. Sesua i d e ng a n ura ia n d i a ta s m a ka seb elum m ela kuka n re sto re b ua t d a hulu d a ta b a se , p a d a c o nto h b erikut kita a ka n m e m b ua t d a ta b a se d eng a n na m a tes .
Gambar 12 Membuat database "tes" Co nto h m eng em b a lika n d um p p a d a b a sis d a ta tes d a n file d um p d b .sq l
Gambar 13 Me-restore dari file dump p g _d um p d a n p sq l m a m p u m em b a c a a ta up un m enulis d um p m ela lui sua tu p ip elines sehing g a p ro g ra m terseb ut m e m ung kinka n m ela kuka n p e m uliha n b a sis data dari satu server ke server lainnya, contohnya: pg_dump -h host1 dbname | psql -h host2 dbname Penting untuk d iing a t b a hw a se luruh d um p ya ng d iha silka n o leh p g _d um p a ka n b ersifa t rela tif terha d a p tem p la te0 a rtinya b a hw a se g a la p ro se d ur, b a ha sa d a n lainnya yang ditambahkan kepada template1 juga akan di-dump oleh pg_dump.
Penggunaan Pg_dumpall p g _d um p ha nya d ig una ka n untuk m e m b ua t d um p sa tu d a ta b a se d a la m sa tu w a ktu, ka rena itu m a ka a d a pg_dumpall yang d ig una ka n untuk m e m b ua t d um p terhadap seluruh basis data yang ada. Sintaks dasarnya adalah: pg_dumpall > outfile Untuk merestore basis data dapat menggunakan: psql -f infile postgres Suatu dump yang dihasilkan oleh pg_dumpall dapat ditampilkan kembali hanya jika m e m iliki a kses sup eruser d a n ha l ini d ip erluka n untuk m eng e m b a lika n informasi mengenai user dan group. Contoh dump untuk semua database dalam postgreSQL
Gambar 14 Membuat dump untuk seluruh databse Pa d a c o nto h d ia ta s terd a p a t 7 p erm inta a n p a ssw o rd d ika rena ka n terd a p a t 7 b a sis d a ta . File d um p b a sis d a ta terse b ut tersim p a n d a la m file a lld b .sq l . Penanganan Basis Data Berukuran Besar Karena PostgreSQL m e m b o lehka n ta b el d eng a n ukura n ya ng leb ih b esa r d a ri m a ksimum ukura n file p a d a sistem , m a ka ha l ini a ka n m enja d i m a sa la h sew a ktu m ela kuka n d um p , ka rena ukura nnya m eleb ihi ukura n m a ksim um p a d a sistem . Ka rena p g _d um p d a p a t d itulis ke d a la m sta nd a r o utp ut, m a ka sta nd a r UNIX to o ls dapat digunakan untuk membereskan kemungkinan masalah ini. Menggunakan compressed dumps. Dump ini dapat menggunakan program pengompres misalnya gzip. pg_dump dbname | gzip > filename.gz Untuk merestore dapat menggunakan createdb dbname gunzip -c filename.gz | psql dbname atau cat filename.gz | gunzip | psql dbname Contoh pembuatan file dump dengan menggunakan pengompres gzip
Gambar 15 pembuatan dump untuk database berukuran besar Contoh untuk melakukan restore dump yang terkompres
Gambar 16 Restore untuk database berukuran besar
Menggunakan Backup Level File Sistem Alterna tif la innya a d a la h d eng a n m eng c o p y file ya ng d ig una ka n p o stg re SQL untuk m enyim p a n d a ta d a la m b a sis d a ta sec a ra la ng sung . Pa d a e ksp lo ra si ka li ini ya itu m eng g una ka n Wind o w XP file system terse b ut b era d a p a d a fo ld er C:\ Program Files\ PostgreSQL\ 8.2\ data. Setela h meng eta hui tem p a tnya m a ka kita d a p a t mengcopy file tersebut dalam bentuk terkompres maupun tidak terkompres. Sehing g a a p a b ila kita m eng -c op y file terseb ut d i kom p uter la in, m a ka kita a ka n mendapatkan basis data yang sama seperti komputer asal. Na mun, terd a p a t 2 b ua h b a ta sa n ya ng m enye b a b ka n p end eka ta n ini kura ng d isuka i d a rip a d a p end e ka ta n p em uliha n b a sis d a ta m eng g una ka n SQL d um p . Dua batasan tersebut adalah: 1. Server b a sis d a ta ha rus d im a tika n untuk m end a p a tka n b a sis d a ta c a d a ng a n ya ng d a p a t d ig una ka n. Ha l ini d ika rena ka n ta r d a n p ro g ra m ko m p res sema c a m nya tid a k a ka n m end a p a tka n sua tu sta tus b a sis d a ta tertentu p a d a sua tu w a ktu tertentu. Akib a tnya , server b a sis d a ta p un ha rus d im a tika n jika ing in dilakukan pemulihan basis data.
2. Pe m b ua ta n a ta u p e muliha n sua tu b a sis d a ta a ta u ta b el tertentu d eng a n meng c o p y sua tu file system tid a k a ka n b erha sil ka rena info rm a si ya ng terd a p a t p a d a file system terseb ut ha nya se b a g ia n. Sete ng a h info rm a si la innya b e ra d a p a d a file c o m m it log p g _c lo g ya ng b erisi sta tus c o m m it d a ri seluruh tra nsa ksi. Ja d i p e m b ua ta n file system c a d a ng a n ha nya b erla ku untuk p e m b ua ta n c a d a ng a n dari keseluruhan cluster basis data.
Pengarsipan berkelanjutan dan Point-In-Time Recovery (PITR) Postg reSQL sela lu m e nja g a se b ua h w rite a hea d lo g (WAL) d a la m d ire kto ri p g _xlo g , ya itu seb ua h sub d irekto ri d a ta d ire kto ri d a ta se b ua h c luster. Lo g terse b ut m end eskrip sika n se m ua p erub a ha n a ta u up d a te ya ng terja d i p a d a seb ua h file d a ta sua tu b a sis d a ta . Lo g ini d ig una ka n jika system m eng a la m i c ra sh. Jika sua tu sistem m e ng a la m i c ra sh p a d a sa a t sua tu tra nsa ksi sed a ng b erla ng sung , m a ka p e muliha n b a sis d a ta d ila kuka n d eng a n c a ra m ela kuka n ula ng seg a la tra nsa ksi ya ng terja d i hing g a m enc a p a i sua tu c hec kp o int tertentu sesua i d eng a n uruta n lo g p a d a WAL. Chec kp o int a d a la h titik p a d a sekuens tra nsa ksi sesa a t setela h d ija m in b a hw a file d a ta tela h d iub a h d eng a n seluruh info rm a si ya ng d im a sukka n seb elum c hec kp o int. Pa d a c hec kp o int, seluruh d a ta p a g e ko to r d itulis ke d isk d a n rec o rd checkpoint khusus ditulis ke dalam log file. Metode ini juga dapat dikombinasikan dengan metode pemulihan basis data pada level file system . Ha l terse b ut d a p a t d ila kuka n d eng a n c a ra m eng em b a lika n b a sis d a ta c a d a ng a n la lu mela kuka n ula ng se g a la tra nsa ksi ya ng terd a p a t p a d a file WAL. Keuntungan dari gabungan metode ini adalah: 1. Ba sis d a ta ya ng ko nsisten tid a k d ip e rluka n p a d a sa a t p e m uliha n b a sis d a ta ka rena se g a la ketid a kko nsistena n a ka n d ija g a d e ng a n m e ng g una ka n pengulangan log. 2. Sua tu file WAL d a p a t b e risi string p a nja ng lo g ya ng tid a k terb a ta s jum la hnya , sehingga pemulihan basis data secara kontinu dapat dilakukan dengan cara m ela kuka n ke m b a li tra nsa ksi ya ng sesua i d eng a n lo g p a d a file WAL se c a ra ko ntinu. Akib a tnya , tid a k d ip erluka n sua tu p em b ua ta n b a sis d a ta c a d a ng a n yang berukuran besar secara berkala. 3. Peng ula ng a n tra nsa ksi ya ng b erka ita n d eng a n file WAL tid a k p erlu d ila kuka n hing g a a khir file terseb ut ka rena sua tu b a sis d a ta ya ng ko nsisten m a sih b isa d id a p a t d eng a n c a ra m ela kuka n p eng ula ng a n tra nsa ksi hing g a sua tu c hec kp o int tertentu. Meto d e ini m end ukung p o int-in-tim e re c o very a rtinya pemulihan basis data dapat dilakukan setiap saat. 4. Meto d e ini ha nya d a p a t d ila kuka n untuk keseluruha n c luster b a sis d a ta d a n d ip erluka n rua ng p enyim p a na n ya ng c ukup b esa r untuk m enja g a file WAL, teruta m a sa a t sistem mend a p a tka n b a nya k p erm inta a n sua tu tra nsa ksi namun metod ini akan menjamin kekonsistenan dan reliability. Berikut adalah hasil eksplorasi dengan uji coba
Gambar 17 Terjadi crash sewaktu transaksi belum commit Gambar 17 merupakan uji coba terhadap peng-update-an data yang terjadi tetapi terjadi crash (service postgre dimatikan) sebelum terjadinya comit.
Gambar 18 Kondisi tabel setelah terjadi crash sebelum commit Gambar 18 merupakan hasil yang terjadi pada table mahasiswa setelah terjadinya crash (service postgre mati sebelum commit), sehingga dihasilkan database yang sama seperti semula, karena pada saat terjadinya crash, semua data hasil transaksi 1 (update) baru disimpan di dalam memory, sehingga ketika terjadi crash data tersebut hilang.
Gambar 19 Kondisi crash ketika transaksi sudah commit Gambar 19 merupakan uji coba dengan melakukan 2 transaksi, commit terjadi setelah transaksi 1, sedangkan system crash setelah terjadi transaksi 2 tetapi belum commit.
Gambar 20 Tabel mahasiswa setelah crash setelah commit Gambar 20 merupakan hasil yang terjadi pada table mahasiswa setelah terjadinya crash pada gambar 19. Untuk transaksi 1 yang sudah commit, maka data akan berubah sesuai dengan transaksi 1, karena pada saat commit semua data dipindah ke storage sehingga sewaktu terjadi crash data tersebut sudah berada pada storage, sedangkan sebelum transaksi 2 commit crach terjadi, sehingga transaksi 2 belum tertulis pada storage (hanya tertulis pada memory)
Gambar 21 Terjadi crash setelah checkpoint Gambar 21 merupakan uji coba dengan melakukan transaksi update. Setelah transaksi update, kemudian melakukan checkpoint. Setelah itu system crash.
Gambar 22 Kondisi tabel mahasiswa setelah sistem hidup kembali Gambar 22 menunjukkan kondisi table setelah sistem normal kembali, di tabel ini dapat dilihat bahwa transaksi pada gambar 21 sudah terlaksana. Hal ini dikarenakan setelah melakukan transaksi system sudah melakukan checkpoint, yaitu melakukan pemindahan data ke file didalam directori pg_xlog, sehingga walaupun belum ditulis ke dalam storage tetapi ketika system normal kembali maka system akan melakukan recovery ke transaksi transaksi yang tersimpan di log. Transaksi yang tersimpan dilog sebelum crash terjadi dilakukan ulang(redo). Pengaturan Arsip WAL Sec a ra lo g is, sistem p o stg reSQL a ka n m e m b ua t runtuna n file WAL ya ng sa ng a t p a nja ng . Sec a ra fisik, sistem terseb ut a ka n m em b a g i runtuna n p em b ua ta n file WAL terseb ut m enja d i WAL se g m ent file ya ng b erukura n 16MB p er file-nya . Pa d a sa a t p eng a rsip a n d a ta WAL, ha l ya ng p e rlu d ila kuka n a d a la h m eng a m b il isi d a ri setia p seg m ent file d a n me nyim p a nnya d i sua tu tem p a t seb elum se g m ent file terseb ut d ig una ka n untuk m e ng a m b il d a ta la innya . Ba nya k c a ra d a p a t d ila kuka n untuk menyimpan isi dari setiap segment file tersebut. Postg reSQL sec a ra fleksib el m e m b erika n p iliha n ke p a d a d a ta b a se a d m inistra to r untuk mela kuka n p eng a rsip a n file WAL d eng a n mena m p ilka n shell c o m m a nd tertentu. Shell c o m m a nd se c a ra khusus d ig una ka n o leh p a ra m eter ko nfig ura si archive_command yang tersimpan pada file postgresql.conf Contoh perintah yang sederhana adalah sebagai berikut :
archive_command = cp -i %p /mnt/server/archivedir/%f
Gambar 23 Pengaturan WAL pada file postgresql.conf
Pembuatan Base Backup Prosedur dalam pembuatan base backup adalah: 1. Pa stika n b a hw a file WAL tela h b e ke rja se sua i d eng a n ya ng d iing inka n d a n bekerja dengan baik 2. Buat koneksi dengan basis data sebagai superuser dan eksekusi perintah: SELECT pg_start_backup( label );
Dimana la b el a d a la h na m a unik d a ri o p era si p em uliha n ya ng d ing inka n. Perinta h p g _sta rt_b a c kup m em b ua t sua tu file b a c kup la b el ya ng d ise b ut b a c kup _la b el d a la m d ire ktori c luster d a n b erisi info rm a si tenta ng p e m uliha n data yang diinginkan. 3. La kuka n p roses p e m uliha n b a sis d a ta m eng g una ka n p era la ta n file-systembackup seperti tar atau cpio. 4. Buat kembali koneksi ke basis data sebagai superuser dan eksekusi perintah: SELECT pg_stop_backup(); 5. Jika berhasil, maka proses telah selesai. Op era si d i a ta s ha rus d ila kuka n sec a ra b eruntun, tid a k a d a o verla p d a n ha rus m enja m in b a hw a d um p ya ng d iha silka n sud a h term a suk se m ua file ya ng b era d a d i bawah direktori cluster basis data (contoh: /usr/local/pgsql/data). Pro ses b a c kup terseb ut a ka n b erg una jika se m ua se g m ent file WAL terus d ib a ng kitka n p a d a sa a t a ta up un setela h p ro ses b a c kup selesa i. p g _sto p _b a c kup b erfung si untuk mela kuka n a ktivita s ya ng d iseb utka n d i a ta s. Prog ra m terse b ut a kan m e m b ua t seb ua h b a c kup histo ry file ya ng la ng sung d isim p a n ke file WAL. File terseb ut ha nya la h seb ua h file teks b erukura n ke c il d a n b erisi string la b el ya ng diberikan kepada pg_start_backup. Pg _sta rt_b a c kup a ka n m e m b ua t file b erna m a b a c kup _la b el ya ng d isim p a n p a d a d ire kto ri c luster b a sis d a ta se b a g a i c a d a ng a n terha d a p file d um p , na mun a ka n d iha p us d a ri rua ng p enyim p a na ketika p g _sto p _b a c kup selesa i d ip ro ses. File terseb ut b erisi string la b el ya ng d ib e rika n kep a d a p g _sta rt_b a c kup , c a ta ta n w a ktu dimulainya p ro ses p g _sta rt_b a c kup d a n na m a d a ri file WAL ya ng p erta m a . Jika terja d i keb ing ung a n m eng ena i file WAL m a na ya ng p erta m a ka li d ija la nka n, m a ka solusinya harus merujuk kepada file dump cadangan. Dum p c a d a ng a n jug a b isa d ila kuka n p a d a sa a t server d ihentika n. Da la m ka sus ini, p g _sta rt_b a c kup d a n p g _sto p _b a c kup tid a k d a p a t d ig una ka n d a n p e nelusura n d um p c a d a ng a n d a n seb e ra p a b a nya k file WAL ya ng tela h terekse kusi ha rus d ila kuka n sec a ra m a nua l. Ha l ini d a p a t d ip e c a hka n d eng a n m eng g una ka n prosedur online backup.
Pemulihan Basis Data dengan menggunakan Online Backup Langkah langkahnya adalah sebagai berikut: 1. Matikan server, jika sedang berjalan 2. Jika rua ng p enyim p a na n ya ng d im iliki c ukup b esa r, m a ka sim p a n seluruh d ire kto ri d a ta c luster d a n seluruh ta b lesp a c es ke tem p a t p e nyim p a na n sem enta ra . Jika rua ng p enyim p a na n tid a k m enc ukup i, m a ka ha nya isi d a ri p g _xlo g ya ng m e rup a ka n sub d ire kto ri d a ri d ire kto ri c luster d a ta ya ng disimpan. 3. Ha p us se m ua file ya ng a d a d a n seluruh sub d ire kto ri ya ng b era d a d i b a w a h direktori c luster d a ta serta d i b a w a h d ire ktori ro o t d a ri seluruh ta b lesp a c es yang digunakan. 4. Ta m p ilka n file b a sis d a ta d a ri d um p c a d a ng a n. Jika ta b lesp a c e s d ig una ka n, maka lakukan verifikasi terhadap pemulihan link simbolik pada pg_tblspc/ 5. Pind a hka n seluruh file ya ng a d a p a d a p g _xlo g / ya ng b era sa l d a ri d um p c a d a ng a n. Jika p g _xlo g / tid a k d isim p a n, m a ka b ua t ula ng d eng a n m e m a stika n a p a ka h sub d ire kto ri p g _xlo g / a rc hive_sta tus/ tela h b erha sil dibuat ulang.
6. Jika terd a p a t seg m ent file WAL ya ng tid a k d isim p a n se p e rti ya ng telah dijelaskan pada langkah kedua, maka simpan file tersebut pada pg_xlog/. 7. Bua t seb ua h file rec o very c o m m a nd rec o very.c o nf d a la m d irekto ri c luste r d a ta . La kuka n m o d ifika si p a d a p g _hb a .c o nf untuk m enc eg a h a g a r sua tu koneksi tidak dibuat oleh user sebelum pemulihan basis data telah berhasil. 8. Jalankan server. Server a ka n b erja la n p a d a m o d e recovery d a n a ka n m ela kuka n ula ng tra nsa ksi sesua i d eng a n file WAL. Setela h p ro ses p e m uliha n selesa i m a ka server a ka n m erub a h na m a recovery.conf menjadi recovery.done dan kembali melakukan operasi basis data secara normal. 9. Insp e ksi ula ng b a sis d a ta ha sil p ro ses p e m uliha n. Jika isi b a sis d a ta terseb ut tidak sesuai dengan yang diinginkan, maka ulangi langkah pertama. Kunc i d a ri setup file rec o very c o m m a nd ya ng mend eskrip sika n b a g a im a na c a ra p e muliha n b a sis d a ta d a n se b era p a ja uh p ro se s p em liha n terse b ut b erla ng sung . Pro to tip e file terseb ut a d a la h rec o very.c o nf.sa m p le (a d a d i d irektori insta lla tio n sha re/ ). Sa tu ha l ya ng ha rus d itentuka n sec a ra sp esifik p a d a re c o very.c o nf a d a lah resto re_c o m m a nd se b ua h shell c o m m a nd string ya ng m e m b erita hu p o stg reSQ L b a g a im a na m end a p a tka n se g m ent file WAL. Sinta ks p eng g una a nnya a d a la h sebagai berikut: restore_command = cp /mnt/server/archivedir/%f %p d eng a n %p m erup a ka n a b so lute p a th d a ri file , %f m erup a ka n na m a file d a n b ia s m e m p erg una ka n %% seb a g a i ka ra kter esc a p e. Perinta h d i a ta s a ka n m enyim p a n segment file WAL ke direktori /mnt/server/archivedir
Pengaturan Recovery Peng a tura n ini ha nya b isa d ila kuka n d i file rec o very.c o nf d a n ha nya b e rfungsi pada sa a t p ro se s p e m uliha n. File terseb ut ha rus d iset ula ng d eng a n runtuna n p ro se s p e muliha n ya ng d iing inka n d a n tid a k d a p a t d irub a h sa a t p ro ses p em uliha n sed a ng berjalan. restore_command (string) Perinta h shell untuk m eng e kse kusi p eng a m b ila n file WAL. Sinta ks penggunaannya: restore_command = cp /mnt/server/archivedir/%f "%p" restore_command = copy /mnt/server/archivedir/%f "%p" # Windows d eng a n %p m erup a ka n a b so lute p a th d a ri file , %f m e rup a ka n na m a file d a n bisa m e m p erg una ka n %% se b a g a i ka ra kter esc a p e . Perinta h d i a ta s ha rus m eng e m b a lika n nila i no l a p a b ila p e rinta h b erha sil d iekse kusi. Jika na m a file yang diinginkan tidak ada maka perintah ini harus membalikan nilai tidak nol. recovery_target_time (timestamp) Pa ra m eter d i a ta s m enentuka n w a ktu d im ula inya p ro ses p e m uliha n b a sis d a ta . Sa la h sa tu d a ri rec o very_ta rg et_tim e a ta u rec o very_ta rg et_xid d a p a t d ip ilih. Tug a snya a d a la h m ela kuka n p e muliha n b a sis d a ta hing g a a khir d a ri lo g WAL. Titik b erhenti p ro ses p e m uliha n b a sis d a ta jug a d a p a t d itentuka n m elalui perintah recovery_target_inclusive. recovery_target_xid (string) Pa ra m eter d i a ta s menentuka n ID tra nsa ksi ya ng iterlib a t d a la m p ro ses p e m uliha n b a sis d a ta . Ha rus d iing a t b a hw a tra nsa ksi d a p a t selesa i se c a ra a c a k d a n tid a k terurut b erd a sa rka n ID tra nsa ksi. Tra nsa ksi p erlu d ip ulihka n adalah transaksi yang telah commit. Salah satu dari recovery_target_xid atau
rec o very_ta rg et_tim e d a p a t d a p a t d ip ilih. Tug a snya a d a la h m ela kuka n p ro ses p em uliha n b a sis d a ta hing g a a khir d a ri lo g WAL. Titik b e rhenti p roses p e m uliha n b a sis d a ta jug a d a p a t d itentuka n m ela lui p erinta h recovery_target_inclusive. recovery_target_inclusive (boolean) Menentuka n a p a ka h p ro ses p e m uliha n b a sis d a ta b erhenti setela h ta rg et p e m uliha n tertentu(true) a ta u seb elum ta rg et p e m uliha n tertentu. Da p a t d ig una ka n p a d a rec o very_ta rg et_xid a ta u rec o very_ta rg et_tim e ya ng d ip ilih untuk proses pemulihan basis data. recovery_target_timeline (string) Menentuka n p ro ses p e m uliha n b a sis d a ta p a d a sua tu tim eline tertentu. Tug a snya a d a la h m ela kuka n p ro ses p e m uliha n b a sis d a ta sesua i d eng a n timeline p a d a sa a t trerja d i c ra sh. Pa ra m eter d i a ta s ha nya p erlu d iset d a la m situa si p e m uliha n ula ng ya ng ko m p le ks ya itu m eng em b a lika n sta tus sesua i ketika point-in-time recovery telah didapat.
Contoh pengaturan recovery pada file recovery.conf
Gambar 24 Pengaturan Recovery pada file recovery.conf
Security Di dalam PostgreSQL, terdapat tiga aspek yang menjadi perhatian ketika kita membahas masalah security. Yang pertama, bagaimana mengamankan file file database yang terdapat di server sehingga hanya orang yang berhak yang bisa mengakses file - file tesebut. Hal ini bisa dilakukan dengan mengatur file permission, misalnya menggunakan perintah chmod di lingkungan Linux (Aspek pertama ini tidak akan dibahas lebih jauh karena lebih berhubungan dengan sistem operasi daripada DBMS). Yang kedua adalah masalah authentication, yaitu bagaimana menjamin bahwa seorang user benar benar merupakan user yang bersangkutan, bukan orang lain yang berpura pura sebagai dirinya. Autentifikasi menjaga bahwa hanya orang yang benarlah yang bisa mengakses database, sedangkan orang lain yang tidak berwenang tidak boleh masuk ke dalam DBMS.
Aspek yang terakhir adalah masalah pengaturan hak akses untuk tiap pengguna yang valid (granting). Hak akses mengatur apa saja yang bisa dilakukan oleh seorang pengguna begitu dia berada di dalam PostgreSQL. Dengan adanya pengaturan seperti ini, maka seorang pengguna tidak bisa berbuat seenaknya meskipun sudah bisa masuk ke dalam DBMS.
Authentication Authentication adalah suatu proses dimana server basisdata mengidentifikasi identitas dari klien, dan kemudian menentukan apakah klien tersebut diijinkan untuk melakukan koneksi dengan database yang diminta. Dalam PostgreSQL, autentikasi klien diatur dalam sebuah file konfigurasi dengan nama pg_hba.conf (terletak di folder {instalasi_direktori}/data/). Cuplikan isi file pg_hba.conf
# local
DATABASE USER METHOD [OPTION]
# host
DATABASE USER CIDR-ADDRESS METHOD [OPTION]
# hostssl
DATABASE USER CIDR-ADDRESS METHOD [OPTION]
# hostnossl DATABASE USER CIDR-ADDRESS METHOD [OPTION] # # (The uppercase items must be replaced by actual values.)
Pada prinsipnya, file konfigurasi ini mengatur host mana saja yang diperbolehkan untuk melakukan koneksi ke database, klien siapa saja yang berhak mengakses, username yang mana yang bisa digunakan dan database apa saja yang bisa diakses. Kolom pertama mengatur jenis koneksi yang digunakan. Kolom DATABASE bisa diganti dengan a ll , sa m euser , sa m ero le , na m a d a ta b a se , a ta u ko m b ina si d ia nta ra nya d eng a n d ip isa hka n ko m a . USER b isa d iisi d eng a n a ll , userna m e tertentu, na m a g rup (d eng a n p refix + ), a ta u ko m b ina si d ia nta ra nya . CIDRADDRESS merupakan daftar host(IP address) yang diijinkan untuk melakukan koneksi ke database. METHOD bisa diisi dengan salah satu diantara berikut: trust semua pengguna di pihak klien bisa mengakses data di DBMS tanpa harus memasukkan password. Tingkat keamanannya paling rendah. Sebaiknya digunakan pada single-user komputer.
Gambar 25 Login dengan mode trust ident Bekerja dengan cara mengecek username untuk login ke sistem operasi pada sisi klien, kemudian menentukan database yang diijinkan untuk diakses berdasar map file pada DBMS yang berisi pasangan user dan database yang bisa diakses. password Setiap kali klien melakukan koneksi ke PostgreSQL, maka akan divalidasi dengan meminta password. Password dikirimkan dalam bentuk plain teks.
Gambar 26 Login dengan mode password atau crypt atau md5 crypt Sama dengan password, hanya saja password yang diketikkan pengguna di klien, akan dikirimkan ke server dalam bentuk yang sudah terenkripsi. md5 Sama dengan password, hanya saja password yang diketikkan pengguna di klien, akan dikirimkan dalam bentuk string hasil algoritma md5. pam (Pluggable Authentication Module) autentikasi pengguna dilakukana bukan di sisi server, melainkan diserahkan ke masing masing aplikasi yang menggunakan server tersebut. Jadi, tiap aplikasi bisa menerapkan mekanisme yang berbeda. reject Menolak setiap koneksi dari host dan user tertentu atau koneksi untuk mengakses database tertentu.
Sementara OPTION merupakan nama dari PAM service( jika METHOD-nya diisi dengan pam) atau mapname(jika METHOD menggunakan ident). OPTION tidak harus diisi.
Privilege(Hak Istimewa) Privilege menjaga setiap pengguna yang sudah ter-autentikasi untuk tetap berada di jalur yang benar. Artinya, seorang pengguna hanya bisa melakukan sesuatu yang menjadi haknya. Jika dia tidak punya hak untuk melihat tabel milik pengguna lain, maka sistem harus menjamin bahwa dia tidak bisa melakukan itu. Privilege mengatur hubungan antara pengguna(user), group(sekumpulan user), database object(tabel, view, sequence). grant Ketika seorang user membuat suatu obyek database, maka otomatis ia menjadi pemilik dari obyek tersebut. Secara default, hanya pemilik yang bisa melakukan apapun terhadap obyek miliknya. Agar pengguna lain bisa mengakses obyek miliknya, maka pengguna tersebut harus diberi privilege oleh yang bersangkutan. Di PostgreSQL versi 8.2, terdapat dua belas macam privilege, yaitu : SELECT, INSERT, UPDATE, DELETE, RULE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, dan USAGE. SELECT Mengijinkan untuk melakukan SELECT pada tabel, view, atau sequence tertentu. Juga mengijinkan menggunakan perintah COPY TO. INSERT Mengijinkan untuk menambah row baru pada tabel tertentu serta perintah COPY TO. DELETE Mengijinkan untuk menghapur row tertentu dari suatu tabel. RULE User bisa membuat RULE baru pada suatu tabel atau view. REFERENCES Memungkinkan untuk membuat konstrain foreign key. Hak akses ini harus dimiliki di kedua tabel (tabel utama dan tabel referensi). TRIGGER Diperbolehkan untuk membuat trigger di tabel tertentu. CREATE Untuk database, maka boleh membuat skema baru dalam database tersebut. Untuk skema, maka bole membuat obyek database baru dalam skema. Untuk tablespace, maka boleh membuat tabel baru dan menetapkan indek.
TEMPORARY Boleh membuat tabel sementara ketika menggunakan database. EXECUTE Boleh memakai fungsi
fungsi tertentu.
USAGE Boleh mengakses obyek database dalam suatu skema. Contoh: Memberi hak pada pengguna dengan username Anton untuk melakukan update terhadap tabel mahasiswa. GRANT UPDATE ON mahasiswa TO anton; Untuk mencabut privilege yang telah kita berikan, bisa menggunakan perintah REVOKE REVOKE UPDATE ON mahasiswa FROM anton;
Role Dengan mengelompokkan sekumpulan user dalam satu grup, maka akan lebih mudah melakukan manajemen daripada harus mengatur satu persatu user tersebut. Dalam PostgreSQL, hal ini dimungkinkan dengan membuat ROLE dengan hak tertentu kemudian setiap user bisa dimasukkan ke dalam ROLE tersebut dan otomatis akan memiliki tambahan privilege sesuai yang dimiliki ROLE. Perintah untuk membuat grup ROLE CREATE ROLE name [atribut]; Jenis atribut yang bisa ditambahkan adalah LOGIN, SUPERUSER, CREATEDB, CREATEROLE. Untuk memasukkan suatu user ke ROLE tersebut, ketikkan perintah: GRANT nama_role TO user1,user2,..; Contoh CREATE ROLE admin LOGIN; CREATE USER uyab;
-- membuat role admin dengan atribut login
-- membuat user baru dengan nama uyab
GRANT admin TO uyab;
-- uyab menjadi anggota role admin
REVOKE admin TO uyab;
-- mencabut keanggotaan uyab dalam role admin
DROP ROLE admin;
-- menghapus role admin
Pengaturan privilege dengan GUI pgAdmin III
Gambar 27 Menambah role baru
Gambar 28 Menambah role baru
Gambar 29 Mengatur keanggotaan suatu role
Kesimpulan 1. Da la m m e na ng a ni tra nsa ksi Po stg reSQL m end ukung p enuh p ro p e rti-properti yang dimiliki oleh transaksi, yaitu : atomicity, consistency, isolation, dan durability 2. p o stg reSQL m ena ng a ni tra nsa ksi ko nkuren d eng a n m eng g una ka n multiversion model (Multiversioin Concurrency Control, MVCC) 3. PostgreSQL mendukung dua level konsistensi, yaitu : Read commited Se b ua h sta tem ent ha nya m e liha t tup p le ya ng c o m m ited seb elum statement tersebut mulai dieksekusi. Serializable Sem ua sta tem ent d a la m tra nsa ksi ha nya m eliha t tup le ya ng c o m m ited sebelum query pertama dalam transaksi yang mengubah database 4. Po stg reSQL m ena ng a ni d e a d lo c k d eng a n d e a d lo c k d etec tio m . Jika a d a d e a d lo c k ya ng terja d i, p o stg re a ka n m e nd eteksinya d a n m e ng -a b o rt sa la h sa tu tra nsa ksi ya ng m enyeb a b ka n d e a d lo c k a g a r tra nsa ksi ya ng la in b isa selesai. 5. Pe m uliha n b a sis d a ta p a d a Po stg reSQL d a p a t d ila kuka n tig a p e nd e ka ta n ya itu dengan cara : a. SQL dump b. File sistem pada level backup c. Pengarsipan berkelanjutan (Continous Archiving). 6. SQL d um p d ila kuka n untuk m e nd a p a tka n ske m a d a n isi b a sis d a ta se c a ra manual, yaitu dengan cara membackupnya ke dalam suatu file tertentu. 7. Da ri ha sil uji c o b a ya ng d ila kuka n Po stg reSQL, kesim p ula n se m enta ra tid a k d a p a t m ela kuka n re d o . Ja d i setela h c hec kp o int a p a b ila terja d i tra nsa ksitra nsa ksi kem ud ia n terja d i fa ilure , m a ka tra nsa ksi-tra nsa ksi ya ng terja d i setela h checkpoint langsung hilang. 8. Peng a tura n untuk WAL d a p a t d ila kuka n p a d a file p o stg reSQL.c o nf ya ng d id a la m nya terd a p a t p eng a tura n tenta ng p eng a tura n c o m m it d a n checkpo int, sed a ng ka n p eng a tura n untuk rec o very b era d a file rec o very.c o nf yang didalamnya terdapat pengaturan property recovery 9. Ad a tig a ting ka ta n sec urity d a la m Po stg reSQL. Ya ng p erta m a a d a la h p eng a m a na n d i ting ka t file (file p ersm issio n). Ting ka ta n sela njutnya a d a la h a utentika si p eng g una (klien) ya ng m enc o b a m ela kuka n ko ne ksi ke d a ta b a se server. Autentika si m em a stika n ha nya user d a n ho st tertentu sa ja ya ng b isa m a suk ke DBMS d a n m eng a kses sua tu d a ta b a se tertentu. Ya ng ketig a a d a la h b a g a im a na m eng a tur ha k a kses user user ya ng sud a h tera utentika si, ya itu user yang sudah diijinkan masuk ke DBMS.
Daftar Referensi [1]
Silberschatz, Korth, Sudharsan, Database System Concept, Chapter 14 Query Optimization, 2002.
[2]
PostgreSQL 8.2.3 Documentation.
[3]
www.dbexperts.net/postgresql/ tanggal akses 24 April 2007 13.05
[4] http://www.thescripts.com/forum/forum59.html tanggal akses 24 April 2007 13.06 [5]
Korry Douglas, Susan Douglas, PostgreSQL, 2003.
This document was created with Win2PDF available at http://www.daneprairie.com. The unregistered version of Win2PDF is for evaluation or non-commercial use only.
