PostgreSQL Tutorial: Migrate Data using DBeaver

October 26, 2023

Summary: Data Migration in DBeaver provides the functionality to transfer data between different databases or between tables within the same database. This tutorial walks you through the steps to execute a data migration using DBeaver.

Table of Contents

Step 1 Define the data source

To initiate the data migration, you need to select your data source. Follow the steps below:

  1. Navigate to the Database Navigator.
  2. Select one or multiple tables that you want to export.
  3. Right-click to open the context menu.
  4. Choose Export Data from the options.

img

Tip: You can export data from a custom SQL query. Execute the query and then choose Export Data from the results context menu.

Step 2 Define data transfer target type

After selecting the data source, the next step is to specify the type of destination for the data transfer. Choose Database type as the data transfer target and press Next.

img

Step 3 Tables mapping

Once you have chosen Database as the transfer target type, the next step involves mapping data. This process includes specifying options, selecting the target container, and setting other configurations. Configure your data mapping settings and press Next to proceed to the next step.

img

Available options

Buttons:

Icon Option Description
img Browse Allows selection of target container.
img Configure Opens additional settings. For details, see the Configure section.
img Preview data Shows a preview of the data to be transferred.
img Up Moves the selected mapping closer to the head of the queue.
img Down Moves the selected mapping closer to the tail of the queue.
img Mapping Rules Choose how new tables and column names are transformed when transferring data. For details, see Mapping Rules section.
img Auto assign Automatically populates the Target column based on the source.
img Choose… Specify the target container where the data will be transferred in.

Fields:

Option Description
Target container Defines the database or schema for the data transfer. For more details, see the Target Container section.
Source Displays the names of selected tables and their columns.
Target Shows the names of destination tables.
Mapping Lists the available actions for data transfer. For more details, see the Mapping section.
Transform Allows for the transformation of column values during the data transfer. For more details, see the Transform section.

Target container and target table

Specify target container

Press the Choose… button to select the container where the data will be transferred.

Define a target table

img

You can specify the target table where the data will be transferred in multiple ways:

  • Manual Entry: Click on a cell in the Target column and manually enter the name of the table where you wish to transfer the data.

  • Drop-down list: Use the drop-down list next to the Target column to choose among the following options:

    • Pre-existing table names.
    • Skip: Skips the data transfer for this table.
    • Browse: Opens the Choose Target Table window
  • Browse button: Alternatively, press the Browse button to select a table from the existing tables in the target container.

img

Mapping

To change the mapping type, click a cell in the Mapping column of Table mapping dialog box and select the required mapping type.

img

Action Description
Create Transfers source data into a newly created table or column in the target container.
Skip Does not transfer the source data.
Existing Transfers source data to an existing table in the target container.
Recreate Recreate the table, which means that the available data, keys, indexes, and other possible entities of the existing table will be lost.

Tip: If the cells are marked with img, it means that in the target table, there are no columns with matching names, otherwise the names will be filled in automatically.

Transform column values

You may also want to transform the values of some columns during the transfer. To do that, define column transformers by clicking on corresponding cells in the Transform column. You can choose one of three options:

img

Option Description
Set to NULL All values in the corresponding column are set to null.
Constant Sets column value to a constant value.
Expression Uses JEXL expressions to calculate the column’s value.

Configure

Click the Configure button to open the Configure metadata structure window. This window allows you to delve into additional settings are distributed across the following tabs:

Column mapping

By navigating to the Column mapping, you can explore detailed mapping between the source and target columns.

img

Element Description
Source Column Contains names of columns existing in the selected source table.
Source Type Lists the data types assigned to the columns in the selected source table.
Target Column Contains names of columns in the target table where the data from the source column will be transferred.
Target Type Lists the data types that will be assigned to the columns in the target table.
Mapping Contains the list of actions to be applied to the data on data transfer.
Transform Displays transformations for the data in a column during the transfer.

Important: Data types that are supported in the source database may not be supported in the target, and vice versa. To set a data type for a target column, click the cell in the Target Type column and choose from the dropdown list.

Table properties

By selecting the Table properties tab, you can modify properties of the target table, such as:

Property Description
Tablespace Specifies the tablespace for the target table.
Partition By Sets the partitioning for the target table.
Comment Allows you to add comments to the target table.

Note: The availability of these settings may vary depending on the database and version you are using.

Target DDL

By selecting the Target DDL tab, you can view the SQL script that will be executed during the data transfer.

If you’re exporting data to a new table or recreating an existing one, the tab will display the necessary SQL statements. If not, the DDL tab will remain empty.

Mapping Rules

Clicking the Mapping Rules button opens a window that provides options for customizing how new tables and column names are transformed during the data transfer.

img

In the window, the following settings are available:

Option Description
Name case Sets the letter casing for table and column names. Choices include Default, Upper case, and Lower case.
Replace spaces Determines how spaces in table and column names are handled. Options are: Do not replace, Replace with underscore, and Remove, convert to CamelCase.
Max data type length Sets the maximum length for data types.
Save current changes to global settings If checked, saves the current mapping rules to global settings. Otherwise, they will be saved at the data source settings level.
Global settings Opens the global settings window for Names Mapping Rules. Alternatively, to open global settings, go to Window -> Preferences -> Connections -> Data Transfer

In addition to using the Global settings button, you can also access these settings by navigating to Window -> Preferences -> Connections -> Data Transfer.

Important: After modifying the Mapping Rules settings, you will be prompted to confirm your changes. Modifying the Mapping Rules may result in the loss of names that were already changed.

Keyboard Shortcuts

The following keyboard shortcuts for easy navigation within the mapping table area of the Tables mapping tab are supported:

Shortcut Action
Up Move one row up.
Down Move one row down.
Right Expand list of source table columns.
Left Collapse list of source table columns.
Space Auto-assigns the target.
Del Sets mapping type to skip.

Step 4 Extraction settings

After setting up your table mappings, the next step is to define how the data will be extracted from the source. The Extraction settings tab offers various options to optimize this process. Configure these settings to suit your specific data transfer requirements and press Next.

img

Option Description
Maximum threads Defines the number of threads to be used for data transfer.
Extract type Choose Single Query for smaller data loads. Use By Segments for larger data migrations. You can also set the Segment Size value when this option is selected.
Open new connection(s) If checked, a new connection is established, ensuring data transfer does not affect other database operations.
Select row count Enables a progress bar to display data migration status.
Fetch size Indicates the number of rows fetched per server round trip, affecting extraction performance.

Step 5 Data load settings

img

After configuring the extraction settings, you’ll need to specify how the data will be loaded into the target database. The Data load settings tab provides a range of options to control this part of the process. Adjust the settings according to your needs and then press Next.

Data load settings tab defines how the extracted data will be pushed to the target. The following options are available.

Option Description
Transfer auto-generated columns Fill in or skip columns marked with the “autogenerated” status. Some databases accept values in such columns, while others will throw a syntax error.
Truncate target table(s) before load Select this checkbox only if you want all the data to be cleared from the target table. Be very careful with this option!
Disable referential integrity checks during the transfer Disabling constraints in the target table. This setting prevents database errors by temporarily disabling the constraints. However, please note that not all databases support this functionality.
Replace method Read our guide on Data Import and Replace to learn more about the replacing method option.
Open new connection(s) Use this option to speed up data transfer. If selected, a new connection will be opened and the data transfer will not interfere with other calls to the database where data is being transferred to.
Use transactions This option allows you to speed up the data transfer and to define the number of rows for each transaction by setting the Commit after insert of parameter.
Do Commit after row insert Performing a commit after a certain number of inserted rows. This setting specifies that a commit operation should be executed after a specified number of rows have been inserted into the table.
Use multi-row value insert Use multi-row insert with extended values number for higher performance. Database-specific setting.
Skip bind values during insert This option can drastically increase performance for some drivers like Redshift by skipping a process of binding values and setting them directly, but it opens up a vulnerability to SQL injections. Not recommended if you are not sure of imported file contents.
Disable batches Select this checkbox if you want to disable the use of batch imports. The import will be made row by row. Enabling this function will show all import errors, but make the import process slower.
Ignore duplicate rows errors In the import process, if a database encounters a duplicate key from the import row in the target table, such errors are ignored, and the import operation continues without failure.
Use bulk load Bypasses transaction settings and loads the entire dataset using the native tool provided by the database.
Open table editor on finish If selected, the table editor is to be opened when data transfer is finished.
Show finish message If selected, a notification message will be shown when the transfer is finished.
Send results by E-Mail Sends data transfer results by E-Mail on finish.

Step 6 Confirm

img

The final step before initiating the data transfer is the Confirm tab. This tab provides a summary of all configurations set up in the previous steps. It serves as a last review to ensure all settings are correct.

Here is a table summarizing the configurations:

Option Description
Source Container Displays the source container from which data will be pulled.
Source Shows the specific source within the container.
Target Container Displays the target container to which data will be pushed.
Target Shows the specific target within the container.
Source Settings Summarizes settings related to data extraction from the source.
Target Settings Summarizes settings related to data loading into the target.

These settings are final and cannot be changed at this stage. If all configurations are correct, click Finish to start the data transfer.

Tip: You can save these configurations for future use and create a task related to the data transfer operation by selecting Save task. This option consolidates your settings into a reusable task.

Upon clicking Proceed, the data transfer process will initiate.

Step 7 Export completion notification

If there are no errors, you will see a notification window indicating the successful completion of the export task. You can continue working with your database during the export process, as it will be performed in the background.

See More

Import Data using DBeaver

Export Table using DBeaver

Merge Data using DBeaver