From PostgreSQL to TiDB Cloud: A Hands-On Data Migration with Apache SeaTunnel

1. Why Migrate the Database?

In the early stages of our business, we used PostgreSQL as both an OLTP and analytical database, deployed and maintained on our own infrastructure. In day-to-day operations, however, maintaining high availability, scheduling regular backups, and handling failure recovery required significant engineering effort. The resulting operational costs and complexity were high, and occasional operational oversights introduced a certain level of risk to business continuity.

Given that TiDB Cloud, PingCAP's cloud database service, eliminates the need for on-premises deployment and maintenance while offering convenient usage and flexible, controllable pricing, we decided to migrate our PostgreSQL database to TiDB Cloud, with business continuity and stability as our top priorities. We also saw this migration as an opportunity to explore a more diversified data storage and analytics architecture that would better fit our business needs.

Our core business focuses on road traffic checkpoint data analytics. Based on massive volumes of vehicle passage records—including license plates, vehicle speeds, locations, and driving behavior characteristics—we provide data support for traffic management and safety oversight.

The core table, driving_data_jsonb, uses a JSONB + partitioned-table design. It processes approximately 5 million vehicle passage records per day, while a single partition stores around 150 million vehicle passage records (approximately 60 GB). It is the largest and most frequently queried table in our business.

Typical analytical scenarios include:

• Road congestion analysis: Aggregating traffic volume and average vehicle speed by time period and road segment to assess current road traffic conditions in real time
• Section speed-limit violation detection: Calculating a vehicle's average speed over a road section based on the travel time and distance between checkpoints, and identifying speeding violations
• Driving trajectory analysis: Reconstructing vehicle routes to analyze driving habits and patterns of abnormal behavior
• Continuous driving duration monitoring: Tracking the continuous driving time of individual vehicles and generating alerts based on traffic regulations, such as the requirement to take a mandatory break after four hours of continuous driving

These analytical queries involve large-scale data aggregation and multidimensional calculations, placing relatively high demands on the database's OLAP capabilities.

2. Migration Environment

2.1 Source Environment

2.2 Target Environment

2.3 Network Topology

The source environment connects directly to TiDB Cloud over the public Internet through a 500 Mbps broadband connection. No dedicated private connection or VPN was deployed.

3. Migration Tool Selection

During the migration, we primarily evaluated two commonly used data migration tools: DataX and SeaTunnel.

Given our core requirements for timely synchronization, stability, and zero data loss, we ultimately selected SeaTunnel 2.3.13 as the migration tool.

4. Migrating Data with SeaTunnel

4.1 Migration Strategy: Full Load + Incremental Sync

Because the PG-CDC connector in SeaTunnel 2.3.13 had a known bug in the combined full-load + incremental synchronization mode (which we reported to the community and helped fix), we adopted a two-step approach: full-load initialization followed by CDC-based incremental synchronization.

Step 1: Perform a full data initialization using the JDBC PostgreSQL connector.

Step 2: Use the CDC incremental connector to synchronize data changes in real time.

4.2 Full-Load Configuration (JDBC Source → JDBC Sink)

The following is the core configuration used during the full-load phase:

env {
  parallelism = 2
  checkpoint.interval = 10000
  pipeline.name = "PG_TO_TiDB_DRIVING"

  flink.execution.checkpointing.mode = "EXACTLY_ONCE"
  flink.execution.checkpointing.timeout = 600000
}
source {
  Jdbc {
    url = "jdbc:postgresql://<source_host>:5432/traffic"
    driver = "org.postgresql.Driver"
    user = "postgres"
    password = "******"

    query = "SELECT record_id, pass_time, plate_no, plate_color,
             vehicle_type, speed, speed_limit, status,
             longitude, latitude, speed_variance,
             lane_change_count, raw_behavior_features,
             create_time, update_time
             FROM driving_data_jsonb"
    column.filters = "update_time"
    start.time = "2025-01-01 00:00:00"
    fetch.size = 1000
    parallelism = 2
  }
}
sink {
  Jdbc {
    url = "jdbc:mysql://<tidb_cloud_host>:4000/traffic?sslMode=VERIFY_IDENTITY"
    driver = "com.mysql.cj.jdbc.Driver"
    user = "******"
    password = "******"
    database = "traffic"
    table = "driving_data_jsonb"

    generate_sink_sql = true

    save_mode = "upsert"
    unique_key = ["record_id"]

    batch.size = 1000
    batch.interval = 1000
    parallelism = 2
  }
}

