SHOW BACKUP

On this page Carat arrow pointing down

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 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 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

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.

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
On this page

Yes No