Consultation Post: Does PITR Support Partial Table Recovery?

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 咨询帖：PITR 支持恢复部分表么？

| username: TiDBer_yyy

[TiDB Usage Environment] Test
[TiDB Version] 6.5.0

[Encountered Problem: Problem Phenomenon and Impact]

  1. Checking the PITR feature of br, it supports full data recovery.
    Question: Does PITR support partial table data recovery?
./br restore point --help
restore data from log until specify commit timestamp

Usage:
  br restore point [flags]

Flags:
  --full-backup-storage string specify the backup full storage. fill it if want restore full backup before restore log.
  -h, --help                   help for point
  --restored-ts string         the point of restore, used for log restore. support TSO or datetime, e.g. '400036290571534337' or '2018-05-11 01:42:23+0800'
  --start-ts string            the start timestamp which log restore from. support TSO or datetime, e.g. '400036290571534337' or '2018-05-11 01:42:23+0800'


Global Flags:
 --ca string                  CA certificate path for TLS connection
 --cert string                Certificate path for TLS connection
 --key string                 Private key path for TLS connection
 -u, --pd strings             PD address (default [127.0.0.1:2379])
 -s, --storage string         specify the url where backup storage, eg, "s3://bucket/path/prefix"
2
| username: 裤衩儿飞上天 | Original post link

My understanding is that restoring partial table data is not supported (which I think is reasonable). Achieving point-in-time recovery for the entire cluster is sufficient. Theoretically, if only partial tables are restored, it could lead to data inconsistency, such as a transaction spanning multiple tables. In the recovery process, there is no filtering step. You can refer to the recovery process:

image
image1338×998 66.8 KB

3
| username: TiDBer_yyy | Original post link

Thank you for the reply!

In the real scenario, our TiDB cluster (version 5.0) uses Dumpling for backups, and we are considering upgrading the cluster and optimizing the backup strategy.

  • Requirement: The business needs data from a specific time one year ago.
  • Shortcoming: The core TiDB cluster is currently a shared large cluster. If the entire cluster is restored, the time and machine costs will be particularly high. (PS: Considering machine costs, it is temporarily impossible to split the shared cluster)

So, we want to know if partial table restoration is supported.

4
| username: 裤衩儿飞上天 | Original post link
  1. PITR definitely does not support single database or single table recovery.
    TiDB Backup and Restore Overview | PingCAP Docs

image
image1330×304 27 KB

  1. For such a large database, requiring the ability to restore data from any point in time a year ago means you need to keep a year’s worth of log backups, which is not a small amount. I think you should ask the business if they really need such precise point-in-time recovery. You can perform full backups separately at important time points.
5
| username: TiDBer_yyy | Original post link

You can refer to the official documentation for more details on backup and restore: TiDB 备份与恢复概述 | PingCAP 文档中心

6
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.