DBeaver Documentation

DOWNLOAD pdf

Backup Restore

Note: This feature is available in Community, Enterprise, Ultimate and Team editions only.

Overview

This article provides an in-depth guide to the Backup and Restore features available in DBeaver. DBeaver supports a wide range of databases for backup and restore operations:

  • Classic databases:
    • PostgreSQL
    • MySQL
    • MariaDB
    • TiDB
    • SingleStore
    • EnterpriseDB
    • Greenplum
    • YugabyteDB
    • CrateDB
    • Materialize
    • TimescaleDB
    • Yellowbrick
  • Cloud databases:
    • AlloyDB
    • Google Cloud SQL for PostgreSQL
    • Google Cloud SQL for MySQL

The native backup and restore differs from the standard DBeaver Data Transfer feature by focusing on creating and applying database backups using specialized utilities. These tools are tailored for high-performance interactions with databases, potentially accelerating the backup and restore processes compared to general data transfer methods.

These functions can be accessed from the Context Menu's Tools or the Main Menu's Database -> Tools.

Database specific settings

Different database systems possess distinct settings for backup and restore processes. This section presents the settings specific to MySQL and PostgreSQL.

MySQL

Backup MySQL database

To initiate a backup:

  1. Select the desired database.

  2. Right-click on the database and choose Tools -> Dump database.

  3. The Dump window will appear. Select the necessary objects and click Next.

  4. The Export configuration tab will be displayed. Here you can find the following checkboxes and fields:

Option/Field Description
Execution Method Method for execution. The default is Normal (no locks).
Execution Method options: Normal (no locks): This is the default method. It performs the dump without acquiring any locks on the tables. Suitable for databases where minimal disruption is required during the dump process.
Online backup in single transaction: This method ensures the consistency of the database by performing the dump within a single transaction. It's ideal for databases that support transactional storage engines like InnoDB. The backup occurs without interrupting other database operations but requires a higher isolation level.
Lock all tables: As the name suggests, this method locks all tables in the database during the dump process. It ensures complete consistency of the dump but may interrupt other database operations. Suitable for databases or situations where you want a consistent snapshot and are okay with potential disruptions.
No CREATE statements Do not include CREATE statements in the dump.
Add DROP statements Include DROP statements before CREATE statements.
Disable keys Disable keys during the dump process.
Extended inserts Use extended INSERT statements in the dump.
Dump events Include events in the dump.
Additional comments Include comments in the dump.
Remove DEFINER Remove DEFINER clause from the dump.
Dump binaries in hex Display binary data as HEX in the dump.
Structure only Only dump table structures, not data.
Output folder Specify the directory where the dump file will be saved. For Ultimate Edition, Team Edition, and CloudBeaver versions, users have the option to save to a remote file system using the Browser remote file system button .
Extra command args Provide additional command-line arguments. See details in the Extra command arguments section.
Authenticate Click to open the Authentication window where you can fill in the Username/Password for override.
Reset to default Reset credentials to their default values.
Override host credentials Use different host credentials, if necessary.
Local Client Open a window to specify the path to the Local client. More about setting it up.

Note: The particular set of configuration options depends on the database type.

  1. After configuring the settings, click Start.

  2. Upon successful completion, a notification will appear with information about the process.

  3. The backup file can be found in the folder specified during the Export configuration step.

Restore MySQL database

To initiate a restore:

  1. Select the desired database.

  2. Right-click on the database and choose Tools -> Restore database.

  3. The Restore window will appear. In this window you have to provide the path to the SQL file you wish to restore from in the designated field. Example path: /path/to/file.sql

Below are the settings for the restore process:

Setting Name Description
Log Level Choose the level of logging for the restore process.
Log Level options: Normal: Provides standard logging information. It captures the essential details of the restore process, ensuring that the logs remain concise and focused on primary events.
Verbose: Gives more detailed logging information than the Normal level. It includes additional context and information, making it suitable for situations where you want to understand the process more in-depth without getting into debugging details.
Debug: Offers the most detailed logging information. It captures all events, including low-level operations and potential issues. This level is ideal for troubleshooting problems or understanding the inner workings of the restore process.
Extra command args Provide additional command-line arguments. See details in the Extra command arguments section.
Disable foreign key checks Disable foreign key checks during the restore process.
Authenticate Click to open the Authentication window where you can fill in the Username/Password for override.
Reset to default Reset credentials to their default values.
Override host credentials Use different host credentials, if necessary.
Local Client Specify the path to the Local client.

Tip: In the input field for specifying the path to the SQL file, you can choose a file from a local directory or use the Browser remote file system button to select a file from a remote file system. This functionality is accessible > to users of the Ultimate Edition, Team Edition, and CloudBeaver.

  1. After configuring the settings, click Start.

  2. Upon successful completion, a notification will appear with information about the process.

PostgreSQL

Backup PostgreSQL database

To initiate a backup:

  1. Select the desired database.

  2. Right-click on the database and choose Tools -> Backup.

  3. The Dump window will appear. Select the necessary objects and click Next.

  4. The Backup settings tab will be displayed. Here you can find the following checkboxes and fields:

