Migrating Table Data Between Different Database Engines Using DBeaver Data Transfer

The Challenge of Cross‑Database Migration

Moving data between two different database engines—such as migrating a staging table from PostgreSQL to a production MySQL instance—often requires complex ETL scripts or intermediate CSV exports. The primary risk is data type mismatch, where a source column type (e.g., JSONB) has no direct equivalent in the target system, leading to silent truncation or execution failure.

The takeaway: DBeaver’s Data Transfer wizard lets you map columns manually and tune batch sizes to prevent JVM memory crashes, providing a GUI‑driven way to handle schema discrepancies without writing custom migration scripts.

Prerequisites

• DBeaver Community or Enterprise Edition installed.
• Active, authenticated connections to both the source and target databases.
• Permissions on the target database to CREATE TABLE (if the table does not exist) and INSERT data.

Step‑by‑Step Migration Procedure

1. Initiating the Transfer

You can transfer an entire table or a specific subset of data defined by a query.

1. Right‑click the source table in the Database Navigator (or a result set in the SQL editor) and select Export Data.
2. In the target selection window, choose Database and click Next.

2. Mapping Source to Target

This is the most critical stage to avoid type mismatches. DBeaver attempts to auto‑map columns based on name and type, but cross‑engine transfers often require manual intervention.

• Target Table: Select an existing table or define a new table name.
• Column Mapping: Review the mapping grid. If the target type is incompatible, click the target type cell to manually select a compatible data type (e.g., change UUID to VARCHAR(36)).
• Transfer Method: Choose Insert for new data or Upsert (if supported by the target engine) to update existing records based on a primary key.

3. Performance Tuning and Batching

Transferring millions of rows without configuration can trigger an OutOfMemoryError in the DBeaver Java Virtual Machine (JVM). Adjust the following in the Extraction settings:

• Fetch size: Set to 10,000 to limit how many rows are held in memory at once.
• Insert batch size: Set to 1,000 to group insert statements, reducing the number of network round‑trips to the target server.

4. Execution and Task Saving

Before clicking Finish, you can save these settings as a Database Task. This allows you to re‑run the migration (e.g., weekly syncs) from the Database Tasks view without re‑configuring the mappings.

Example Configuration: PostgreSQL to MySQL

Source (PostgreSQL)	Target (MySQL)	Mapping Adjustment
timestamp with time zone	DATETIME	Manual cast to UTC
jsonb	JSON or LONGTEXT	Verify target version supports JSON
bigint	BIGINT	Auto‑mapped

Verification and Troubleshooting

Once the transfer completes, do not assume success based on the progress bar alone. Perform these checks:

1. Row Count Validation: Run SELECT COUNT(*) on both source and target tables to ensure no rows were dropped.
2. Execution Log: Review the Execution Log tab in the wizard. Look for "Skipped" rows, which usually indicate constraint violations (e.g., duplicate keys).
3. Data Integrity Sample: Run a query on the target database for a row containing special characters or long strings to ensure encoding was preserved.

Handling Common Failures

• Foreign Key Violations: If the target table has foreign keys, the transfer will fail if the referenced data isn’t already present. Solution: Migrate tables in dependency order (parents first, then children).
• JVM Memory Crash: If DBeaver freezes, reduce the Fetch size and Batch size in the extraction settings.

Rollback Procedure

Because this operation modifies the state of the target database, use the following to revert changes if the data is corrupted:

1. If the wizard created a new table:

   DROP TABLE target_table_name;

2. If data was inserted into an existing table:

   DELETE FROM target_table_name
   WHERE migration_timestamp_column > 'start_time';

   (This requires the target table to have a timestamp for tracking).