Category: Replication

Semab Tariq shows how to replicate a limited number of columns:

Selective column replication, a feature of logical replication in PostgreSQL, enables the selective transfer of data changes from one database to another. This feature offers flexibility by addressing bandwidth and storage optimization concerns. It allows users to choose specific columns to replicate rather than replicating entire tables, ensuring that only essential data is transferred. This selective approach reduces network traffic and storage space required for replication while maintaining data consistency across databases.

In this blog, I will demonstrate the process of replicating specific columns from a table using logical replication. Additionally, we will see how you can create multiple subscribers connected to a single publisher to replicate data across multiple locations.

Replication seems to be a lot easier to set up in Postgres than SQL Server, though check out the FAQ at the end for a few kicks in the pants around Postgres replication.

Jose Manuel Jurado Diaz goes through a lot of work to replicate replication:

This week, we addressed a service request from a customer who wanted to keep two tables synchronized across different databases on the same Azure SQL Database server. Unfortunately, in Azure SQL Database, it’s not possible to directly call the database in the operation, for instance using the command select * from master.sys.sys_databases. For this reason, I’d like to share an alternative to tackle this limitation.

This is a way to do it, and it’s fine if there’s only one entry point for the data and you’re okay with writes taking more than twice as long. But this is a perfect use case for transactional replication. The only problem is, Azure SQL Database doesn’t support being a transactional replication publisher or distributor, only a (push) subscriber.

Muhammad Ali takes us through replication in Postgres:

PostgreSQL provides two main types of replication: Physical Streaming Replication and Logical Replication. In this blog post, we explore the details of Logical Replication in PostgreSQL. We will compare it with Physical Streaming Replication and discuss various aspects such as how it works, use case, when it’s useful, its limitations, and key points to keep in mind.

Logical replication is the Postgres equivalent to SQL Server replication. Read on to see how it works.

Aisha Bukar continues a series on replication in MySQL:

Welcome back to another replication series! As a quick reminder, we explored various methods of using MySQL’s replication capabilities in our previous discussions. Initially, we employed the traditional binary-log-based replication approach to set up our replication servers. This involved tracking the binary log file and its positions to facilitate replication.

In this article, we will dive into a more recent and acceptable approach to creating replication – using the Global Transaction Identifiers (GTID) based replication.

Click through to understand how they work and the trade-offs you’ll need to make regarding them.

Aisha Bukar continues a series on replication in MySQL:

MySQL’s multi-source replication allows a replica server to receive data from multiple source servers. Let’s say you have a replica server at your workplace, and there are multiple source servers in different locations, you need a way to directly receive data from these source servers to your replica server. This is where the multi-source replication technique comes into play. It allows you to efficiently gather data from various sources and consolidate it on your replica server.

Note that this is quite different from merge replication or peer-to-peer replication in SQL Server and there are some limits to its capabilities. That said, I could see this being really useful for performing ELT into a warehouse: use replication to keep the staging tables in sync and then run a job to perform transformations into facts and dimensions periodically.