Exporting the catalog

Vertica function EXPORT_CATALOG generates a SQL script for copying a database design to another cluster.

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