Migrate entire Schema in Vertica

In this article i will show how to migrate a schema from one Vertica Cluster(source) to another Vertica Cluster(target).
We could make use of the “COPY FROM” command to move data between our databases but this is not the case.

I will use the public schema as the candidate schema for our demo.

We need to export the DDL(Data Definition Language) of all underlining objects of public schema from the source database.

-for this task we will use the build-in function EXPORT_OBJECTS().

Next we will generate the command that will export all the data our of the public schema tables from the source database.

  • the script will generate the export command for each table encountered in the public schema

The output will be something like this:

  • where the result will be piped and then zipped and stored in to a file called schema.table_name.gz
  • also the data will be delimited by pipe ‘|’ as ruled by -F$.

Execute the /tmp/exp_public_data.sh

  • this will export all of the data for the the tables in the public schema from the source database.
Similar Article  Export and Import a Table in Vertica

Create the import script

-the output of this script will be executed on the target Vertica database after the DDL script was executed and the data was copied on to the target server.
-is very important that the data files be present in the tmp directory, if you choose another directory make the required changes in the export imp_public_data.sql script.

Copy all the scripts and the data files to the target host.

Log in to the target host

Log in into your Vertica database

 Execute the  file that will create the objects of the public schema

 Execute the imp_public_data.sql file, this will import/load the data into the tables.

Data is ready to be queried now.


I hope this was helpful and i appreciate if you would like, subscribe or share our posts. I it is just a click


No Comments
Leave a Comment

%d bloggers like this: