Migrate one Schema fast and dirty from one Vertica Cluster to another. This approach is used when :
[dbadmin@aodba]$ vsql
Password:
Welcome to vsql, the Vertica Analytic Database interactive terminal.
Type: h or ? for help with vsql commands
g or terminate with semicolon to execute query
q to quit
- export ddl for the schema
dbadmin= SELECT EXPORT_TABLES('/tmp/testschema.sql','testschema');
EXPORT_TABLES
-------------------------------------
Catalog data exported successfully
(1 row)
-- execute the ddl schema import
dbadmin= ! vsql -U dbadmin -w password -h 111.133.444.222 -d db_name -f /tmp/testschema.sql
CREATE SCHEMA
CREATE SEQUENCE
CREATE SEQUENCE
CREATE SEQUENCE
CREATE SEQUENCE
CREATE SEQUENCE
CREATE SEQUENCE
CREATE SEQUENCE
CREATE SEQUENCE
CREATE SEQUENCE
CREATE SEQUENCE
CREATE SEQUENCE
CREATE SEQUENCE
CREATE SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
SELECT DISTINCT
'vsql -U dbadmin -w $source_passwd$ -h $source_ip$ -d $source_db_name$-At -c "SELECT * from $source_schema$.'
||table_name
||'"| vsql -U dbadmin -w $target_passwd$ -h $target_ip$ -d $target_db_name$ -c "DROP TABLE IF EXISTS public.'
||table_name
||'_reject; truncate table '
||'$target_schema$'
||'.'
||table_name
||'; COPY '
||'$target_schema$'
||'.'
||table_name
||' FROM STDIN DELIMITER ''|'' direct null as '''' REJECTED DATA AS TABLE public.'
||table_name
||'_reject NO ESCAPE;"; vsql -U dbadmin -w $target_passwd$ -h $target_ip$ -d $target_db_name$ -c "SELECT '''
||table_name
||'_reject'' as Table_Name , count(*) as Rejects ,CASE count(*) WHEN 0 THEN ''No Rejects'' ELSE ''You got rejects'' END FROM '
||'public.'
||table_name
||'_reject;"'
FROM
tables
WHERE
table_name IN (select table_name from tables where table_schema=$source_schema$)
vsql -U dbadmin -w sourcepasswd -h 11.111.11.11 -d sourcedbname -At -c
"SELECT * from testschema.tbltest"|
vsql -U dbadmin -w targetpasswd -h 99.999.99.99 -d sourcedbname -c
"DROP TABLE IF EXISTS public.testschema.tbltest_reject; truncate table testschema.tbltest;
COPY testschema.tbltest FROM STDIN DELIMITER '|' direct null as ''
REJECTED DATA AS TABLE public.testschema.tbltest_reject NO ESCAPE;";
vsql -U dbadmin -w targetpasswd -h 99.999.99.99 -d sourcedbname -c
"SELECT 'testschema.tbltest_reject' as Table_Name , count(*) as Rejects ,
CASE count(*) WHEN 0 THEN 'No Rejects' ELSE 'You got rejects' END
FROM public.testschema.tbltest_reject;"
Table_Name | Rejects | case
-------------------------------+---------+-----------------
testschema.tbltest | 11 | You got rejects
(1 row)
Table_Name | Rejects | case
-------------------------------+---------+-----------------
testschema.tbltest | 0 | No Rejects
(1 row)