Using EXPORT functions
Vertica provides several EXPORT_ functions that let you recreate a database, or specific schemas and tables, in a target database. For example, you can use the EXPORT_ functions to transfer some or all of the designs and objects you create in a development or test environment to a production database.
The EXPORT_ functions create SQL scripts that you can run to generate the exported database designs or objects. These functions serve different purposes to the export statements, COPY FROM VERTICA (pull data) and EXPORT TO VERTICA (push data). These statements transfer data directly from source to target database across a network connection between both. They are dynamic actions and do not generate SQL scripts.
The EXPORT_ functions appear in the following table. Depending on what you need to export, you can use one or more of the functions. EXPORT_CATALOG creates the most comprehensive SQL script, while EXPORT_TABLES and EXPORT_OBJECTS are subsets of that function to narrow the export scope.
| Use this function... | To recreate... |
|---|---|
| EXPORT_CATALOG |
These catalog items:
|
| EXPORT_TABLES | Non-virtual objects up to, and including, the schema of one or more tables. |
| EXPORT_OBJECTS | Catalog objects in order dependency for replication. |
The designs and object definitions that the script creates depend on the EXPORT_ function scope you specify. The following sections give examples of the commands and output for each function and the scopes it supports.
Saving scripts for export functions
All of the examples in this section were generated using the standard Vertica VMART database, with some additional test objects and tables. One output directory was created for all SQL scripts that the functions created:
/home/dbadmin/xtest
If you specify the destination argument as an empty string (''), the function writes the export results to STDOUT.