Using DBeaver’s Data Transfer Wizard to Move Tables Between PostgreSQL and MySQL

Problem: copying data across heterogeneous databases

When you need to refresh a reporting table in MySQL from a master copy in PostgreSQL, writing custom export/import scripts can be error‑prone and time‑consuming. DBeaver’s built‑in Data Transfer wizard offers a graphical way to move data while handling JDBC‑based type conversion, DDL generation, and logging.

Thesis

The Data Transfer wizard lets you define source and target connections, preview column mappings, tune fetch size and commit interval, and verify results through integrated task logs—all without leaving the DBeaver UI.

1. Prepare the connections

First, ensure both databases are registered in DBeaver’s Connection Manager.

1. Open Database → New Connection and choose the PostgreSQL driver.
2. Fill in host, port, database name, username, and password; test the connection.
3. Repeat for MySQL, selecting the MySQL driver.

Both connections should appear under the Database Navigator pane.

2. Launch the wizard and configure the transfer

Right‑click the source schema (e.g., public) and choose Tools → Data Transfer.

• Source: automatically set to the selected connection and schema.
• Target: click Change and pick the MySQL connection and the schema where you want the table.
• Objects: expand the source schema, check the table you wish to copy (e.g., employees).
• Mapping: the wizard shows a grid of source columns → target columns. By default, names match; you can rename, exclude, or change the target data type if needed.

Click Next to reach the DDL preview step. Here DBeaver generates a CREATE TABLE statement for the target based on JDBC metadata. Review it; if the target already exists, you can choose Truncate before insert or Append.

3. Tune performance and handle type mismatches

Before executing, open the Options tab:

• Fetch size: number of rows retrieved per JDBC call. Increase from the default 100 for large tables (e.g., 1000) to reduce round‑trips, but monitor memory.
• Commit interval: rows processed per transaction. A value of 500–2000 balances log size and rollback safety.
• Error handling: choose Stop on error to abort the whole transfer, or Continue to skip problematic rows and log them.

If the preview shows a column marked with a warning icon, the JDBC driver could not map the source type precisely (e.g., PostgreSQL numeric to MySQL decimal). In that case, manually set the target type in the mapping grid or preprocess the column with a CAST in a view.

4. Execute, monitor, and verify

Press Finish. DBeaver creates a task that appears in the Task Navigator with a spinner. Double‑click the task to view its log:

• Look for lines like Started transfer of 3 tables, Rows fetched: 1245, Rows inserted: 1245.
• Any warnings about truncated values or conversion errors will appear here.
• When the task finishes, the status changes to Success or Failed.

After success, verify the result:

1. In the MySQL connection, run SELECT COUNT(*) FROM employees; and compare the count to the source.
2. Spot‑check a few rows: SELECT * FROM employees LIMIT 5; and verify values match the PostgreSQL source.
3. Check the generated DDL (if you chose to create the table) by opening the target table’s DDL editor.

Trade‑off and limitation

The wizard loads data into memory according to the fetch size and commit interval. For very large tables (hundreds of millions of rows) this can cause noticeable RAM usage and network saturation. A practical mitigation is to enable Chunked transfer via the Advanced tab, which splits the work into multiple tasks based on a primary‑key range, or to export to CSV and load with native tools.

Actionable closing

For regular synchronization, save the wizard configuration as a Data Transfer template (available from the Options menu) and reuse it or invoke it from the command line with dbeaver-cli -dataTransfer <template>. Combine the template with a scheduler (cron, Windows Task Scheduler) to automate nightly refreshes while keeping an eye on the task logs for any anomalies.