Copying Data Between Vertica Databases

Vertica can easily import data from and export data to other Vertica databases. Importing and exporting data is useful for common tasks such as moving data back and forth between a development or test database and a production database, or between databases that have different purposes but need to share data on a regular basis.

Moving Data Directly Between Databases

To move data between databases you first establish a connection using CONNECT TO VERTICA and then use one of the following statements to move data:

These statements are symmetric; copying from cluster A to cluster B is the same as exporting from cluster B to cluster A. The difference is only in which cluster drives the operation.

To configure TLS settings for the connection, see Configuring Connection Security Between Clusters.

Creating SQL Scripts to Export Data

Three functions return a SQL script you can use to export database objects to recreate elsewhere:

While copying and exporting data is similar to Backing Up and Restoring the Database, you should use them for different purposes, outlined below:

Task Backup and Restore COPY and EXPORT Statements

Back up or restore an entire database, or incremental changes

YES

NO

Manage database objects (a single table or selected table rows)

YES

YES

Use external locations to back up and restore your database

YES

NO

Use direct connections between two databases

OBJECT RESTORE ONLY

YES

Use external shell scripts to back up and restore your database

YES

NO

Use SQL commands to incorporate copy and export tasks into DB operations

NO

YES

The following sections explain how you import and export data between Vertica databases.

When importing from or exporting to a Vertica database, you can connect only to a database that uses trusted (username only) or password-based authentication, as described in Security and Authentication. SSL authentication is not supported.

Other Exports

This section is about exporting data to another Vertica database. For information about exporting data to Parquet files in HDFS, see Exporting Data in Parquet Format in Integrating with Apache Hadoop.