Copy schema and create new schema with different name in the same data base

Question

I there a way to copy the existing schema and generate new schema with another name in the same database in postgres.

Answer 1

• If by copy schema you mean copy a database, then just use TEMPLATE option to create a copy: CREATE DATABASE dbname_target TEMPLATE dbname_source;

This will copy data too. So you might want to create your own template if you need many copies. See Template Databases.

• If you need only schema, then I suggest that you put your DB DDL scripts under source control (which is a good idea anyways) and have a separate (templated) script which will create the schema for you. Basically you have one SQL file, where you replace a ${schema_name} with your new schema name, and then execute this script on the database. In this way if you make a changes to this schema, you can also have scripts to update the schema to a new version, which you will have to do for every user schema in this case.

Answer 2

Use pg_dump to dump your current schema in a SQL-formated file. Open the file, replace the schemaname with the new name and excute this script in your database to create the new schema and all other objects inside this schema.