Handling Heterogeneous Database Migrations with DBeaver Data Transfer

Moving data between different database engines—such as migrating a legacy MySQL instance to a modern PostgreSQL cluster—often leads to a tedious cycle of exporting CSVs, writing custom Python scripts to handle type mismatches, and manually correcting SQL syntax. The primary challenge isn't just moving the rows, but ensuring that the target schema can actually accommodate the source data without truncation or type errors.

DBeaver’s Data Transfer engine solves this by acting as a transport orchestrator. Instead of relying on flat files, it allows for direct source-to-target mapping, enabling you to transform data types and rename columns in real-time during the migration process.

Managing Schema Mapping and Type Conversion

When transferring data between heterogeneous databases, a 1:1 type match is rare. For example, a MySQL TINYINT might be better represented as a PostgreSQL SMALLINT to allow for future growth, or a generic TEXT field might need to be mapped to a specific VARCHAR(N) to maintain performance on the target.

During the mapping phase of the Data Transfer wizard, DBeaver provides a grid where you can manually override the target data type. This is essential for handling custom ENUMs or spatial data types that do not have a direct equivalent in the destination engine. By adjusting these mappings before execution, you avoid the common "type mismatch" errors that typically crash bulk imports halfway through.

Preventing Memory Overflow in Bulk Loads

A frequent failure point when using local database clients for large migrations is the OutOfMemoryError. This happens when the client attempts to load too many rows into local RAM before pushing them to the target server. To prevent this, you must tune the transfer settings based on your available system memory:

• Fetch Batch Size: This controls how many rows DBeaver retrieves from the source in a single request. For most workstations, a value between 1,000 and 10,000 is the sweet spot for balancing speed and memory usage.
• Commit Interval: This defines how many rows are inserted into the target before a transaction is committed. A smaller interval prevents the target database's undo logs or transaction logs from filling up, though it may slightly increase total transfer time.

Worked Example: SQLite to PostgreSQL Migration

Consider a scenario where you are moving a products table from a local SQLite file to a remote PostgreSQL production instance.

1. Right-click the SQLite table in the Database Navigator and select Export Data.
2. Select Database as the target and click Next.
3. In the Mapping screen, locate the price column. SQLite uses REAL, but for financial data in PostgreSQL, manually change the target type to NUMERIC(10,2) to prevent floating-point rounding errors.
4. Navigate to Settings and set the Fetch batch size to 5000 and the Commit interval to 5000.
5. Click Proceed to execute the transfer.

To verify the result, run a row count check on both the source and target consoles:

-- Run on SQLite source
SELECT COUNT(*) FROM products;

-- Run on PostgreSQL target
SELECT COUNT(*) FROM products;

Constraints and Trade-offs

DBeaver's engine is powerful, but it does not automatically resolve relational dependencies. You should be aware of these limitations:

• Foreign Key Order: If you migrate multiple tables, DBeaver will not automatically determine the correct order based on foreign keys. You must migrate parent tables before child tables, or temporarily disable constraints on the target database.
• Proprietary Extensions: Specialized types like PostgreSQL JSONB indexes or custom extensions must be created via DDL (Data Definition Language) on the target before the transfer begins, as the wizard focuses on data and basic types rather than advanced indexing.

Automating with Saved Tasks

If this migration is part of a recurring synchronization pipeline, avoid repeating the wizard manually. At the end of the configuration, use the Save as Task option. This saves the mapping and batch settings into a DBeaver task, which can then be triggered via the UI task scheduler or executed through the command line, turning a manual migration into a repeatable process.