SHOW BACKUP

On this page Carat arrow pointing down

The SHOW BACKUP statement lists the contents of a backup created with the BACKUP statement.

Warning:

The SHOW BACKUP syntax without the IN keyword is deprecated as of v22.1 and will be removed in a future release.

We recommend using SHOW BACKUP FROM {subdirectory} IN {collectionURI} to view backups in a subdirectory at a collection's URI.

For guidance on the syntax for SHOW BACKUP FROM, see the examples.

Required privileges

SHOW BACKUP requires read permissions to its target destination.

You can grant a user the EXTERNALIOIMPLICITACCESS system-level privilege to interact with external resources that require implicit access.

Either the EXTERNALIOIMPLICITACCESS system-level privilege or the admin role is required for the following scenarios:

No special privilege is required for:

  • Interacting with an Amazon S3 and Google Cloud Storage resource using SPECIFIED credentials. Azure Storage is always SPECIFIED by default.
  • Using Userfile storage.

We recommend using cloud storage.

Synopsis

SHOW BACKUPS IN collectionURI BACKUP SCHEMAS FROM subdirectory IN collectionURI WITH kv_option_list OPTIONS ( kv_option_list )

Parameters

Parameter Description
SHOW BACKUPS IN collectionURI List the backup paths in the given collection URI. See the example.
SHOW BACKUP FROM subdirectory IN collectionURI Show the details of backups in the subdirectory at the given collection URI. Also, use FROM LATEST in collectionURI to show the most recent backup. See the example.
SHOW BACKUP SCHEMAS FROM subdirectory IN collectionURI Show the schema details of the backup in the given collection URI. See the example.
collectionURI The URI for the backup storage.
Note that SHOW BACKUP does not support listing backups if the nodelocal storage location is a symlink. Cockroach Labs recommends using remote storage for backups.
kv_option_list Control the behavior of SHOW BACKUP with a comma-separated list of these options.

Options

