Exporting the Catalog

Vertica function EXPORT_CATALOG generates a SQL script for copying a database design to another cluster. This script replicates the physical schema design of the source database. You call this function as follows:

EXPORT_CATALOG ( '[destination]' [, 'scope' ] )

EXPORT_CATALOG and EXPORT_OBJECTS return equivalent output.

Setting Scope of Export

You can set the scope of the export operation to various levels:

To export… Set scope to…

Schemas, tables, constraints, views, and projections

DESIGN (default)

All design objects and system objects created in Database Designer, such as design contexts and their tables

DESIGN ALL

Only tables, constraints, and projection

TABLES

Exporting All Catalog Objects

Use the DESIGN scope to export all design elements of a source database in order dependency. This scope exports all catalog objects in their OID (unique object ID) order, including schemas, tables, constraints, views, and all types of projections. This is the most comprehensive export scope, without the Database Designer elements, if they exist. 

=> SELECT EXPORT_CATALOG(
     '/home/dbadmin/xtest/sql_cat_design.sql',
     'DESIGN' );
           EXPORT_CATALOG            
-------------------------------------
 Catalog data exported successfully
(1 row)

The SQL script includes the following types of statements, each needed to recreate a new database:

  • CREATE SCHEMA
  • CREATE TABLE
  • CREATE VIEW
  • CREATE SEQUENCE
  • CREATE PROJECTION (with ORDER BY and SEGMENTED BY)
  • ALTER TABLE (to add foreign keys)
  • PARTITION BY

Projection Considerations

If a projection to export was created with no ORDER BY clause, the SQL script reflects the default behavior for projections. Vertica implicitly creates projections using a sort order based on the SELECT columns in the projection definition. The EXPORT_CATALOG script reflects this behavior.

The EXPORT_CATALOG script is portable if all projections were generated using UNSEGMENTED ALL NODES or SEGMENTED ALL NODES.

Exporting Database Designer Schema and Designs

Use the DESIGN ALL scope to generate a script to recreate all design elements of a source database and the design and system objects that were created by the Database Designer: 

=> SELECT EXPORT_CATALOG (
     '/home/dbadmin/xtest/sql_cat_design_all.sql',
     'DESIGN_ALL');
           EXPORT_CATALOG            
-------------------------------------
 Catalog data exported successfully
(1 row)

Exporting Table Objects

Use the TABLES scope to generate a script to recreate all schemas tables, constraints, and sequences: 

=> SELECT EXPORT_CATALOG (
     '/home/dbadmin/xtest/sql_cat_tables.sql',
     'TABLES');
           EXPORT_CATALOG            
-------------------------------------
 Catalog data exported successfully
(1 row)

The SQL script includes the following types of statements:

  • CREATE SCHEMA
  • CREATE TABLE
  • ALTER TABLE (to add constraints)
  • CREATE SEQUENCE

See Also