Point-in-time restore Beta

YSQL
YCQL

Refer to the YSQL tab for details on this feature.

Try out the PITR feature

You can test the PITR feature (BETA) by creating a database and populating it, creating a snapshot, and restoring (data only!) from that snapshot.

Create and snapshot a table

Create and populate a table, look at a timestamp to which you'll restore, and then write a row.

Start the YCQL shell and connect to your local instance:
```
$ bin/ycqlsh
```

Create a table and populate some sample data:

create keyspace pitr;

use pitr;

create table employees (
  employee_no integer PRIMARY KEY,
  name text,
  department text,
  salary integer
);

insert into employees (employee_no, name, department, salary) values (1221, 'John Smith', 'Marketing', 50000);
insert into employees (employee_no, name, department, salary) values (1222, 'Bette Davis', 'Sales', 55000);
insert into employees (employee_no, name, department, salary) values (1223, 'Lucille Ball', 'Operations', 70000);
insert into employees (employee_no, name, department, salary) values (1224, 'John Zimmerman', 'Sales', 60000);

SELECT * from employees;

 employee_no | name           | department | salary
-------------+----------------+------------+--------
        1223 |   Lucille Ball | Operations |  70000
        1224 | John Zimmerman |      Sales |  60000
        1221 |     John Smith |  Marketing |  50000
        1222 |    Bette Davis |      Sales |  55000

(4 rows)

Create a snapshot of the table from a shell prompt:

$ bin/yb-admin create_database_snapshot pitr employees

Started snapshot creation: bb5fc435-a2b9-4f3a-a510-0bacc6aebccf

Verify that the snapshot is complete:

$ bin/yb-admin list_snapshots

Snapshot UUID                         State
bb5fc435-a2b9-4f3a-a510-0bacc6aebccf  COMPLETE
No snapshot restorations

Restore from an absolute time

Get a timestamp. YCQL doesn't have a now() function, so use a command such as one of the following. You can also use a YCQL timestamp with the restore command, if you like.

# Ruby: remove the decimal point before using the timestamp
$ ruby -e 'puts Time.now.to_f'

1617818825.646913

# Python: remove the decimal point before using the timestamp
$ python -c 'import datetime; print datetime.datetime.now().strftime("%s.%f")'

1617818868.669611

# Linux and some other systems (but not macOS): use the timestamp as-is
$ date +%s%N | cut -b1-16

1617818943892323

Add a row for employee 9999 to the table:

insert into employees (employee_no, name, department, salary) values (9999, 'Wrong Name', 'Marketing', 10000);

select * from employees;

 employee_no | name           | department | salary
-------------+----------------+------------+--------
        1223 |   Lucille Ball | Operations |  70000
        9999 |     Wrong Name |  Marketing |  10000
        1224 | John Zimmerman |      Sales |  60000
        1221 |     John Smith |  Marketing |  50000
        1222 |    Bette Davis |      Sales |  55000

(5 rows)

List snapshots, at a terminal prompt:

$ bin/yb-admin list_snapshots

Snapshot UUID                         State
bb5fc435-a2b9-4f3a-a510-0bacc6aebccf  COMPLETE
No snapshot restorations

Restore the latest snapshot to the timestamp you obtained before you deleted the data, at a terminal prompt:
```
$ bin/yb-admin restore_snapshot bb5fc435-a2b9-4f3a-a510-0bacc6aebccf 1617818943892323
```

Next, verify the restoration is in RESTORED state:

$ bin/yb-admin list_snapshots

Snapshot UUID                     State
bb5fc435-a2b9-4f3a-a510-0bacc6aebccf  COMPLETE
Restoration UUID                      State
bd7e4e52-b763-4b95-87ce-9399e1ac206e  RESTORED

In the YCQL shell, verify the data is restored, and there is no row for employee 9999:

ycqlsh:pitr> select * from employees;

 employee_no | name           | department | salary
-------------+----------------+------------+--------
        1223 |   Lucille Ball | Operations |  70000
        1224 | John Zimmerman |      Sales |  60000
        1221 |     John Smith |  Marketing |  50000
        1222 |    Bette Davis |      Sales |  55000

(4 rows)

Restore from a relative time

In addition to restoring to a particular timestamp, you can also restore from a relative time, such as "ten minutes ago". In this example, you'll delete some data from the existing employees table, then restore the state of the database to what it was five minutes prior.

When you specify a relative time, you can specify any or all of days, hours, minutes, and seconds. For example:

"5m" to restore from five minutes ago
"1h" to restore from one hour ago
"3d" to restore from three days ago
"1h 5m" to restore from one hour and five minutes ago

Relative times can be in any of the following formats (again, note that you can specify any or all of days, hours, minutes, and seconds):

ISO 8601: 3d 4h 5m 6s
Abbreviated PostgreSQL: 3 d 4 hrs 5 mins 6 secs
Traditional PostgreSQL: 3 days 4 hours 5 minutes 6 seconds
SQL standard: D H:M:S

Careful! If you specify a time prior to when you created the table, the restore will leave the table intact, but empty.

Wait at least five minutes after you complete the steps in the previous section. This is so that you can easily use a known relative time for the restore.

From the YCQL shell, remove employee 1223 from the table:

ycqlsh:pitr> delete from employees where employee_no=1223;

ycqlsh:pitr> select * from employees;

employee_no |      name      | department | salary 
-------------+----------------+------------+--------
        1224 | John Zimmerman | Sales      |  60000
        1221 | John Smith     | Marketing  |  50000
        1222 | Bette Davis    | Sales      |  55000

(3 rows)

At a terminal prompt, restore the snapshot you created earlier:

$ bin/yb-admin restore_snapshot bb5fc435-a2b9-4f3a-a510-0bacc6aebccf minus "5m"

Verify the restoration is in RESTORED state:

$ bin/yb-admin list_snapshots

Snapshot UUID                     State
bb5fc435-a2b9-4f3a-a510-0bacc6aebccf  COMPLETE
Restoration UUID                      State
bd7e4e52-b763-4b95-87ce-9399e1ac206e  RESTORED

Verify the data is restored, and employee 1223 is back:

ycqlsh:pitr> select * from employees;

 employee_no | name           | department | salary
-------------+----------------+------------+--------
        1223 |   Lucille Ball | Operations |  70000
        1224 | John Zimmerman |      Sales |  60000
        1221 |     John Smith |  Marketing |  50000
        1222 |    Bette Davis |      Sales |  55000

(4 rows)

Limitations

This is a BETA feature, and is in active development. Currently, you can restore data only. The feature doesn't support metadata; in other words, rolling back past operations such as CREATE, ALTER, TRUNCATE, and DROP TABLE is unsupported.

Development for this feature is tracked in issue 7120. Some forthcoming features include:

Automatic configuration
Support for undoing metadata operations, such as CREATE, ALTER, TRUNCATE, or DROP TABLE
Schedules to take snapshots at user-defined intervals
Options to restore with different granularities, such as a single YSQL database or the whole YCQL dataset.