sys.backup_snapshot

This system view returns a list of backup snapshots in the current database. Backup snapshots in this view derive from both database replication cycles and regular backup operations.

Column Name Data Type Description
database_id bigint The unique ID for the database.
backup_txid bigint The transaction ID for the backup snapshot.
backup_txsnap text This value is for internal use only.
snapshot_name text The name of the backup snapshot.
  • Snapshots that start with ybd_ and end with _epoch are used by the system internally during a full backup.
  • Backup snapshots that are the results of full, cumulative, or incremental backups taken with the ybbackup tool have the names they were given when those backups were run (--name option).
  • Backup snapshots for replication cycles are prefixed with the replica name.
creation_time timestamptz When the snapshot was created. Replication snapshots are created at regular intervals, based on the frequency set for the replica.

Examples

For example, Dec17Full is the result of a full backup:
premdb=# select * from sys.backup_snapshot;
 database_id | backup_txid | backup_txsnap |                 snapshot_name                  |          creation_time         
-------------+-------------+---------------+------------------------------------------------+-------------------------------
       16391 |        1260 | 3:3:          | ybd_af97315a-622b-ab81-ad4d-59ac80ec6fc5_epoch | 2019-12-17 18:03:01.502767-08
       16391 |        1262 | 1262:1262:    | Dec17Full                                      | 2019-12-17 18:03:01.511742-08
(2 rows)
(The initial snapshot that begins with ybd_ is an internally generated snapshot.)
The entries in this example that are prefixed with premdb_local_replica1 are all the result of replication cycles:
premdb=# select * from sys.backup_snapshot;
 database_id | backup_txid |  backup_txsnap   |                 snapshot_name                  |          creation_time         
-------------+-------------+------------------+------------------------------------------------+-------------------------------
       16432 |        4119 | 0:3:3:           | ybd_0035c89e-ccec-9fe5-f1f4-59fe687a960f_epoch | 2020-01-13 16:07:56.195664-08
       16432 |        4123 | 0:4123:4123:     | premdb_local_replica1_20_01_14_00_07_56        | 2020-01-13 16:07:56.204702-08
       16432 |        4542 | 0:4542:4542:     | premdb_local_replica1_20_01_14_00_08_56        | 2020-01-13 16:08:56.058381-08
       16432 |        4581 | 0:4581:4581:     | premdb_local_replica1_20_01_14_00_09_56        | 2020-01-13 16:09:56.058157-08
       16432 |        4624 | 0:4624:4624:     | premdb_local_replica1_20_01_14_00_10_56        | 2020-01-13 16:10:56.065316-08
       16432 |        4709 | 0:4709:4709:     | premdb_local_replica1_20_01_14_00_11_56        | 2020-01-13 16:11:56.071239-08
       16432 |        4749 | 0:4749:4749:     | premdb_local_replica1_20_01_14_00_12_56        | 2020-01-13 16:12:56.05596-08
       16432 |        4788 | 0:4788:4788:     | premdb_local_replica1_20_01_14_00_13_56        | 2020-01-13 16:13:56.054972-08
       16432 |        4827 | 0:4827:4827:     | premdb_local_replica1_20_01_14_00_14_56        | 2020-01-13 16:14:56.064773-08
       16432 |        4866 | 0:4866:4866:     | premdb_local_replica1_20_01_14_00_15_56        | 2020-01-13 16:15:56.063238-08
       16432 |        4926 | 0:4926:4926:     | premdb_local_replica1_20_01_14_00_16_56        | 2020-01-13 16:16:56.038004-08
       16432 |        5043 | 0:5043:5043:     | premdb_local_replica1_20_01_14_00_17_56        | 2020-01-13 16:17:56.056254-08
       16432 |        5222 | 0:5222:5222:     | premdb_local_replica1_20_01_14_00_18_56        | 2020-01-13 16:18:56.040732-08
...
The following example joins this view with sys.database to return the database name. The query also constrains on snapshots that have the prefix premrep, which is a replica with a frequency of 90 seconds:
premdb=# select name dbname, snapshot_name, creation_time 
from sys.backup_snapshot sb, sys.database sd 
where sb.database_id=sd.database_id and snapshot_name like 'premrep%'
order by creation_time;
 dbname |       snapshot_name       |         creation_time         
--------+---------------------------+-------------------------------
 premdb | premrep_20_04_01_13_52_13 | 2020-04-01 13:52:13.345599-07
 premdb | premrep_20_04_01_13_53_42 | 2020-04-01 13:53:42.200495-07
 premdb | premrep_20_04_01_13_55_12 | 2020-04-01 13:55:12.204403-07
 premdb | premrep_20_04_01_13_56_42 | 2020-04-01 13:56:42.217729-07
 premdb | premrep_20_04_01_13_58_12 | 2020-04-01 13:58:12.203774-07
 ...

Logging of Backup Snapshots

The backup snapshots logged by this view are filtered. Snapshots that are no longer usable as the basis for a subsequent backup are removed. For example, consider a database that is both the target of a sequence of backups and the source of database replication to another system. At this point in the log recorded by the view, one full backup and two incremental backups have been taken in quick succession (snapshots beginning with April1). Meanwhile replication is occurring on a regular schedule for this database:

premdb=# select * from sys.backup_snapshot order by creation_time;
 database_id | backup_txid |  backup_txsnap   |                 snapshot_name                  |         creation_time         
-------------+-------------+------------------+------------------------------------------------+-------------------------------
       16395 |      137080 | 0:3:3:           | ybd_f89f1179-08cc-43f9-a1c2-1ed724d886f1_epoch | 2020-03-31 18:43:12.156275-07
       16395 |      186750 | 0:186750:186750: | April1Full                                     | 2020-04-01 13:19:34.955359-07
       16395 |      186855 | 0:186855:186855: | premrep_20_04_01_13_20_42                      | 2020-04-01 13:20:42.200568-07
       16395 |      189364 | 0:189364:189364: | premrep_20_04_01_13_22_12                      | 2020-04-01 13:22:12.194519-07
       16395 |      189495 | 0:189495:189495: | April1Inc                                      | 2020-04-01 13:23:22.827597-07
       16395 |      189537 | 0:189537:189537: | premrep_20_04_01_13_23_42                      | 2020-04-01 13:23:42.207404-07
       16395 |      189659 | 0:189659:189659: | premrep_20_04_01_13_25_12                      | 2020-04-01 13:25:12.21102-07
       16395 |      190025 | 0:190025:190025: | premrep_20_04_01_13_26_42                      | 2020-04-01 13:26:42.197756-07
       16395 |      190211 | 0:190211:190211: | April1Inc2                                     | 2020-04-01 13:26:54.542625-07
       16395 |      190300 | 0:190300:190300: | premrep_20_04_01_13_28_12                      | 2020-04-01 13:28:12.197437-07
       16395 |      190351 | 0:190351:190351: | premrep_20_04_01_13_29_42                      | 2020-04-01 13:29:42.18738-07
       16395 |      190400 | 0:190400:190400: | premrep_20_04_01_13_31_12                      | 2020-04-01 13:31:12.20491-07
       16395 |      190528 | 0:190528:190528: | premrep_20_04_01_13_32_42                      | 2020-04-01 13:32:42.195353-07
...
Next, some changes are made to the database and a new cumulative backup is taken. Now the results of the system view query look like this:
premdb=# select * from sys.backup_snapshot order by creation_time;
 database_id | backup_txid |  backup_txsnap   |                 snapshot_name                  |         creation_time         
-------------+-------------+------------------+------------------------------------------------+-------------------------------
       16395 |      137080 | 0:3:3:           | ybd_f89f1179-08cc-43f9-a1c2-1ed724d886f1_epoch | 2020-03-31 18:43:12.156275-07
       16395 |      191574 | 0:191574:191574: | April1Cumu                                     | 2020-04-01 13:52:05.523348-07
       16395 |      191621 | 0:191621:191621: | premrep_20_04_01_13_52_13                      | 2020-04-01 13:52:13.345599-07
(3 rows)

The new backup, April1Cumu resets the "horizon" for backup and restore operations. Snapshots prior to April1Cumu cannot be used as the starting point of a new backup, so the previous three backups are all removed from the history. The same rule applies to the replication cycles that occurred before the cumulative backup. All of those snapshots are obsolete as well.

A few minutes later the query is run again:
premdb=# select * from sys.backup_snapshot order by creation_time;
 database_id | backup_txid |  backup_txsnap   |                 snapshot_name                  |         creation_time         
-------------+-------------+------------------+------------------------------------------------+-------------------------------
       16395 |      137080 | 0:3:3:           | ybd_f89f1179-08cc-43f9-a1c2-1ed724d886f1_epoch | 2020-03-31 18:43:12.156275-07
       16395 |      191574 | 0:191574:191574: | April1Cumu                                     | 2020-04-01 13:52:05.523348-07
       16395 |      191621 | 0:191621:191621: | premrep_20_04_01_13_52_13                      | 2020-04-01 13:52:13.345599-07
       16395 |      191761 | 0:191761:191761: | premrep_20_04_01_13_53_42                      | 2020-04-01 13:53:42.200495-07
       16395 |      191810 | 0:191810:191810: | premrep_20_04_01_13_55_12                      | 2020-04-01 13:55:12.204403-07
(5 rows)

Two new snapshots have been added, as created by replication cycles that run every 90 seconds. These snapshots, and subsequently created snapshots, are all valid and remain in the view until a new full or cumulative backup marks them obsolete.

Although backup snapshots may be removed from the view history, this behavior has no effect on backups that are already persisted and stored in backup bundles. These physical backups may be required for the purpose of restoring a database. For example, if you run the ybbackupctl --list command for this scenario, you will see the following results: 
Restore points:

        type                                snapshot_name            backup_timestamp   size (GB)  
        FULL                                   April1Full 2020-04-01T20:19:34.955359Z        1.00  
 INCREMENTAL                                    April1Inc 2020-04-01T20:23:22.827597Z        0.51  
 INCREMENTAL                                   April1Inc2 2020-04-01T20:26:54.542625Z        2.02  
  CUMULATIVE                                   April1Cumu 2020-04-01T20:52:05.523348Z        3.61

A full database restore to snapshot April1Cumu will start restoring from snapshot April1Full, regardless of the fact that this snapshot is no longer present in the results of the sys.backup_snapshot view.