Export schema as a whole and not just tables

Hi Saumya,

Do you want to export the definitions of the schema or you want export the data of all the tables as well?

Thanks

Hi Shobhit,

I want to export the data of all tables too.

I already came across the procedure to export the definitions of the schema by using export_objects function.

I am looking for something which can export the whole schemas data as well at one time rather than one table at a time.

Is there a way to do this? Whats the best way to export a schema with its data as a whole.

Thanks
Saumya

Hi Shobhit,

I want to export the data of all tables too.

I already came across the procedure to export the definitions of the schema by using export_objects function.

I am looking for something which can export the whole schemas data as well at one time rather than one table at a time.

Is there a way to do this? Whats the best way to export a schema with its data as a whole.

Thanks
Saumya

After exports objects, you might have write a script to extract data from the source schema and then load to the target schema ( in the same database). If you are copying to other cluster, you may follow the below:

--- To export the DDL from the 1000 tables
=>select EXPORT_OBJECTS('/tmp/design.sql') ;

--- To create a file with the export command of the 1000 tables
=>\o /tmp/export.sql
=>select 'export to vertica ' || table_schema || '.' || table_name || ' from ' || table_schema || '.' || table_name  from  tables;
=>\o

Then move the files to the new cluster

and create the database and import the design

=>\i << DESIGN FILE >>>

In the production
Connect to the database to the new cluster ( check the examples in the db but it should be like this in the
=>connect to vertica <database> user <dbadmin> password <password> ON '<IP>',<PORT> ;
then call the files that has all the export commands

=>\i /tmp/export.sql

You could also import using the COPY FROM in that case you make the connection from DEV to PROD but need to change the script to generate the COPY FROM commands like this :

=> select 'COPY '|| table_schema || '.' || table_name || ' FROM  ' || table_schema || '.' || table_name  from  tables ;

Is there a way to export PROCEDURE from one SCHEMA to another ?

Hey Mayank, Grant execute privilege to that procedure for the user who wants to execute that procedure on that schema.