Copy schema and create new schema with different n

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

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.

Answer 2

This seems to be the best solution I came up to.

The idea is to use pg_dump with -O (no owner) and -o (oids) options to get plain text output without source schema and owner information.

Such output i filter through sed replacing the default entry

SET search_path = source_schema, pg_catalog;

with command to create the new schema and set the default search path to it

CREATE SCHEMA new_schema;
SET search_path = new_schema, pg_catalog;

After that I redirect the stream to psql logging to desired user and database to which copy of the schema will be transfered.

The final command to copy schema 'public' to schema '2016' in the same database 'b1' looks like this:

pg_dump -U postgres -Oo -n public -d b1 | sed 's/SET search_path = public, pg_catalog;/CREATE SCHEMA "2016";SET search_path = "2016", pg_catalog;/' | psql -U postgres -d b1

Please note that GRANTS are not transfered from the source schema to the new one.