Postgresql 9 Stream Replication II

  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.

PostgreSQL menjadi Default Database di Mac OS X Lion Server

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.

  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.

Design Table BLOB

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.

   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.

Pgfouine menganalisa PostgreSQL Log

  Setelah kita mengaktifkan fasilitas log dari database Postgresql, maka di dalam direktori log akan muncul file *.log. File *.log ini jika kita dapat kita baca dengan menggunakan text editor, isinya bisa bermacam macam bergantung setting parameter yang kita lakukan pada file postgresql.conf. Untuk DBA yang sudah berpengalaman, mereka dapat membaca isi file log ini dengan cepat untuk mendapatkan gambaran mengenai isi file log tersebut. Tetapi apabila file log tersebut sudah berukuran cukup besar, tentu saja mereka akan kesulitan untuk membaca dan memahami file log tersebut.

Umumnya logging di PostgreSQL tidak dilakukan dalam waktu yang lama. Waktu selama 3 jam sudah cukup lama untuk ukuran database yang aktif. Karena proses logging ini dapat membuat sistem database menjadi lambat, sehingga mengaktifkan fasilitas logging harus dilakukan dengan hati hati dan dengan penuh pertimbangan.

Komunitas Postgresql telah menyediakan suatu tools untuk menganalisa file log yang dihasilkan, namanya pgfouine. Tools ini akan membantu menganalisa isi file log yang telah dihasilkan. Tools ini dibuat melalui bahasa pemrograman php, sehingga anda perlu menginstall php sebelum menggunakan file ini.

Berikut adalah step untuk melakukan analisa file log postgresql di sistem saya, Ubuntu 11.04 :

    1. aktifkan fasilitas log di postgresql
    2. edit file postgresql.conf

      $ sudo vim /etc/postgresql/postgresql.conf

      Dan ubah parameter parameter berikut ini :

      1. log_destination = 'stderr'
      2. silent_mode = on
      3. log_min_duration = 0
      4. log_duration = on
      5. log_statement = 'all'
      6. log_line_prefix = '%t [%p]: [%l-1] '
      7. lc_messages = 'en_GB.utf8'
    3. restart database postgresql

$ sudo /etc/init.d/postgresql restart

    1. download tools pgfouine dari website pgfouine, ambil file yang tarball (*tar.gz)
    2. extract pgfouine di direktory anda, dan berpindahlah ke direktory tersebut


1. $ tar -xfs pgfouine.tar.gz
2. $ cd pgfouine

  1. jalankan pgfouine

    sudo ./pg_fouine.php -logtype stderr -file /var/log/postgresql/pgsql.log > report.html
  2. buka file report.html melalui browser anda

Didalam file report tersebut, anda bisa melihat query mana saja yang lambat, query mana saja yang dieksekusi berulang ulang dan seterusnya. Untuk query yang lambat tersebut anda harus mencari solusinya dengan cara melakukan tuning. Umumnya solusi untuk tuning 1 query akan berbeda untuk query yang lainnya. Kita akan mempelajari tuningnya dilain kesempatan.

Thank you.

Aktivasi PostgreSQL Log

  Setelah sekian lama menggunakan postgresql, saya masih penasaran dengan bagaimana cara postgresql melakukan log terhadap semua sql statement yang di jalankannya. Akhirnya saya coba untuk mengaktifkan feature loggind di database postgresql 9 di local komputer saya untuk memastikan apakah semua applikasi yang saya pakai memakai prepared statement.

Berikut adalah langkah langkah setting postgresql.conf untuk menaktifkan log :

  • Edit file postgresql.conf, di ubuntu saya lokasinya seperti ini :

    sudo vim /etc/postgresql/9.0/main/postgresql.conf
  • Setelah itu ubah nilai parameter parameter berikut :

    log_destination = 'stderr'
    logging_collection = 'on'
    log_directory = '/var/log/postgresql'
    log_filename = 'pgsql-%Y-%m-%d_%H%M%S.log'
    log_connections = on
    log_disconnections = on
    log_error_verbositu = verbose
    log_statement = on
  • setelah itu restart database postgresql kita:

    sudo /etc/init.d/postgresql restart
  • perhatikan direktory /var/log/postgresql, akan muncul 1 file baru dengan awalan pgsql-XXX.log, nah file inilah yang akan kita pergunakan untuk melihat isi file log yang di hasilkan oleh postgres, :

    sudo tail -f /var/log/postgresql/pgsql-XXX.log
  • Kemudian jalankan applikasi kita yang menggunakan database, dan amati perubahan yang terjadi di dalam file log tersebut. Berikut adalah hal-hal yang biasanya saya amati didalam file log :
    • error message
    • warning message
    • prepare statement
    • client connection / disconnection
  • Thank you

Postgresql 9 Stream Replication

  Stream Replication adalah feature terbaru dari Postgresql 9. Stream Replication termasuk dalam kategory Standby Database, yang artinya database replikasi ini berada dalam mode standby, read-only, tidak dipergunakan untuk transaksi.

Stream Replication di postgresql melakukan sinkronisasi data background prosess yang sebut walsender dan walreceiver. Background process inilah yang bertanggung jawab mengirimkan perubahan data melalui network port.
Perubahan yang terjadi di master server akan dengan cepat diimplementasikan di slave server.

