SHOW BACKUP

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

Required privileges

SHOW BACKUP requires read permissions to its target destination.

The source file URL does not require the ADMIN role in the following scenarios:

  • S3 and GS using SPECIFIED (and not IMPLICIT) credentials. Azure is always SPECIFIED by default.
  • Userfile

The source file URL does require the ADMIN role in the following scenarios:

We recommend using cloud storage for bulk operations.

Synopsis

SHOW BACKUPS IN location BACKUP subdirectory IN SCHEMAS location WITH kv_option_list OPTIONS ( kv_option_list )

Parameters

Parameter Description
SHOW BACKUPS IN location List the backup paths in the given location. See the example below.
SHOW BACKUP location Show the details of the backup in the given location. See the example below.
SHOW BACKUP SCHEMAS location Show the schema details of the backup in the given location. See the example below.
SHOW BACKUP subdirectory IN location List the full and incremental backups that are stored in the given full backup's subdirectory within a location. See the example below.
kv_option_list Control the behavior of SHOW BACKUP with a comma-separated list of these options.

Options

Option Value Description
privileges N/A List which users and roles had which privileges on each table in the backup. Displays original ownership of the backup.
encryption_passphrase STRING The passphrase used to encrypt the files (BACKUP manifest and data files) that the BACKUP statement generates.
debug_ids N/A New in v21.2: Display descriptor IDs of every object in the backup, including the object's database and parent schema.
as_json N/A New in v21.2: Display the backup's manifest as JSON in the response.

Response

The following fields are returned:

Field Description
database_name The database name.
parent_schema_name The name of the parent schema.
object_name The name of the database, table, type, or schema.
object_type The type of object: database, table, type, or schema.
backup_type New in v21.2: The type of backup: full or incremental.
start_time 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 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 The size of the backup, in bytes.
create_statement 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 Whether the backup is of a full cluster or not.
path The list of the full backup's subdirectories. This field is returned for SHOW BACKUPS IN location 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.

Example

Show a backup

icon/buttons/copy
> SHOW BACKUP 's3://test/backup-test?AWS_ACCESS_KEY_ID=[placeholder]&AWS_SECRET_ACCESS_KEY=[placeholder]';
  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)

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 's3://test/backup-test?AWS_ACCESS_KEY_ID=[placeholder]&AWS_SECRET_ACCESS_KEY=[placeholder]';
          path
------------------------
  2020/09/24-204152.88
  2020/09/24-204623.44
  2020/09/24-205612.40
  2020/09/24-207328.36
(4 rows)

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

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 '2020/09/24-204152.88' IN 's3://test/backup-test?AWS_ACCESS_KEY_ID=[placeholder]&AWS_SECRET_ACCESS_KEY=[placeholder]';
  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 20:41:52.880553+00:00 |       NULL | NULL |      true
  system        | public             | users                      | table       | full              | NULL                             | 2020-09-24 20:41:52.880553+00:00 |        144 |    3 |      true
  system        | public             | zones                      | table       | full              | NULL                             | 2020-09-24 20:41:52.880553+00:00 |        201 |    7 |      true
  system        | public             | settings                   | table       | full              | NULL                             | 2020-09-24 20:41:52.880553+00:00 |        875 |    6 |      true
  system        | public             | ui                         | table       | full              | NULL                             | 2020-09-24 20:41:52.880553+00:00 |          0 |    0 |      true
  system        | public             | jobs                       | table       | full              | NULL                             | 2020-09-24 20:41:52.880553+00:00 |     795117 |   80 |      true
  system        | public             | locations                  | table       | full              | NULL                             | 2020-09-24 20:41:52.880553+00:00 |        261 |    5 |      true
  system        | public             | role_members               | table       | full              | NULL                             | 2020-09-24 20:41:52.880553+00:00 |        184 |    2 |      true
  system        | public             | comments                   | table       | full              | NULL                             | 2020-09-24 20:41:52.880553+00:00 |          0 |    0 |      true
  system        | public             | scheduled_jobs             | table       | full              | NULL                             | 2020-09-24 20:41:52.880553+00:00 |       1013 |    2 |      true
  NULL          | NULL               | defaultdb                  | database    | full              | NULL                             | 2020-09-24 20:41:52.880553+00:00 |       NULL | NULL |      true
  NULL          | NULL               | postgres                   | database    | full              | NULL                             | 2020-09-24 20:41:52.880553+00:00 |       NULL | NULL |      true
  NULL          | NULL               | movr                       | database    | full              | NULL                             | 2020-09-24 20:41:52.880553+00:00 |       NULL | NULL |      true
  movr          | public             | users                      | table       | full              | NULL                             | 2020-09-24 20:41:52.880553+00:00 |       4911 |   50 |      true
  movr          | public             | vehicles                   | table       | full              | NULL                             | 2020-09-24 20:41:52.880553+00:00 |       3182 |   15 |      true
  movr          | public             | rides                      | table       | full              | NULL                             | 2020-09-24 20:41:52.880553+00:00 |     156387 |  500 |      true
  movr          | public             | vehicle_location_histories | table       | full              | NULL                             | 2020-09-24 20:41:52.880553+00:00 |      73918 | 1000 |      true
  movr          | public             | promo_codes                | table       | full              | NULL                             | 2020-09-24 20:41:52.880553+00:00 |     216083 | 1000 |      true
  movr          | public             | user_promo_codes           | table       | full              | NULL                             | 2020-09-24 20:41:52.880553+00:00 |          0 |    0 |      true
  defaultdb     | NULL               | org_one                    | schema      | full              | NULL                             | 2020-09-24 20:41:52.880553+00:00 |       NULL | NULL |      true
  NULL          | NULL               | system                     | database    | incremental       | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00        |       NULL | NULL |      true
  system        | public             | users                      | table       | incremental       | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00        |          0 |    0 |      true
  system        | public             | zones                      | table       | incremental       | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00        |          0 |    0 |      true
  system        | public             | settings                   | table       | incremental       | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00        |          0 |    0 |      true
  system        | public             | ui                         | table       | incremental       | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00        |          0 |    0 |      true
  system        | public             | jobs                       | table       | incremental       | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00        |     102381 |    1 |      true
  system        | public             | locations                  | table       | incremental       | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00        |          0 |    0 |      true
  system        | public             | role_members               | table       | incremental       | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00        |          0 |    0 |      true
  system        | public             | comments                   | table       | incremental       | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00        |          0 |    0 |      true
  system        | public             | scheduled_jobs             | table       | incremental       | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00        |       1347 |    2 |      true
  NULL          | NULL               | defaultdb                  | database    | incremental       | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00        |       NULL | NULL |      true
  NULL          | NULL               | postgres                   | database    | incremental       | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00        |       NULL | NULL |      true
  NULL          | NULL               | movr                       | database    | incremental       | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00        |       NULL | NULL |      true
  movr          | public             | users                      | table       | incremental       | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00        |          0 |    0 |      true
  movr          | public             | vehicles                   | table       | incremental       | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00        |          0 |    0 |      true
  movr          | public             | rides                      | table       | incremental       | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00        |          0 |    0 |      true
  movr          | public             | vehicle_location_histories | table       | incremental       | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00        |          0 |    0 |      true
  movr          | public             | promo_codes                | table       | incremental       | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00        |          0 |    0 |      true
  movr          | public             | user_promo_codes           | table       | incremental       | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00        |          0 |    0 |      true
  defaultdb     | NULL               | org_one                    | schema      | incremental       | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00        |       NULL | NULL |      true
