pg_dump at GPDB 5.x does not capture a sequence attached to a table

Products

• VMware Tanzu Greenplum

Issue/Introduction

When backing up a table using pg_dump at 5.x, if the table has a sequence attached to it, the sequence isn't backed up. Restoring the dump will fail with "ERROR: relation" does not exist. This only happens when backing up with table names, it does not happen when backing the whole schema or DB.

Environment

Product Version: 5.24

Resolution

This is a bug in postgres 8.2, it is resolved in postgres 9.x. Therefore, it is only an issue with GPDB 5.x and GPDB 4.x. GPDB 6.x does not have this issue so upgrade to 6.x to avoid this issue.

To workaround at 5.x, create a backup of the schema or DB and extract the sequence DDL from it.