Copy schema from one database to another

ssheikh

I am currently running 2 databases on different ports in the same cluster, and I want to copy a schema from one db to the other. It seems like COPY and EXPORT only support tables or columns. 

 

Thanks!

SruthiA

HI,

 

  You can use export_objects to copy entire schema from one database to another.

 

select export_objects('filename','schemaname') will generate a SQL script in the catablog directory of the database with the specified filename. Once it is generated, you can execute the SQL script in another database using

 

\i script_name

 

This will copy the entire schema from one database to another. For more information regarding export_objects, please refer to http://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/VerticaFunctions/EXPORT_OBJECTS.htm?Highlight= export_objects

 

-Regards,

 Sruthi

 

ssheikh

Will this also export the data that was stored in the schema?

SruthiA

Hi,

 

  It just recreates catalog objects in another database. it does not load any data. Once you load the schema into another database, You can use Export to vertica to copy data to another schema

 

For more information on how to use export to vertica, Please refer to http://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/AdministratorsGuide/CopyExportData/ExportingData.htm%3FTocPath%3DAdministrator's%2520Guide%7CCopying%2520and%2520Exporting%2520Data%7C_____1

 

 

-Regards,

 Sruthi

ssheikh

Thank you!

 

However, I'm looking to export all the data stored in the schema as well. Is there a way to go about this?

SruthiA

Hi,

 

 As far as I know, you can do it in two steps.

 

1) export the catalog objects to create projections in another database.

2) After that, use export to vertica to copy data into the projections created in step 1.

 

-Regards,

 Sruthi

ssheikh

Thanks so much. I'll try this out. 

SruthiA

Hi,

 

  Let me know how it goes.

 

 

-Regards,

 Sruthi