How to duplicate schemas in PostgreSQL

I will share a solution for my problem which was the same with a small addition. I needed to clone a schema, create a new database user and assign ownership of all objects in the new schema to that user.

For the following example let's assume that the reference schema is called ref_schema and the target schema new_schema. The reference schema and all the objects within are owned by a user called ref_user.

1. dump the reference schema with pg_dump:

pg_dump -n ref_schema -f dump.sql database_name

2. create a new database user with the name new_user:

CREATE USER new_user

3. rename the schema ref_schema to new_schema:

ALTER SCHEMA ref_schema RENAME TO new_schema

4. change ownership of all objects in the renamed schema to the new user

REASSIGN OWNED BY ref_user TO new_user

5. restore the original reference schema from the dump

psql -f dump.sql database_name

I hope someone finds this helpful.

A bit late to the party but, some sql here could help you along your way:

get schema oid:

namespace_id = SELECT oid 
                  FROM pg_namespace 
                 WHERE nspname = '<schema name>';

get table's oid:

table_id = SELECT relfilenode 
                FROM pg_class 
               WHERE relnamespace = '<namespace_id>' AND relname = '<table_name>'

get foreign key constraints:

SELECT con.conname, pg_catalog.pg_get_constraintdef(con.oid) AS condef 
  FROM pg_catalog.pg_constraint AS con 
  JOIN pg_class AS cl ON cl.relnamespace = con.connamespace AND cl.relfilenode = con.conrelid 
 WHERE con.conrelid = '<table_relid>'::pg_catalog.oid AND con.contype = 'f';

A good resource for PostgreSQL system tables can be found here. Additionally, you can learn more about the internal queries pg_dump makes to gather dump information by viewing it's source code.

Probably the easiest way to see how pg_dump gathers all your data would be to use strace on it, like so:

$ strace -f -e sendto -s8192 -o pg_dump.trace pg_dump -s -n <schema>
$ grep -oP '(SET|SELECT)\s.+(?=\\0)' pg_dump.trace

You'll still have to sort through the morass of statements but, it should help you piece together a cloning tool programmatically and avoid having to drop to a shell to invoke pg_dump.

I would use pg_dump to dump the schema without data:

-s
--schema-only

Dump only the object definitions (schema), not data.

This option is the inverse of --data-only. It is similar to, but for historical reasons not identical to, specifying --section=pre-data --section=post-data.

(Do not confuse this with the --schema option, which uses the word "schema" in a different meaning.)

To exclude table data for only a subset of tables in the database, see --exclude-table-data.

pg_dump $DB -p $PORT -n $SCHEMA -s -f filename.pgsql

Then rename the schema in the dump (search & replace) and restore it with psql.

psql $DB -f filename.pgsql

Foreign key constraints referencing tables in other schemas are copied to point to the same schema.
References to tables within the same schema point to the respective tables within the copied schema.

You can probably do it from the command line without using files:

pg_dump -U user --schema='fromschema' database | sed 's/fromschmea/toschema/g' | psql -U user -d database

Note that this searches and replaces all occurrences of the string that is your schema name, so it may affect your data.