(40 rows)

Show a backup with schemas

icon/buttons/copy
> SHOW BACKUP SCHEMAS 's3://test/backup-test?AWS_ACCESS_KEY_ID=[placeholder]&AWS_SECRET_ACCESS_KEY=[placeholder]';
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                      | 2021-10-04 15:19:18.25435  |       NULL | NULL |      false      | NULL
movr          | public             | users       | table       | full        | NULL                      | 2021-10-04 15:19:18.25435  |      35876 |  392 |      false      | CREATE TABLE users (
              |                    |             |             |             |                           |                            |            |      |                 |     id UUID NOT NULL,
              |                    |             |             |             |                           |                            |            |      |                 |     city VARCHAR NOT NULL,
              |                    |             |             |             |                           |                            |            |      |                 |     name VARCHAR NULL,
              |                    |             |             |             |                           |                            |            |      |                 |     address VARCHAR NULL,
              |                    |             |             |             |                           |                            |            |      |                 |     credit_card VARCHAR NULL,
              |                    |             |             |             |                           |                            |            |      |                 |     CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
              |                    |             |             |             |                           |                            |            |      |                 |     FAMILY "primary" (id, city, name, address, credit_card)
              |                    |             |             |             |                           |                            |            |      |                 | )
NULL          | NULL               | movr        | database    | incremental | 2021-10-04 15:19:18.25435 | 2021-10-04 15:19:38.005984 |       NULL | NULL |      false      | NULL
movr          | public             | users       | table       | incremental | 2021-10-04 15:19:18.25435 | 2021-10-04 15:19:38.005984 |          0 |    0 |      false      | CREATE TABLE users (
              |                    |             |             |             |                           |                            |            |      |                 |     id UUID NOT NULL,
              |                    |             |             |             |                           |                            |            |      |                 |     city VARCHAR NOT NULL,
              |                    |             |             |             |                           |                            |            |      |                 |     name VARCHAR NULL,
              |                    |             |             |             |                           |                            |            |      |                 |     address VARCHAR NULL,
              |                    |             |             |             |                           |                            |            |      |                 |     credit_card VARCHAR NULL,
              |                    |             |             |             |                           |                            |            |      |                 |     CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
              |                    |             |             |             |                           |                            |            |      |                 |     FAMILY "primary" (id, city, name, address, credit_card)
              |                    |             |             |             |                           |                            |            |      |                 | )
