Creating Incremental Backups with gpbackup and gprestore

    Incremental backups are efficient when the total amount of data in append-optimized tables or table partitions that changed is small compared to the data that has not changed since the last backup.

    An incremental backup backs up an append-optimized table only if one of the following operations was performed on the table after the last full or incremental backup:

    • ALTER TABLE
    • DELETE
    • INSERT
    • TRUNCATE
    • UPDATE
    • DROP and then re-create the table

    To restore data from incremental backups, you need a complete incremental backup set.

    Parent topic: Parallel Backup with gpbackup and gprestore

    An incremental backup set includes the following backups:

    • A full backup. This is the full backup that the incremental backups are based on.
    • The set of incremental backups that capture the changes to the database from the time of the full backup.

    For example, you can create a full backup and then create three daily incremental backups. The full backup and all three incremental backups are the backup set. For information about using an incremental backup set, see Example Using Incremental Backup Sets.

    When you create or add to an incremental backup set, gpbackup ensures that the backups in the set are created with a consistent set of backup options to ensure that the backup set can be used in a restore operation. For information about backup set consistency, see .

    When you create an incremental backup you include these options with the other gpbackup options to create a backup:

    • --leaf-partition-data - Required for all backups in the incremental backup set.

      • Required when you create a full backup that will be the base backup for an incremental backup set.
      • Required when you create an incremental backup.

    • --from-timestamp - Optional. This option can be used with --incremental. The timestamp you specify is an existing backup. The timestamp can be either a full backup or incremental backup. The backup being created must be compatible with the backup specified with the --from-timestamp option.

      If you do not specify --from-timestamp, gpbackup attempts to find a compatible backup based on information in the gpbackup history file. See Incremental Backup Notes.

    Parent topic:

    Using Incremental Backups

    • --dbname - The database must be the same.

    • --backup-dir - The directory must be the same. The backup set, the full backup and the incremental backups, must be in the same location.

    • --single-data-file - This option must be either specified or absent for all backups in the set.

    • --plugin-config - If this option is specified, it must be specified for all backups in the backup set. The configuration must reference the same plugin binary.

    • --include-table-file, --include-schema, or any other options that filter tables and schemas must be the same.

      When checking schema filters, only the schema names are checked, not the objects contained in the schemas.

    • --no-compression - If this option is specified, it must be specified for all backups in the backup set.

      If compression is used on the on the full backup, compression must be used on the incremental backups. Different compression levels are allowed for the backups in the backup set. For a backup, the default is compression level 1.

    If you try to add an incremental backup to a backup set, the backup operation fails if the gpbackup options are not consistent.

    For information about the gpbackup and gprestore utility options, see and gprestore in the Greenplum Database Utility Guide.

    Each backup has a timestamp taken when the backup is created. For example, if you create a backup on May 14, 2017, the backup file names contain . The hhmmss represents the time: hour, minute, and second.

    This example assumes that you have created two full backups and incremental backups of the database mytest. To create the full backups, you used this command:

    You created incremental backups with this command:

    1. gpbackup --dbname mytest --backup-dir /mybackup --leaf-partition-data --incremental

    When you specify the --backup-dir option, the backups are created in the /mybackup directory on each Greenplum Database host.

    In the example, the full backups have the timestamp keys 20170514054532 and 20171114064330. The other backups are incremental backups. The example consists of two backup sets, the first with two incremental backups, and second with one incremental backup. The backups are listed from earliest to most recent.

    • 20170514054532 (full backup)
    • 20170714095512
    • 20170914081205
    • 20171114064330 (full backup)
    • 20180114051246

    To create a new incremental backup based on the latest incremental backup, you must include the same --backup-dir option as the incremental backup as well as the options --leaf-partition-data and --incremental.

    1. gpbackup --dbname mytest --backup-dir /mybackup --leaf-partition-data --incremental --from-timestamp 20170914081205

    This command creates an incremental backup set based on the full backup 20171114064330 and is separate from the backup set that includes the incremental backup 20180114051246.

    To restore a database with the incremental backup 20170914081205, you need the incremental backups 20120914081205 and 20170714095512, and the full backup 20170514054532. This would be the gprestore command.

    1. gprestore --backup-dir /backupdir --timestamp 20170914081205

    The gpbackup output displays the timestamp of the backup on which the incremental backup is based. In this example, the incremental backup is based on the backup with timestamp 20180802171642. The backup 20180802171642 can be an incremental or full backup.

    When restoring an from an incremental backup, you can specify the --verbose option to display the backups that are used in the restore operation on the command line. For example, the following gprestore command restores a backup using the timestamp 20180807092740, an incremental backup. The output includes the backups that were used to restore the database data.

    2. 20180807:16:31:56 gprestore:gpadmin:mdw:008603-[INFO]:-Pre-data metadata restore complete
    3. 20180807:16:31:56 gprestore:gpadmin:mdw:008603-[DEBUG]:-Verifying backup file count
    4. 20180807:16:31:56 gprestore:gpadmin:mdw:008603-[DEBUG]:-Restoring data from backup with timestamp: 20180807162654
    5. 20180807:16:31:56 gprestore:gpadmin:mdw:008603-[DEBUG]:-Reading data for table public.tbl_ao from file (table 1 of 1)
    6. 20180807:16:31:56 gprestore:gpadmin:mdw:008603-[DEBUG]:-Checking whether segment agents had errors during restore
    7. 20180807:16:31:56 gprestore:gpadmin:mdw:008603-[DEBUG]:-Restoring data from backup with timestamp: 20180807162819
    8. 20180807:16:31:56 gprestore:gpadmin:mdw:008603-[DEBUG]:-Reading data for table public.test_ao from file (table 1 of 1)
    9. 20180807:16:31:56 gprestore:gpadmin:mdw:008603-[DEBUG]:-Checking whether segment agents had errors during restore
    10. 20180807:16:31:56 gprestore:gpadmin:mdw:008603-[DEBUG]:-Restoring data from backup with timestamp: 20180807162904
    11. 20180807:16:31:56 gprestore:gpadmin:mdw:008603-[DEBUG]:-Reading data for table public.homes2 from file (table 1 of 4)
    12. 20180807:16:31:56 gprestore:gpadmin:mdw:008603-[DEBUG]:-Reading data for table public.test2 from file (table 2 of 4)
    13. 20180807:16:31:56 gprestore:gpadmin:mdw:008603-[DEBUG]:-Reading data for table public.homes2a from file (table 3 of 4)
    14. 20180807:16:31:56 gprestore:gpadmin:mdw:008603-[DEBUG]:-Reading data for table public.test2a from file (table 4 of 4)
    15. 20180807:16:31:56 gprestore:gpadmin:mdw:008603-[DEBUG]:-Checking whether segment agents had errors during restore
    16. 20180807:16:31:57 gprestore:gpadmin:mdw:008603-[INFO]:-Data restore complete
    17. 20180807:16:31:57 gprestore:gpadmin:mdw:008603-[INFO]:-Restoring post-data metadata
    18. 20180807:16:31:57 gprestore:gpadmin:mdw:008603-[INFO]:-Post-data metadata restore complete
    19. ...

    The output shows that the restore operation used three backups.

    When restoring an from an incremental backup, gprestore also lists the backups that are used in the restore operation in the gprestore log file.

    During the restore operation, gprestore displays an error if the full backup or other required incremental backup is not available.

    To create an incremental backup, or to restore data from an incremental backup set, you need the complete backup set. When you archive incremental backups, the complete backup set must be archived. You must archive all the files created on the master and all segments.

    Each time gpbackup runs, the utility adds backup information to the history file gpbackup_history.yaml in the Greenplum Database master data directory. The file includes backup options and other backup information.

    If you do not specify the --from-timestamp option when you create an incremental backup, gpbackup uses the most recent backup with a consistent set of options. The utility checks the backup history file to find the backup with a consistent set of options. If the utility cannot find a backup with a consistent set of options or the history file does not exist, gpbackup displays a message stating that a full backup must be created before an incremental can be created.

    If you specify the --from-timestamp option when you create an incremental backup, gpbackup ensures that the options of the backup that is being created are consistent with the options of the specified backup.

    The gpbackup option --with-stats is not required to be the same for all backups in the backup set. However, to perform a restore operation with the gprestore option --with-stats to restore statistics, the backup you specify must have must have used the when creating the backup.

    You can perform a restore operation from any backup in the backup set. However, changes captured in incremental backups later than the backup use to restore database data will not be restored.

    When restoring from an incremental backup set, gprestore checks the backups and restores each append-optimized table from the most recent version of the append-optimized table in the backup set and restores the heap tables from the latest backup.

    The incremental back up set, a full backup and associated incremental backups, must be on a single device. For example, the backups in a backup set must all be on a file system or must all be on a Data Domain system.

    Warning: Changes to the Greenplum Database segment configuration invalidate incremental backups. After you change the segment configuration (add or remove segment instances), you must create a full backup before you can create an incremental backup.