Virtual Access on Backup

This article explains how to configure virtual access to a database backup.

You can access a database backup using a virtual database instance. This method is especially useful when you want to quickly recover specific objects from a backup without doing a full restore.

This procedure is carried out using ConfD.

Virtual database instances do not count toward the license limit.

Prerequisites

  • The backup must be stored in a local archive volume. Virtual access is not supported for remote backups.

  • The backup must not have an expire date.

  • You must have sufficient free main memory in your database for the virtual instance. If the database is configured to use all available memory, you must prepare for a short downtime while adjusting DB RAM. For more details, see Calculate DB RAM .

  • Your network settings must allow traffic on the port that you configure for the virtual instance.

Calculate DB RAM 

You must have sufficient DB RAM available to be able to create the virtual instance that you will use to access the backup. To calculate the DB RAM that will be required for the virtual instance, use this formula: 

Number of active nodes * 4 GiB = DB RAM for virtual instance
Example:
  • The database is running on 4 active nodes and has 112 GiB of DB RAM
  • Each node has 31 GiB of main memory
  • The amount of data is very small (small tables), which means 4 GiB DB RAM per node is enough.

Using our formula, the DB RAM required for the virtual instance is: 

4 * 4 GiB = 16 GiB DB RAM

If your database is configured to use all the available memory as DB RAM, you will not be able to create a virtual instance. You must therefore decrease the amount of DB RAM used by the database. To calculate how much you need to reduce it, use this formula: 

DB RAM - Virtual Instance DB RAM = Reduced DB RAM

Using the values from our example, you must reduce the DB RAM used by the database to 96 GiB to be able to create a virtual database instance: 

112 GiB - 16 GiB = 96 GiB

To adjust DB RAM you must stop and restart the database, which means a short downtime is required.

Set up virtual access

The following examples use 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.

Step 1: Adjust DB RAM (if needed)

  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.

    confd_client db_list
    ...
    - MY_DATABASE

  3. Stop the database using the ConfD job db_stop. 

    confd_client db_stop db_name: MY_DATABASE

  4. To get the current DB RAM in MiB, use the ConfD job db_info. 

    confd_client db_info db_name: MY_DATABASE | grep mem_size 
    ...
    mem_size: 114688

  5. To change the DB RAM value, use the ConfD job db_configure with the following parameters:

    Parameter Name Data Type Description
    db_name string The name of the database
    mem_size string Amount of DB RAM as a string value consisting of an integer + unit in MiB, GiB, or TiB
    Example:
    confd_client db_configure db_name: MY_DATABASE mem_size: '98304 MiB'

  6. Start the database using the ConfD job db_start.

    confd_client db_start db_name: MY_DATABASE

Step 2: Create a data volume for the virtual instance

A temporary data volume is required for the virtual database instance. This volume is not used to store data.

To create the data volume, use the ConfD job st_volume_create with the following parameter settings:

Parameter name Data type Description
name string Enter a name for the new data volume
disk string Disk name in the backup data volume
type string data
size string

Size of the new data volume as a string value consisting of an integer + unit MiB, GiB, or TiB
num_master_nodes integer The number of master nodes in the backup data volume
nodes list List of node IDs (integers) of the active nodes in the backup data volume
redundancy integer 1
owner tuple, list Owner ID and owner group ID in the backup data volume

The number of active nodes in the virtual instance must be exactly the same as the number of nodes in the backup.

Example:
confd_client st_volume_create name: VIRTUAL_DATA disk: disk2 type: data size: '20 GiB' num_master_nodes: 4 nodes: '[11, 12, 13, 14]' redundancy: 1 owner: '[500, 500]' 

Step 3: Create a virtual database instance

The next step is to create a virtual database instance that will use the data volume that you created in the previous step.

  • The connection port for the virtual database must be different from the port used by other databases. In this example, we use port 9563 for the virtual database.

  • The port used for the virtual database must be open in your firewall settings.

  • The number of nodes in the virtual instance must be exactly the same as the number of active nodes in the backup.

  • The required amount of virtual DB RAM depends on the amount of data to be restored. We recommend minimum 4 GiB per node for a small amount of data, and maximum 5% of the memory capacity of the node. See also Calculate DB RAM .

To create the virtual database instance, use the ConfD job db_create with the following parameters:

Parameter name Data type Description
db_name string A unique name for the new database
version string The database version, for example 8.26.0
data_volume_name string

The name of the data volume that you created in the previous step
mem_size string

The size of the data volume as a string value consisting of an integer + unit MiB, GiB, or TiB
num_active_nodes integer

The number of active nodes in the backup
nodes list

List of node IDs (integers) of the active nodes in the backup
Example:
confd_client db_create db_name: VIRTUAL_DB data_volume_name: VIRTUAL_DATA nodes: '[11, 12, 13, 14]' owner: '[500, 500]' num_active_nodes: 4 port: 9563 version: 8.26.0 mem_size: '20 GiB'

Step 4: Find the backup to restore

Before you start the virtual restore process, you must know the ID of the backup that you want to restore.

To find the backup ID, use the ConfD job db_backup_list. The backup ID is listed under the key ID.

Example:
confd_client db_backup_list db_name: MY_DATABASE
...
id: 9 MY_DATABASE/id_2/level_0/node_0/backup_202305081443 MY_DATABASE

Backups that have an expire date cannot be virtually restored.

Step 5: Start the restore process

To start the virtual restore process, use the ConfD job db_restore with the following parameters:

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

The ID of the backup that you want to restore.

The format of a backup ID is <volume_id> <path_to_backup> <db_name>.

The backup ID can be obtained with the job db_backup_list, under the key ID.
restore_type string virtual access
Example:
confd_client db_restore db_name: VIRTUAL_DB restore_type: 'virtual access' backup_id: '9 MY_DATABASE/id_2/level_0/node_0/backup_202305081443 MY_DATABASE'

Import/export data

You can now connect to the virtual database instance using an SQL client and start to import or restore data.

Example: Import data 
CREATE OR REPLACE TABLE SCHEMA.TABLE AS
SELECT * FROM ( IMPORT FROM EXA AT 'CONNECTION-STRING:PORT' USER 'myuser' IDENTIFIED BY "mypass" TABLE SCHEMA.TABLE );
Example: Export data
EXPORT SCHEMA.TABLE
INTO EXA AT 'CONNECTION-STRING:PORT' USER "myuser" IDENTIFIED BY "mypass" TABLE SCHEMA.TABLE;

Clean up

Once you have imported the required data, you can delete the virtual database and the data volume.

  1. Stop the virtual database instance using the ConfD job db_stop.

  2. Delete the virtual database instance using the ConfD job db_delete.

  3. Delete the data volume using the ConfD job st_volume_delete.

Example:
confd_client db_stop db_name: VIRTUAL_DB
confd_client db_delete db_name: VIRTUAL_DB
confd_client st_volume_delete vname: VIRTUAL_DATA

After you have finished the cleanup step, you can increase the DB RAM in your production database again if needed.

To adjust DB RAM you must stop and restart the database, which means a short downtime is required.

Example:
confd_client db_stop db_name: MY_DATABASE
confd_client db_configure db_name: MY_DATABASE mem_size: '114688 MiB'
confd_client db_start db_name: MY_DATABASE