. . .

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 's3://test/backup-test?AWS_ACCESS_KEY_ID=[placeholder]&AWS_SECRET_ACCESS_KEY=[placeholder]' 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       | 2020-09-24 19:05:40.542168+00:00 |       NULL | NULL |      true       | GRANT GRANT, SELECT ON system TO admin; GRANT GRANT, SELECT ON system TO root;                                                                                            | root
  system        | public             | users                      | table       | full        | NULL       | 2020-09-24 19:05:40.542168+00:00 |        144 |    3 |      true       | GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON users TO admin; GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON users TO root;                                              | root
  system        | public             | zones                      | table       | full        | NULL       | 2020-09-24 19:05:40.542168+00:00 |        201 |    7 |      true       | GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON zones TO admin; GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON zones TO root;                                              | root
  system        | public             | settings                   | table       | full        | NULL       | 2020-09-24 19:05:40.542168+00:00 |        431 |    6 |      true       | GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON settings TO admin; GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON settings TO root;                                        | root
  system        | public             | ui                         | table       | full        | NULL       | 2020-09-24 19:05:40.542168+00:00 |          0 |    0 |      true       | GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON ui TO admin; GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON ui TO root;                                                    | root
  system        | public             | jobs                       | table       | full        | NULL       | 2020-09-24 19:05:40.542168+00:00 |     434302 |   62 |      true       | GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON jobs TO admin; GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON jobs TO root;                                                | root
  system        | public             | locations                  | table       | full        | NULL       | 2020-09-24 19:05:40.542168+00:00 |        261 |    5 |      true       | GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON locations TO admin; GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON locations TO root;                                      | root
  system        | public             | role_members               | table       | full        | NULL       | 2020-09-24 19:05:40.542168+00:00 |        184 |    2 |      true       | GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON role_members TO admin; GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON role_members TO root;                                | root
  system        | public             | comments                   | table       | full        | NULL       | 2020-09-24 19:05:40.542168+00:00 |          0 |    0 |      true       | GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON comments TO admin; GRANT SELECT ON comments TO public; GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON comments TO root;    | root
  system        | public             | scheduled_jobs             | table       | full        | NULL       | 2020-09-24 19:05:40.542168+00:00 |        875 |    2 |      true       | GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON scheduled_jobs TO admin; GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON scheduled_jobs TO root;                            | root
  NULL          | NULL               | defaultdb                  | database    | full        | NULL       | 2020-09-24 19:05:40.542168+00:00 |       NULL | NULL |      true       | GRANT ALL ON defaultdb TO admin; GRANT CREATE ON defaultdb TO max; GRANT ALL ON defaultdb TO root;                                                                        | root
  NULL          | NULL               | postgres                   | database    | full        | NULL       | 2020-09-24 19:05:40.542168+00:00 |       NULL | NULL |      true       | GRANT ALL ON postgres TO admin; GRANT ALL ON postgres TO root;                                                                                                            | root
  NULL          | NULL               | movr                       | database    | full        | NULL       | 2020-09-24 19:05:40.542168+00:00 |       NULL | NULL |      true       | GRANT ALL ON movr TO admin; GRANT ALL ON movr TO root;                                                                                                                    | root
  movr          | public             | users                      | table       | full        | NULL       | 2020-09-24 19:05:40.542168+00:00 |       4911 |   50 |      true       | GRANT ALL ON users TO admin; GRANT ALL ON users TO root;                                                                                                                  | root
  movr          | public             | vehicles                   | table       | full        | NULL       | 2020-09-24 19:05:40.542168+00:00 |       3182 |   15 |      true       | GRANT ALL ON vehicles TO admin; GRANT ALL ON vehicles TO root;                                                                                                            | root
  movr          | public             | rides                      | table       | full        | NULL       | 2020-09-24 19:05:40.542168+00:00 |     156387 |  500 |      true       | GRANT ALL ON rides TO admin; GRANT ALL ON rides TO root;                                                                                                                  | root
  movr          | public             | vehicle_location_histories | table       | full        | NULL       | 2020-09-24 19:05:40.542168+00:00 |      73918 | 1000 |      true       | GRANT ALL ON vehicle_location_histories TO admin; GRANT ALL ON vehicle_location_histories TO root;                                                                        | root
  movr          | public             | promo_codes                | table       | full        | NULL       | 2020-09-24 19:05:40.542168+00:00 |     216083 | 1000 |      true       | GRANT ALL ON promo_codes TO admin; GRANT ALL ON promo_codes TO root;                                                                                                      | root
  movr          | public             | user_promo_codes           | table       | full        | NULL       | 2020-09-24 19:05:40.542168+00:00 |          0 |    0 |      true       | GRANT ALL ON user_promo_codes TO admin; GRANT ALL ON user_promo_codes TO root;                                                                                            | root
  defaultdb     | NULL               | org_one                    | schema      | full        | NULL       | 2020-09-24 19:05:40.542168+00:00 |       NULL | NULL |      true       |                                                                                                                                                                           | root
(20 rows)

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 location. 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 's3://test/backup-test?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 '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

New in v21.2: Use WITH debug_ids to display the descriptor IDs related to each object in the backup:

icon/buttons/copy
SHOW BACKUP 's3://test/backup-test?AWS_ACCESS_KEY_ID=[placeholder]&AWS_SECRET_ACCESS_KEY=[placeholder]' 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

See also

YesYes NoNo