I have been using replication slot since its birth in PostgreSQL 9.4 version. This feature is very helpful in terms of keeping the WAL files on master server to make sure that its replica servers are in good state and (a)sync with it. But, something horrible will happen if you left them inactive.

In one of our database clusters, the design was simply like this:

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)

Sejak PostgreSQL 9.4, ada parameter baru di Postgres yaitu max_replication_slot. Apa sih ini?

Replication slot adalah mekanisme dalam Postgres yang secara otomatis akan memastikan bahwa WAL files dari host master dijaga keberadaannya sampai semua standby server menerimanya. Dengan kata lain, slot replikasi akan memastikan bahwa WAL files yang ada di host master TIDAK dihapus sampai semua server standby sudah mengambilnya. Slot replikasi ini sebenarnya bisa digantikan dengan parameter wal_keep_segments dan archive_command, hanya saja cara kerja keduanya berbeda karena slot memastikan kebutuhan WAL files terpenuhi sedangkan wal_keep_segments menggunakan parameter jumlah file. Adapun archive_command memberikan beban kerja kepada server untuk memprosesnya.

There are lots of ways to replicate PostgreSQL database including streaming replication, logical replication, trigger-based replication and so on. And sometimes you need all of them depending on you and your business needs. I can say that replication options in Postgres is somewhat limitless.

And this time, I am writing how to replicate and use Amazon RDS as the master and use another as the slaves.

Bucardo is one of the replication system for PostgreSQL where it works asynchronous and using triggers to replicate the data. And one of the cool feature is you can have multi masters as well as slaves. Since the replication is working on table level to replicate the data, we can use it to replicate partial data and of course DLL is not supported. There are many more features you can look here.

Recently, I have to setup replication from Amazon RDS on one of our busiest databases to another cloud services provider for some reasons. And the only way to do this is using Bucardo.