Export schema as a whole and not just tables

saumya_tamma

Hi,

Is there a way to export a complete schema and all its tables in vertica from one database to other?

Export from vertica seems to be exporting one table at a time.

I need to do an copy of complete schema to a different database without the schema existing in the target database. Is this possible?

Thanks
Saumya

Shobhit_Garg

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

saumya_tamma

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

saumya_tamma

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

Prasanta_Pal

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 ;

mayank_gupta

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

Siddarth

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