Offline Copy of an Unlimited Storage Database

Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

You are testing an application that uses bottomless_db and you want to perform testing on the database copy.
You want to perform an audit of the contents of bottomless_db at a time in the past. You make a copy and restore the copy to a past milestone.
bottomless_db has become corrupted and you make a copy and restore the copy to the point (the milestone) before the corruption occurred. Then, you can compare bottomless_db with the copy.

Procedure for Performing a Copy

The following example demonstrates how to copy an unlimited storage database, where Amazon S3 is used as the remote object store. The Setup section contains the prerequisite steps needed to create the database and milestones, prior to performing the copy.

Setup

1. Create the unlimited storage database bottomless_db in the object storage bucket bottomless_db_bucket in the folder bottomless_db_folder.

The following definition assumes you are using Amazon S3 as the object storage provider.

Note that aws_session_token is required only if your credentials in the CREDENTIALS clause are temporary.

SQL

CREATE DATABASE bottomless_db ON S3 "bottomless_db_bucket/bottomless_db_folder"
  CONFIG '{"region":"us-east-1"}'
  CREDENTIALS '{"aws_access_key_id":"your_access_key_id","aws_secret_access_key":"your_secret_access_key","aws_session_token":"your_session_token"}';

The following definition assumes you are using Azure as the object storage provider.

SQL

CREATE DATABASE bottomless_db ON AZURE "bottomless_db_bucket/bottomless_db_folder"  
    CONFIG ''  
    CREDENTIALS '{"account_name":"your_account_name","account_key":"your_account_key"}';

The following definition assumes you are using GCS as the object storage provider.

SQL

CREATE DATABASE bottomless_db ON GCS "bottomless_db_bucket/bottomless_db_folder"
  CONFIG '' 
  CREDENTIALS'{"access_id":"your_access_key_id", "secret_key":"your_secret_access_key"}';

2. Make some updates to bottomless_db. In this example, you update the database by creating a table and inserting some data:

SQL

USE bottomless_db;
CREATE TABLE t(a INT);
INSERT INTO t(a) VALUES (10);
INSERT INTO t(a) VALUES (20);

3. Create a milestone (a restore point):

SQL

CREATE MILESTONE "after_second_insert" FOR bottomless_db;

4. Make more updates to bottomless_db:

SQL

INSERT INTO t(a) VALUES (30);
INSERT INTO t(a) VALUES (40);

Perform the Copy

Detach bottomless_db and name the milestone after_fourth_insert:

SQL

DETACH DATABASE bottomless_db AT "after_fourth_insert";

In remote object storage, create a new bucket copy_of_bottomless_db_bucket where you will copy the objects from bottomless_db_bucket.

Next, copy the bottomless_db folder in the bottomless_db_bucket to the copy_of_bottomless_db_bucket. Refer to the instructions in your remote object storage provider's documentation for copying objects from one bucket to another. For example, with Amazon S3 you could use these instructions.

Your remote object storage provider may have a copy-on-write option that can be enabled when copying objects from one bucket to another. Copy-on-write allows the copy to complete faster than it otherwise would.

Caution

The source database (bottomless_db) must remain detached while the objects from the source bucket (bottomless_db_bucket) are copying. Otherwise, the destination database may become unusable.

After the copy completes, attach copy_of_bottomless_db_bucket at the after_fourth_insert, the milestone that was created right before you copied the objects in the remote object store. Name the copied database copy_of_bottomless_db:

SQL

ATTACH DATABASE copy_of_bottomless_db ON S3 "copy_of_bottomless_db_bucket/bottomless_db"
  CONFIG '{"region":"us-east-1"}'
  CREDENTIALS '{"aws_access_key_id":"your_access_key_id","aws_secret_access_key":"your_secret_access_key"}'
  AT MILESTONE "after_fourth_insert";

SQL

ATTACH DATABASE copy_of_bottomless_db ON AZURE "copy_of_bottomless_db_bucket/bottomless_db"
    CONFIG ''   
    CREDENTIALS '{"account_name":"your_account_name","account_key":"your_account_key"}'  
    AT MILESTONE "after_fourth_insert";

For GCS:

SQL

ATTACH DATABASE copy_of_bottomless_db ON GCS "copy_of_bottomless_db_bucket/bottomless_db"
    CONFIG ''   
    CREDENTIALS '{"access_id":"your_access_key_id", "secret_key":"your_secret_access_key"}'
    AT MILESTONE "after_fourth_insert";

Note

You could have also attached copy_of_bottomless_db at a previous milestone, which would be after_second_insert in this scenario. You would do this, for example, if you wanted to compare the database updates that occurred between the after_second_insert milestone and after_fourth_insert milestone.

Finally, attach bottomless_db at the after_fourth_insert milestone, which is the same milestone you had detached it at.

SQL

ATTACH DATABASE bottomless_db ON S3 "bottomless_db_bucket/bottomless_db"
  CONFIG '{"region":"us-east-1"}'
  CREDENTIALS '{"aws_access_key_id":"your_access_key_id","aws_secret_access_key":"your_secret_access_key"}'
  AT MILESTONE "after_fourth_insert";

For Azure

SQL

ATTACH DATABASE bottomless_db ON AZURE "bottomless_db_bucket/bottomless_db"
    CONFIG ''   
    CREDENTIALS '{"account_name":"your_account_name","account_key":"your_account_key"}' 
    AT MILESTONE "after_fourth_insert";

For GCS

SQL

ATTACH DATABASE bottomless_db ON GCS "bottomless_db_bucket/bottomless_db"
    CONFIG ''   
    CREDENTIALS '{"access_id":"your_access_key_id", "secret_key":"your_secret_access_key"}'
    AT MILESTONE "after_fourth_insert";