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.

SELECT EXPORT_OBJECTS('/tmp/PUBLIC_ddl.sql','PUBLIC',true);

--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

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;

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