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]']] )

The SQL script conforms to the following requirements:

  • Only includes objects to which the user has access.
  • Orders CREATE statements according to object dependencies so they can be recreated in the correct sequence. For example, if a table is in a non-PUBLIC schema, the required CREATE SCHEMA statement precedes the CREATE TABLE statement. Similarly, a table's CREATE ACCESS POLICY statement follows the table's CREATE TABLE statement.
  • If possible, creates projections with their KSAFE clause, if any, otherwise with their OFFSET clause.

Setting Export Scope

If you omit specifying a scope, EXPORT_CATALOG exports all objects. You can set the scope of the export operation to one of the following levels:

Scope Exports...

TABLES

Tables, schemas, and table-dependent objects: constraints, and access policies

DESIGN

All catalog objects: schemas, tables, constraints, views, access policies, projections, SQL macros, and stored procedures.
DIRECTED_QUERIES All directed queries that are stored in the catalog. For details, see Managing Directed Queries.

Exporting Table Objects

Use the TABLES scope to generate a script that recreates all tables and the objects that they depend on: schemas, sequences, constraints, and access policies: 

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

The SQL script can include the following statements:

  • CREATE SCHEMA
  • CREATE TABLE
  • ALTER TABLE (constraints)
  • CREATE SEQUENCE
  • CREATE ACCESS POLICY
  • CREATE PROCEDURE (Stored)

Exporting All Catalog Objects

Use the DESIGN scope to export all design elements of a source database in order of their dependencies. This scope exports all catalog objects, including schemas, tables, constraints, projections, views, and access policies. 

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

The SQL script include statements that are required to recreate the database:

  • CREATE SCHEMA
  • CREATE TABLE
  • ALTER TABLE (constraints)
  • CREATE VIEW
  • CREATE SEQUENCE
  • CREATE ACCESS
  • CREATE PROJECTION (with ORDER BY and SEGMENTED 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 is portable if all projections are created using UNSEGMENTED ALL NODES or SEGMENTED ALL NODES.

See Also