Migrasi data paradox ke postgresql

  Hari ini kita memerlukan melakukan migrasi data dari program kita yang lama, yang memakai database paradox untuk di migrasi ke postgresql.

setelah mencari kesana kemari dan tidak menemukan tools yang bisa langsung mentransfer dari paradox ke postgresql, akhirnya kita menemukan tools yang gratis, yang merubah paradox ke excel. Nama toolsnya adalah Paradox DBase Reader buatan smartmox.

http://www.sportamok.com/development/delphi/8-paradox-dbase-reader

cukup lah jika bisa di transfer ke excel, karena dari excel kita bisa mentrasfernya ke postgresql melalui libreoffice.

tools ini gratis dan free

Extract table dari file Full Backup

  Hi semua, jumpa lagi. Sudah lama rasanya tidak update blog ini lagi. Kali ini saya akan berbagi pengalaman saya beberapa hari terakhir ini. Salah seorang rekan team di tempat saya, sering kali meminta untuk di restore-kan data dari tabel tertentu yang telah di backup hari sebelumnya. Problemnya adalah, file backup saya adalah full-backup dalam bentuk file *.sql.

Step untuk restore tersebut adalah sebagai berikut :

  • Siapkan file backup yang akan di restore salah satu tabelnya
  • Buka file tersebut dengan text editor, misalnya notepad, wordpad, gedit, vi dan lain lain
  • Cari awal dari tabel yang akan di restore, bisa dengan search nama tabel
  • Copy blok data insert yang paling awal dari tabel tersebut, hingga yang paling akhir
  • dan paste di file text yang baru
  • Simpan file test tersebut dalam format *.sql.
  • Eksekusi / restore file tersebut di database tujuan.

Nah ternyata step tersebut menjadi rumit untuk saya laksanakan, mengapa?
Karena file full backup yang saya miliki ukurannya sangat besar , 3 GB. Ini akan mengakibatkan text editor yang saya pergunakan tidak mampu untuk membaca file tersebut. Saya mempergunakan gedit, dan programnya tidak kuat, hang.

Akhirnya saya harus mencari fungsi file text di linux untuk melakukan pemotongan data itu. Berikut adalah step yang saya lakukan :

  • Siapkan file backup yang akan di restore salah satu tabelnya
  • Saya masuk ke console linux.
  • Saya pakai fungsi fgrep di linux untuk mencari awal tabel yang akan saya copy.
    • $> fgrep -n “nama_tabel” nama_file_backup.sql | more
  • Jika sudah muncul datanya, saya cari baris yang paling awal menampilkan perintah insert terhadap tabel yang saya cari. Saya catat nomor baris nya.
  • Kemudian, saya cari baris yang paling akhir menampilkan perintah insert terhadap tabel yang saya cari. Saya catat nomor baris nya.
  • Jika sudah mencatat baris awal dan akhirnya, sekarang saatnya kita copy data dari awal baris itu ke akhir barisnya, ke file yang lain. Saya pakai perintah sed.
    • sed -n ‘81484141,81498477’p nama_file_backup.sql > file_restore.sql
  • Eksekusi / restore file tersebut di database tujuan.

Nah selesai sudah, dengan ini, berapapun ukuran file backup yang saya miliki, saya tetap bisa melakukan restore. Bagaimana dengan rekan rekan yang lain ? apakah ada alternatif yang lainnya ?

Memindahkan MS-Access Database ke PostgreSQL

  Beberapa waktu yang lalu, saya memiliki kebutuhan untuk mentransfer database MS-Access ke dalam PostgreSQL. Sempat terpikir untuk melakukan transfer data secara manual dengan penggunakan pentaho, tetapi karena jumlah tabel yang cukup banyak dan waktu yang sangat singkat, sehingga saya memutuskan untuk mencari software gratisan untuk melakukan proses transfer tersebut. Akhirnya setelah mencari melalui GOOGLE, saya menemukan software yang dimaksud, yaitu “Access To PostgreSQL” buatan dari BullZip. Software gratis inilah yang mengkonversikan database MS-Access saya ke PostgreSQL.

  Berikut langkah langkah saya dalam menggunakan software ini :

  • Download dan Install terlebih dahulu PostgreSQL ODBC di sini. Software ini di test dengan odbc versi 8.4.
  • Buat Database Target di dalam PostgreSQL.
  • Jalankan Program “Access To PostgreSQL” dan pilih ms-access file sebagai database sumber dan masukkan database postgresql yang baru sebagai database tujuan.
  • Apabila anda memiliki tabel dengan ukuran yang besar, misal 500 ribu record, maka upload tabel tersebut secara terpisah, untuk menghindari kegagalan.

