PostgreSQL: How to create full copy of database schema in same database?

There's no simple way to do this in pg_dump/pg_restore itself. You could try the following if you are able to remove the database temporarily.

1. Take a dump of your public schema using pg_dump
2. run "ALTER SCHEMA public RENAME TO public_copy"
3. Restore your dump of your public schema from step 1 using pg_restore

pg_dump -n schema_name > dump.sql
vi dump.sql # edit the schema name
psql: psql -f dump.sql

If you're stuck with php then use either back tics

`/usr/bin/pg_dump-n myschema mydb -U username > /tmp/dump.sql`

or the exec() command. For the change you can use sed the same way.

Here are 6 more chars

Using pgAdmin you can do the following. It's pretty manual, but might be all you need. A script based approach would be much more desirable. Not sure how well this will work if you don't have admin access and if your database is large, but should work just fine on a development database that you just have on your local computer.

1. Right-click schema name you want to copy and click Backup. (You can go deeper than this and choose to just backup the structure instead of both).

2. Give the backup file a name and also choose a format. (I usually use Tar.)

3. Click Backup.

4. Right-click the schema you backed up from and click properties and rename it to something else temporarily. (e.g. temprename)

5. Click the schemas root and right-click it in the object browser and click create new schema and give the schema the name public. This will be the schema you are copying into from your backup.

6. Right-click the new schema public from step 5. and click restore. Restore from the backup file in step 3.

7. Rename new schema public to a different name (e.g. newschema).

8. Rename schema temprename change from step 4 back to the original name.