Sunday 26 January 2014

Copy all objects of one schema to other schema with in same database


There are two ways to copy all objects of one schema to other schema with in the same database.

Procedure 1:
------------
1. Take the dump of Source schema using below command.
$./pg_dump -U <username> -p <port> -n <schema name> <database name> >> <dump filename>
Ex:-
-bash-4.1$ /opt/PostgreSQL/9.3/bin/pg_dump -U postgres -p 5435 -n test postgres >> /tmp/test_dmp.sql
2. Open the dumpfile which was created in step 1, and remove the "CREATE SCHEMA" and "ALTER SCHEMA" commands. Below are for example:
CREATE SCHEMA test;

ALTER SCHEMA test OWNER TO postgres;
3. Modify the Source schema name with Target schema name in the dump file.
4. Restore in the database by using below command.

$./psql -U <username> -p <port> -d <database> -f <dump filename>

Ex:- 
$./psql -U postgres -p 5435 -d postgres -f dump.sql
Or connect to psql prompt and execute the dump file from target database.
postgres=#\i <dump file location>
Ex:- #\i dump.sql
Procedure 2:
 ------------ --
-- Rename the target schema to different name and rename the source schema to target schema name. Take the dump of source schema which is renamed to target schema(You can use step 1 in procedure 1 to take the dump). And rename the source schema and target schema to their previous names. And then restore the schema(you can use step 4 in procedure 1 to restore the dump)

 For example your source schema name is test1 and target schema name is test2.
 1. Rename the test2 schema to test3 and rename the test1 schema to test2.
postgres=#ALTER SCHEMA <schemaname> RENAME TO <schemaname>;
2. Take the dump of test2.
$./pg_dump -U <username> -p <port> -n <schema name> <database name> >> <dump filename>
3. Rename test2 to test1 and test3 to test2.
postgres=#ALTER SCHEMA <schemaname> RENAME TO <schemaname>;
4. Restore the dump of test2 which is taken in step 2.
$./psql -U <username> -p <port> -d <database> -f <dump filename>
Note: You can ignore the errors of schema already exists during the restore process as target schema is already exists.

2 comments:

  1. Hi Baji,

    I need your help, here two procedures proc_get_subscriber_info are running with same name in one data base. I have tried to rename but getting error.( ERROR: type "proc_get_subscriber_info" does not exist). I have also tried drop/delete/replace but none of this is working. We are using PostgreSQL.

    ReplyDelete