Replication Renaming Schemas, Tables and Columns

Schema definitions on replication slaves sometimes differ. Ability to rename schemas, tables and columns and still setup replication with comfort is useful for MySQL to MySQL replication, while for heterogeneous topologies like these, it's a must. In this article I'll present a component which I recently developed to enable an easy way of renaming database objects during both (a) initial slave load (provisioning) and (b) real-time replication.

It is called RenameFilter and is part of the latest Tungsten Replicator build. RenameFilter works with all DBMS types that Replicator supports.

Specifying What to Rename

First, let's look at how rename definitions file looks like. It is a CSV file which first three columns specify what to rename, while last three - what into.

# CSV format:
#
# originalSchema,originalTable,originalColumn,newSchema,newTable,newColumn 

There are two special characters:

* (asterisk) - match all objects (schemas, tables or columns);
               used in the search part (first three columns).
- (dash)     - leave original object name;
               used in the rename part (last three columns).

Let's look into one example in detail:

# Rename schema from schemac to schemac1.
#
#    Match all tables
#       |         Leave table names intact   
#       |            |
schemac,*,*,schemac1,-,-
#         |            |
#         |         Leave column names intact
#      Match all columns 

It is quite flexible: it's possible to rename from one specific column in a specific table and schema all the way up to renaming all occurrences anywhere in the database.

Rename during Provisioning

First use case for this is during initial provisioning when you are preparing your slave database.

a.) For homogeneous MySQL to MySQL replication, it's a manual, though straightforward, process. After restoring a backup to the slave, we go inside the database and make needed changes (like rename schema, some tables and columns). While doing this, we log everything to the tables_to_rename.csv in format introduced above. We will then use this file while setting up replication (see one section below).

b.) For heterogeneous topologies, we'll use ddlscan tool. Let's consider a MySQL to Oracle replication topology. Tables are in MySQL master, but Oracle is completely empty without even a schema. We'll need:

• MySQL DBMS with some tables on the master.
• Empty Oracle DBMS on the slave.
• Tungsten Replicator with ddl-mysql-to-oracle.vm template available for ./ddlscan utility (translates MySQL schema dialect into Oracle dialect).
• CSV file with rename defitinions.

In the CSV we define all MySQL tables and columns that exceed 30 characters and how to rename (shorten) them, tables and columns which start with a number and how to rename these, etc. A trivial example:

test,3table,*,-,freetable,-

Then we provide this CSV to the ./ddlscan:

./ddlscan
  -template ddl-mysql-oracle.vm
  -rename tables_to_rename.csv
  -db test
  -out test.sql

In effect, ddlscan working together with the RenameFilter, will transform a MySQL table like this:

describe test.3table;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+

And provide DDL SQL that is Oracle-compatible (note how table was renamed):

DROP TABLE test.freetable;
CREATE TABLE test.freetable
(
  i NUMBER(10, 0),
  PRIMARY KEY (i)
);

Rename during Real-Time Replication

After schema (with some objects renamed) is ready on the slave, we can configure Tungsten Replicator for replication. However, we want all transactions that happen on the master be aware of the renamed objects - eg. if there's a transaction on "3table", it should be transformed and applied into "freetable" on the slave. Luckily, Replicator can use the same rename definitions CSV file we defined during initial provisioning. All we need to do is provide it upon installation:

RENAME=/home/oracle/install/tables_to_rename.csv
tungsten-installer -a \
  --master-slave \
  --datasource-user=tungsten \
  --datasource-password=secret \
  --service-name=mysql2oracle \
  --home-directory=$TUNGSTEN_DEPLOY \
  --cluster-hosts=$SLAVE_HOST \
  --datasource-type=oracle \
  --datasource-oracle-service=ORCL \
  --master-host=$MASTER_HOST \
  --svc-applier-filters=dropstatementdata,zerodate2null,rename \
  --property=replicator.filter.rename.definitionsFile=$RENAME \
  --start-and-report

When Replicator starts-up, you'll see how it parses the definition file and constructs a look-up structure in the trepsvc.log:

INFO  filter.RenameDefinitions Parsing /home/oracle/install/tables_to_rename.csv:
INFO  filter.RenameDefinitions test.3table.* -> -.freetable.-

