Restore Database from Backup
This section 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 either 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 on 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: $EXASOL_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 uses a self-signed certificate or a certificate that is not valid. Only use this option if certificate verification is not possible and you trust the server.
-
To get information about the database, send a
GET
request to the/api/v1/databases
endpoint.curl --insecure -X "GET" \
"https://$EXASOL_IP/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.
-
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://$EXASOL_IP/api/v1/databases/$DATABASE_ID/backups' \
-H 'accept: application/json' \
-H 'Authorization: Basic $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 toTrue
.Get ID of backup from a foreign database
A backup ID has the structure VOLUME_IDBACKUP_NAMEBACKUP_DATABASE_NAME.
-
VOLUME_ID is the ID of the archive volume containing the backup.
-
BACKUP_NAME is the path and name of the backup file, consisting of the following parameters:
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.
For example:
-
-
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:
When the backup has been successfully restored, the database will automatically restart.
Procedure - ConfD
The following examples use ConfD through the command-line tool confd_client, which is accessible on all database nodes. You can also access ConfD through XML-RPC in your own Python programs. For more information, see ConfD.
Placeholder values are indicated with UPPERCASE characters. Replace the placeholders with your own values.
-
To find the name of the database, use the ConfD job db_list. For example:
-
To restore a database you need to supply 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 ID of backup from the same database
To get the ID of a backup, use the ConfD job db_backup_list:
Get ID of backup from a foreign database
The ID of a backup from a foreign database can be determined based on known information about the backup. In general, a backup ID has the format
VOLUME_IDBACKUP_NAMEBACKUP_DATABASE_NAME
. For example:The VOLUME_ID corresponds to the ID of the archive volume containing the backup. 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 10002.
- For local archive volumes, use the ConfD job st_volume_info to find the ID stored under “id”.
The BACKUP_NAME is the path and name of the backup file, which can be found by viewing the file structure of the volume using a separate client. For example, if your remote archive volume is in S3, you can log in to S3 and view the file structure and eventual file name. The BACKUP_NAME can be further broken down into:
BACKUP_DATABASE_NAME/id_BACKUP_ID/level_BACKUP_LEVEL/node_0/backup_TIMESTAMP
The BACKUP_DATABASE_NAME is the name of the database that was backed up.
-
To restore a database, use the ConfD job db_restore and fill in the information from the previous steps. For more information about restore types, see Restore Essentials. The command with all placeholders looks like:
confd_client -c db_restore -a '{db_name: DATABASE_NAME, backup_id: VOLUME_ID BACKUP_DATABASE_NAME/id_BACKUP_ID/level_BACKUP_LEVEL/node_0/backup_TIMESTAMP BACKUP_DATABASE_NAME, restore_type: RESTORE_TYPE}'
For example, the following command performs a blocking restore of a level 0 backup with the id 12 from the database prod_db into the database prod_db_new:
When the backup has been successfully restored, the database will automatically restart.
Verification
To verify that the database has been restored, connect to the database and view the latest entries in EXA_SYSTEM_EVENTS:
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 |