Support point in time restore in YugabyteDB

[shamil]

Jira Link: DB-4724
The ask is to perform incremental backups and point in time restores in YSQL, similar to what PostgreSQL does:

• Blog post covering PostgreSQL incremental backup and recovery
• PostgreSQL docs. The relevant excerpt is shown below.

At all times, PostgreSQL maintains a write ahead log (WAL) in the pg_wal/ subdirectory of the cluster's data directory. The log records every change made to the database's data files. This log exists primarily for crash-safety purposes: if the system crashes, the database can be restored to consistency by “replaying” the log entries made since the last checkpoint. However, the existence of the log makes it possible to use a third strategy for backing up databases: we can combine a file-system-level backup with backup of the WAL files. If recovery is needed, we restore the file system backup and then replay from the backed-up WAL files to bring the system to a current state. This approach is more complex to administer than either of the previous approaches, but it has some significant benefits:

• We do not need a perfectly consistent file system backup as the starting point. Any internal inconsistency in the backup will be corrected by log replay (this is not significantly different from what happens during crash recovery). So we do not need a file system snapshot capability, just tar or a similar archiving tool.
• Since we can combine an indefinitely long sequence of WAL files for replay, continuous backup can be achieved simply by continuing to archive the WAL files. This is particularly valuable for large databases, where it might not be convenient to take a full backup frequently.
• It is not necessary to replay the WAL entries all the way to the end. We could stop the replay at any point and have a consistent snapshot of the database as it was at that time. Thus, this technique supports point-in-time recovery: it is possible to restore the database to its state at any time since your base backup was taken.
• If we continuously feed the series of WAL files to another machine that has been loaded with the same base backup file, we have a warm standby system: at any point we can bring up the second machine and it will have a nearly-current copy of the database.

[kmuthukk]

hi @shamil

At present YB supports full-backups.

Incremental backups and the ability to restore to an arbitrary point-in-time (not just the time of the backup) have not yet been implemented. This is something we'll likely look at around Q4 of this year.

regards,
Kannan

[jk-1]

PostgreSQL Incremental Backup and Point-In-Time Recovery:

https://pgdash.io/blog/postgres-incremental-backup-recovery.html

This would be very valuable feature.