Attach an Unlimited Storage Database Using Point-in-Time Recovery (PITR)
On this page
PITR is not available in all editions of SingleStore.
The following example demonstrates how to restore an unlimited storage database.
CREATE DATABASE bottomless_db;
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
40 were inserted into
t in error.
DETACH DATABASE bottomless_db;
Then attach the database at the restore point:
ATTACH DATABASE bottomless_db AT MILESTONE "after_second_insert";
You can restore to any point in time for which all the data is available on an object store.
A database cannot be restored to a PITR milestone taken before the database was dropped.
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.
The solution is to use Unix time stamps or alternatively, use milestones to avoid this problem.
Last modified: May 3, 2023