Setting Description
Format Choose the format for the dump. Options include Directory, Tar, Custom, and Plain.
Format options: Directory: Splits the output into one file per table. Suitable for larger databases as it facilitates parallel restoration.
Tar: Produces archive in tar format. Useful for backups since it can be read by standard tools.
Custom: A flexible format that allows selective restore and other operations.
Plain: Produces plain-text SQL script file. It can be used with psql for restoring.
Compression Set the compression level for the dump, ranging from 0 (no compression) to 9 (maximum compression).
Encoding Select the character encoding for the dump. The available encodings depend on database configuration and locale.
Use SQL INSERT instead of COPY for rows Use the INSERT command instead of the COPY command for row data.
Do not backup privileges (GRANT/REVOKE) Exclude privilege commands (GRANT/REVOKE) from the dump.
Discard objects owner Exclude the ownership information from the dump.
Add drop database statement Include a statement in the dump to drop the database before restoring.
Add create database statement Include a statement in the dump to create the database when restoring.
Output folder Specify the directory where the dump file will be saved. For Ultimate Edition, Team Edition, and CloudBeaver versions, users have the option to save to a remote file system using the Browser remote file system button.
File name pattern Define the naming pattern for the dump file, with variables like ${database} and ${timestamp} to customize the filename.
Extra command args Provide additional command-line arguments. See details in the Extra command arguments section.
Authentication Click to open the Authentication window where you can fill in the Username/Password for override.
Reset to default Reset credentials to their default values.
Override host credentials Use different host credentials, if necessary.
Local Client Specify the path to the Local client.

Note: The particular set of configuration options depends on the database type.

  1. After configuring the settings, click Start.

  2. Upon successful completion, a notification will appear with information about the process.

  3. The backup file can be found in the folder specified during the Backup settings step.

Global PostgreSQL database backup

When performing a Global PostgreSQL database Backup, the entire database is dumped, including roles and tablespaces. This differs from standard backup procedures where only specific schemas and their contents can be selected. Additionally, multiple databases can be chosen for backup at once in the global method.

To initiate a global backup:

  1. Select the desired database.

  2. Right-click on the database and choose Tools -> Global Backup.

  3. The Global Dump window will appear. Select the necessary objects and click Next.

  4. The Backup settings tab will be displayed. Here you can find the following checkboxes and fields:

Setting Description
Encoding Set the character encoding for the backup.
Dump only the object definitions, not data Includes only the structure of the database objects, excluding the data.
Dump only global objects, no databases Includes only global objects and excludes individual databases.
Dump only roles Includes only user roles.
Dump only tablespaces Captures only tablespaces without the databases.
Do not backup privileges (GRANT/REVOKE) Excludes privilege statements like GRANT and REVOKE.
Discard objects owner Excludes the ownership information of the database objects.
Dump passwords for roles Includes passwords associated with the user roles.
Output folder Specify the directory where the dump file will be saved. For Ultimate Edition, Team Edition, and CloudBeaver versions, users have the option to save to a remote file system using the Browser remote file system button .
File name pattern Define the naming pattern for the dump file, with variables like ${database} and ${timestamp} to customize the filename.
Extra command args Provide additional command-line arguments. See details in the Extra command arguments section.
Authenticate Click to open the Authentication window where you can fill in the Username/Password for override.
Reset to default Reset credentials to their default values.
Override host credentials Use different host credentials, if necessary.
Local Client Specify the path to the Local client.
  1. After configuring the settings, click Start.

  2. Upon successful completion, a notification will appear with information about the process.

  3. The backup file can be found in the folder specified during the Global backup settings step.

Restore PostgreSQL database

To initiate a restore:

  1. Select the desired database.

  2. Right-click on the database and choose Tools -> Restore.

Setting Description
Format Choose the format for the restore operation. Options include Directory, Tar, Custom, and Plain.
Format options: Directory: Assumes the input consists of one file per table. Suitable for larger databases as it supports parallel restore.
Tar: Assumes the input is an archive in tar format. This is useful when restoring backups made in tar format.
Custom: A flexible input format that allows selective restore and other operations.
Plain: Assumes the input is a plain-text SQL script file. Suitable for scripts that can be executed with psql.
Clean (drop) database objects before recreating them Drop database objects before recreating them from the backup.
Create database Create a new database from the backup.
Discard objects owner Exclude ownership information during the restore.
Backup file Specify the path to the backup file that you wish to restore. For Ultimate Edition, Team Edition, and CloudBeaver versions, use the Browser remote file system button to select a backup file from a remote file system via Cloud Storage.
Extra command args Provide additional command-line arguments. See details in the Extra command arguments section.
Authentication Click to open the Authentication window where you can fill in the Username/Password for override.
Reset to default Reset credentials to their default values.
Local Client Specify the path to the Local client.
  1. After configuring the settings, click Start.

  2. Upon successful completion, a notification will appear with information about the process.

Additional features

Execute Script Command

In DBeaver, besides the standard backup and restore operations, there's an additional capability to run scripts directly through the Execute script command. This functionality uses native database clients.

Steps to use the Execute script command:

  1. Right-click on the desired database in the navigator tree.
  2. Navigate to Tools -> Execute script.
  3. A window will appear prompting you to provide the path to your script.

Tip: In the input field for specifying the path to the SQL file, you can choose a file from a local directory or use the Browser remote file system button to select a file from a remote file system. This functionality is accessible > to users of the Ultimate Edition, Team Edition, and CloudBeaver.

The Execute script command provides an alternative way to perform database operations that may not be covered by the traditional restore functionality. This includes running complex scripts or batch operations that require direct execution in the database's native environment.

Extra command arguments

The Extra command args field allows you to input additional command-line arguments to further customize the dump process. This offers more specific control over the operation.

For example, when configuring backup/restore for PostgreSQL, you may want to exclude specific tables. To exclude a table named employee_data, use the --exclude-table option. Enter --exclude-table=employee_data in the Extra command args field during the setup process.

By doing this, the resulting process won't contain any data or structure from the employee_data table.

Note: Always consult the database's official documentation for a comprehensive list and explanation of available command-line arguments.

Dump and restore operations as tasks

You can set up both restore and dump operations as tasks. To save an operation as a task, click the Save task button during the dump/restore process. For detailed instructions and features related to task management in DBeaver, refer to Task management article.