Viswamitra's blog

This is very typical requirement we get in DBA world where we need to copy tables from one schema to another schema. In Oracle we have a option like remap_schema while doing restoration which handles this automatically. But in PostgreSQL we don’t have such parameter.

Below is the way how I have done that in my dev environment.

PostgreSQL Version is 9.6

Source schema : s_schema (Database name : noncompliance)
Destination schema : d_schema (Database name : compliance)

Scenario 1:

If the source schema does not exists in the destination database but destination schema is there and it has some objects.

1. Restore the complete schema
2. Change the schema for all restored tables

---- Take dump in custom format 
pg_dump -Fc -n s_schema -f s_schema.dmp noncompliance
---- Restore the schema in destination database
pg_restore -Fc -d compliance s_schema.dmp
---- Change the schema for restored tables
DO
$$
DECLARE r record;
BEGIN
    FOR r IN SELECT 'ALTER TABLE s_schema.'||tablename||' SET SCHEMA d_schema' as query FROM pg_tables WHERE schemaname = 's_schema'
    LOOP
        EXECUTE r.query;
    END LOOP;
END$$;
----- We can see the schema of the table is changed
compliance=# \dt d_schema.*
               List of relations
  Schema  |       Name       | Type  |  Owner
----------+------------------+-------+----------
 d_schema | pgbench_accounts | table | postgres
 d_schema | pgbench_branches | table | postgres
 d_schema | pgbench_history  | table | postgres
 d_schema | pgbench_tellers  | table | postgres
(4 rows)

Scenario 2

If the source schema already exists in the destination database and assume it has other objects too.

1. Take the backup of source schema s_schema
2. replace the s_schema word in the backup file with d_schema (user data is in binary format only the schema, table definition are in string format so this works. I tested by inserting s_schema name in a varchar column)
3. Restore the file

---- Take backup from the source database
pg_dump -Fc -n s_schema -f s_schema.dmp noncompliance
---- Replace the s_schema with d_schema
sed -i 's/s_schema/d_schema/g' s_schema.dmp
---- Restore the file
pg_restore -Fc -n d_schema -d compliance s_schema.dmp
---- The objects are restored directly into the destination schema
compliance=# \dt d_schema.*
               List of relations
  Schema  |       Name       | Type  |  Owner
----------+------------------+-------+----------
 d_schema | pgbench_accounts | table | postgres
 d_schema | pgbench_branches | table | postgres
 d_schema | pgbench_history  | table | postgres
 d_schema | pgbench_tellers  | table | postgres
 d_schema | student          | table | postgres
(5 rows)

NOTE:

Very careful when running the sed command to replace the string as it is a binary file there are some chances it can corrupt the file and you can get below error.

-bash-4.2$ pg_restore -Fc -c --if-exists -t student -d compliance s_schema.dmp
pg_restore: [custom archiver] could not read from input file: end of file