Attach an Unlimited Storage Database Using Point-in-Time Recovery (PITR)

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

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

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

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

CREATE MILESTONE "after_second_insert" FOR bottomless_db;

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

Create a second milestone:

CREATE MILESTONE "after_fourth_insert" FOR bottomless_db;

Suppose the values 30 and 40 were inserted into t in error. You now want to restore the database to the milestone after_second_insert. Detach the database to bring it offline:

DETACH DATABASE bottomless_db;

Then attach the database at the restore point:

For S3:

ATTACH 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"}'
  AT MILESTONE "after_second_insert";

For Azure:

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

For GCS:

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

You can restore to any point in time for which all the data is available on an object store. It is not necessary to restore to a named milestone.

A database cannot be restored to a PITR milestone taken before the database was dropped. This is because DROP DATABASE removes the PITR history.

When you do a PITR using the following timestamp format,

ATTACH DATABASE x_db AT TIME 'YYYY-MM-DD HH:MM:SS';

the engine uses the server timezone. This can create ambiguity when daylight saving time (DST) changes time by one hour. For example, if you input the time as 1:05AM, was that before or after DST? The time used by the engine and the user scripts may not match.

The solution is to use Unix time stamps or alternatively, use milestones to avoid this problem.

The standard approach to PITR is offline, because you must detach and then attach the database. As a result, the database cannot be accessed between those two operations. However, for some scenarios you may not want to take the database offline, but you still want to do a PITR to a new database and manually copy some lost data back to the source database.

See Online Copy of an Unlimited Storage Database for instructions on how to do that.