Backing up and restoring PGAA tables on WarehousePG v1.9

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

Restore PGAA tables into a new, empty database. Restoring into the original database or an existing database with PGAA tables is not supported.

1. Create the target database and create the PGAA and PGFS extensions:

   → WrapCopy

   createdb mydb_restored --template=template0
   psql -d mydb_restored -c "CREATE EXTENSION pgaa CASCADE;"

2. Run gprestore:

   → WrapCopy

   gprestore --backup-dir /path/to/backup-dir --timestamp <backup-timestamp> \
     --redirect-db mydb_restored --on-error-continue

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

   Note

   The --create-db gprestore option isn't supported with PGAA.

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

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

   → WrapCopy

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