Product Documentation

Appearance

Running a Single-Table Restore ​

This section lays out the steps and requirements for restoring a single table in a specific database, as opposed to all the tables in a database. A table-level restore is a restore to a new table, with its own table ID. Before running a single-table restore, note that a single-table restore cannot participate in continuous restore scenarios. If you want a restored table to replace an existing table, you must move the rows to the original table manually.

Compatibility of Client Tools and Backups ​

Note the following requirements regarding compatibility between backup and restore tools and physical backups:

  • In general, Version 5.4 and later ybrestore clients can operate on older backups, using pre-Version 5.4 functionality. However, single-table restore operations, which are new in Version 5.4, are not supported for backups taken with pre-Version 5.4 tools.
  • To enable single-table restores, a new backup must be taken with a Version 5.4.0 or later ybbackup client.
  • A system that is running pre-Version 5.4 Yellowbrick database software does not support single-table restore operations even if the system is used with a Version 5.4 ybrestore client.

Notes on the Single-Table Restore Process ​

When you restore a single table, two session IDs are logged, the first for restoring the metadata and the second for restoring the actual data in the table. The database name listed for the first session is an internal staging database name: _YB_BAR2_STR_*. For example:

_YB_BAR2_STR_6ed06f17-b3b8-4714-8a77-208a2a303f4c

The actual target database name is identified by the second session. Databases with names that begin with _YB_ are reserved in Yellowbrick. Do not try to create a database that begins with this string.

See also sys.log_restore.

The process of running a single-table restore also involves writing data to a temporary staging schema that exists solely for this purpose. The name of this schema is _YB_STR_staging. This schema is temporary and is periodically deleted when there are no single-table restores running; therefore, you should not try to create or use any objects in that schema. The name _YB_STR_staging is also reserved.

When a single-table restore operation succeeds and is complete (whether it consists of restoring from one backup or multiple incremental backups), the table data is written to the persistent schema that you specified in the ybrestore command. Because of this behavior, the restored table is only visible when it is fully restored.

Procedure for Restoring a Single Table ​

To restore a single table from a backup, follow these steps:

  1. Back up the source database with at least one full backup.
  2. Identify the backup point you want to use for the restore. You can use either ybbackupctl or the sys.log_backup system view to list backup points.
  3. Restore the table by specifying the original table name (-t) and a new table name (-tt) in the ybrestore command. (The new table name is not required if you are restoring the table to a different system or database, or if the original table has been dropped since the specified backup point.)

For example:

ybrestore -W --username yellowbrick -d premdb -t match -tt match_restored --chain premdb2021 --point latest /tmp/data

Note: Do not specify --security-mode, --hot-standby, or --preserve-ldap; these options are not compatible with single-table restores.

  1. Monitor the restore by running queries on the sys.restore and sys.log_restore views, or by going to Monitor > Backup/Restore Processes in the SMC.
  2. Check the integrity of the restored table by running a select count(*) query.

Parent topic:ybrestore Commands