Succeeding transactions on this table will be transformed accordingly.

More RenameFilter CSV Examples

More examples covering common cases:

# Rename schema for all tables.
schemac,*,*,schemac1,-,-

# This table moved to a different schema.
schemac,tablew,*,schemac2,-,-

# Table renamed and moved to a different schema.
schemav,tablee,*,schemav1,tableee,-

# Rename table in a particular schema.
schemab,tabler,*,-,tabler1,-

# Rename each table with a given name across all schemas.
*,tablet,*,-,tablet1,-

# Rename column of this particular table.
schemaz,tableq,cola,-,-,colaa # Column renaming doesn't change schema/table.

# Rename column of each table with a given name across all schemas.
*,tablew,colb,-,-,colbb

# Rename column occurring in any table across all schemas.
*,*,colc,-,-,colcc

# Rename column across all tables in the given schema.
schemax,*,cold,-,-,coldd

A Note on Order of Preference

Order of lines in the CSV file doesn't matter - all of them are read during first load of the filter and they fill a fast in-memory look-up structure. The first three CSV columns (the search path) is used for look-up. With that said, it is possible to define rules that overlap. Consider the following two cases:

# Renamed schema, but one table is left in the original schema
# and some of its columns renamed.
scheman,*,*,scheman2,-,-
scheman,tabley,*,-,tabley2,- # Leave table in original schema.
scheman,tabley,colj,-,-,colj2
scheman,tabley,colk,-,-,colk2

# Renamed schema and one table with one column in it.
schemam,*,*,schemam2,-,-
schemam,tableu,*,schemam2,tableu2,- # Move renamed table to the new schema too.
schemam,tableu,colj,-,-,colj2 # Column renaming doesn't change schema/table.

The order of preference that is followed is straightforward. There are three cases to cover: finding new name for a column, table and schema. Each of these search from the most specific definition to the most generic. To be exact:

a. Order of preference when searching for a new column name:

1. schema.table
2. schema.*
3. *.table
4. *.*

Example:

client100,orders,amount,-,-,amount1 # 1. schema.table
client100,*,amount,-,-,amount2      # 2. schema.*
*,orders,amount,-,-,amount3         # 3. *.table
*,*,amount,-,-,amount4              # 4. *.* 

b. Order of preference when searching for a new table name:

1. schema.table
2. *.table

Example:

client100,orders,*,-,orders1,- # 1. schema.table
*,orders,*,-,orders2,-         # 2. *.table

c. Order of preference when searching for a new schema name:

1. schema.table
2. schema.*

Example:

client100,orders,*,client101,-,- # 1. schema.table
client100,*,*,client102,-,-      # 2. schema.*

Conclusion

We have reviewed how to use the new RenameFilter, that is available in the open-source Tungsten Replicator. RenameFilter is designed to help with various renaming needs during both initial provisioning and following replication, as opposed to developing multiple small filters to handle various rename needs that appear.

ddlscan - Utility to Help Analyze and Migrate Database Schemas

Intro

While working on one of the MySQL to Oracle replication projects for Continuent, I needed to implement an open-source utility for transforming MySQL schema to an Oracle dialect (DDL statements that create specific schema on Oracle) to save from otherwise tedious work. This article introduces ./ddlscan tool, which does that and is extensible to do much more.

Ingredients

Here's what you'll need:

• Your favorite DBMS with some tables. Currently supported MySQL, Oracle and PostgreSQL.
• Latest Tungsten Replicator build. Not even needed to install, enough to untar.
• Velocity template of your choice. You will find currently available ones in tungsten-replicator/samples/extensions/velocity/ folder.

Recipe

Mix ./ddlscan with your chosen schema and a template:

$ cd tungsten-replicator/bin
$
$ ./ddlscan -db test -template ddl-mysql-oracle.vm -url jdbc:mysql:thin://localhost/ -user tungsten -pass secret

If you have Replicator installed, it's even simpler - ./ddlscan can read connection credentials from Replicator properties for you (no need for "-url" parameter):

$ ./ddlscan -db test -template ddl-mysql-oracle.vm -user tungsten -pass secret

Result - rendered text file (or stdout). Eg. of ddl-mysql-oracle.vm template:

/*
SQL generated on Wed Feb 27 14:13:21 EET 2013 by ./ddlscan utility of Tungsten

url = jdbc:mysql:thin://localhost/
user = tungsten
dbName = test
*/

CREATE TABLE 3colors /* ERROR: table starts with a number, use rename option and a filter */
(
  id NUMBER(10, 0),
  color VARCHAR2(1) /* ENUM('R','G','B') */,
  enabled NUMBER(1) /* BIT(1) - no constraints for 0 or 1 */,
  acolumnthatdoesntfittooracleat NUMBER(10, 0) /* WARN: truncated column name exceeding 30 characters (acolumnthatdoesntfittooracleatall) */

  /* ERROR: table must have a primary key! */
);

Architecture

To make it more scientific, here's an overview of how it works:

When you call ./ddlscan it uses Tungsten Replicator libraries and connects to the underlying DBMS through appropriate JDBC driver. There are classes in the Replicator which extract metadata from the requested schema. Tables, columns, keys and so on are identified and then provided as JAVA objects to the Velocity template-generating engine.

Velocity templates are not hard to write. Here's an example of a template that reports tables without primary keys: ddl-check-pkeys.vm - if comments and headers were removed, it's only 3 lines of code (at least, at the time of writing this article). To make it easier to handle the syntax, use some plugin like veloedit.

Finally, user receives a rendered result from applying schema metadata to a template.

Possibilities

By leveraging this simple approach many tasks can be automated. Here's just a few ideas:

• The mentioned transformation of schema dialects between different DBMS types.
• Automatic trigger generation for trigger-based replication or ETL tools.
• Staging tables generation for loading data warehouses (eg. Vertica).
• Schema validation (scanning for potential issues).
• Automatic code generation. Eg. generating shell scripts to do something with the tables, generating JavaScript filters for Tungsten Replicator.

Conclusion

We have briefly reviewed how ./ddlscan tool works. More articles about specific use cases to come. Happy rendering!

Replicate from Oracle to MySQL *without* GoldenGate

Intro

Oracle is widely use to support back-end systems.  On the other hand, MySQL is the "go-to" data management solution for the web-facing part of many businesses.  If you have both Oracle and MySQL in-house, you may already also have the need to share data between them.  In this article I'll describe software that my colleagues and I have been working on to move data from Oracle to MySQL in real-time without costing an arm and a leg.

Tungsten to the Rescue!

Latest Tungsten Replicator has many features, most of which are open-source, but the recent one for me is particularly exciting - thanks to the development done by my colleague Stephane Giron in the extraction part, Tungsten Replicator learned to get new transactions from Oracle. This is very powerful, as it opened up a possibility for us to implement replication from Oracle to MySQL. 

Disclaimer

Tungsten licensing differs depending on which direction you need to replicate to:

• Replicating from MySQL to Oracle is open-source (though we do encourage to use consulting and support services).

• On the other hand, replicating from Oracle to MySQL needs a reasonably priced closed-source component.

Supported Oracle Versions

Tungsten Oracle extractor requires Oracle Change Data Capture (CDC).

We support the following Oracle versions: 10g Release 5 (10.2.0.5) and 11g.

It works with both Standard Edition (SE) and Enterprise Edition (EE).

Change Data Capture Modes

Tungsten supports two CDC modes:

1. Synchronous CDC, which uses triggers. As transactions execute DML, CDC triggers are called. In effect, CDC becomes part of the transaction. Note, that triggers are set only on the tables that are replicated.
2. Asynchronous CDC HotLog, which requires database-level supplemental logging to be enabled. It uses data sent to the redo log files, hence has no direct effect on the original transaction.

Data Type Limitations

In general, Tungsten can extract everything that CDC can provide. However, this means that the following types can't be replicated, as they are not supported by the CDC itself.

Not supported by both Synchronous and Asynchronous CDC:

• BFILE
• LONG
• ROWID
• UROWID
• object types (for example, XMLType)

In addtition, Synchronous CDC doesn't support:

• BLOB
  
• CLOB
• NCLOB

Just to clarify, replication out of Oracle XML DB is not supported. Other than that, all usual suspects are handled.

The Recipe

Here are the steps required to setup replication from Oracle to MySQL:

1. Choose a database to replicate.
2. Identify tables that require replication and make sure all their column types are supported.
3. Create corresponding tables in MySQL.
4. Get Tungsten.
5. Execute CDC preparation script that Tungsten provides.
6. Provision MySQL tables with the initial data to have a consistent starting point.
7. Run tungsten-installer command, which deploys Tungsten and starts replication.

Transaction Flow

The image below provides a view of how transactions travel from Oracle through Tungsten Replicator to MySQL. In this example, we are replicating data from an Oracle DEMO database:

During the initial setup, Tungsten's CDC preparation script (step 5 above) takes care of creating Oracle CDC publisher (DEMO_PUB) and subscriber (TUNGSTEN) users. This is one of the most complex parts of the setup and it is handled under-the-hood. The script subscribes to all the tables in the database to be replicated (DEMO). In effect, the created publisher user gets a change table for each of the real tables.

Below is an example of a table BIRDS in the DEMO database:



Here's a corresponding change table CT_BIRDS in the DEMO_PUB (note six metadata columns at the top and three user data columns at the bottom):

When transactions hit tables in the DEMO database, the change sets (rows that were changed by the transaction) are saved in the corresponding change-holding-table in the DEMO_PUB database. This happens within a few seconds, depending on the CDC mode being used and configuration peculiarities. Usually, replication from point-to-point happens in less than a minute.

In the mean time, Replicator is monitoring the DEMO_PUB tables as a CDC subscriber user. As soon as there's new data, OracleCDCExtractor component reads it, formats it as a standard Tungsten platform-independent row change structure and saves it in a disk-based Transaction History Log (THL). For example, an UPDATE to our BIRDS table looks like this (you will be familiar with this generic structure if you tried Tungsten Replicator for pure MySQL replication):

SEQ# = 86 / FRAG# = 0 (last frag)
- TIME = 2012-05-31 11:08:44.0
- EPOCH# = 83
- EVENTID = 962670580
- SOURCEID = dwh.example.com
- METADATA = [service=default;shard=DEMO]
- TYPE = com.continuent.tungsten.replicator.event.ReplDBMSEvent
- SQL(0) =
 - ACTION = UPDATE
 - SCHEMA = DEMO
 - TABLE = BIRDS
 - ROW# = 0
  - COL(1: ID) = 1
  - COL(2: NAME) = The Spirit Swans
  - COL(3: NOTE) = Continuent
  - KEY(1: ID) = 1
  - KEY(2: NAME) = Swans
  - KEY(3: NOTE) = Continuent

As soon as transaction is in the master's THL, even if Oracle stops working, THL serves accumulated transactions to the slave Replicator. As soon as Oracle is back, master Replicator knows which transactions it already extracted and continues from that point. In a similar way, slave Replicator is restart and crash safe too.

When transaction event reaches the slave, it is applied to the underlying MySQL. Applying to MySQL incorporates a few filters. For example, often Oracle uses upper case letters for table names, while MySQL uses lower case - there's a filter to change the case on the fly.

Overall, Replicator does not differentiate between Oracle generated events or MySQL ones, hence many of the Replicator functions work. For example, you could easily add more than one MySQL slave, filter out tables that are not needed, or do some data obscuring, in case you are replicating sensitive data. 

Note about DDL

The SQL dialect differences between different DBMS vendors are major, hence in heterogeneous topologies (like replicating from MySQL to Oracle) we usually recommend (a) setting up Tungsten filters which remove DDL statements from the queue completely or (b) if the DDL statement set is known, write a filter which translates one dialect to the other.

However, in the Oracle to MySQL case DDL statements are not seen at all. This is driven by the fact that CDC doesn't catch them. So, adding, altering or removing a table is done manually and should be added to your application upgrade process.

Anyone interested in an alternative for Oracle to Oracle replication?

I would like to finish this article with an exciting teaser, if you will. If you have been following progress on our work, you already know that Tungsten knows how to apply transactions to Oracle. So, now that we have both Oracle applier and Oracle extractor, it opens up another possibility - to do Oracle to Oracle replication. As the matter of fact, I'm working on this project right now. If you happen to be in need for this, let us know.

Conclusion

