Problem: Need to duplicate the content of say schema001 inside a DB name dbtest001 to a new schema with name schema999.
Specs: Postgres 8.2.17
Solution:
1. Login as root, su - postgres
2. create a backup first of the DB dbtest001, command below:
pg_dump -U postgres dbtest001 -f /tmp/dbtest001-backup.sql
3. psql dbtest001
4. We need to rename the schema001 by alter command:
SCHEMA schema001 RENAME TO schema999;
then control d to exit inside psql.
5. After renaming it to the new schema name, dump the database schema only for the particular schema.
pg_dump -U postgres dbtest001 --schema=schema999 --schema-only -f /tmp/schema999.sql
6. Rename back the schema999 to its original name - schema001.
psql dbtest001
SCHEMA schema999 RENAME TO schema001;
7. Now create schema999
CREATE SCHEMA schema999;
control d to exist again from psql console.
8. Now restore the dumped file on the database.
psql -U postgres -d dbtest001 < /tmp/schema999.sql
The schema999 should now been populated with the content of schema001.
-Ohbet
Edit:
01302012
There is a request to do this on a live system, so I cannot rename the schema, and for searching, some mentioned the dump the original schema to a file, rename the schema name on that file to a new name, here im using sed to rename then create the new schema on the DB and restore the dumped file.
1. Of course, create a backup first. - see 1 above
2. Dump the existing schema.
pg_dump -U postgres dbtest001 --schema=schema001 --schema-only -f /tmp/schema001.sql
3. Rename the schema001 entry on that file and dump in on another file named schema999.
sed 's\schema001\schema999\g' /tmp/schema001.sql > /tmp/schema999.sql
4. psql dbtest001
5. Create the new schema.
CREATE SCHEMA schema999;
6. Dump the edited schema named schema999.sql
psql -U postgres -d dbtest001 < /tmp/schema999.sql
Note: This is the same result as doing the above, the advantage is you dont need to rename the schema, since its a live system.
