Practice point-in-time recovery¶
PITR needs history within its retention window. Creating a policy does not make any arbitrary older time recoverable. On an isolated test account, create a table and policy, wait until the second in which the policy was created has passed, then record a literal timestamp:
CREATE DATABASE cookbook_pitr;
CREATE TABLE cookbook_pitr.orders(id INT PRIMARY KEY, amount INT);
INSERT INTO cookbook_pitr.orders VALUES(1,10),(2,20);
CREATE PITR cookbook_orders_pitr FOR DATABASE cookbook_pitr RANGE 1 'h';
SHOW PITR;
SELECT SLEEP(1);
SELECT NOW() AS restore_point;
SLEEP(1) ensures the recorded restore point is later than the policy creation time, regardless of the timestamp precision shown by the client. Once PITR has retained the restore point, note restore_point, then run DELETE FROM cookbook_pitr.orders WHERE id=2;. Replace the placeholder below with that recorded time, within the retention window:
RESTORE DATABASE cookbook_pitr FROM PITR cookbook_orders_pitr '2026-10-19 10:00:00';
SELECT COUNT(*) AS restored_rows, SUM(amount) AS amount_sum
FROM cookbook_pitr.orders;
DROP PITR cookbook_orders_pitr;
DROP DATABASE cookbook_pitr;
For a restore point before the deletion, expect 2 and 30. Restore may overwrite current target objects, so use only an isolated test database. See RESTORE FROM PITR for syntax and scope.