Restore Database from Backup

This article explains how to restore a database from a backup.

A database backup is stored in an archive volume, which can be located within the cluster (local archive volume) or on another system (remote archive volume). In case of a database instance failure or error, the database can be restored to a previous state using a backup.

This procedure can be carried out using the Administration API or ConfD.

Prerequisites

  • The database must be stopped. For more information, see Stop a Database.
  • The database that you are restoring the backup to must have the same number of active nodes as the one used to create the backup.
  • The version of the database that you are restoring the backup to must be based on the same LTS version as the backup version. For more information, see Product Life Cycle.

To restore a backup of an Exasol 7.1 database to an Exasol 8 database, see Migrate from Exasol 7.1 to Exasol 8.

Procedure - Administration API

The following examples use curl in a Linux terminal to send REST calls to endpoints in the Administration API. You can also use other interfaces and languages to interact with the API. For more information, see Administration API.

Placeholder values are styled as Bash variables, for example: $NODE_IP. Replace the placeholders with your own values.

The option --insecure or -k tells curl to bypass the TLS certificate check. This option allows you to connect to a HTTPS server that does not have a valid certificate. Only use this option if certificate verification is not possible and you trust the server.

  1. To get information about the database, send a GET request to the /api/v1/databases endpoint.

    curl --insecure -X "GET" \
    "https://$NODE_IP:4444/api/v1/databases" \
    -H "accept: application/json" \
    -H "Authorization: Basic $AUTH_TOKEN"

    The response includes the database ID, name, and version. Additional details are included depending on the configuration and platform.

  2. If the database is running, stop it now by sending a PUT request to the /api/v1/databases/$DATABASE_ID/stop endpoint:

    curl --insecure -X "PUT" \
    "https://$NODE_IP:4444/api/v1/databases/$DATABASE_ID/stop" \
    -H "accept: application/json" \
    -H "Authorization: Basic $AUTH_TOKEN"

  3. Determine the ID of the backup to restore.

    How to determine the ID for a backup depends on whether you are restoring the backup to the same database that the backup was taken on, or to a different database. In the latter case, the backup is said to have been taken on a foreign database.

    List backups from the same database

    To get information about all existing backups for the current database, send a GET request to /api/v1/databases/DATABASE_ID/backups. For example:

    curl --insecure -X 'GET' \
      'https://$NODE_IP:4444/api/v1/databases/$DATABASE_ID/backups' \
      -H 'accept: application/json' \
      -H 'Authorization: Basic $AUTH_TOKEN'

    List backups from all databases

    Backups taken on foreign databases cannot be listed using the Administration API. In this case you must use the ConfD job db_backup_list with the show_foreign parameter set to True. 

    confd_client db_backup_list db_name: MY_DATABASE show_foreign: True

    Get ID of backup from a foreign database

    The ID of a backup that was taken from a foreign database cannot be directly extracted, but it can be determined based on known information about the backup and how a backup ID is structured.

    Backup ID format

    A backup ID has the format VOLUME_ID BACKUP_NAME BACKUP_DATABASE_NAME.

    • VOLUME_ID corresponds to the ID of the archive volume containing the backup.

      • For local archive volumes, use the ConfD job st_volume_info to find the ID stored under “id”.

      • For remote archive volumes, use the ConfD job remote_volume_info to find the ID stored under “vid”.

        Remote archive volume IDs typically start at 10001.

    • BACKUP_NAME is the full path to the backup file, which can be further broken down into:

      BACKUP_DATABASE_NAME/id_BACKUP_ID/level_BACKUP_LEVEL/node_0/backup_TIMESTAMP

    • BACKUP_DATABASE_NAME is the name of the database that was backed up.

    Example of a backup ID: 
    10001 MY_DATABASE/id_1/level_0/node_0/backup_202406131208 MY_DATABASE

    To determine the path and filename, you may have to use a separate client to log in to the service where the remote archive is stored (for example, Amazon S3).

  4. To restore a backup, send a PUT request to the /api/v1/databases/DATABASE_ID/backups/restore endpoint, and include the backup ID and restore type for the backup in the request. For example:

    curl -k -X 'PUT' \
      'https://$NODE_IP:4444/api/v1/databases/$DATABASE_ID/backups/restore' \
      -H 'accept: application/json' \
      -H 'Authorization: Basic $AUTH_TOKEN' \
      -H 'Content-Type: application/json' \
      -d '{
      "backupId": "$VOLUME_ID $BACKUP_ID $DATABASE_ID",
      "restoreType": "blocking"
    }'

When the backup has been successfully restored, the database will automatically start.

Procedure - ConfD

The following examples use ConfD through the command-line tool confd_client, which is available on all database nodes. For more information, see ConfD.

