Backing up and restoring PGAA tables on WarehousePG

PGAA tables store their data in object storage, not in the WarehousePG cluster. The gpbackup utility , provided by the whpg-backup package, supports mixed databases containing both PGAA and regular heap tables. For PGAA tables, gpbackup captures only metadata (table mappings, storage locations, and path bindings), not the underlying data in object storage. This metadata is automatically restored by gprestore, so PGAA tables are immediately queryable after restore without manual reconfiguration.

Protecting the underlying data in object storage is the responsibility of your object storage provider.

Prerequisites

whpg-backup version 1.33 or later installed in your WarehousePG cluster.

Backing up

Run gpbackup on the coordinator:

gpbackup --dbname mydb --backup-dir /path/to/backup-dir

For a full list of options, see the gpbackup reference page.

Note the backup timestamp from the output. You will need it for the restore step. For example:

[INFO]:-Backup Timestamp = 20260417100433

Restoring

Use --create-db to have gprestore create the target database, and --redirect-db to restore into a database other than the one that was backed up. gprestore creates the pgaa and pgfs extensions automatically.

When restoring into an existing database that already has PGAA tables, a table is skipped if its metadata already exists, so only the missing tables are restored.

1. Run gprestore:

gprestore --backup-dir /path/to/backup-dir --timestamp <backup-timestamp> \
  --redirect-db mydb_restored --create-db

For a full list of options, see the gprestore reference page.

1. Confirm the restore succeeded by checking Tables restored in the gprestore output.

2. Verify that storage locations are restored and PGAA tables are immediately queryable against object storage:

SELECT * FROM pgfs.list_storage_locations();
SELECT count(*) FROM my_table;