SingleStore DB

Copy an Unlimited Storage Database

Note

This topic does not apply to the SingleStore DB Free Edition.

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

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