What is Database Replication?

Database replication is the process of copying data from a source database to one or more target databases. It involves the frequent copying or streaming of data from a database or server to another database so all users have access to synced data, regardless of which system they are using to access it. If there is a data change, the data replication tool will also make sure that the changes are applied to the target database. The result is a distributed data storage network where anyone can easily access important and relevant data with increased availability across different locations.

Typically, after a data replication solution is implemented, users will notice an improvement in the consistency of data across each node, reduced data redundancy, increased data reliability, and ultimately, an increase in performance. Database replication can happen as part of a batch job, a more legacy approach, or in real-time as data is created, updated, and deleted on the source database.

What is a Data Replication Process?

The data replication process refers to how data is replicated from one database to another. Database replication can either be done as a single occurrence, used as a tool in data migration projects, or a continuous ongoing process that involves the replication and distribution of data to multiple data sources. The process ensures that changes, additions, and deletions carried out on a source database are automatically synced to the other target databases where those changes are needed.

Simply put, the data replication process involves one or more “publishers” and one or more “subscribers”, following the traditional Publisher-Subscriber software pattern. Any update that happens on a “publisher” system will then be replicated to the “subscriber” databases. If the system is bi-directional, users can make changes in the subscriber databases, the subscribers replicate them to the publisher database which then distributes the changes to all the other subscribers in the network.

Most times, the subscribers have a fixed connection with the publisher to enable changes or updates to automatically take place without any type of manual intervention. These updates might occur in periodic batch intervals or may be triggered and applied in real time.

Benefits of Database Replication

There are a few benefits from database replication:

It allows users to have access to in-sync data across diverse geographical locations, including the ones closest to them.
It improves read performance by allowing for multiple systems of access, relieving the strain on a single system.
Database replication makes data more reliable, durable, and data systems more resilient.
It improves disaster recovery of data as multiple copies of the data can be made and stored in different locations, including across different cloud platforms.
Database replication also makes it easier to enable analytics use cases since data can be loaded into an analytics platform without impacting the performance or reliability of the source of the data.

Importance of Database Replication

Based on the above benefits, the importance of database replication can be seen throughout the enterprises that adopt it. For example:

High data availability: Data replication ensures that you have data available to users at all times. Even when issues may arise with one of the replicated databases, users can still access copies of the database found in other locations.
Reduced server load: A replicated, distributed database requires less processing power for each server since data is being processed and hosted on multiple servers. This distribution of processing and storage leads to load reduction on the servers compared to a single server handling loads and suffering from performance issues.
Lower ;atency: Network performance is improved as copies of data are found in multiple locations. This reduces roundtrip times to retrieve data since replicas are closer to the users.
More reliable and accurate data: With database replication in place, data is constantly processed and updated to match the source data, increasing data integrity and reliability.
Disaster recovery: Database replication has a positive effect on the recovery and resiliency of data. The latest versions of data can be retrieved in situations where a particular server breaks down to avoid data loss and offer a better disaster management process.

Types of Database Replication

There are different types of database replication that can be applied. Choosing a particular type of replication will largely depend on your business and what you want to do with the data. Below is an explanation of each of the types of database replication.

Full-table replication: Full-table replication copies everything from the source database to the destination storage. It transfers new, updated, and existing rows from the publisher to the subscriber. It enables hard-deletes operations but this method of replication is associated with a high cost of maintenance since the processing power and network bandwidth requirements needed to copy everything is high. It places a burden on the network and can cause delays in replication especially when the data volume is large.
Snapshot replication: This type of database replication takes a snapshot of the source database and replicates the data in the target destination database. It does not regard changes made on data such as new, updated, or deleted, rather, it makes a copy of what it captures at that given time. This mode of replication is preferably used when changes made to the data are infrequent. It is faster than full-table replication but does not keep records of the hard-deleted data.
Merge replication: Merge replication merges two or more databases into a single database. It is commonly found in server-to-client models and allows independent changes to be made by both the publisher and subscriber.
Key-based incremental replication: This method of replication scans keys or indexes in a DBMS to check for changes such as delete, new, and updated. The replication process then replicates only the relevant replication keys to the replica database to reflect the changes since the last update. These keys are typically a timestamp, datestamp, or integer. The process is fast since only marked changes are copied to the replica database. Unfortunately, this approach does not support hard-deletes as the key value is also deleted when the record is deleted in the primary database.
Log-based incremental replication: This replication type copies data based on the database binary log file. The binary log file, when scanned, provides information on changes such as inserts, updates, and deletes that have occurred in the primary database. These same changes are then implemented in the destination database. This is the most popular solution since it is very efficient for static databases and is supported by most database vendors including MySQL, PostgreSQL, Oracle, and MongoDB.
Transactional replication: Transactional replication first copies all the existing data from the source database into the destination location then with any new development in the source data, the same transaction is executed in the replicas. Though it is an efficient approach to replication, the replicas are mostly used for read operations, and may not support create, delete, and update operations.

How To Perform Database Replication

For an easy understanding of how to perform database replication, the database replication process can be broken down into the steps listed below (a much simplified version):

