How to Migrate Data from PostgreSQL to SelectDB in Real Time?

PostgreSQL, as an open-source and powerful relational database management system, has been widely used in OLTP systems. Many enterprises leverage its excellent performance and flexible architecture to handle high-concurrency transactions and rapid response requirements.

However, for OLAP scenarios, PostgreSQL may not be the best choice.

To implement large-scale data analysis scenarios, enterprises will opt for professional data warehouse products. For example: ClickHouse, Doris, SelectDB, Greenplum, Redshift, etc. When choosing a data warehouse, one must consider query performance and storage efficiency. SelectDB excels in both aspects. In terms of query performance, SelectDB uses advanced columnar storage and vectorized execution engines, which can efficiently handle complex queries and provide excellent query performance. In terms of storage efficiency, SelectDB's columnar storage structure and efficient compression algorithms greatly reduce disk space and I/O overhead.

This article takes SelectDB as an example to introduce how to migrate data from PostgreSQL to SelectDB.

What are the difficulties in data migration?

Imagine if you are now doing data migration from PostgreSQL to SelectDB, what do you need to do?

• Table initialization: Does the SelectDB end need to create a table structure consistent with the source end first? Then can it receive data from the source end. If you initialize manually, and if there are hundreds or thousands of tables on the PostgreSQL end, how should you deal with it?
• Data structure mapping: The data structures of PostgreSQL and SelectDB are different, which is very important. It is necessary to ensure that the data is still complete after being synchronized from the source end. It is very responsible to say that unless you have an extremely acute understanding of the data models of both and ensure that there are absolutely no errors, you can achieve the ideal result.
• Real-time synchronization rate: Keep the data on the SelectDB end consistent with the source end of PostgreSQL at all times. The premise of using SelectDB for real-time data analysis is that the data in SelectDB must be the latest, and the analysis results are meaningful.
• Source end DDL statement linkage: Real-time capture of DDL changes on the source end of PostgreSQL and synchronize execution in the target end of SelectDB in a timely manner. This is definitely not an easy job. In most cases, when the data structure on the source end changes, the synchronization link will be interrupted, leading to migration failure.
• Synchronization task stability: Imagine if you need to synchronize PostgreSQL and SelectDB online for a long time, what is the most important? Of course, it is the stability of the synchronization task. You have to consider how to ensure the availability of the task in the case of network or server exceptions.

These issues are very critical. Relying on manual or simple scripts, the migration effect is definitely not very good, so choosing the right tool is crucial.

In fact, NineData has absolute authority on the above issues. Let's first look at its features:

• Structure replication: Based on the characteristics of the target end data source, automatically and efficiently complete table creation, data structure mapping, and other work.
• Replication performance: Based on dynamic batch accumulation, parallel replication, Stream Load, and other technologies, the replication performance can easily reach 200 MB/S.
• DDL capture and execution: Real-time detection of DDL operations on the source end and synchronization execution on the target end to ensure that other business changes can proceed stably.
• High availability mechanism of tasks: For various fault scenarios such as PostgreSQL master-slave switching, data source exceptions, network jitter, server exceptions, etc., it can automatically detect exceptions and perform self-diagnosis and self-repair; at the same time, for scenarios that cannot be self-healed, it provides a wealth of repair methods in the task.

Let's demonstrate in practice.

Step 1: Enter the source and target data sources

1. Log in to the NineData console, click on Data Source Management > Data Sources, and then click on Create Data Source on the page to select the data source you need to enter.

   

2. Configure according to the page prompts, and then click Create Data Source to complete the creation.

   

Step 2: Configure the synchronization link

1. Log in to the NineData console, click on Data Replication > Data Replication, and then click on Create Replication.

   

2. Configure the replication task according to the page prompts. Since we want to achieve long-term real-time data synchronization, we need to check Incremental Replication in the Replication Type.

   

3. After the configuration is completed, start the task. For all the synchronization objects you have configured, NineData will first perform a full migration of all the existing data, and then synchronize the incremental data newly added in PostgreSQL in real time. All newly written data will be synchronized to SelectDB without omission. Whenever the incremental data on the target end catches up with the source end, the task panel will display Delay 0 seconds, as shown in the figure below.

   

Step 3 (Optional): Verify the integrity of the synchronized data on the target end

In addition to the synchronization function, NineData also provides a comparison function for the synchronized data between the source end and the target end after synchronization to ensure the integrity of the data on the target end.

1. Log in to the NineData console, click on Data Replication > Data Replication, and then click on the replication task ID created in Step 2.

   

2. Click on the Data Comparison tab, and click on Start Data Comparison (if Start Data Consistency Comparison is checked in the task configuration of Step 2, the comparison result is directly displayed here).

   

   tip

   After enabling, the system will automatically compare the synchronization objects between the source end and the target end and give the comparison results.

3. You can click on Re-compare on the page after a while to verify the synchronization results of the latest incremental data.

   

Step 4 (Optional): Configure task exception alerts

Since it is a long-term task, you may need the system to monitor the task status in real time and notify you immediately when there is an exception.

1. Log in to the NineData console, click on Data Replication > Data Replication, and then click on the replication task ID created in Step 2.

   

2. Click on Configure Alerts in the upper right corner.

   

3. Enter the Policy Name, select Alert Rules, and click Save Configuration. You can directly use the built-in default rules to send SMS reminders when the task fails or the replication delay is greater than 10 minutes. You can also create custom rules to notify according to your needs.

   

Summary

In the process of building a data warehouse, data integration is crucial, but as long as you choose the right tool, it's not that difficult, it's a piece of cake. Just like the scenario of migrating from PostgreSQL to SelectDB in this article, after a series of operations, the task is up immediately, and the data is also transferred immediately. The new incremental data can be queried on the SelectDB end in the blink of an eye. It seems like you didn't do anything, but you find that the preparatory work for data analysis has been completed.

The actual configuration process is far less time-consuming than reading this article, and it is far less complicated than you think. It's more intuitive to try the product directly than to listen to me talk about it. So if enterprises have this need, they might as well try it directly. NineData supports real-time synchronization and periodic data integration scenarios for dozens of different data sources. The full amount is free, and an incremental link is also given.