In this second post on real-time data replication from MySQL, MariaDB or Percona Server we’re looking at PostgreSQL replication.
Even though PostgreSQL celebrates its 24th anniversary this year, it’s almost like it’s never been more current than it is today.
It has noticeably and consistently gained in popularity in recent years; so much so that it is the only RDBMS to have won db-engines’ DBMS of the Year Award three times. It is currently the reigning champion in that category and also ranks 4th on db-engines’ database management systems popularity rankings.
‘The World's Most Advanced Open Source Relational Database’ just keeps on expanding its reach in the world of open source databases and it’s no surprise that it would regularly go head to head with the ‘World’s Most Popular Open Source Database’: MySQL.
Plenty of good content has been written about PostgreSQL vs MySQL, use case stories as to why companies choose to move from one to the other, etc. and this blog post here won’t go into that discussion. If you’d like to read more about it, there’s a handy blog post on hackr.io to get you started on this topic without taking sides.
However, this discussion is often also where the need to replicate from the one to the other comes into play.
Replication Between MySQL & PostgreSQL
Replication allows the movement of data from the source database(s) to target database(s) in real time, keeping the target in sync with the source. Although most databases support some sort of replication, rarely is heterogeneous replication supported, where source and target are different database technologies altogether.
In the case of MySQL and PostgreSQL, replication is built between two heterogeneous databases where changes may need to continuously replicate from a MySQL database to a PostgreSQL database.
This is where Tungsten Replicator comes in, and in particular, the Tungsten Replicator AMI on the Amazon Marketplace, which is the focus of this post.
Tungsten Replicator (AMI) for PostgreSQL
Tungsten Replicator provides high-performance and improved replication functionality over the native MySQL replication solution and into a range of target databases, such as PostgreSQL of course… as well as MySQL (all versions), Oracle, Vertica, AWS RedShift, ClickHouse, Hadoop, MongoDB & Kafka.
It is the most advanced heterogeneous replication solution for MySQL, MariaDB & Percona Server, including Amazon RDS MySQL and Amazon Aurora. It’s available as an AMI and can be accessed via AWS.
How Replication to PostgreSQL Works
Option 1: Local Install
The Extractor is installed on the MySQL source hosts and reads directly from the binary logs on disk. This is the most common and highest performance installation. With a local install, the Extractor does not need to log into the database to query binary log events.
Option 2: Remote Install
The Extractor is installed on a separate server and requests log data via MySQL Replication protocols (which requires the DBMS service to be online). This is how we handle Amazon Aurora extraction and other managed database replication.
How the Tungsten Replicator Can Be Used
One practical application is using a POD style deployment with MySQL. It’s useful to replicate data from each POD into a single Postgres deployment (fan-in) to take advantage of Postgres’ handling of complex queries and ability to store extremely large datasets. Tungsten replication supports fan-in (as well as fan-out), and can add additional columns into the replication stream to keep track of the source of the data.
So the original table may look like this:
| Table A | |
|---|---|
| id | 100 |
| textdata | Hello, World! |
| last_updated | 2021-02-23 |
And after fan-in replication to the Postgres target:
| Table A | |
|---|---|
| source_db | db1 |
| id | 100 |
| textdata | Hello, World! |
| last_updated | 2021-02-23 |
So now it’s possible to identify the POD that this record originated from.
Let us know about your own replication needs or use cases … and check out the details below in order to experience the Tungsten Replicator (AMI) yourselves!
How to Get Started With the Tungsten Replicator AMI
Getting started with the 14-Day free trial
Users can try one instance of each type of the AMI for free for 14 days to get them started (AWS infrastructure charges still apply).
Please note that free trials will automatically convert to a paid hourly subscription upon expiration and the following then applies in the case of PostgreSQL targets.
Replicate from AWS Aurora, AWS RDS MySQL, MySQL, MariaDB & Percona Server to PostgreSQL from as little as $0.40/hour
With Tungsten Replicator (AMI) on AWS, users can replicate GB's of data from as little as $40c/hour:
- Go to the AWS Marketplace, and search for Tungsten, or click here.
- Choose and Subscribe to the Tungsten Replicator for MySQL Source Extraction.
- Choose and Subscribe to the target Tungsten Replicator AMI of your choice.
- Pay by the hour.
- When launched, the host will have all the prerequisites in place and a simple "wizard" runs on first launch and asks the user questions about the source and/or target and then configures it all for them.
Watch the Getting Started Walkthrough
Our colleague Chris Parker recorded this handy walk-through on how to get started with the Tungsten Replicator AMI if you need some tips & tricks.
Extraction and Appliers
Below you’ll find the full listing of extractors and appliers that are available with Tungsten Replicator.
Replication Extraction from Operational Databases
- MySQL (all versions, on-premises and in the cloud)
- MariaDB (all versions, on-premises and in the cloud)
- Percona Server (all versions, on-premises and in the cloud)
- AWS Aurora
- AWS RDS MySQL
- Azure MySQL
- Google Cloud SQL
Available Replication Target Databases
Do give Tungsten Replicator AMI a try and let us know how you get on or if you have any questions by commenting below!
Finally, do check out our blog on Real-Time MySQL Analytics & How Vertica Replication if you’re also working with Vertica.
Comments
Add new comment