Software ini sangat bagus, dan saya rekomendasikan untuk teman teman yang ingin melakukan konversi. Silahkan mencoba.

Desain Table untuk replikasi

  Hi, sudah lama blog ini tidak saya update, tentu saja alasan paling utama adalah kesibukan. Klise sekali ya, jadi ingat para blogger yang lain, selalu memakai alasan yang sama, sibuk.

Baiklah, topik kita kali ini adalah desain tabel yang akan di pergunakan untuk replikasi. Replikasi data ditempat kita adalah Master to Master, artinya setiap lokasi dapat melakukan update data dan masing masing lokasi akan melakukan sinkronisasi dengan lokasi lainnya. Desain tabel ini berdasarkan pengalaman yang kita miliki selama ini, yaitu :

  • Primary Key
    • Setiap record yang tercatat harus memiliki primary key, yang dapat dipergunakan untuk identifikasi unik record tersebut. Tingkat ke-unik-annya harus mempertimbangkan kode lokasi dan kode perusahaan.
    • Dengan pertimbangan di atas, pada saat replikasi, kita yakin bahwasannya data setiap record itu adalah unik, tidak akan mungkin tercampur / bertabrakan dengan data dari lokasi lainnya.
    • Contoh primary key nya :
      • A1   : A untuk kode lokasi, 1 untuk no urut data
      • XA1 : X untuk kode perusahaan, A untuk kode lokasi, 1 untuk no urut data.
  • Tanggal Pembuatan
    • Kita harus mencatat juga kapan record tersebut di catat di dalam tabel. Umumnya kita kombinasikan dengan constraint DEFAULT.
    • Contoh untuk Postgresql dan Oracle :
      • created_date timestamp default NOW()
      • created_by numeric(8)
  • Tanggal Perubahan
    • Kita juga harus mencatat kapan record tersebut berubah/diubah di dalam tabel. Pencatatannya dilakukan pada saat kita menjalankan perintah UPDATE atau REPLACE.
    • Contoh untuk Postgresql dan Oracle :
      • modified_date timestamp
      • modified_by numeric(8)

ID yang unik adalah suatu keharusan, sementara untuk tanggal Pembuatan dan perubahan sebagai data pendukung terhadap status record tersebut. Tentusaja data pendukungnya dapat bertambah sesuai kebutuhan, tetapi kolom kolom yang diatas itu adalah keharusan di tempat kita. Jadi standard DDL untuk tabel yang akan di replikasi seperti berikut :

<!-- Center Pane -->
Create Table Master_tbl
( id varchar(16),
  ...
  ...
  created_date timestamp default NOW(),
  created_by numeric(8),
  modified_date timestamp,
  modified_by numeric(8),
  contraint master_tbl_pk primary key (id)
);

Bagaimana dengan teman teman ?

Nulls hanya untuk tipe data berbasis character dan timestamp

  Bruse momjian adalah salah satu orang penting di dunia PostgreSQL, salah satu pendiri The PostgreSQL Global Development Group, dan dia sudah bekerja dengan PostgreSQL sejak tahun 1996. Beliau juga menulis buku yang berjudul “PostgreSQL: Introduction and Concepts“.

Menurut beliau, nulls adalah nothing atau pointer yang tidak menunjuk kemana mana. Untuk itipe data character, nulls dapat ditulis dengan spasis kosong atau zero length, sedangkan untuk numeric dan timestamp, nulls harus di tulis dengan apa ? 0, -1 atau yang lain ? timestamp dengan 01/01/01 ?

Berikut adalah file presentasi beliau mengenai Nulls, silahkan diambil disini. Presentasi ini telah di presentasikan dalam ajang pgCon 2013. Bruce juga menjelaskan apa, mengapa dan bagaimana pemakaian nulls yang benar. Benar benar penjelasan yang menarik untuk disimak. Sayangnya tidak ada penjelasan mengenai performance database apabila kita menggunakan nulls, jadi kita tidak tahu apa efek dari pemakaian nulls terhadap performance database kita, lebih cepat atau lebih lambat.

Berdasarkan presentasi tersebut, saya dapat mengambil kesimpulan bahwa nulls sebaiknya hanya dipergunakan untuk tipe data yang berbasis character, semisal tipe data character, text, atau variable character. Untuk tipe data berbasis numeric dan timestamp lebih baik menghindari nulls.

