Database export and import
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. OAuth and Kerberos authentication methods are not supported.
Other exports
This section is about exporting data to another Vertica database. For information about exporting data to files, which can then be used in external tables or COPY statements, see File export.