Option Value Description
as_json N/A Display the backup's internal metadata as JSON in the response.
check_files N/A Validate that all files belonging to a backup are in the expected location in storage. See Validate a backup's files for an example.
debug_ids N/A Display descriptor IDs of every object in the backup, including the object's database and parent schema.
encryption_passphrase STRING The passphrase used to encrypt the files that the BACKUP statement generates (the data files and its manifest, containing the backup's metadata).
kms STRING The URI of the cryptographic key stored in a key management service (KMS), or a comma-separated list of key URIs, used to take and restore encrypted backups. Refer to URI Formats.
incremental_location STRING List the details of an incremental backup taken with the incremental_location option.
privileges N/A List which users and roles had which privileges on each table in the backup. Displays original ownership of the backup.

Response

The following fields are returned:

Field Value Description
database_name STRING The database name.
parent_schema_name STRING The name of the parent schema.
object_name STRING The name of the database, table, type, or schema.
object_type STRING The type of object: database, table, type, or schema.
backup_type STRING The type of backup: full or incremental.
start_time TIMESTAMPTZ The time of the earliest data encapsulated in the backup. Note that this only displays for incremental backups. For a full backup, this is NULL.
end_time TIMESTAMPTZ The time to which data can be restored. This is equivalent to the AS OF SYSTEM TIME of the backup. If the backup was not taken with revision history, the end_time is the only time the data can be restored to. If the backup was taken with revision history, the end_time is the latest time the data can be restored to.
size_bytes INT The size of the backup objects, in bytes. Note that size_bytes indicates the logical size of the table objects, which is computed as the sum of the size of each key value pair. See file_bytes in this table for more detail.
rows INT Number of rows in tables that are part of the backup.
create_statement STRING The CREATE statement used to create table(s), view(s), or sequence(s) that are stored within the backup. This displays when SHOW BACKUP SCHEMAS is used. Note that tables with references to foreign keys will only display foreign key constraints if the table to which the constraint relates to is also included in the backup.
is_full_cluster BOOL Whether the backup is of a full cluster or not.
regions STRING The ALTER DATABASE statement(s) used to configure the database with multi-region zone configurations, if they exist. If the database is a part of a single region cluster configuration, NULL will show.
file_bytes INT (With the check_files option only) The estimated bytes in external storage for a particular table object. This is the physical bytes that a given table object is taking up. For example, when the files are written to disk in storage they could be compressed. If you total all file bytes, the result is the physical bytes in your storage location. Note that for smaller tables the byte size in file_bytes may be larger than size_bytes because of the overhead required to create an SST file.
path STRING The list of the full backup's subdirectories. This field is returned for SHOW BACKUPS IN collectionURI only. The path format is <year>/<month>/<day>-<timestamp>.

See Show a backup with descriptor IDs for the responses displayed when the WITH debug_ids option is specified.

Examples

The examples in this section use one of the following storage URIs:

  • External connections, which allow you to represent an external storage or sink URI. You can then specify the external connection's name in statements rather than the provider-specific URI. For detail on using external connections, see the CREATE EXTERNAL CONNECTION page.
  • Amazon S3 connection strings with the default AUTH=specified parameter. For guidance on using AUTH=implicit authentication with Amazon S3 buckets instead, read Cloud Storage Authentication.

For guidance on connecting to other storage options or using other authentication parameters instead, read Use Cloud Storage.

View a list of the available full backup subdirectories

To view a list of the available full backups subdirectories, use the following command:

icon/buttons/copy
SHOW BACKUPS IN 'external://backup_s3';
        path
-------------------------
/2022/04/13-202334.48
/2022/04/13-202413.70
(2 rows)

The path format is <year>/<month>/<day>-<timestamp>.

Show the most recent backup

To view the most recent backup, use the LATEST syntax:

icon/buttons/copy
SHOW BACKUP FROM LATEST IN 'external://backup_s3';
database_name | parent_schema_name |        object_name         | object_type | backup_type | start_time |          end_time          | size_bytes | rows | is_full_cluster |                                             regions
----------------+--------------------+----------------------------+-------------+-------------+------------+----------------------------+------------+------+-----------------+--------------------------------------------------------------------------------------------------
NULL          | NULL               | movr                       | database    | full        | NULL       | 2023-05-23 15:27:33.251745 |       NULL | NULL |        f        | ALTER DATABASE movr SET PRIMARY REGION "us-east-2"; ALTER DATABASE movr ADD REGION "us-west-1";
movr          | NULL               | public                     | schema      | full        | NULL       | 2023-05-23 15:27:33.251745 |       NULL | NULL |        f        | NULL
movr          | public             | users                      | table       | full        | NULL       | 2023-05-23 15:27:33.251745 |      30069 |  327 |        f        | NULL
movr          | public             | vehicles                   | table       | full        | NULL       | 2023-05-23 15:27:33.251745 |      17456 |   88 |        f        | NULL
movr          | public             | rides                      | table       | full        | NULL       | 2023-05-23 15:27:33.251745 |     251254 |  862 |        f        | NULL
movr          | public             | vehicle_location_histories | table       | full        | NULL       | 2023-05-23 15:27:33.251745 |     660910 | 9649 |        f        | NULL
movr          | public             | promo_codes                | table       | full        | NULL       | 2023-05-23 15:27:33.251745 |     227789 | 1020 |        f        | NULL
movr          | public             | user_promo_codes           | table       | full        | NULL       | 2023-05-23 15:27:33.251745 |       6973 |   83 |        f        | NULL
movr          | public             | crdb_internal_region       | type        | full        | NULL       | 2023-05-23 15:27:33.251745 |       NULL | NULL |        f        | NULL
movr          | public             | _crdb_internal_region      | type        | full        | NULL       | 2023-05-23 15:27:33.251745 |       NULL | NULL |        f        | NULL
(10 rows)

View a list of the full and incremental backups in a specific full backup subdirectory

To view a list of the full and incremental backups in a specific subdirectory, use the following command:

icon/buttons/copy
SHOW BACKUP FROM '2022/04/08-142355.33' IN 'external://backup_s3';
database_name | parent_schema_name |        object_name         | object_type | backup_type |        start_time         |          end_time          | size_bytes | rows  | is_full_cluster
--------------+--------------------+----------------------------+-------------+-------------+---------------------------+----------------------------+------------+-------+------------------
NULL          | NULL               | movr                       | database    | full        | NULL                      | 2022-04-08 14:23:55.33557  |       NULL |  NULL |      false
movr          | NULL               | public                     | schema      | full        | NULL                      | 2022-04-08 14:23:55.33557  |       NULL |  NULL |      false
movr          | public             | users                      | table       | full        | NULL                      | 2022-04-08 14:23:55.33557  |      25856 |   281 |      false
NULL          | NULL               | system                     | database    | incremental | 2022-04-08 14:23:55.33557 | 2022-04-08 14:26:01.699694 |       NULL |  NULL |      true
system        | public             | users                      | table       | incremental | 2022-04-08 14:23:55.33557 | 2022-04-08 14:26:01.699694 |         99 |     2 |      true
system        | public             | zones                      | table       | incremental | 2022-04-08 14:23:55.33557 | 2022-04-08 14:26:01.699694 |        236 |     8 |      true
system        | public             | settings                   | table       | incremental | 2022-04-08 14:23:55.33557 | 2022-04-08 14:26:01.699694 |        372 |     5 |      true
system        | public             | ui                         | table       | incremental | 2022-04-08 14:23:55.33557 | 2022-04-08 14:26:01.699694 |          0 |     0 |      true
system        | public             | jobs                       | table       | incremental | 2022-04-08 14:23:55.33557 | 2022-04-08 14:26:01.699694 |      30148 |    23 |      true
system        | public             | locations                  | table       | incremental | 2022-04-08 14:23:55.33557 | 2022-04-08 14:26:01.699694 |        261 |     5 |      true
system        | public             | role_members               | table       | incremental | 2022-04-08 14:23:55.33557 | 2022-04-08 14:26:01.699694 |         94 |     1 |      true
. . .

Show a backup taken with the incremental location option

To view an incremental backup that was taken with the incremental_location option, run SHOW BACKUP with the full backup and incremental backup location following the original BACKUP statement.

You can use the option to show the most recent backup where incremental_location has stored the backup:

icon/buttons/copy
SHOW BACKUP FROM LATEST IN {'full backup collectionURI'} WITH incremental_location = {'incremental backup collectionURI'};
database_name | parent_schema_name |        object_name         | object_type | backup_type |         start_time         |          end_time          | size_bytes | rows  | is_full_cluster
--------------+--------------------+----------------------------+-------------+-------------+----------------------------+----------------------------+------------+-------+------------------
NULL          | NULL               | movr                       | database    | full        | NULL                       | 2022-04-13 20:01:15.177739 |       NULL |  NULL |      false
movr          | NULL               | public                     | schema      | full        | NULL                       | 2022-04-13 20:01:15.177739 |       NULL |  NULL |      false
movr          | public             | rides                      | table       | full        | NULL                       | 2022-04-13 20:01:15.177739 |     395716 |  1415 |      false
NULL          | NULL               | system                     | database    | incremental | 2022-04-13 20:01:15.177739 | 2022-04-13 20:05:04.2049   |       NULL |  NULL |      true
system        | public             | users                      | table       | incremental | 2022-04-13 20:01:15.177739 | 2022-04-13 20:05:04.2049   |         99 |     2 |      true
system        | public             | scheduled_jobs             | table       | incremental | 2022-04-13 20:01:15.177739 | 2022-04-13 20:05:04.2049   |        250 |     1 |      true
system        | public             | database_role_settings     | table       | incremental | 2022-04-13 20:01:15.177739 | 2022-04-13 20:05:04.2049   |          0 |     0 |      true
system        | public             | tenant_settings            | table       | incremental | 2022-04-13 20:01:15.177739 | 2022-04-13 20:05:04.2049   |          0 |     0 |      true
NULL          | NULL               | defaultdb                  | database    | incremental | 2022-04-13 20:01:15.177739 | 2022-04-13 20:05:04.2049   |       NULL |  NULL |      true
defaultdb     | NULL               | public                     | schema      | incremental | 2022-04-13 20:01:15.177739 | 2022-04-13 20:05:04.2049   |       NULL |  NULL |      true
NULL          | NULL               | postgres                   | database    | incremental | 2022-04-13 20:01:15.177739 | 2022-04-13 20:05:04.2049   |       NULL |  NULL |      true
postgres      | NULL               | public                     | schema      | incremental | 2022-04-13 20:01:15.177739 | 2022-04-13 20:05:04.2049   |       NULL |  NULL |      true
NULL          | NULL               | movr                       | database    | incremental | 2022-04-13 20:01:15.177739 | 2022-04-13 20:05:04.2049   |       NULL |  NULL |      true
movr          | NULL               | public                     | schema      | incremental | 2022-04-13 20:01:15.177739 | 2022-04-13 20:05:04.2049   |       NULL |  NULL |      true
movr          | public             | users                      | table       | incremental | 2022-04-13 20:01:15.177739 | 2022-04-13 20:05:04.2049   |      81098 |   892 |      true
movr          | public             | vehicles                   | table       | incremental | 2022-04-13 20:01:15.177739 | 2022-04-13 20:05:04.2049   |      57755 |   296 |      true
. . .

Show locality-aware backups

Note:

SHOW BACKUP is able to display metadata using check_files for locality-aware backups taken with the incremental_location option.

To view a list of locality-aware backups, pass the endpoint collection URI that is set as the default location with COCKROACH_LOCALITY=default:

icon/buttons/copy
> SHOW BACKUPS IN 's3://{default collection URI}/{path}?AWS_ACCESS_KEY_ID={placeholder}&AWS_SECRET_ACCESS_KEY={placeholder}';
        path
-------------------------
/2023/02/23-150925.62
/2023/03/08-192859.44
(2 rows)

To view a locality-aware backup, pass locality-aware backup URIs to SHOW BACKUP:

icon/buttons/copy
> SHOW BACKUP FROM LATEST IN ('s3://{bucket name}/locality?AWS_ACCESS_KEY_ID={placeholder}&AWS_SECRET_ACCESS_KEY={placeholder}&COCKROACH_LOCALITY=default', 's3://{bucket name}/locality?AWS_ACCESS_KEY_ID={placeholder}&AWS_SECRET_ACCESS_KEY={placeholder}&COCKROACH_LOCALITY=region%3Dus-west');
  database_name | parent_schema_name |        object_name         | object_type | backup_type | start_time |          end_time          | size_bytes | rows | is_full_cluster
----------------+--------------------+----------------------------+-------------+-------------+------------+----------------------------+------------+------+------------------
  NULL          | NULL               | movr                       | database    | full        | NULL       | 2023-02-23 15:09:25.625777 |       NULL | NULL |        f
  movr          | NULL               | public                     | schema      | full        | NULL       | 2023-02-23 15:09:25.625777 |       NULL | NULL |        f
  movr          | public             | users                      | table       | full        | NULL       | 2023-02-23 15:09:25.625777 |       5633 |   58 |        f
  movr          | public             | vehicles                   | table       | full        | NULL       | 2023-02-23 15:09:25.625777 |       3617 |   17 |        f
  movr          | public             | rides                      | table       | full        | NULL       | 2023-02-23 15:09:25.625777 |     159269 |  511 |        f
  movr          | public             | vehicle_location_histories | table       | full        | NULL       | 2023-02-23 15:09:25.625777 |      79963 | 1092 |        f
  movr          | public             | promo_codes                | table       | full        | NULL       | 2023-02-23 15:09:25.625777 |     221763 | 1003 |        f
  movr          | public             | user_promo_codes           | table       | full        | NULL       | 2023-02-23 15:09:25.625777 |        927 |   11 |        f
(8 rows)

Show a backup with schemas

icon/buttons/copy
SHOW BACKUP SCHEMAS FROM '2022/04/08-142601.69' IN 'external://backup_s3';
database_name | parent_schema_name |        object_name         | object_type | backup_type |        start_time         |          end_time          | size_bytes | rows  | is_full_cluster |                                                                                                                create_statement
--------------+--------------------+----------------------------+-------------+-------------+---------------------------+----------------------------+------------+-------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NULL          | NULL               | movr                       | database    | full        | NULL                      | 2022-04-08 14:23:55.33557  |       NULL |  NULL |      false      | NULL
movr          | NULL               | public                     | schema      | full        | NULL                      | 2022-04-08 14:23:55.33557  |       NULL |  NULL |      false      | NULL
movr          | public             | users                      | table       | full        | NULL                      | 2022-04-08 14:23:55.33557  |      25856 |   281 |      false      | CREATE TABLE users (
              |                    |                            |             |             |                           |                            |            |       |                 |     id UUID NOT NULL,
              |                    |                            |             |             |                           |                            |            |       |                 |     city VARCHAR NOT NULL,
              |                    |                            |             |             |                           |                            |            |       |                 |     name VARCHAR NULL,
              |                    |                            |             |             |                           |                            |            |       |                 |     address VARCHAR NULL,
              |                    |                            |             |             |                           |                            |            |       |                 |     credit_card VARCHAR NULL,
              |                    |                            |             |             |                           |                            |            |       |                 |     CONSTRAINT users_pkey PRIMARY KEY (city ASC, id ASC)
              |                    |                            |             |             |                           |                            |            |       |                 | )
NULL          | NULL               | system                     | database    | incremental | 2022-04-08 14:23:55.33557 | 2022-04-08 14:26:01.699694 |       NULL |  NULL |      true       | NULL
system        | public             | users                      | table       | incremental | 2022-04-08 14:23:55.33557 | 2022-04-08 14:26:01.699694 |         99 |     2 |      true       | CREATE TABLE users (
              |                    |                            |             |             |                           |                            |            |       |                 |     username STRING NOT NULL,
              |                    |                            |             |             |                           |                            |            |       |                 |     "hashedPassword" BYTES NULL,
              |                    |                            |             |             |                           |                            |            |       |                 |     "isRole" BOOL NOT NULL DEFAULT false,
              |                    |                            |             |             |                           |                            |            |       |                 |     CONSTRAINT "primary" PRIMARY KEY (username ASC),
              |                    |                            |             |             |                           |                            |            |       |                 |     FAMILY "primary" (username),
              |                    |                            |             |             |                           |                            |            |       |                 |     FAMILY "fam_2_hashedPassword" ("hashedPassword"),
              |                    |                            |             |             |                           |                            |            |       |                 |     FAMILY "fam_3_isRole" ("isRole")
              |                    |                            |             |             |                           |                            |            |       |                 | )
. . .

Show a backup with privileges

Use the WITH privileges option to view a list of which users and roles had which privileges on each database and table in the backup. This parameter also displays the original owner of objects in the backup:

icon/buttons/copy
SHOW BACKUP FROM '2022/07/07-160311.96' IN 'external://backup_s3' WITH privileges;
 database_name  | parent_schema_name |        object_name         | object_type | backup_type | start_time |          end_time          | size_bytes | rows  | is_full_cluster |                                                                                        privileges                                                                                         | owner
----------------+--------------------+----------------------------+-------------+-------------+------------+----------------------------+------------+-------+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------
NULL            | NULL               | system                     | database    | full        | NULL       | 2022-07-07 16:03:11.962683 |       NULL |  NULL |      true       | GRANT CONNECT ON DATABASE system TO admin; GRANT CONNECT ON DATABASE system TO root;                                                                                                      | node
system          | public             | users                      | table       | full        | NULL       | 2022-07-07 16:03:11.962683 |         99 |     2 |      true       | GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON TABLE users TO admin; GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON TABLE users TO root;                                                  | node
system          | public             | zones                      | table       | full        | NULL       | 2022-07-07 16:03:11.962683 |        236 |     8 |      true       | GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON TABLE zones TO admin; GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON TABLE zones TO root;                                                  | node
system          | public             | settings                   | table       | full        | NULL       | 2022-07-07 16:03:11.962683 |        423 |     6 |      true       | GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON TABLE settings TO admin; GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON TABLE settings TO root;                                            | root
movr            | NULL               | public                     | schema      | full        | NULL       | 2022-07-07 16:03:11.962683 |       NULL |  NULL |      true       | GRANT ALL ON SCHEMA public TO admin; GRANT CREATE, USAGE ON SCHEMA public TO public; GRANT ALL ON SCHEMA public TO root;                                                                  | admin
movr            | public             | users                      | table       | full        | NULL       | 2022-07-07 16:03:11.962683 |      57787 |   634 |      true       | GRANT ALL ON TABLE users TO admin; GRANT ALL ON TABLE users TO root;                                                                                                                      | root

You will receive an error if there is a collection of backups in the storage location that you pass to SHOW BACKUP. It is necessary to run SHOW BACKUP with the specific backup directory rather than the backup collection's top-level directory. Use SHOW BACKUPS IN with your storage location to list the backup directories it contains, which can then be run with SHOW BACKUP to inspect the metadata.

Show details for scheduled backups

When a backup is created by a schedule, it is stored within a collection of backups in the given collection URI. To view details for a backup created by a schedule, you can use the following:

Show an encrypted backup

Depending on how the backup was encrypted, use the encryption_passphrase option and the same passphrase that was used to create the backup:

icon/buttons/copy
SHOW BACKUP FROM '2020/09/24-190540.54' IN 's3://{bucket name}?AWS_ACCESS_KEY_ID={placeholder}&AWS_SECRET_ACCESS_KEY={placeholder}'
      WITH encryption_passphrase = 'password123';

Or, use the kms option and the same KMS URI that was used to create the backup:

icon/buttons/copy
SHOW BACKUP FROM '2020/09/24-190540.54' IN 's3://test/backups/test_explicit_kms?AWS_ACCESS_KEY_ID=123&AWS_SECRET_ACCESS_KEY=123'
      WITH kms = 'aws:///arn:aws:kms:us-east-1:123456789:key/1234-abcd-5678-efgh-90ij?AWS_ACCESS_KEY_ID=123456&AWS_SECRET_ACCESS_KEY=123456&REGION=us-east-1';
  database_name | parent_schema_name |        object_name         | object_type | backup_type | start_time |          end_time                 | size_bytes | rows | is_full_cluster
----------------+--------------------+----------------------------+-------------+-------------+------------------------------------------------+------------+------+------------------
  NULL          | NULL               | system                     | database    | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |       NULL | NULL |      true
  system        | public             | users                      | table       | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |        144 |    3 |      true
  system        | public             | zones                      | table       | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |        201 |    7 |      true
  system        | public             | settings                   | table       | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |        431 |    6 |      true
  system        | public             | ui                         | table       | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |          0 |    0 |      true
  system        | public             | jobs                       | table       | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |     434302 |   62 |      true
  system        | public             | locations                  | table       | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |        261 |    5 |      true
  system        | public             | role_members               | table       | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |        184 |    2 |      true
  system        | public             | comments                   | table       | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |          0 |    0 |      true
  system        | public             | scheduled_jobs             | table       | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |        875 |    2 |      true
  NULL          | NULL               | defaultdb                  | database    | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |       NULL | NULL |      true
  NULL          | NULL               | postgres                   | database    | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |       NULL | NULL |      true
  NULL          | NULL               | movr                       | database    | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |       NULL | NULL |      true
  movr          | public             | users                      | table       | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |       4911 |   50 |      true
  movr          | public             | vehicles                   | table       | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |       3182 |   15 |      true
  movr          | public             | rides                      | table       | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |     156387 |  500 |      true
  movr          | public             | vehicle_location_histories | table       | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |      73918 | 1000 |      true
  movr          | public             | promo_codes                | table       | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |     216083 | 1000 |      true
  movr          | public             | user_promo_codes           | table       | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |          0 |    0 |      true
  defaultdb     | NULL               | org_one                    | schema      | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |       NULL | NULL |      true
(20 rows)

Show a backup with descriptor IDs

Use WITH debug_ids to display the descriptor IDs related to each object in the backup:

icon/buttons/copy
SHOW BACKUP FROM '/2021/11/15-150703.21' IN 'external://backup_s3' WITH debug_ids;
database_name | database_id | parent_schema_name | parent_schema_id |        object_name         | object_id | object_type | backup_type |         start_time         |          end_time          | size_bytes | rows  | is_full_cluster
--------------+-------------+--------------------+------------------+----------------------------+-----------+-------------+-------------+----------------------------+----------------------------+------------+-------+------------------
NULL          |        NULL | NULL               |             NULL | movr                       |        52 | database    | full        | NULL                       | 2021-10-04 15:18:29.872912 |       NULL |  NULL |      false
movr          |          52 | public             |               29 | users                      |        53 | table       | full        | NULL                       | 2021-10-04 15:18:29.872912 |      35876 |   392 |      false
movr          |          52 | public             |               29 | vehicles                   |        54 | table       | full        | NULL                       | 2021-10-04 15:18:29.872912 |      25404 |   129 |      false
movr          |          52 | public             |               29 | rides                      |        55 | table       | full        | NULL                       | 2021-10-04 15:18:29.872912 |     280020 |   971 |      false
movr          |          52 | public             |               29 | vehicle_location_histories |        56 | table       | full        | NULL                       | 2021-10-04 15:18:29.872912 |     865205 | 12686 |      false
movr          |          52 | public             |               29 | promo_codes                |        57 | table       | full        | NULL                       | 2021-10-04 15:18:29.872912 |     229155 |  1043 |      false
movr          |          52 | public             |               29 | user_promo_codes           |        58 | table       | full        | NULL                       | 2021-10-04 15:18:29.872912 |      10824 |   128 |      false
NULL          |        NULL | NULL               |             NULL | movr                       |        52 | database    | incremental | 2021-10-04 15:18:29.872912 | 2021-10-04 15:18:53.354707 |       NULL |  NULL |      false
movr          |          52 | public             |               29 | users                      |        53 | table       | incremental | 2021-10-04 15:18:29.872912 | 2021-10-04 15:18:53.354707 |          0 |     0 |      false
movr          |          52 | public             |               29 | vehicles                   |        54 | table       | incremental | 2021-10-04 15:18:29.872912 | 2021-10-04 15:18:53.354707 |          0 |     0 |      false
movr          |          52 | public             |               29 | rides                      |        55 | table       | incremental | 2021-10-04 15:18:29.872912 | 2021-10-04 15:18:53.354707 |          0 |     0 |      false
movr          |          52 | public             |               29 | vehicle_location_histories |        56 | table       | incremental | 2021-10-04 15:18:29.872912 | 2021-10-04 15:18:53.354707 |          0 |     0 |      false
movr          |          52 | public             |               29 | promo_codes                |        57 | table       | incremental | 2021-10-04 15:18:29.872912 | 2021-10-04 15:18:53.354707 |          0 |     0 |      false
movr          |          52 | public             |               29 | user_promo_codes           |        58 | table       | incremental | 2021-10-04 15:18:29.872912 | 2021-10-04 15:18:53.354707 |          0 |     0 |      false

Validate a backup's files

  1. Use SHOW BACKUP ... check_files with a backup for validation:

    icon/buttons/copy
    SHOW BACKUP "2022/09/19-134123.64" IN "s3://bucket?AWS_ACCESS_KEY_ID={Access Key ID}&AWS_SECRET_ACCESS_KEY={Secret Access Key}" WITH check_files;
    

    This will return the following output after validating that the backup files are correct and present:

      database_name | parent_schema_name |        object_name         | object_type | backup_type | start_time |          end_time          | size_bytes | rows  | is_full_cluster | file_bytes
    ----------------+--------------------+----------------------------+-------------+-------------+------------+----------------------------+------------+-------+-----------------+-------------
      NULL          | NULL               | movr                       | database    | full        | NULL       | 2022-09-19 13:41:23.645189 |       NULL |  NULL |        f        |       NULL
      movr          | NULL               | public                     | schema      | full        | NULL       | 2022-09-19 13:41:23.645189 |       NULL |  NULL |        f        |       NULL
      movr          | public             | users                      | table       | full        | NULL       | 2022-09-19 13:41:23.645189 |      31155 |   340 |        f        |      16598
      movr          | public             | vehicles                   | table       | full        | NULL       | 2022-09-19 13:41:23.645189 |      22282 |   113 |        f        |      12459
      movr          | public             | rides                      | table       | full        | NULL       | 2022-09-19 13:41:23.645189 |     261950 |   902 |        f        |     135831
      movr          | public             | vehicle_location_histories | table       | full        | NULL       | 2022-09-19 13:41:23.645189 |     742557 | 10850 |        f        |     318583
      movr          | public             | promo_codes                | table       | full        | NULL       | 2022-09-19 13:41:23.645189 |     228320 |  1034 |        f        |     118376
      movr          | public             | user_promo_codes           | table       | full        | NULL       | 2022-09-19 13:41:23.645189 |       9320 |   111 |        f        |       4832
    

    The output will return file_bytes along with the columns you receive from SHOW BACKUP without check_files. The file_bytes column indicates the estimated bytes in external storage for a particular table object. For more detail on the output columns, see the SHOW BACKUP Response table.

  2. If SHOW BACKUP ... check_files cannot read from a file, it will return an error message similar to the following:

    ERROR: The following files are missing from the backup:
      s3:/bucket-name/2022/09/19-134123.64/data/797981063156727810.sst 
    

    SHOW BACKUP ... check_files will return up to ten file paths for incorrect or missing files.

For more information on validating a backup, see the Backup Validation page.

Show a backup's internal metadata

Use the WITH as_json option to output a backup's internal metadata, contained in its manifest file, as a JSON value:

icon/buttons/copy
SHOW BACKUP FROM '/2021/11/15-150703.21' IN 'external://backup_s3' WITH as_json;

The response will include a manifest column with the file's contents as the JSON value. Use JSONB functions to query particular data or edit the format of the response.

Note:

The response returned from SHOW BACKUP FROM ... WITH as_json is a backup's internal metadata. This content is subject to change from version to version of CockroachDB and does not offer the same stability guarantees as the other SHOW BACKUP options and their responses. As a result, as_json should only be used for debugging or general inspection purposes.

For example, to return a specific entry from the JSON response as a string indented and with newlines use the jsonb_pretty() function:

icon/buttons/copy
WITH x AS (SHOW BACKUP FROM '/2021/11/15-150703.21' IN 'external://backup_s3' with as_json) SELECT jsonb_pretty(manifest->'entryCounts') AS f FROM x;
  {
      "dataSize": "458371",
      "indexEntries": "1015",
      "rows": "2565"
  }

To query for particular data, use the jsonb_array_elements() function to expand the desired elements from the JSON response. The following query returns the paths to each of the data files within the backup:

icon/buttons/copy
WITH x AS (SHOW BACKUP FROM '/2021/11/15-150703.21' IN 'external://backup_s3' WITH as_json) SELECT f->>'path' FROM (SELECT jsonb_array_elements(manifest->'files') AS f FROM x);
          ?column?
-------------------------------
  data/710798326337404929.sst
  data/710798326337404929.sst
  data/710798328891998209.sst
  data/710798326337404929.sst
  data/710798326337404929.sst
  data/710798328434982913.sst
  data/710798328891998209.sst
  data/710798326337404929.sst
  data/710798326337404929.sst

See also


Yes No
On this page

Yes No