Untuk tipe data numeric, saya memiliki trik untuk menghindari nulls yakni dengan selalu menambahkan integrity NOT NULL DEFAULT 0. Jadi jika saya melakukan insert terhadap tabel tersebut dan tidak mencantumkan suatu value, maka secara otomatis database akan menambahkan angka 0.

Sementara untuk tipe data berbasis timestamp, saya memiliki kesulitan, karena dalam beberapa kasus kolom tersebut tidak dapat dikenakan integrity NOT NULL, sehingga nulls tetap diperkanankan. Integrity DEFAULT juga tidak dapat dikenakan karena saya tidak dapat menentukan tanggal berapa yang di pergunakan sebagai nilai default. Contoh, setiap tabel saya memiliki kolom MODIFIED_DATE, dan kolom ini hanya berisikan data pada saat terjadi perintah UPDATE. Sehingga pada saat dia berisikan nulls,saya dapat mengetahui bahwa memang belum pernah terjadi perintah UPDATE pada baris data tersebut. Seandainya saya menentukan default tanggalnya adalah 17/08/1945 dan menerapkan integrity NOT NULL DEFAULT TO_DATE(’17/08/1945′,’DD/MM/YYY’), maka akan muncul permasalahan pada penampilan data, karena secara default nilai kolom tersebut adalah ’17/08/1945′ padahal saya ingin melihat kolom tersebut dengan kosong untuk menyatakan tidak ada proses UPDATE.

Jadi, untuk saat ini, nulls akan saya terapkan untuk tipe data berbasis charcter dan timestamp. Bagaimana dengan teman teman ?

Replikasi melambat setelah tabel reorganize dengan CTAS

  Teman teman pasti tahu kalau saya memakai Bucardo untuk replikasi. Nah kejadian terakhir yang saya alami dengan bucardo berhubungan dengan tabel reorganisasi. Begini ceritanya.

Saya memiliki 3 server yang saya pergunakan untuk master to master replication dan hanya salah satu server saja yang di pergunakan sebagai server transaksi utama, lainnya untuk keperluan backup dan baca data. Nah saat saya melakukan analisa terhadap server untuk keperluan backup dan data ini, saya menemukan beberapa tabel yang ukurannya 3x lebih besar dari asalnya di server transaksi utama. Saya cek apakah mungkin tabelnya ter-fragmentasi atau tidak. Ternyata benar ada fragmentasi disana.

Akhirnya tabel yang ter-fragmentasi itu saya betulkan dengan metode CTAS. Berhasil. Salah satu tabel tersebut setiap malamnya melakukan update data sekitar 150k records. Jadi kalau dengan bucardo akan menjadi 300K records. Saya berkeyakinan bahwa semuanya akan lancar.

Esoknya, hari ke 1, sewaktu saya memonitor ZABBIX, ini adalah tools di linux untuk melihat kondisi server, saya mendapati bahwa dua server dimana saya melakukan CTAS kemarin cpu-load-nya sangat tinggi. Itu selesai setelah 6 Jam process, baru load nya turun drastis. Langsung saja saya curiga, mungkin itu terjadi karena tabel yang kemarin saya CTAS. Kemudian saya coba matikan bucardo-nya dan berharap esok hari zabbix akan memberikan laporan yang lebih baik.

Esoknya, hari ke 2, pagi pagi langsung saya lihat kondisi Zabbix, ternyata benar, load selama 12 jam terakhir rendah sekali. Jadi saya berkesimpulan bahwa load yang tinggi kemarin itu karena Bucardonya. Saya remove tabel tabel yang kemarin saya CTAS dari daftar tabel yang di replikasi di bucardo. Kemudian bucardo saya start lagi untuk menyelesaikan semua data data yang belum di replikasi. Dalam 3 jam ke depan Zabbix menunjukan kalau 2 server tersebut sibuk, karena menyelesaikan transaksi bucardo. Setelah itu rendah kembali, normal seperti sedia kala.,

Nah, saya browsing mencari jawaban mengenai hal ini, tetapi masih belum juga ketemu jawabannya. Mengapa setelah tabel tersebut di CTAS, process bucardo di  tabel tersebut jadi lebih lambat. Apakah ada teman teman yang memiliki pengalaman seperti ini ? mohon info nya.

