Incremental backups

There are two ways backups can be grouped into sets:

See the incremental backup process example below to learn how to create backups in both these ways.

All backups within a single set must be created with compatible gpbackup options. gpbackup checks this when selecting a backup to base a new incremental backup on.

Backups are considered compatible in terms of a backup set if the following parameters match:

To start using incremental backups, you first need to create a full backup. This full backup will serve as the base for all subsequent incremental ones.

The backup must be created with the --leaf-partition-data option:

This command performs a regular Greengage DB backup, which can be restored independently if needed. In this example, the backup has the following timestamp (January 1):

It includes all tables from the database. You can inspect its content on a segment host by listing the backup directory:

Each file corresponds to a table or partition in the database: customers (19135), products (19125), and four partitions of sales.

Once the base backup is in place, you can start creating incremental backups. To create the first incremental backup, run the same command as before but add the --incremental and --leaf-partition-data options:

When invoked with these options, gpbackup automatically searches for a compatible full backup to use as its base. The output displays both the new and the base backup timestamps (January 1 and January 2):

After completion, the new incremental backup forms a backup set together with the base full backup. This is shown in the backup report:

To verify what was captured, check the segment directory:

Here you can see that the incremental backup contains only:

Unchanged partitions of sales are omitted since their data remains valid in the base backup.

From this point, you can either restore the database using this backup set or continue adding new incremental backups to it.

To append another incremental backup to the same backup set, repeat the same command:

If previous incremental backups exist, the new one (January 3) is automatically based on the most recent in the set (January 2):

Now the backup set grows to three backups:

Each new incremental backup is appended sequentially to the chain.

Inspect the content of the latest backup:

This output shows that only the partition of sales (the one with OID 19366) was modified since the previous incremental backup. Heap table customers (19135) is always included in incremental backups.

In some cases, you may want to base an incremental backup on a specific earlier point in time — for example, to merge several backups into one or to capture a custom range of changes for restore testing.

To do this, use gpbackup with the --from-timestamp option and specify the desired base backup timestamp:

This forces gpbackup to base the new backup on the specified timestamp, even if a newer compatible backup exists:

The new backup captures changes between 20250102091528 and 20250104094401. This creates a new backup set that includes the new backup, the selected base backup, and all backups it depends on. The backup report shows this new set:

To fully restore a database from an incremental backup set, run gprestore with the --timestamp option. This option specifies the most recent backup in the set — the one that defines the target database state.

For example, use the timestamp 20250103091621 to restore the backup set created earlier:

This command restores the database as follows:

As with a regular restore, the tables being restored must not already exist in the target database.

If you use the --verbose flag, the command output shows exactly which backups each table is restored from. For example:

This log shows how gprestore selects the correct data snapshot for each table based on when it last changed.

Since any leading subset of a backup set is also valid, you can restore the database to any earlier state simply by specifying the timestamp of an earlier backup. For example, restoring from the 20250102091528 backup would bring the database back to its state as of January 2.

Sometimes you may only need to restore specific data changes from one incremental backup rather than the entire database. This process is called an incremental restore.

An incremental restore restores the following data captured in a single incremental backup:

It’s important to note that incremental restore does not apply individual row changes. Instead, it replaces the affected tables or partitions with their complete snapshots from the backup. For example, if a single row was added to an AO table, restoring incrementally from that backup will bring the entire table to its state after that insert, not simply append the missing row. Similarly, for partitioned tables, all partitions that were changed are fully rewritten.

To perform an incremental restore, specify the backup timestamp along with two additional options: --incremental and --data-only. The target tables must already exist in the database before running the restore.

This command restores only the data captured in the incremental backup with timestamp 20250103091621. The tables omitted in this backup remain unchanged.