We have very briefly reviewed how Tungsten replicates from Oracle to MySQL. For me heterogeneous replication is one of the most exciting database areas to work in and I wanted you to know where to find the "GoldenGate without the Price Tag".

P.S.

If you're planning to visit MySQL Connect 2012 this September, come on over to hear Robert Hodges and me showing more of this in action (read: live demos) in a talk titled "Replicating from MySQL to Oracle Database and Back Again".

A Glance at Real-Time Replication From MySQL To Oracle

Intro

The open source Tungsten Replicator is very powerful. It's exciting to see how a flexible initial design allows to push the boundaries of replication with each new release. The recently published System of Record approach for multi-master databases does exactly this for multi-master databases.

Nevertheless, in today's database environments it is often not enough to move data, however well it is done, between the nodes of a single DBMS vendor like MySQL.  There is an entirely new set of challenges that appear when Oracle joins the Enterprise room.

A snapshot from Lithuanian animated classic "Baubas" (eng. "Bugaboo")

Thus, this article presents how Tungsten helps to interconnect the whole database picture by replicating data from MySQL to other vendors. This time I'll specifically glance at replication to Oracle.

Background

One of my main development focuses for Tungsten Replicator is support for heterogeneous replication. Allow me to define the word for complete clarity:

Heterogeneous replication - replication across different DBMS types.

Currently, the following topologies are production ready:

• MySQL -> Oracle
• MySQL -> PostgreSQL
• MySQL -> Greenplum

There are quite a few others waiting behind the curtains in POC stage too. What is really fun, that a lot of details provided below are valid for replication not only to Oracle. The basic recipe stays the same...

Motivation

Before we dive in, let's look at the motives behind heterogeneous replication. There are at least four major forces driving the interest:

• Real-time integration of data between different DBMS types
• Seamless migration out of one DBMS type to another
• Data warehousing (real-time) from different DBMS types
• Leveraging specific SQL power of other DBMS types

In other words, having software at your fingertips which can seamlessly (with some careful preparation though) move data from one DBMS type to another provides a lot more freedom in choosing the DBMS, which is best suitable for a particular task.

The Recipe

Here are general steps to replicate transactionally consistent data from MySQL to Oracle:

1. Extract the last committed transaction from MySQL's binary log.
2. Wrap the transaction with metadata, including Global Transaction ID.
3. Filter (transform) it to conform to the taste of Oracle.
4. Apply this transaction to Oracle.
5. If succeeded, go to (1), if not - break the replication.

Yes, if your data is sensitive, it's better to break the replication if unexpected happens. Just like Data Charmer loves failing installations, transactional consistency loves failing replication. One can compare this with a car - if oil pressure indicator light comes up, you better stop. Unless, you're keen in seeing how far the engine can drive without oil before breaking irreversibly...

After the error appeared, you can investigate it, fix it and resume. That's where the step (2) comes handy - Tungsten Replicator has global transaction IDs in heterogeneous replication too and hence knows where it stopped and where to resume.

Transaction Flow

With that in mind, how does a transaction travel from MySQL through Tungsten Replicator to Oracle? There are couple of available topologies to choose from. It is possible to configure everything with (a) a single Replicator on the Oracle side, or (b) with two - one on MySQL and one on Oracle. Let's see how the latter setup looks like:

In this picture you can see all the mentioned components (Java classes) of the Replicator, including extractor, filters and applier. Let's drill down to each of them in a little more detail.

Extracting from MySQL

This is the entry point. Every transaction that is written by MySQL to the binary log is extracted by the MySQLExtractor class. At this point, one of the primary challenges I come across when developing heterogeneous replication is, of course, SQL dialect differences. Almost naturally, each vendor has different DDL semantics, but not only that - you cannot rely on DML statements to be standardized either. Luckily, for MySQL there is solution - use row based replication. This is a great way to get all new transactions as row change data, as opposed to textual SQL strings.

For example, if "DELETE * FROM t" is committed, which, effectively, deletes 3 rows, Tungsten's built-in Transaction History Log viewer will show the extracted transaction like this:

$ ./thl list -seqno 152 -service default
...
SEQ# = 152 / FRAG# = 0 (last frag)
- TIME = 2011-09-09 03:52:18.0
- EPOCH# = 93
- EVENTID = mysql-bin.000006:0000000000071708;0
- SOURCEID = sp.continuent.com
- METADATA = [mysql_server_id=103;service=default;shard=test]
- TYPE = com.continuent.tungsten.replicator.event.ReplDBMSEvent
- SQL(0) =
 - ACTION = DELETE
 - SCHEMA = test
 - TABLE = t
 - ROW# = 0
  - KEY(1: ) = 1
  - KEY(2: ) = a
  - KEY(3: ) = NULL
 - ROW# = 1
  - KEY(1: ) = 2
  - KEY(2: ) = b
  - KEY(3: ) = NULL
 - ROW# = 2
  - KEY(1: ) = 3
  - KEY(2: ) = c
  - KEY(3: ) = NULL

In other words, this is a very handy structure, which will be reconstructed into an Oracle-compatible SQL by the Oracle applier at the later stages. This, at the base level, covers the DML, but what to do with DDL statements?

Handling DDL

Currently there are three choices:

1. Leave them as is: if DDL is compatible with Oracle, it is applied, if not - replication breaks and DBA can investigate it. Usually, this is a good idea, because you will know if someone is executing unexpected DDLs against the cluster.
2. Transform DDL SQL into Oracle compatible one by the help of filters: if application generates some DDL statements by itself and they are possible to identify.
3. Drop all DDL from the replication stream: useful during provisioning phase, when you're dumping a backup on the MySQL side and want data to be propagated to Oracle, which has the schema already prepared.

The application/schema upgrades are handled manually by a procedure like the following:

1. Prepare DDL upgrade script for MySQL.
2. Prepare DDL upgrade script for Oracle.
3. Disconnect applications.
4. Turn off binary logging on MySQL (SET SESSION SQL_LOG_BIN=0).
5. Execute upgrade scripts against MySQL and Oracle.
6. Turn the binary logging back on (SET SESSION SQL_LOG_BIN=1).
7. Resume applications.

Filters

Before we send our first transaction to Oracle, it needs to get by through important transformations. This is a separate topic on its own, but here's a brief overview of filters at work:

• EnumToStringFilter - MySQL saves ENUM column type values as indexes in the binary log; this filter maps the textual representation instead, so we'd save appropriate text values on the Oracle side, as opposed to meaningless numbers.
• To upper case filter - MySQL table and column names are usually lower case; this filter transforms the case to what Oracle likes.
• PrimaryKeyFilter - for UPDATE and DELETE row changes we strip down all the key-value pairs that are excessive in the WHERE clause, leaving only the primary key comparison.
• OptimizeUpdatesFilter - just like MySQL binary log is excessive in the key (WHERE) part, it has also abundant information in the SET part of an UPDATE; eg. if a table has hundred columns, but statement updates only one, the row change event will contain all the hundred columns updated; this filter strips abundant assignments and leaves only the columns that actually changed.
• and so on...

Applying to Oracle

And so the event, representing a transaction and holding the transformed-and-prepared-for-Oracle data change set, arrives to the doors of OracleApplier. The applier constructs a Prepared Statement, begins a transaction and links the values to it. Each row of the event is executed to Oracle. When it reaches the end of the event, this transaction is committed and data is ready for applications connected to the Oracle instance.

The beauty here is that all the stages mentioned above are working in parallel. Each stage is separated from the other by queues, which work like buffers. Eg. extraction from MySQL binary log is usually very fast, thus it pushes the data to the queue, from which next stage is reading and so on, thus extraction is not blocked.

Summary

I have briefly shared with you how Tungsten Replicator implements real-time replication from MySQL to Oracle. Of course, each topic above could be extended into an article of its own, but for now I just invite you to download the latest Replicator release, try out the MySQL->MySQL replication and, when comfortable, MySQL->Oracle one. My colleague, Jeff Mace, had made it a lot easier to setup advanced topologies like these with recent additions to the tungsten-installer.

Finally, heterogeneous replication projects like these are very fun to work on and I'm sure there will come more. Though cross DBMS type replication is primarily driven by customer demand and customers drive the priorities.

Happy replicating!

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!

Welcome

Welcome to a blog about DBMS, replication, clustering and related topics. Posting not too often - rather when there is something, hopefully, useful to share with fellow colleagues in this both wide and deep field.