Bucardo : Perintah Truncate Table Ter-Replikasi

  Hari ini saya mendapatkan pelajaran berharga dari bucardo. Saya secara tidak sengaja, maksudnya aktivitas yang saya kerjakan akan ter-replikasi ke server yang lain.

Jadi ceritanya, saya akan mendaftarkan tabel baru ke dalam replikasi bucardo. Tabel ini tabel aktif, dan transaksi masih sedang berlangsung. Rencana saya, setelah saya daftarkan tabel tersebut di bucardo, saya akan sinkronisasi datanya terlebih dahulu, baru kemudian mengaktifkan bucardonya. Sebelum melakukan sinkronisasi saya harus memastikan tabel di server yang lain kosong, saya pakai perintah truncate, dan ini adalah kesalahan saya, karena ternyata perintah truncate termasuk perintah SQL yang akan ter-replikasi juga.

Berikut kronologis  nya :

  • Server A: Tambahkan tabel yang baru ke dalam replikasi bucardo
  • Server A: Validate sync-nya
  • Server B: kosongkan tabel yang baru di server B pakai perintah truncate
  • Server A: copy data dari server A ke server B
  • Server A: start bucardo

Nah, begitu bucardo start, terjadilah replikasi dari server B ke server A, perintah truncate tersebut, sehingga data di server A terhapus juga. Berikut ini adalah urutan yang benar  :

  • Server B: kosongkan tabel yang baru di server B pakai perintah truncate
  • Server A: copy data dari server A ke server B
  • Server A: Tambahkan tabel yang baru ke dalam replikasi bucardo
  • Server A: Validate sync-nya
  • Server A: start bucardo

Dijamin, truncate tidak akan ikut ter-replikasi.

Bucardo : Mengatur object dalam sync

  Sekarang  bucardo sudah berjalan dengan lancar. Kadang kala kita perlu mengatur object postgresql di dalam bucardo sync. Object ini adalah table dan sequence. Kedua object ini dapat kita tambahkan atau kita hapus.

Contoh yang saya miliki adalah sebagai berikut :

  • Table A, ingin saya replikasi dari db1 ke db2, tetapi tidak untuk db3.
  • Sequence B, ingin saya replikasi dari db1 ke db3, tetapi tidak untuk db2.

Berikut langkah langkah untuk menambah suatu object ke dalam sync :

  • stop bucardo
  • bucardo add table schema.nama_table herd=my_herd
  • bucardo add sequence schema.nama_sequence herd=my_herd
  • bucardo validate sync my_sync
  • start bucardo

Dan untuk menghapus, langkah langkah nya sebagai berikut :

  • stop bucardo
  • bucardo remove table schema.nama_table
  • bucardo remove sequence schema.nama_sequence
  • bucardo validate sync my_sync
  • start bucardo

Jika setelah menghapus suatu object, kita menemukan pesan errod di dalam file log, seperti ini :

“(23143) [Mon Dec 3 13:18:24 2012] VAC Warning! VAC was killed at line 6486: DBD::Pg::st pg_result failed: ERROR: relation “bucardo.delta_265672″ does not exist”

artinya ada object postgresql yang secara fisik sudah tidak ada di postgresql tetapi masih terdaftar di dalam sistem bucardo. Mengapa ini dapat terjadi ? bucardo yang kita pakai bukan versi stabil, masih versi beta atau alpha. Tenang saja, kita bisa memperbaikinya kan.

Untuk membetulkannya:

  • bucardo stop
  • login ke db1
  • cek melalui perintah
    • select * from pg_class where oid = 265672
  • jika tidak ada hasilnya, berarti object memang tidak ada.
  • delete from bucardo.bucardo_delta_targets where tablename = 265672
  • if no result…. ulang step ke db yang lainnya  ..dan seterusnya
  • bucardo start

Bagaimana pengalaman anda dengan bucardo ? mohon infonya.

Bucardo Server Down

  Yup betul. Pagi ini saya mendapati server bucardo saya mati. Hardware error. Saya coba restart server, selalu terhenti dengan pesan kesalahan ada pada perangkat keras. Sedikit panik sih, tetapi harus tetap tenang.  Saya hitung pilihan yang saya punya.

  • 1 Mesin DB slave
  • Tidak ada backup database bucardo
  • Server DB Master dan Server DB Slave masih berfungsi.