The first thing to do is to identify your data source and where you want to replicate it too.
Determine the files, folders, and applications that will be copied, if applicable.
Select a suitable type of replication from the ones listed above that will meet your business needs.
State and acknowledge how often you want an update on the replication be it scheduled or in real-time and either in batches or in bulk.
You can now proceed to set up the data replication by writing a custom application or by using one of many available replication tools that can give you your desired outcome.
Finally, monitor the replication process to ensure that the data is replicated consistently and as expected to ensure data quality.

Database Replication Challenges

Data replication comes with some challenges as well. Some of these are apparent and others may be a bit more hidden. A few potential challenges are mentioned below:

High costs: Maintaining and running multiple copies of data across various server locations will result in a high cost of management and infrastructure. This is because you will have to provide hardware and lots of processing power to make sure that the databases are functioning optimally, as well as a team to ensure things are running smoothly and to maintain the infrastructure.
High network bandwidth: To have consistency across the replicas, a high network bandwidth will be needed to ensure a smooth operation as it caters to the increased traffic. This will also increase the costs to build and maintain a network that is able to handle such traffic.
Inconsistent data: Copying data from different locations at different intervals can bring room for challenges that may arise from trying to sync the data. When this happens, the data found in the locations may not be in sync, thereby causing inconsistency that might lead to incorrect analysis and potentially negative customer and business impacts.
Loss of data: Data can be lost when carrying out the replication of databases. This can arise when inappropriate technologies are used and when incorrect data is copied or iterations of data updates are unaccounted for. This can also be one of the tougher challenges to detect and correct.

Database Replication Versus Mirroring

Mirroring of data, simply put, is the creation of a backup database server that acts as a safety net for the primary database. This is used to prevent loss of data in case anything happens to the primary database. The mirrored database kicks in to act as a primary database server when the primary database server is down. Traffic is immediately redirected back to the primary server immediately after it is restored. The mirrored database server does this only when the primary database server is out of service since only one of the two can be active at a given time. The remainder of the time, the backup database is on standby and does not receive any traffic. Mirroring of data is carried out on the whole database and can not be implemented on distributed database systems as you would see within a replication approach.

While database replication, on the other hand, is the process of copying and storing data from one database to several target databases found in different locations around the globe. It is also a very useful approach that is used, for example, with file servers as it helps users to search and locate files and access the files stored in servers closest to them. Whether you are replicating a database or some other type of storage, replication thrives in a distributed system.

A Simple Database Replication Example

This section will look at how to implement database replication using PostgreSQL to demonstrate a simple replication setup. PostgreSQL replication is the process of copying data from a PostgreSQL database server to another server. The source database server is referred to as the primary server, and the server receiving the copied data is known as the replica server.

In PostgreSQL, all writes go to a primary node. Changes are made there and then broadcasted to the secondary nodes. Types of replication we can use include volume level replication, PostgreSQL streaming replication, or taking incremental backups. In this example, we will use PostgreSQL streaming replication.

The PostgreSQL streaming replication is also known as WAL replication. WAL stands for Write-Ahead Logging and is the standard protocol used to ensure that all the changes made to a database are logged properly in the order that they occurred. In the case of a database crash, it helps maintain data integrity by facilitating a dependable recovery process. WAL is enabled in PostgreSQL by default and has a default size of 16MB.

WAL can be set up after installing PostgreSQL on all servers so that the WAL files can be moved from the primary database to the target database. A primary-secondary configuration is used in the example. This configuration will use the primary server to host the primary database. As changes are made to the primary database, the secondary server acts as the supplementary instance and executes changes made to the primary on itself by generating an identical copy.

To implement this, a brief description is given below of how the primary and secondary nodes will need to be configured in order to achieve this.

Configuring Primary Node

The primary node should be configured for streaming replication with the following steps:

1. Initialize the Database: The initidb utility command is used to initialize the database before creating a new user with replica privileges with the command below.

CREATE USER  REPLICATION LOGIN ENCRYPTED PASSWORD '';

The code below is a sample showing the creation of a user. The query contains both the user’s password and username. The REPLICATION keyword is used to give the user the required privileges.

CREATE USER rep_user REPLICATION LOGIN ENCRYPTED PASSWORD 'rep_pass'

2. Configure the Streaming Properties: Configure the streaming properties with the PostgreSQL configuration file postgresql.conf as seen in the sample below.

wal_level = logical
wal_log_hints = on
max_wal_senders = 8
max_wal_size = 1GB
hot_standby = on

wal_log_hints: When the standby server is out of sync with the primary server, the parameter wal_log_hints is needed for the pg_rewind capability. This will be required to try to put it back in sync with the primary server.

wal_level: This parameter is used to enable PostgreSQL streaming replication, with possible values including minimal, replica, or logical.

max_wal_size: The size of WAL files that can be retained in log files are specified using this parameter.

hot_standby: Read-on connection with the secondary server can be leveraged by using this parameter when it is set to ON.

max_wal_senders: The parameter max_wal_senders is used to specify the maximum number of concurrent connections that can be established with the standby servers.

