Can I copy a full schema (for testing needs ) on the same node?

kfruchtman

I need to copy a full schema "public" and call it another name for test purposes .I need all the tables +data +objects+ constraints and everything that is in my "public" schema .It is for test purposes.I want to start a new DBD layout for this new schema and I don't want to create a mess with the working schema.any suggestions?
I have only one node on the cluster with one machine.(ubuntu)

Jim_Knicely

Do you have any other objects other than tables?

If you have just tables to move (and their constraints), you can simply use the ALTER TABLE ... SET SCHEMA to bulk move them from PUBLIC to your new schema...

Example:

dbadmin=> SELECT COUNT(*) FROM tables WHERE table_schema = 'public';
 COUNT
-------
    20
(1 row)

dbadmin=> CREATE SCHEMA my_new_schema;
CREATE SCHEMA

dbadmin=> \! vsql -Atc "SELECT 'ALTER TABLE public.' || table_name || ' SET SCHEMA my_new_schema;' FROM tables WHERE table_schema = 'public';" | vsql
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
dbadmin=> SELECT COUNT(*) FROM tables WHERE table_schema = 'public';
 COUNT
-------
     0
(1 row)

dbadmin=> SELECT COUNT(*) FROM tables WHERE table_schema = 'my_new_schema';
 COUNT
-------
    20
(1 row)

See:
https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Statements/ALTERTABLE.htm

kfruchtman

wow thanks so much for your answer , the thing is that I don´t want to move the tables I want to copy them exactly to a new schema on the same db.

mosheg

COPY_TABLE copies one table to another.
This lightweight, in-memory copy increases performance by initially sharing the same storage between two tables.
The copied table includes copies of any explicitly created projections from the source table.
Once copied, the source and copy tables are independent of each other.
Users can perform operations on one table without impacting the other.
Here is an example:

CREATE SCHEMA my_new_schema;
SET ESCAPE_STRING_WARNING TO OFF;
SET STANDARD_CONFORMING_STRINGS TO OFF;
\a
\t
\o | vsql
SELECT 'SELECT COPY_TABLE (\'public.' || table_name || '\',\'my_new_schema.' || table_name || '\');' FROM tables WHERE table_schema = 'public';

kfruchtman

Thanks! so if I query my new copied table I will have all the data in it?(because data in vertica is stored in projections and not in tables it makes me a bit confused. )
I want to create this new schema for testing purposes so I don't "destroy" the original schema.

mosheg

Q - If I query my new copied table I will have all the data in it?
A - Yes, the copied table includes copies of any explicitly created projections from the source table.

kfruchtman

Thanks again.
Is there a way to bulk copy all objects from the schema or do I need to do it manually table by table?

mosheg

There are other options but the big advantage of the COPY_TABLE method is that the source and target tables initially share the same storage. You can duplicate huge tables very fast, with almost no I/O, and initially with no need for extra storage.
It is advised to do the copy tables with COPY_TABLE sequentially, as shown above.
For more info see: https://www.vertica.com/docs/10.1.x/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/COPY_TABLE.htm

kfruchtman

Thanks so much for your help! I did it and it works.:-)

mosheg

Happy to assist