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().
SELECT EXPORT_OBJECTS('/tmp/PUBLIC_ddl.sql','PUBLIC',true);
Next we will generate the command that will export all the data our of the public schema tables from the source database.
--set the schema name
set schema_name '''public'''
--show only tuples
t
--export the output to this file
o /tmp/exp_public_data.sh;
select 'vsql -At -U dbadmin -w -F$''|'' -c "select * from ' ||
table_schema || '.' || table_name || '" | gzip -f  /tmp/' ||
table_schema || '.' || table_name || '.gz;'
from tables
where table_schema = :schema_name;
  • the script will generate the export command for each table encountered in the public schema
The output will be something like this:
vsql -At -U dbadmin -w -F$'|' -c "select * from public.store_sales_fact" | gzip -f  /tmp/public.store_sales_fact.gz;
  • 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

bash /tmp/exp_public_data.sh
  • this will export all of the data for the the tables in the public schema from the source database.

Create the import script

--set the schema name
set schema_name '''public'''
--show only tuples
t
--export the output to this file
o /tmp/imp_public_data.sql;
select 'COPY ' || table_schema || '.' || table_name ||
' FROM LOCAL ''/tmp/' || table_schema || '.' || table_name ||
'.gz'' GZIP DELIMITER ''|'' direct;'
from tables
where table_schema = :schema_name;
-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.

scp /tmp/* dbadmin@target:/tmp/

Log in to the target host

ssh dbadmin@target

Log in into your Vertica database

vsql -U dbadmin -w <passwd

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

i /tmp/PUBLIC_ddl.sql

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

i /tmp/imp_public_data.sql
Data is ready to be queried now.