Attach an Unlimited Storage Database Using Point-in-Time Recovery (PITR)
On this page
Note
PITR is not available in all editions of SingleStore.
The following example demonstrates how to restore an unlimited storage database.
Setup
1.bottomless_
.
CREATE DATABASE bottomless_db;
2.bottomless_
.
USE bottomless_db;CREATE TABLE t(a INT);INSERT INTO t(a) VALUES (10);INSERT INTO t(a) VALUES (20);
3.
CREATE MILESTONE "after_second_insert" FOR bottomless_db;
4.bottomless_
:
INSERT INTO t(a) VALUES (30);INSERT INTO t(a) VALUES (40);
Create a second milestone:
CREATE MILESTONE "after_fourth_insert" FOR bottomless_db;
Perform the Restore
Suppose the values 30
and 40
were inserted into t
in error.after_
.
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.
Daylight Saving Time Ambiguity in PITR
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