nareswara.com

Selama ini, ada dua operasi yang sering kali dilakukan berkaitan dengan merubah baris record menjadi suatu bentuk format data di kolom , yakni :

• Merubah baris record menjadi beberapa kolom.
• dan merubah baris record menjadi suatu string.

Selama ini, dua hal tersebut di selesaikan dengan membuat suatu fungsi di dalam database. Tetapi untuk point terakhir, Postgresql telah menyediakan suatu feature sederhana yang cukup ampuh.

Untuk merubah baris record menjadi suatu string, yang perlu dilakukan adalah mengkonversi hasil dari sub-query kita kedalam suatu array, dan merubahnya menjadi string dengan fungsi builtin array_to_string untuk menggabungkan hasilnya dengan parameter yang ke dua sebagai elemen penggabung.

Berikut adalah contoh dengan menggunakan table user untuk menghasilkan string username.

$sql > select array_to_string(array(select username from users), ', ') as result;


Cukup sederhana bukan ?

Seringkali saya harus menginstall ulang server postgresql saya. Saking seringnya saya melakukan instalasi postgresql, saya sampai harus melakukan proses pengaturan yang berulang ulang.

Dilain pihak, saya sering mendapat pertanyaan dari rekan rekan pengguna postgresql, mengenai pengaturan apa yang harus di lakukan setelah postgresql di install.

Kondisi awal :

• Server postgresql di operating sistem Ubuntu Linux.
• RAM server hanya 2 GB
• User yang terkoneksi sekitar 100 concurent user.

Berikut saya bagikan langkah langkah yang seringkali saya lakukan setelah instalasi postgresql. :

1. merubah default password dari postgresql.
   
$ su postgres
$ psql -d template1
template1=# ALTER USER postgres WITH PASSWORD '${POSTGRESQL_POSTGRES_PASSWORD}';

2. merubah pengaturan kernel.
   
   edit file pengaturan kernel sysctl.conf
   
$ vim /etc/sysctl.conf

   
   dan kemudian tambahkan baris berikut ini.
   
   kernel.shmmax = 1073741824

   # increase TCP maximum buffer size
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
#
# # increase Linux autotuning TCP buffer limits
# # min, default, and maximum number of bytes to use
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216


3. mengubah file postgresql.conf
   
   Pengaturan ini adalah pengaturan awal saja. Nilai parameter dapat berubah apabila kondisi applikasi dan lingkungan berbeda.
   
listen_addresses = '*' # agar dapat diakses dari jaringan
port = 8370 # default port yang berbeda, tidak memakai default
max_connections = 100 # maximal concurrent user
shared_buffers = 256MB # 1/4 dari kapasitas fisik RAM

4. mengubah file pg_hba.conf
   
   tambahkan baris perintah berikut jika anda menginginkan postgresql dapat diakses dalam satu subnet jaringan.
   
host all all 192.168.83.0/24 md5

   tambahkan baris perintah berikut jika anda menginginkan postgresql dapat diakses dari satu alamat ip tertentu.
   
host all all 192.168.83.70 md5

5. restart postgresql.
   
   setelah pengaturan di simpan. Kita restart database postgresql.
   
$ service postgresql restart;

Demikian pengaturan yang sering saya lakukan. Pengaturan ini adalah pengaturan awal.

Baru baru ini, saya migrate database dari 8.3 ke 9.1. Berhubung waktu yang tersedia sangat sedikit, sehingga saya tidak melakukan banyak persiapan. Hampir semuanya menggunakan default setting dari postgres.

Nah, saat ini saya butuh merapikan layout fisik dari database saya. Target saya adalah memisahkan table dan index dari tablespace default, dan meletakkannya pada harddisk yang terpisah.

Berikut step yang saya lakukan :

1. Membuat tablespace data dan index di harddisk yang lain
   
   postgresql$ create tablespace app_data owner postgres location '/data1/app/tablespaces';

   postgresql$ create tablespace app_index owner postgres location '/data2/app/tablespaces';


