Sign inTry Free

Offline Copy of an Unlimited Storage Database

On this page

Note

Unlimited storage databases are not available in all editions of SingleStore. For more information, see SingleStore Editions.

Suppose your unlimited storage database is named bottomless_db. Some situations where you would make a copy of bottomless_db include:

  • 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.

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.

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.

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:

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):

CREATE MILESTONE "after_second_insert" FOR bottomless_db;

4. Make more updates to bottomless_db:

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:

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:

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";

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:

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.

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

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

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";

Last modified: May 5, 2023

Was this article helpful?

On this page

Was this article helpful?