Available in English and Indonesian versions.

Up until this moment, PostgreSQL version 9.x, the only way to have limited replication, as in tables and its data and not the whole database cluster, is to use dump and restore option or the trigger. And if you wanto to use the trigger option, the only tools with this capability are Bucardo and Skytool’s Londiste with their own good and bad side. You can easily set trigger-based replication up and the replicated data you have is mostly nearly realtime unless you forgot to run the daemon. On the other hand, dump and restore options doesn’t give you the ability to have near realtime replication but the limitation gives you more power over the trigger-based one. (Depending on how you look at it)

But, the good news is here. Because this problem with having limited replication is now (almost) gone because of the awesome work of Postgres developer with something called LOGICAL REPLICATION.

The logical replication is an ability to replicate limited tables and data to another Postgres cluster without third-party tools. This near-realtime replication is using WAL files which also used by streaming replication so the delay should be better compared to the third-party tools. With this method, one master (called provider) can have multiple replicas (subscribers) or the other way around. And not just that, but this feature also gives you the option to replicate on row or column level.

Logical replication is using pub-sub (publish and subscribe) method where ths subscriber is actively looking for changes and ask them from the master instead of the master sending them. And if the subscriber is also acting as a provider, it can give these changes to the other subscriber below is anyone is asked for them.

From the above explanation, we can see that logical replication have values in many cases:

  • upgrading Postgres versions without downtime
  • create a central analytic database cluster from multiple clusters
  • splitting one database from a cluster to a new ones
  • and many more

Indonesian version

Kondisi saat ini, sampai PostgreSQL 9.x, untuk replikasi database dengan batasan tertentu alias tidak replikasi data secara keseluruhan pada cluster database dapat dilakukan dengan dua cara yaitu dump-restore, dan replikasi dengan memanfaatkan trigger. Jika menggunakan trigger, maka pilihan alatnya adalah Londiste dari Skytools atau Bucardo yang masing-masing memiliki nilai plus dan minus. Dengan memanfaatkan trigger ini replikasi cukup bisa diandalkan karena nyaris realtime. Sementara memanfaatkan dump-restore tentu saja tidak dapat dilakukan secara realtime akan tetapi masing-masing host memiliki sedikit ketergantungan. Intinya adalah, apa yang sesuai untuk kebutuhan saja.

Namun, berkat kerja keras developer Postgres, urusan replikasi dengan batasan tidak lagi menyusahkan berkat kehadiran LOGICAL REPLICATION.

Dengan logical replication, replikasi tidak lagi harus dilakukan secara menyeluruh pada cluster, melainkan dapat dibatasi pada tabel-tabel tertentu saja. Dan ini berjalan secara realtime layaknya streaming replication. Dengan demikian satu provider (master) dapat mengirimkan replikasi tabel-tabel pada banyak subscriber (slave) atau sebaliknya. Selain itu dengan logical replication kita bisa melakukan replikasi pada level row atau kolom tertentu.

Logical replication memanfaatkan metode publish dan subscribe dengan cara kerja yaitu subscriber akan menarik data dari provider(-provider) yang di daftarnya. Dan jika diperlukan subscriber akan mem-publish data yang diterimanya untuk dapat diambil oleh subscriber lain di bawahnya (cascaded).

Dari penjelasan di atas, dapat ditarik kesimpulan bahwa logical replication bisa berguna pada banyak kasus contohnya: