Advanced Logical Replication for PostgreSQL (1/3)

Intro

Open source Tungsten Replicator for MySQL enables multi-master, parallel and even heterogeneous replication, together with zero-down time schema upgrades, transaction filtering and more, but all these features are hardly available for another elephant in the room - i.e. PostgreSQL. At least, that was true until now.

These article series present my recent extension to Tungsten Replicator, which enables logical replication for PostgreSQL and, effectively, opens up all the advanced features, described above, too. But first - some background...

Background

For a few years now I have been yearning for a user friendly and flexible replication mechanism for PostgreSQL. Especially, seeing how Tungsten grew and enhanced MySQL replication month after month, PostgreSQL development in this field seemed relatively limited.

PostgreSQL 9 introduced Streaming Replication and Hot Standby support, which was a big step forward. After implementing support for these features in Tungsten, transparent connectivity and management features, which were already developed for MySQL, opened up for PostgreSQL too. Tungsten Replicator supports plugins, so the major work I've done was in Ruby scripts, which automatically manage WAL Shipping and Streaming Replication. They connected PostgreSQL to Tungsten features like transparent automatic failover, smart read load balancing, zero-dataloss master/slave switching and all others, which can work on a physical replication pair.

Nevertheless, in order to unleash the big power in DBMS clustering, you need to have logical replication.

Physical vs. Logical Replication

To make things simple:

• Logical replication - data from one DBMS is copied to another as transactions. One can actually see each transaction as SQL statements or row change structures. This is where MySQL's Statement or Row Based Replication fall in.
• Physical replication - data from one DBMS is copied to another as differences in file system or table spaces. In other words, there is not enough transactional content in these evens to reconstruct the original statement or row change structure, thus this event can only be applied into a slave DBMS which is a bit-to-bit copy of the master. This is where PostgreSQL's WAL Shipping and Streaming Replication fall in.

Effectively, you cannot do the following on a physical replication method:

1. Have a different data and/or schema on the slave. Really, one can't change data on a slave at all (eg. a standby PostgreSQL server is read-only if you use Streaming Replication).
2. Filter (transform) events on the fly. In practice, one can't even see what is actually being replicated via the physical replication...

Both of these items are essential if you want to implement multi-master, heterogenous or other features, described at the beginning of this article. So, how to implement a flexible and user friendly logical replication for PostgreSQL?

Introducing Tungsten PostgreSQL Extractor

For MySQL Tungsten Replicator extracts directly from the MySQL Binary Log files. It is possible to do that, as Binary Logs contain enough transactional information. Unfortunately, the same cannot be said about PostgreSQL XLOG files. Solution? Revert to the good old trigger based replication. Specifically, the time proven Slony.

One might say, that "Slony is complex" or "hard to administer". But, in fact, the only thing that I will use are the Slony logs. I.e. there won't be any Slony replication deamons setup/running - only the triggers on the master will be reused. Here's how the complete flow looks like:

To put it from transaction's perspective:

1. Transaction is completed on the master.
2. Slony triggers fire and save the row changes in sl_log_1 or sl_log_2 tables. This is where Slony's responsibilities end.
3. Tungsten's PostgreSQLSlonyExtractor reads from the log tables and structures the event in a DBMSEvent type. From now on generic Tungsten components do the remaining work.
4. Event passes filtering and is saved in THL (Transaction History Log).
5. Slave Tungsten Replicator extracts the event remotely, passes it through its pipeline down to the PostgreSQLApplier, which then executes this statement against the slave DBMS.

Note: as illustration of flexibility, the very same PostgreSQLApplier is used when implementing heterogeneous MySQL->PostgreSQL replication.

The new component here is the PostgreSQLSlonyExtractor, which initial implementation you may find at our code.google.com homepage. What is really nice, that the approach above stacks up inside the whole Tungsten Replicator architecture transparently and connects to all the components that are available and are being developing for MySQL.

PostgreSQLSlonyExtractor vs. Streaming Replication

As mentioned above, Tungsten can already manage Streaming Replication, which provides many features, but the ability to have each distinct transaction going through the Replicator engine, opens up a whole new dimension. To let you feel the potential, below is a possible-to-implement feature comparing matrix:

Feature	Physical Replication (Streaming Replication)	Logical Replication (PostgreSQLSlonyExtractor)
High availability	+	+
Read performance scaling	+	+
Zero-downtime upgrade	± (H/W, OS only)	+ (PostgreSQL & schema too)
Cross-version replication	-	+
Heterogenous replication	-	+
Multi-master replication	-	+
Parallel replication	-	+
Write performance scaling (sharding)	-	+

Conclusion - To Be Continued

We have briefly reviewed the fresh logical replication method in Tungsten Replicator for PostgreSQL based on Slony logs. The real details and setup example are left for your enjoyment in the second part of this article. You are more than welcome to stay tuned!