2. Memindahkan table ke tablespace data. Untuk memindahkan table, anda harus membuat script terlebih dahulu, yang berisikan daftar table yang anda miliki. Kemudian mengeksekusinya. Kebetulan schema yang saya pergunakan adalah public.Berikut cara untuk mengenerate scriptnya.
   
postgresql$ select 'alter table '||tablename||' set tablespace app_data;' from pg_tables where schemaname='public';

   Setelah itu, copy paste script diatas dan di eksekusi.
3. Memindahkan index ke tablespace index. Untuk memindahkan index, anda harus membuat script terlebih dahulu, yang berisikan daftar index yang anda miliki. Kemudian mengeksekusinya. Berikut cara untuk mengenerate scriptnya.
   
postgresql$ select 'alter index '||indexname||' set tablespace app_index;' from pg_indexes where schemaname='public';

   Setelah itu, copy paste script diatas dan di eksekusi.
4. Langkah terakhir adalah merubah default tablespace dari database menjadi app_data. Dengan ini secara default, table yang dibuat akan menggunakan tablespace ini.
   
postgresql$ alter database "app" set default_tablespace app_data;


Anda juga dapat menganalisa tabel tabel yang sangat sering dipergunakan untuk dipisah ke tablespace yang lain di harddisk yang lain. Dengan cara ini, anda dapat memastikan data anda tersebar di semua harddisk yang anda miliki.

Hari ini, saya ada keperluan untuk melakukan reinstall ulang database postgresql yang ada di mesin ubuntu maverick. Setelah berulang ulang mencoba akhirnya ketemu caranya.

Berikut langkah langkah yang dilakukan :

1. Stop database postgresql
   
   $ sudo /etc/init.d/postgresql stop

   atau

   $ sudo service postgresql stop


2. Uninstall postgresql dan sekaligus purge
   
$ sudo apt-get remove postgresql-9.1 libpq-dev postgresql-contrib-9.1 postgresql-client-9.1 postgresql-common
$ sudo apt-get purge postgresql-9.1 libpq-dev postgresql-contrib-9.1 postgresql-client-9.1 postgresql-common

3. Remove semua file postgresql
   
$ sudo rm -r /etc/postgresql/
$ sudo rm -r /etc/postgresql-common/
$ sudo rm -r /var/lib/postgresql/
$ sudo rm -r /var/log/postgresql/

4. Install ulang database postgresql
   
$ sudo apt-get install postgresql-9.1 libpq-dev postgresql-contrib-9.1 postgresql-client-9.1 postgresql-common

Dengan cara ini, maka otomatis cluster database akan di buat pada saat install dilakukan.
Silakahkan mencoba.

Pada bagian I, kita telah mempelajari bagaimana melakukan setup Stream Replication, Master dan Slave. Slave Server ini akan bekerja dalam modus read-only. Kita tidak dapat membuat tabel baru, update data atau hapus data.  Seandainya Master Server rusak karena suatu hal, maka Slave Server ini bisa kita ubah menjadi Master Server.

Berikut adalah langkah langkah mengubah Slave Server menjadi Master server, pada mesin Ubuntu Linux.

Master Server :

1. Asumsi Master Server rusak karena suatu hal.

Slave Server :

1. Stop database postgresql
   
$ sudo /etc/init.d/postgresql stop

2. Ubah file konfigurasi postgresql.conf
   
$ vim /etc/postgresql/9.0/main/postgresql.conf

3. ubah parameter yang ada menjadi seperti ini dan save filenya :
   
listen_address = '*'
hot_standby = off

4. kemudian hapus file recovery.conf di direktory data_directory. Default data_directory postgresql 9 di ubuntu adalah di direktori /var/lib/postgresql/9.0/main. :
   
$ sudo rm /var/lib/postgresql/9.0/main/recovery.conf

5. Kemudian stop database postgresql
   
$ sudo /etc/init.d/postgresql start

6. Nah sekarang database Slave Server anda sudah berubah menjadi database Master Server, anda dapat melakukan testing dengan menjalankan perintah insert, delete, update sql. Selamat Mencoba.
7. Thank you.