Jadi, transaksi database tetap dilakukan di server master, seperti insert, delete, update dan seterusnya. Server
slave dipergunakan untuk transaksi read-only, seperti query, export data dan seterusnya. Apabila terjadi suatu hal terhadap server master, misalnya terjadi kerusakan sistem, maka server slave bisa disetting untuk menggantikan master server.

Persiapan :

  1. Siapkan 2 server linux ubuntu. Saya menggunakan IP address 192.168.0.1 untuk master dan 192.168.0.2 untuk slave servernya. Stream Replication hanya bisa berjalan apabila versi postgresql sama dan menggunakan operating system dengan versi yang sama pula.
  2. Matikan postgresql yang sedang berjalan, baik di master maupun di slave, agar tidak ada user yang melakukan transaksi ke database :

    $ sudo /etc/init.d/postgresql stop
  3. pastikan versi linux anda berjalan pada arsitektur yang sama, 32 bit atau 64 bit :

    $ sudo uname -m

    jika yang keluar adalah x86_64 berarti 64 bit, jika yang keluar adalah i386 / i686 berarti 32 bit.

Master Server :

  1. buat direktori archive di dalam data_directory untuk menampung file wal. Default data_directory postgresql 9 di ubuntu adalah di direktori /var/lib/postgresql/9.0/main.

    $ sudo mkdir /var/lib/postgresl/9.0/main/archive
    $ sudo chown postgres:postgres /var/lib/postgresl/9.0/main/archive
     
  2. buka file postgresql.conf yang menyimpan informasi parameter inisialisasi :

    $ sudo nano /etc/postgresql/9.0/main/postgresql.conf
     
  3. ubah parameter yang ada menjadi seperti ini dan save filenya :
                     listen_address = '*'
                     wal_level = hot_standby
                     checkpoint_segment = 16                 
                     archive_mode = on
                     archive_command = 'cp -i %p /var/lib/postgresql/9.0/main/archive/%f <dev/null'
                     max_wal_senders = 3
                     wal_keep_segments = 32
    
                
  4. buka file pg_hba.conf, yang menyimpan konfigurasi koneksi ke database :

    $ sudo nano /etc/postgresql/9.0/main/pg_hba.conf
     
  5. tambahkan baris konfigurasi seperti ini, untuk memastikan agar slave dapat terkoneksi ke master server, dan save filenya :

    host replication all 192.168.0.2/32 trust 

Slave Server :

  1. buka file postgresql.conf yang menyimpan informasi parameter inisialisasi :

    $ sudo nano /etc/postgresql/9.0/main/postgresql.conf
     
  2. ubah parameter yang ada menjadi seperti ini dan save filenya :

    listen_address = '*'
    hot_standby = on
     
  3. buat file recovery.conf di direktory data_directory. Default data_directory postgresql 9 di ubuntu adalah di direktori /var/lib/postgresql/9.0/main. :

    $ sudo nano /var/lib/postgresql/9.0/main/recovery.conf
     
  4. tambahkan parameter berikut ini dan save filenya:

    standby_mode = 'on'
    primary_conninfo = 'host=192.168.0.1 port=5432 user=postgres password=postgres'
    restore_command = 'cp /path/for/backups/%f %p'
     

Sinkronisasi :

  1. Setelah master dan slave server selesai di konfigurasi, maka langkah berikutnya adalah melakukan sinkronisasi data. Lakukan step step berikut ini dengan cepat.
  2. Start database postgresql di MASTER SERVER :

    $ sudo /etc/init.d/postgresql start
     
  3. Login ke postgresql di MASTER SERVER :

    $ sudo psql -U postgres
     
  4. Jalankan perintah backup di MASTER SERVER :

    postgres# select pg_start_backup('clone', true);
     
  5. copy file database di MASTER SERVER ke SLAVE SERVER :

    postgres# \! rsync -av --exclude pg_xlog --exclude postgresql.conf --exclude postgresql.pid /var/lib/postgresql/9.0/main/* 192.168.0.2:/var/lib/postgresql/9.0/main/
     
  6. stop backup database di MASTER SERVER :

    postgres# select pg_stop_backup();
     
  7. copy direktory pg_xlog di MASTER SERVER ke SLAVE SERVER:

    postgres# \! rsync -av /var/lib/postgresql/9.0/main/pg_xlog 192.168.0.2:/var/lib/postgresql/9.0/main/
     
  8. Start database postgresql di SLAVE SERVER :

    $ sudo /etc/init.d/postgresql start
     
  9. Nah sekarang anda dapat melakukan testing melalui master server, buatlah tabel kosong dan lakukan semua perintah insert, delete, update ke dalam tabel tersebut, dan insert kan beberapa record kedalamnya, dan lihat hasilnya di slave server.

Thank you.

Install postgresql 9 di Ubuntu 10.10

  Postgresql telah melepas postgresql 9. Saatnya kita mencoba kemampuannya. Berikut cara instalasi postgresql di ubuntu 10.10.

Tutorial :

  1. Kita perlu menambahkan repository postgres 9.0, maka edit file source.list

    $ sudo vim /etc/apt/source.list
  2. Tambahkan baris perintah berikut, dan kemudian simpan :

    deb http://ppa.launchpad.net/pitti/postgresql/ubuntu maverick main
    deb-src http://ppa.launchpad.net/pitti/postgresql/ubuntu maverick main
  3. Kemudian update repository anda :

    $ sudo apt-get update
  4. Kemudian install postgres database :

    $ sudo apt-get install postgresql-9.0 libpq-dev postgresql-contrib-9.0
  5. Done.