Point-in-Time Recovery
Database replication by itself does not guarantee data loss protection. If by accident a table is dropped in the primary replica, it will be dropped in the secondary ones too, leaving no way to restore the data.
With Point-in-time Recovery you can restore the database to a previous point in time, without the need to make backups and with almost no overhead on transactions.
It can be used so in the primary replica as in the slave ones. It can even be used locally without replication at all.
Activation
To activate PITR just add pitr=on in the URI filename:
"file:/path/to/file.db?pitr=on"
Log Storage Location
The binary logs will be stored in a sub-folder named "binlogs". If you want to store them on a different location, use the pitr_path parameter:
"file:/path/to/file.db?pitr=on&pitr_path=."
The path is relative to the database path, and you can use an absolute path too.
Using the same folder as the database makes it slightly faster.
Log Storage Limit
If you want to limit the space used by the binary logs you can set the pitr_limit parameter.
To limit the total size to an absolute value use the M (for MegaBytes) or k (for KiloBytes) symbols:
"file:/path/to/file.db?pitr=on&pitr_limit=50M"
To limit the total size to a value relative to the database file size use the x symbol:
"file:/path/to/file.db?pitr=on&pitr_limit=20x"
And to limit the amount of log files use the p (points) symbol:
"file:/path/to/file.db?pitr=on&pitr_limit=100p"
It is possible to use many options separated by commas, so the engine will use the lowest limit:
"file:/path/to/file.db?pitr=on&pitr_path=/path/to/logs&pitr_limit=50M,20x,100p"
Listing the restore points
Open the database with the SQLite shell and run the .binlogs command.
Restore to a previous point in time
Open the database without replication enabled in the SQLite shell and run the .restore_to command.
It accepts a date-time string as argument. Example:
.restore_to '2016-12-31 15:50'
You can also inform the seconds.
It will restore the database to the state it was at the specified time, if there is enough log files to go back until the specified time.