Step-by-Step Guide to Capture DDL Dump in GPDB 7.X

Products

• VMware Tanzu Data Suite

Issue/Introduction

To capture a DDL dump of a partitioned table in Greenplum, you can use the pg_dump command with specific options to ensure that both the parent table and its child partitions are included in the output. 

 

Prerequisites: 

• Ensure you have access to the Greenplum database and appropriate permissions to run pg_dump.
• You should run these commands from the Greenplum master node for accurate results.
  
  
• The command you will use is:

pg_dump --table=<schema.table_name> --table-and-children --schema-only --gp-syntax -U gpadmin <dbname> > table_ddl.sql

 

Explanation of Options: 

• --table=<schema.table_name>: Specifies the parent table for which you want to dump the DDL.
• --table-and-children: Ensures that all child partitions of the specified table are included in the dump.
• --schema-only: Dumps only the schema (DDL) without any data.
• --gp-syntax: Ensures that the output uses Greenplum-specific SQL syntax.
• -U gpadmin: Specifies the username to connect to the database (replace with your username if different).
• <dbname>: The name of your Greenplum database.
•  table_ddl.sql: Redirects the output to a file named table_ddl.sql.

 

Verification:

• After running the command, you can verify that your DDL dump file contains all necessary definitions by opening sales_data_ddl.sql in a text editor or using a command like:
  
  cat table_ddl.sql
  
  
• This will display the contents of your DDL dump, allowing you to confirm that it includes all relevant partition definitions. By following these steps, you will successfully capture a DDL dump of a partitioned table in Greenplum.

Environment

GPDB 7.X 

Cause

Syntax changes in GPDB 7.X have changed and require changes to the syntax. 

Resolution

Command used to capture DDL dump for partitioned tables. 

pg_dump --table=<schema.table_name> --table-and-children --schema-only --gp-syntax -U gpadmin <dbname> > table_ddl.sql