Wow, judul diatas sudah langsung menceritakan apa yang dimaksud dalam posting kita kali ini. Sistem operasi yang terbaru dari Apple yaitu Mac OS/X Server Lion menggunakan Postgresql sebagai database defaultnya dan menggantikan MySQL. Artikelnya dapat dibaca disini.

Untuk saat ini, Apple belum menyediakan GUI-Tool yang dipergunakan untuk memanage PostgreSQL, semuanya masih memakai console. Mungkin ini akan menyulitkan untuk rekan rekan yang tidak terbiasa bekerja dengan PostgreSQL melalui console. Tidak apa apa menurut saya, fase awal sudah cukup bagus, semoga di versi berikutnya GUI-Tools-nya sudah tersedia, mungkin PGAdmin III.

Saya belum pernah menggunakan Mac OS X, sehingga bagaimana MySQL dipergunakan dan bagaimana PostgreSQLmenggantikannya juga masih belum buat saya. Tetapi berita ini tentu mengagetkan beberapa pihak, termasuk saya. Semoga kedepannya makin banyak applikasi yang menggunakan MySQL beralih ke PostgreSQL.

Okay, artikel sebelumnya kita sudah membahas tentang bagaimana keuntungan mempergunakan tipe data BLOB dan FileSystem untuk menyimpan file Binary kita. Contoh penggunaan tipe data BLOB dengan bahasa PHP juga sudah dijelaskan didalam artikel yang lalu. Nah untuk saat ini kita akan membahas bagaimana cara membuat tabel dengan tipe data BLOB yang optimal.

Kita ambil contoh tabel profil user. Umumnya tabel profil user berisikan data pribadi user dan foto profil. Dari informasi ini, maka prakiraan tabel profil kira kira akan seperti ini :

create table profil_user

( id numeric(18,0) NOT NULL DEFAULT nextval('profile_seq'::regclass),

nama varchar(32),

alamat varchar(64),

no_telp varchar(16),

no_fax varchar(16),

foto BLOB

);

Secara umum tidak ada yang salah tabel profil user seperti diatas. Tetapi kita perlu ingat, bahwasannya tipe data BLOB ini dapat menyimpan file dalam ukuran besar hingga gigabyte. Apa yang terjadi jika kita mengirimkan perintah select * terhadap tabel ini ?

Database Administrator, terlepas dari apapun jenis database yang dipergunakan, umumnya akan membuat tablespace atau tablearea atau  spacearea dan berbagai istilah yang lainnya, untuk memisahkan data dan index. Prinsip ini sudah merupakan dasar untuk memastikan tingkat kestabilan performance database. Table akan di buat dan diletakkan di dalam tablespace DATA. Index akan dibuat dan diletakkan didalam tablespace INDEX. Akan lebih bagus lagu apabila tablespace DATA dan INDEX diletakkan didalam Harddisk yang terpisah. Mengapa demikian ? karena DATA dan INDEX memiliki cara pengaksesan yang berbeda, sehingga dengan meletakkan mereka didalam harddisk yang terpisah akan membantu Database Engine untuk bekerja lebih optimal.

Bagaimana dengan tipe data BLOB. Hampir sama dengan DATA dan INDEX, kita harus memisahkan mereka ditablespace yang terpisah dari DATA dan INDEX. Memisahkan mereka dalam tablespace yang terpisah juga sangat disarankan. Table yang berisikan BLOB sebisa mungkin memiliki sedikit tambahan atribut/kolom informasi.

Sehingga apabila prinsip diatas diterapkan di tabel profil_user, maka struktur tabelnya akan seperti ini :

create table profile_user_foto

( id numeric(18,0) NOT NULL DEFAULT nextval('profil_user_foto_seq'::regclass),

content binary,

update_date timestamp

) using tablespace BLOB;

create table profil_user

( id numeric(18,0) NOT NULL DEFAULT nextval('profil_user_seq'::regclass),

nama varchar(32),

alamat varchar(64),

no_telp varchar(16),

no_fax varchar(16),

foto_id numeric(18),

constraint foreign key profile_user_data1_fk (foto_id) references profile_user_foto (id)

) using tablespace DATA;