3. Create New Entry: After modifying the parameters in the postgresql.conf file, a new replication entry in the pg_hba.conf file allows the server to establish a connection with each other for replication. This file is found in the data directory of a PostgreSQL instance. An example of a code snippet shown below can be used for it.

host replication rep_user IPaddress md5

Once the code above is executed, the primary server allows a user called rep_user to connect and act as the standby server by using a specified IP address for replication as seen in the sample code below.

host replication rep_user 192.168.0.22/32 md5

Configuring Standby Node

To configure the standby node for streaming replication, do the following steps.

4. Back-Up Primary Node: Use the pg_basebackup utility to generate a backup of the primary node. This serves as the starting point for the standby node.

host replication pg_basebackp -D  -h  -X stream -c fast -U 
rep_user -Wrep_user 192.168.0.22/32 md5

The parameters used in the syntax mentioned above are as follows:

-h: This is used to specify the primary host.

-D: This is used to indicate the directory you are currently working on.

-C: You can set the checkpoints using this parameter.

-X: The parameter -X is used to include the necessary transactional log files.

-W: This is used to prompt the user for a password before linking to the database.

5. Set Up Replication Configuration File: Generate the replication configuration file as recovery.conf. If the replication configuration file does not exist, create the file in the data directory of PostgreSQL installation automatically by using the -R option within the pg_basebackup utility. The recovery.conf file should contain the command shown below.

standby_mode = ‘on’ 

primary_conninfo = ‘host= port= user= password= application_name=”host_name”‘ 

recovery_target_timeline = ‘latest’

The parameters used in the commands above are as follows:

Primary_conninfo: This is used for the creation of a connection between the primary and secondary servers by leveraging a connection string.

standby_mode: When switched ON, standby_mode causes the primary server to start as the standby server.

recovery_target_timeline: This sets the recovery time.

To set up a connection, you need to provide the username, IP address, and password as values for the primary_conninfo parameter as seen below:

primary_conninfo = 'host=192.168.0.26 port=5432 user=rep_user password=rep_pass'

6. Restart Secondary Server: Finally, restart the secondary server to complete the configuration process.

With this, your PostgreSQL instance is set up for WAL Streaming replication and will replicate data between the primary and secondary database.

Introducing Arcion

Above we assessed a single way to implement CDC with PostgreSQL. Of course, this required a lot of configuration to set up and is only applicable to use when replicating from PostgreSQL to PostgreSQL. Obviously, in many use cases, there may be a mix of multiple technologies including moving data from a primary database to another database, such as Microsoft SQL Server or Oracle Database. You may also use CDC to move data to big data platforms like DataBricks or Snowflake.

When looking for a more robust way to implement data replication, through Change Data Capture, look no further than Arcion. Instead of forcing engineers to work through configuration files and code, Arcion allows users to create a no-code data pipeline for replication. On top of these advantages, Arcion offers both self-managed on-premise and fully-managed cloud products to fit your exact needs.

By using Arcion, it’s easy for organizations to build pipelines for replication using many different data sources. Easily move data from a source database to an external system, such as a big data platform like Snowflake or Databricks, with no code required. For many use cases, Arcion is much easier and more flexible than the built-in replication and CDC tools supported by the major database providers.

Benefits of using Arcion include:

No-code connector support for 20+ sources and target databases and data warehouses
Agentless CDC ensures there is zero impact to the source data systems. Arcion reads directly from database logs, never reading from the production database. No need to install a software process on the production system.
Multiple deployment types supported across cloud and on-premise installations
Configuration can easily be done through UI, with minimal effort and zero code
Automatic schema conversion & schema evolution support out-of-the-box (including SQL and NoSQL conversion)
Patent-pending distributed & highly scalable architecture: Arcion is the only end-to-end multi-threaded CDC solution on the market that auto-scales vertically & horizontally. Any process that Arcion runs on Source & Target is parallelized using patent-pending techniques to achieve maximum throughput.
Built-in high availability (HA): Arcion is designed with high availability built-in. It makes the pipeline robust without disruption and data is always available in the target, in real-time.
Auto-recovery (patent-pending): Internally, Arcion does a lot of check-pointing. Therefore, any time the process gets killed for any reason (e.g., database, disk, network, server crashes), it resumes from the point where it was left off, instead of restarting from scratch. The entire process is highly optimized with a novel design that makes the recovery extremely fast.

Conclusion

The above content has given you a deep explanation and examples of database replication. Several modules were used to cover the topic and an example using PostgreSQL was used to illustrate how to set up a simple replication process.

In the article, we went over database replication, its benefits, and why it is of utmost importance in today’s world. We also explained the types of replication available and compared them to database mirroring.

Lastly, we looked at using a tool like Arcion for building scalability and flexibility in your data replication setup. To get a no-code data replication and CDC solution that works seamlessly with all of the most popular databases and big data platforms, try out Arcion Cloud and have a scalable and reliable CDC-enabled pipeline set up in minutes. If you’re looking for a flexible on-premise solution, try out Arcion Self-hosted. Regardless of the deployment type chosen, Arcion provides unparalleled flexibility and scalability for the data pipelines that facilitate robust data replication.

‍

A Complete Guide on Database Replication