Generate SQL DDL Create Scripts for Greenplum Database Objects using pg_dump Utility

On Greenplum Data Warehouse, just like PostgreSQL databases SQL developers can use pg_dump utility to auto generate DDL CREATE statements of database objects easily. Database developers create SQL DDL statements of database objects to create the same database structure on a separate database by executing the pg_dump utility output file. It is a way of copying a database without its data or without its content into another database.

In this Greenplum database tutorial, I want to show how easy it is to use pg_dump utility if developers require to generate DDL scripts for all database objects in a Greenplum database.

Here is a sample script to generate DDL statements for all schema objects within Greenplum database named "dev"

After connecting to Greenplum master node, following command line script can be executed which will create a file name dev-schema.sql including all SQL statement for all objects in dev database.

This pg_dump creates a single SQL script file including only the database objects DDL statements without data.
Here is a screenshot of executed commands on Greenplum master node

Since I run the Greenplum cluster on AWS cloud environment and a direct SSH connection cannot be established, I copy the SQL script file containing DDL scripts of the Greenplum database objects, to an Amazon S3 bucket using AWS CLI command.

Of course, be sure that AWS CLI is installed and configured on the Pivotal Greenplum node where you execute above command.

Later the file is copied into AWS S3 bucket, I can download it to my computer easily either using an other AWS CLI copy command or via AWS Management Console.
SQL developers can open the SQL scripts file using a SQL editor like DBeaver or DbVisualizer.
The SQL script file contains such CREATE command for different database objects as seen in following image:

The autocatically created Greenplum database dump scipt includes CREATE statements for all database objects including schemas, tables, extensions, external tables, SQL functions, etc.

Of course pg_dump command can be executed with different options which enable Pivotal Greenplum data warehouse developers to generate scripts including data as well.
On the other hand, these files can be quite big to handle.