Ini adalah contoh dengan menggunakan database postgresql.

Jikalau anda memutuskan untuk menggunakan tipe data BLOB di database untuk menyimpan file binary anda, maka dibawah ini adalah beberapa hal yang harus anda perhatikan pada saat bekerja dengan tipe data BLOB dan PHP :

1. Submit file binary dengan HTML File :
   • Pastikan anda menggunakan parameter enctype di dalam form submit. Parameter ini dipergunakan untuk mengirimkan file binary anda.

     </pre>
     <form action="add_blob.php" method="post" enctype="multipart/form-data"><input id="userfile" type="file" name="userfile" value="" /></form>
     <pre>
     

2. Menangkap file binary
   • File Binary yang telah di submit, akan diterima oleh PHP dalam array $_FILES["userfile"]
   • Isi dari variabel $_FILES["userfile"] adalah sebagai berikut :

     $_FILES["userfile"]["tmp_name"] = File binary
     $_FILES["userfile"]["name"] = Nama file binary
     $_FILES["userfile"]["type"] = Tipe ekstensi file
     $_FILES["userfile"]["size"] = ukuran file dalam byte
     

   • Untuk mendapatkan lebar dan tinggi dari image gunakan perintah berikut ini :

     list ($file_width, $file_height, $type, $attr) = getimagesize($_FILES["userfile"]["tmp_name"]);
     

3. Untuk menyimpan file binary ke database dengan kohana, php framework.
   • Disini kita menggunakan function fopen dan filesize dari php.

     $image = ORM::factory('table_image');
     $image->file_content = fread (fopen ($_FILES["userfile"]["tmp_name"], "r"),
     filesize ($_FILES["userfile"]["tmp_name"]));
     $image->last_update = new DATE();
     $image->save();
     

4. Untuk membaca file dari BLOB dan menampilkannya ke browser terdapat 2 cara :
   • Tanpa Kompresi, file langsung dibaca dari database dan dikirim ke browser.

     $result = ORM::factory('table_image')->find($id);
     if ($result->id)
     {  header ("Content-type: ".$result->file_type);
        echo $result->file_content;
     };
     

   • Dengan Kompresi, file dibaca dari database dan di quality di turunkan ke 75%.

     $result = ORM::factory('table_image')->find($id);
     if ($result->id)
     {  header ("Content-type: ".$result->file_type);
        echo imagejpeg(imagecreatefromstring($result->file_content), "", 75);
     };
     

Demikanlah tutorial cara menggunakan tipe data BLOB dengan PHP.
Semoga bermanfaat.

BLOB adalah tipe data untuk menyimpan file binary. Image, video, music, document, semuanya bisa disimpan di dalam tipe data ini. Pertanyaan yang umum diajukan oleh para designer database adalah apakah akan menggunakan BLOB atau FileSystem untuk menyimpan suatu file binary ?, disimpan di BLOB bisa, disimpan di Filesystem juga bisa. Manakah yang terbaik ?

Jawaban saya :  Sesuaikan dengan jenis applikasi anda !.

Keuntungan File System :

• Mudah untuk di rawat.
• bisa diperlakukan seperti file pada umumnya.
• process READ sangat cepat.

Keuntungan FileSystem adalah kekurangan BLOB dan sebaliknya, keuntungan BLOB adalah kekurangan FileSystem. Sehingga dari sini kita dapat menentukan file binary akan disimpan dimana ?

Jika applikasi kita bekerja dengan banyak process READ terhadap file binary ini, maka FileSystem jawabannya. Apalagi kalau jenis applikasinya adalah Web-based dan diakses melalui Proxy-Server, maka user akan dapat menerima file binary nya yang diinginkannya dengan cepat . Flickr dan Google Picasa bisa menjadi contoh, mereka melayani banyak user yang ingin “melihat picture” yang dishare. Arsitektur Flickr dapat anda lihat di tautan berikut ini.

Jika applikasi kita tidak terlalu banyak process READ dan lebih kearah Replikasi maka BLOB adalah solusinya. File binary akan disimpan ke dalam database. Database dapat di replikasikan ke beberapa server di lain lokasi. User pun masih bisa menerima delay yang terjadi saat process READ.

