Open search page
Hello, I’m DocuDroid!
Open Ask AI page
Submitting feedback
Thank you for rating our AI Search!
We would be grateful if you could share your thoughts so we can improve our AI Search for you and other readers.
GitHub

Partial backups

Pavel Semyonov

This topic describes how to perform a partial backup and restore in Greengage DB by selecting specific schemas and objects to back up.

NOTE

All gpbackup and gprestore commands must be executed on the master host under the gpadmin user account.

By default, gpbackup and gprestore operate on the entire database: all schemas, tables, and other objects belonging to the specified database are backed up and restored at once.

You can fine-tune both backup and restore operations by explicitly selecting which database objects to include or exclude. Filtering lets you reduce backup size and duration when only specific schemas or tables need to be preserved, for example, when migrating part of a database or archiving a subset of data.

Schema-level filtering

To define the backup scope on the schema level, use the following gpbackup options:

  • --exclude-schema — backs up all schemas in the database except the specified one.

  • --include-schema — backs up only the specified schema.

Examples:

$ gpbackup --dbname marketplace --exclude-schema sales
$ gpbackup --dbname marketplace --include-schema sales

Table-level filtering

For more granular control, use the table-level options:

  • --exclude-table — backs up all database tables except the specified one.

  • --include-table — backs up only the specified table.

Table names must be schema-qualified, for example:

$ gpbackup --dbname marketplace --exclude-table sales.orders
$ gpbackup --dbname marketplace --include-table sales.orders

Table-level options also apply to sequences, views, and materialized views.

NOTE

Object names containing uppercase letters or special characters must be enclosed in double quotes, exactly as defined in the database. For example:

$ gpbackup --include-table '"SalesData"."Quarterly Totals"'

All four options can be specified multiple times:

$ gpbackup --dbname marketplace --exclude-table sales.customers --exclude-table sales.orders
NOTE

--include-schema and --exclude-table can be used together to include all objects from a schema except the specified ones. For example, the following command backs up only the sales schema, excluding one table from it:

$ gpbackup --dbname marketplace --include-schema sales --exclude-table sales.audit_log

Other combinations of these options are not allowed.

Dependent objects

When using include filters, you must explicitly list all dependent objects that need to be restored. For example, if you include only a view but not the underlying tables, the restore will fail because the dependencies are missing.

File-based filtering

For large databases, listing all object names in command-line options can be inconvenient. For cases with many objects, there are file-based counterparts for the above options: --exclude-schema-file, --include-schema-file, --exclude-table-file, and --include-table-file.

These options let you specify schemas or tables in a plain text file. The file must include one object name per line, without trailing blank lines, for example:

  • Schemas:

    sales
util
customers
"SalesData"

  • Tables:

    sales.orders
public.test
"SalesData"."Quarterly Totals"

Example:

$ gpbackup --dbname marketplace --exclude-schema-file internal-schemas.txt

Filter data for restore

All the same filtering options are available for gprestore:

  • --exclude-schema

  • --include-schema

  • --exclude-table

  • --include-table

  • Their *-file counterparts.

Examples:

$ gprestore --timestamp 20251010043530 --exclude-schema sales
$ gprestore --timestamp 20251010043530 --include-schema sales --exclude-table sales.orders
$ gprestore --timestamp 20251010043530 --include-table-file restore_tables.txt

Partitioned tables

By default, gpbackup stores all partitions of a partitioned table in a single data file per segment. To gain more control over individual partitions, use the --leaf-partition-data option. It creates separate files for each leaf partition, enabling selective backup and restore at the partition level.

For example, to exclude certain partitions from a backup, use --exclude-table together with --leaf-partition-data. In this case, you can pass leaf partition names as the --exclude-table value:

$ gpbackup --dbname marketplace --leaf-partition-data --exclude-table public.sales_1_prt_2

Similarly, you can include a subset of partitions using --include-table:

$ gpbackup --dbname marketplace --leaf-partition-data --include-table public.sales_1_prt_2
NOTE

Only the root table and leaf partition names can be used in --exclude-table or --include-table. Intermediate partitions in multi-level partition hierarchies cannot be specified directly.

The metadata of the root table is always included when any of its partitions are backed up.

When restoring a subset of partitions, gprestore recreates the root table and restores only the selected partitions. This also works when restoring individual partitions from a full-table backup:

$ gprestore --timestamp 20251010043530 --include-table public.sales_1_prt_2
$ gprestore --timestamp 20251010043530 --exclude-table public.sales_1_prt_2

If some partitions belong to other schemas, inclusion behavior depends on whether --leaf-partition-data is used:

  • With --leaf-partition-data: all partitions are included, even those in excluded schemas.

  • Without --leaf-partition-data: partitions in excluded schemas are not included.

Previous
Full data backup and restore
Next
Incremental backups