4.3 Incremental Synchronization Configuration (Postgres-CDC Source → JDBC Sink)

Once the full load was completed, we started the CDC incremental connector to continuously synchronize changes from the source database in real time:

env {
  execution.parallelism = 1
  job.mode = "STREAMING"
  checkpoint.interval = 5000
}
source {
  Postgres-CDC {
    username = "postgres"
    password = "******"
    database-names = ["traffic"]
    schema-names = ["public"]
    table-names = ["traffic.public.driving_data_jsonb"]
    url = "jdbc:postgresql://<source_host>:5432/traffic"
    decoding.plugin.name = "pgoutput"
    slot.name = "final_slot"
    startup.mode = "latest"
    plugin_output = "out"
  }
}
sink {
  Jdbc {
    url = "jdbc:mysql://<tidb_cloud_host>:4000/traffic?sslMode=VERIFY_IDENTITY"
    driver = "com.mysql.cj.jdbc.Driver"
    user = "******"
    password = "******"
    database = "traffic"
    table = "driving_data_jsonb"

    generate_sink_sql = true

    save_mode = "upsert"
    unique_key = ["record_id"]

    batch.size = 1000
    batch.interval = 1000
    parallelism = 2
  }
}

4.4 Key Configuration Details

The following table highlights the key configuration options used during full-load and incremental synchronization:

4.5 Migration Results

Note: The migration first focused on the largest core table, driving_data_jsonb. The remaining tables contain significantly less data and will be synchronized as needed.

5. Challenges and Lessons Learned

5.1 PG-CDC Connector Bug

The PG-CDC connector in SeaTunnel 2.3.13 had a bug in the combined full-load + incremental synchronization mode, which prevented the workflow from completing successfully. We reported the issue to the community, worked with developers to identify and fix the problem, and ultimately avoided it by running the full-load and incremental synchronization stages separately.

Recommendation: When using SeaTunnel for PostgreSQL migration, we recommend adopting a two-step full-load + incremental synchronization strategy first, as it provides better stability. If you need to use the integrated mode, make sure the SeaTunnel version you are using contains the fix for this issue.

5.2 The Impact of Network Bandwidth

The source database was deployed on a local virtual machine and connected to TiDB Cloud over the public Internet through a 500 Mbps broadband connection. Synchronizing 60 GB of data from a single partition took approximately 12 hours during the full-load phase.

If the source database is deployed in the same cloud region or connected through a dedicated private connection, the synchronization time is expected to be significantly shorter.

6. Business Performance After Migration

The most noticeable improvement after migrating to TiDB Cloud was business performance in OLAP workloads. Statistical analysis and complex queries became noticeably more efficient, reducing query wait times for business users and improving the overall query experience.

Going forward, we plan to explore enabling TiFlash, the columnar storage engine, based on specific business scenarios. This will allow us to further optimize analytical query performance and make better use of TiDB's HTAP architecture for workloads that combine row- and column-oriented processing.

Note: We have not yet completed a detailed analysis of application performance after the migration. Specific query performance comparisons will be added in a future update.

7. Conclusion and Recommendations

Overall Assessment

Overall, the migration from PostgreSQL to TiDB Cloud achieved the expected results.

SeaTunnel performed reliably as the migration tool. Both the full-load and incremental synchronization stages were completed successfully, with no data loss observed. TiDB Cloud delivered noticeable performance improvements for OLAP analytical workloads while significantly reducing the operational burden of database management.

Migration Recommendations for Other PostgreSQL Users

1. Tool selection: We recommend SeaTunnel for its rich connector ecosystem and active community. Its JDBC connector can quickly adapt to both PostgreSQL and TiDB.
2. Migration strategy: We recommend using a two-step full-load + incremental synchronization strategy, which provides better stability than the integrated mode.
3. Data consistency: Be sure to enable the EXACTLY_ONCE checkpoint mode and use upsert writes to prevent duplicate records.
4. Network planning: Network quality between the source environment and TiDB Cloud has a significant impact on full-load synchronization time. Where possible, prioritize deployment within the same cloud environment or use a dedicated private connection.
5. Secure connections: TiDB Cloud requires TLS. Configure sslMode=VERIFY_IDENTITY on the Sink side to ensure encrypted data transmission.