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

Required privileges

Only members of the admin role can run SHOW BACKUP. By default, the root user belongs to the admin role.

Synopsis

SHOW BACKUP SCHEMAS location

Parameters

Parameter Description
location The location of the backup to inspect. For more details, see Backup File URLs.

Response

The following fields are returned.

Field Description
database_name The database name.
table_name The table name.
start_time The time at which the backup was started. For a full backup, this will be empty.
end_time The time at which the backup was completed.
size_bytes The size of the backup, in bytes.
create_statement New in v19.2: 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.

Example

Show a backup

copy
icon/buttons/copy
> SHOW BACKUP 'azure://acme-co-backup/tpch-2017-03-27-full?AZURE_ACCOUNT_KEY=hash&AZURE_ACCOUNT_NAME=acme-co';
+---------------+---------------+------------+----------------------------------+------------+
| database_name |  table_name   | start_time |             end_time             | size_bytes |
+---------------+---------------+------------+----------------------------------+------------+
| tpch          | nation        |            | 2017-03-27 13:54:31.371103+00:00 |       3828 |
| tpch          | region        |            | 2017-03-27 13:54:31.371103+00:00 |       6626 |
| tpch          | part          |            | 2017-03-27 13:54:31.371103+00:00 |       8128 |
| tpch          | supplier      |            | 2017-03-27 13:54:31.371103+00:00 |       2834 |
| tpch          | partsupp      |            | 2017-03-27 13:54:31.371103+00:00 |       3884 |
| tpch          | customer      |            | 2017-03-27 13:54:31.371103+00:00 |      12736 |
| tpch          | orders        |            | 2017-03-27 13:54:31.371103+00:00 |       6020 |
| tpch          | lineitem      |            | 2017-03-27 13:54:31.371103+00:00 |     729811 |
+---------------+---------------+------------+----------------------------------+------------+
(8 rows)

Time: 32.540353ms

Show a backup with schemas

New in v19.2: You can add number of rows and the schema of the backed up table.

copy
icon/buttons/copy
> SHOW BACKUP SCHEMAS 'nodelocal:///extern/employee.sql';
  database_name | table_name | start_time |             end_time             | size_bytes | rows |                      create_statement
+---------------+------------+------------+----------------------------------+------------+------+-------------------------------------------------------------+
  movr          | users      | NULL       | 2019-09-19 14:51:03.943785+00:00 |       4913 |   50 | 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)
                |            |            |                                  |            |      | )
(1 row)

Time: 30.337ms

See also



Yes No