Online Copy of an Unlimited Storage Database

The following steps can be used to copy an unlimited storage database whose long-term historical contents are kept on external object storage, such as Amazon S3. This is an online operation, which means that during the copying process, the source database is attached and actively used.

Step 1: Create a milestone in the source database.

This flushes all data prior to that point to external storage. This step is optional but recommended since it will allow you to recover the copy to any time up to the milestone.

Step 2: Copy the bucket containing the database files to a new location. Files for the database will be under <root-path>.

The <root-path> is the path used when the database was created, which should include a bucket and optionally a path within the bucket.

Step 3: Delete the lock file(s) that are located under the <root-path>/<storage-id>/storage_locks/ path.

Remove all files under this path. The <storage-id> is a two-part number of the format <part1>_<part2>, which can be manually obtained from the information_schema.mv_bottomless_status_extended table.

Step 4: Attach the new copy of the bucket at the desired milestone.

Example:

Suppose in the database DB1, a user accidentally deleted all rows from the table F for CUSTOMER_ID 1000, at 1PM.

To recover these rows online, do the following:

1. Execute:

CREATE MILESTONE "copy_point";

2. Copy the bucket for DB1.

3. Delete the lock file(s).

4. Attach the database as name DB2 at a time before 1 PM when the desired data exists.

ATTACH DATABASE DB1 AS DB2 AT TIME '2022-02-10 12_59_00';

5. Execute:

INSERT INTO db1.f SELECT * FROM DB2.f WHERE customer_id = 1000;