Placeholder values are indicated with UPPERCASE characters. Replace the placeholders with your own values.

  1. Connect to EXAClusterOS (COS) on the cluster using c4 connect -t <DEPLOYMENT>[.<NODE>]/cos. For example:

    ./c4 connect -t 1.11/cos

    If you do not specify a node, c4 will connect to the first active node in the deployment.

    For more information about how to use c4 connect, see How to use c4.

  2. To find the name of the database, use the ConfD job db_list. For example:

    confd_client db_list
    - MY_DATABASE

  3. If the database is running, stop it now using the ConfD job db_stop. For example:

    confd_client db_stop db_name: MY_DATABASE

  4. To restore a database you must first determine the ID of the backup. How to determine the ID of a backup depends on whether you are restoring the backup to the same database the backup was taken on, or to a different database. In the latter case, the backup is said to have been taken on a foreign database.

    Get the ID of a backup taken from the same database

    To get the ID of a backup, use the ConfD job db_backup_list. For example:

    confd_client db_backup_list db_name: MY_DATABASE

    - bid: 1
      comment: ''
      dependencies: '-'
      expire: 2024-06-20 12:08
      expire_alterable: 10001 MY_DATABASE/id_1/level_0
      expired: false
      id: 10001 MY_DATABASE/id_1/level_0/node_0/backup_202406131208 MY_DATABASE
      last_item: true
      level: 0
      path: MY_DATABASE/id_1/level_0/node_0/backup_202406131208
      system: MY_DATABASE
      timestamp: 2024-06-13 12:08
      ts: '202406131208'
      usable: true
      usage: 0.011 GiB
      volume: r0001

    Get the ID of a backup taken from a foreign database

    The ID of a backup that was taken from a foreign database cannot be directly extracted, but it can be determined based on known information about the backup and how a backup ID is structured.

    Backup ID format

    A backup ID has the format VOLUME_ID BACKUP_NAME BACKUP_DATABASE_NAME.

    • VOLUME_ID corresponds to the ID of the archive volume containing the backup.

      • For local archive volumes, use the ConfD job st_volume_info to find the ID stored under “id”.

      • For remote archive volumes, use the ConfD job remote_volume_info to find the ID stored under “vid”.

        Remote archive volume IDs typically start at 10001.

    • BACKUP_NAME is the full path to the backup file, which can be further broken down into:

      BACKUP_DATABASE_NAME/id_BACKUP_ID/level_BACKUP_LEVEL/node_0/backup_TIMESTAMP

    • BACKUP_DATABASE_NAME is the name of the database that was backed up.

    Example of a backup ID: 
    10001 MY_DATABASE/id_1/level_0/node_0/backup_202406131208 MY_DATABASE

    To determine the path and filename, you may have to use a separate client to log in to the service where the remote archive is stored (for example, Amazon S3).

  5. To restore a database, use the ConfD job db_restore with the parameter values that you determined in the previous steps.

    Parameter name Data type Description
    db_name string The name of the database to be restored.
    backup_id string

    The ID of the backup, in the following format:

    VOLUME_ID BACKUP_NAME BACKUP_DATABASE_NAME

    For more details, see Backup ID format.
    restore_type string

    The type of restore.

    Allowed values are blocking, nonblocking, and virtual access.

    For information about restore types, see Restore Essentials.

    For example, the following command performs a blocking restore into the database MY_OTHER_DATABASE of a level 0 backup on volume 10001 that was taken from the database MY_DATABASE:

    confd_client db_restore db_name: MY_OTHER_DATABASE backup_id: '10001 MY_DATABASE/id_1/level_0/node_0/backup_202406131208 MY_DATABASE' restore_type: blocking

Monitor the restore process

To monitor the status of the restore process, use the ConfD job db_backup_progress. The progress of the restore process is returned as a percentage where 100 means restore is completed. For example: 

confd_client db_backup_progress db_name: MY_DATABASE
Comment: Restore is active
Files: []
Level: 0
Name: MY_DATABASE/id_1/level_0/node_0/backup_202406131208
Progress: 7
Type: Restore
Volume ID: 10001
...
confd_client db_backup_progress db_name: MY_DATABASE
Comment: Restore has been successfully finished
Files: []
Level: 0
Name: MY_DATABASE/id_1/level_0/node_0/backup_202406131208
Progress: 100
Type: Restore
Volume ID: 10001

When the backup has been successfully restored, the database will automatically start.

Verification

To verify that the database has been restored, connect to the database and view the latest entries in EXA_SYSTEM_EVENTS:

SELECT MEASURE_TIME, EVENT_TYPE FROM EXA_SYSTEM_EVENTS ORDER BY MEASURE_TIME DESC;

You should see entries with RESTORE_START and RESTORE_END similar to the following:

MEASURE_TIME EVENT_TYPE
2021-10-05 13:18:47.351000 STARTUP
2021-10-05 13:18:42.727000 RESTORE_END
2021-10-05 13:18:40.503000 RESTORE_START