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.
- Restore the complete schema
- Change the schema for all restored tables
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | ---- 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.
- Take the backup of source schema s_schema
- 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)
- Restore the file
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | ---- 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.
| 1 2 | -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 |