Pilihan yang ada adalah install bucardo di mesin DB Slave, dan register ulang semua tabel yang akan di replikasi. Seharusnya proses registrasi ulang ini dapat di hindari, tetapi karena saya tidak punya backup database bucardo, jadi harus registrasi ulang. Kalau ini berjalan, saya harus ikutkan database bucardo dalam skema backup saya.

Ada sedikit kekhawatiran dalam hati, jangan jangan bucardo nya tidak mau replikasi karena saya install database bucardo yang baru. Dalam hati berharap semoga bucardo nya tetap dapat melakukan replikasi.

So, setelah install ulang bucardo di mesin DB Slave, registrasi ulang, maka sekarang saatnya untuk start bucardo. Sambil berharap harap cemas, saya start bucardo dan tail -f log.bucardo.

Ternyata berjalan normal, tanpa kurang suatu apapun. L E G A.

Bagaimana dengan teman teman, ada memiliki pengalaman seperti ini ?

5 Tips PostgreSQL dari Instagram

  Teman teman pasti tahu tentang instagram.  Instagram adalah website social networking dan berbagi photo secara online. User dapat meng-upload foto dan membagikannya ke teman teman yang lainnya. Foto yang di upload, dapat juga di modifikasi dengan penggunaan filter. Banyak sekali filter yang dimilikinya. Instagram sangat populer, mereka mengklaim sudah mendapatkan 100 juta pengguna pada february 2013.

Instagram menggunakan database PostgreSQL sebagai database backend mereka dan framework Django sebagai framework applikasi mereka. Instagram juga memiliki beberapa server postgresql yang bekerja secara shared. Saya masih tidak paham apakah mereka menggunakan clustering atau replication, selengkapnya dapat teman teman baca disini.

Nah, team engineering instagram membagikan 5 tips Postgresql yang sangat membantu database mereka :

  • Partial Indexes
    • Partial index ini adalah index yang dibuat berdasarkan karakteristik query yang kita buat. Dan dalam proses pencariannya memakan waktu yang lama. Karakteristiknya juga selalu tetap dan tidak pernah berubah.
    • Contoh karakteristik query selalu memakai where item_name ilike ‘software%’.
    • Maka partial indexnya adalah … Create Index idx1 on mytable where item_name ilike ‘software%’;
    • Saya sendiri belum pernah memakai partial index ini, karena karakteristik query saya lebih banyak memakai ‘%software%’ dibandingkan dengan ‘software%’; Jadi walaupun saya pakai partial index untuk kolom tersebut, tetap saja tidak akan terpakai indexnya, mengapa ? karena saya memakai  ilike ‘%…%’, yang mana akan melakukan pencarian sequential dari awal tabel hingga akhir tabel.
  • Functional Indexes
    • Index yang dibuat dengan menggunakan fungsi, contohnya substr, lower, upper, concatenate dan seterusnya.
    • Karakter query yang di pergunakan juga bersifat tetap dan tidak pernah berubah.
    • Contoh karakter query yang selalu memakai where … substr(item_name, 0, 8 ) = ‘software’
    • Maka functional indexnya adalah … Create index idx1 on mytable (substr(item_name,0,8))
    • Saya sendiri jarang memakai functional index, saya pernah pakai untuk concatenate, penggabungan dua kolom.
  • pg_reorg For Compaction
    • Seperti yang pernah saya tulis, database postgresql juga pasti akan mengalami fragmentasi suatu saat nanti, demikian juga database postgresql yang dipakai di instagram.
    • Team instagram memakai pg_reorg untuk melakukan defrag.
    • Saya sendiri masih memakai cara manual, tetapi prinsipnya sama dengan yang dipakai pg_reorg, yakni dengan metode CTAS.
  • WAL-E for WAL archiving and backups
    • WAL-E adalah program untuk membackup file postgresql WAL archive file yang dapat melakukan archive secara terus menerus.
    • Saya sendiri belum pernah menggunakannya. Karena karakteristik backup saya beda.
  • Autocommit mode and async mode in psycopg2
    • Tips ini dipergunakan jika kita menggunakan psycopg2.
    • Saya tidak memakai psycopg2, jadi belum bisa berkomentar.

Berdasarkan tips tips tersebut, team instagram mampu membuat PostgreSQL mereka melaju dengan sangat cepat. Secepat apa ? terus terang saya juga tidak tahu. Yang pasti, tips seperti partial index dan functional index itu sangat membantu sekali, walaupun saya belum pernah menggunakannya. Tetapi dari simulasi yang saya lakukan, hasilnya memang menggembirakan.

Bagaimana pendapat teman teman ?