Database saat ini sudah memiliki algoritma yang sangat bagus dalam menangani tipe data BLOB. Bahkan ada yang menggunakan Database Firebird untuk melakukan testing antara Firebird dan FileSystem, dan mengklaim kalau Firebird lebih cepat daripada FileSystem dalam menangani tipe data BLOB. Anda dapat membacanya blog ini dan hasil diskusinya di reddit.com. Bagaimana dengan database yang lain ?

Dalam kasus saya, kita memakai BLOB. Data file binary disimpan di dalam database. Ada beberapa pertimbangan yang mendasari keputusan ini, yaitu :

• Database harus dapat di replikasi.
• Size file binary yang di upload dibatasin max 5 MB.
• Applikasi dalam bentuk Web-Based.
• Process READ terhadap file binary dalam konteks “Download File“, artinya user harus mendownload dulu file binary tersebut.
• Process READ tidak terlalu banyak dan low traffic.

Michael Stonebraker menulis dalam artikelnya yang berjudul Facebook trapped in MySQL ‘fate worse than death’ tentang bagaimana database MySQL dipergunakan oleh FaceBook. Saya sangat salut dengan para engineernya Facebook dalam menggunakan database MySQL. Bayangkan 4000 Shard Instance database MySQL dipergunakan bersama sama dan 9000 Instance MemCache.

Wow, 4000 shard instance ? apa itu shard ? shard itu dalam arti lain adalah “horisontal partition”, artinya data yang dimiliki dipisah kedalam 4000 Instance MySQL. Nah pemisahannya ini dilakukan secara logika,developer Facebook yang menentukan data A pergi ke Instance MySQL yang mana, data B pergi ke instance MySQL yang mana. Proses pemisahaan ini sangatlah rumit, parameter yang dipergunakan mungkin juga sangat banyak dan beragam. Pekerjaan besar untuk developernya.

Bayangkan pula, 4000 mesin itu bisa terletak di belahan dunia yang lain, mungkin saja ada yang di amerika, singapura, eropa, china dan sebagainya. Tentu saja mereka memiliki cara untuk menyatukan kembali data tersebut, detailnya saya juga kurang tahu.Tapi saya yakin prosesnya juga rumit.

Di artikel tersebut juga disebutkan, menurut informasi terakhir tahun 2008, Facebook memiliki 1800 server khusus hanya untuk MySQL dan 805 server untuk MemCache. Wow, menakjubkan. Tentu saja DBAnya akan bekerja keras untuk memaintain database server dengan jumlah sebanyak itu.

Kembali ke artikel yang di tulis pak michael, dia mengatakan, dengan jumlah database MySQL yang sangat banyak dan hanya bisa melakukan “horisontal partition” untuk scalabilitasnya, maka Facebook akan mengalami kesulitan jika arsitektur seperti ini di pertahankan. Mengapa ? karena untuk melakukan ini semua itu sangat kompleks, ribet, dan memusingkan.

Itulah sebabnya, pak michael menyarankan Facebook untuk memindahkan databaseny dari MySQL ke database yang lain yang dapat melakukan scalabilitas dengan benar. Beliau menyarankan untuk berpindah ke database dengan konsep NewSQL seperti database VoltDB, NimbusDB, dan GenieDB. Konsep ini berbeda dengan konsep NoSQL yang menurut beliau masih merupakan database MySQL dalam bentuk yang lain.

Sudah beberapa hari ini pendapat dari pak michael ini mengundang perdebatan diantara para developer database. Slashdot, salah satu website rujukan para developer, ikut pula menjadi ramai. Topik ini pun berkembang menjadi ajang membahas solusi apa yang tepat untuk Facebook. Menariknya para developer ini juga menyarankan untuk memakai PostgreSQL.

Apapun solusi yang ditawarkan, bagaimana bentuk pelaksanaannya, semua developer setuju dengan pendapat pak michael, Facebook harus segera “menulis ulang” applikasinya dan menggunakan database yang baru