{"id":2498,"date":"2023-02-14T08:00:21","date_gmt":"2023-02-14T08:00:21","guid":{"rendered":"https:\/\/osdbtechnologies.com\/?p=2498"},"modified":"2023-02-14T08:00:21","modified_gmt":"2023-02-14T08:00:21","slug":"remap_schema-in-postgresql","status":"publish","type":"post","link":"https:\/\/divaind.com\/ie1\/2023\/02\/14\/remap_schema-in-postgresql\/","title":{"rendered":"REMAP_SCHEMA IN POSTGRESQL"},"content":{"rendered":"

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\u2019t have such parameter.<\/h3>\n

Below is the way how I have done that in my dev environment.<\/p>\n

PostgreSQL Version is 9.6<\/strong><\/p>\n

Source schema : s_schema (Database name : noncompliance)
\nDestination schema : d_schema (Database name : compliance)<\/p><\/blockquote>\n

Scenario 1:\u00a0<\/strong><\/h3>\n

If the source schema does not exists in the destination database but destination schema is there and it has some objects.<\/p>\n

    \n
  1. Restore the complete schema<\/li>\n
  2. Change the schema for all restored tables<\/li>\n<\/ol>\n
    ---- Take dump in custom format \npg_dump -Fc -n s_schema -f s_schema.dmp noncompliance\n---- Restore the schema in destination database\npg_restore -Fc -d compliance s_schema.dmp\n---- Change the schema for restored tables\nDO\n$$\nDECLARE r record;\nBEGIN\n    FOR r IN SELECT 'ALTER TABLE s_schema.'||tablename||' SET SCHEMA d_schema' as query FROM pg_tables WHERE schemaname = 's_schema'\n    LOOP\n        EXECUTE r.query;\n    END LOOP;\nEND$$;\n----- We can see the schema of the table is changed\ncompliance=# dt d_schema.*\n               List of relations\n  Schema  |       Name       | Type  |  Owner\n----------+------------------+-------+----------\n d_schema | pgbench_accounts | table | postgres\n d_schema | pgbench_branches | table | postgres\n d_schema | pgbench_history  | table | postgres\n d_schema | pgbench_tellers  | table | postgres\n(4 rows)<\/pre>\n
    Scenario 2<\/strong><\/h2>\n
    If the source schema already exists in the destination database and assume it has other objects too.<\/p>\n
      \n
    1. Take the backup of source schema s_schema<\/li>\n
    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)<\/li>\n
    3. Restore the file<\/li>\n<\/ol>\n
      ---- Take backup from the source database\npg_dump -Fc -n s_schema -f s_schema.dmp noncompliance\n---- Replace the s_schema with d_schema\nsed -i 's\/s_schema\/d_schema\/g' s_schema.dmp\n---- Restore the file\npg_restore -Fc -n d_schema -d compliance s_schema.dmp\n---- The objects are restored directly into the destination schema\ncompliance=# dt d_schema.*\n               List of relations\n  Schema  |       Name       | Type  |  Owner\n----------+------------------+-------+----------\n d_schema | pgbench_accounts | table | postgres\n d_schema | pgbench_branches | table | postgres\n d_schema | pgbench_history  | table | postgres\n d_schema | pgbench_tellers  | table | postgres\n d_schema | student          | table | postgres\n(5 rows)<\/pre>\n
      NOTE:<\/strong><\/p>\n
      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.<\/p>\n<\/blockquote>\n
      -bash-4.2$ pg_restore -Fc -c --if-exists -t student -d compliance s_schema.dmp\npg_restore: [custom archiver] could not read from input file: end of file<\/pre>\n
       <\/p>\n","protected":false},"excerpt":{"rendered":"
      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\u2019t have such parameter. Below is the way how I have done that in…<\/p>\n","protected":false},"author":1,"featured_media":2507,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23],"tags":[189,266],"class_list":["post-2498","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-linux","tag-linux","tag-postgresql","category-23","description-off"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2498","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/comments?post=2498"}],"version-history":[{"count":0,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2498\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media\/2507"}],"wp:attachment":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media?parent=2498"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/categories?post=2498"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/tags?post=2498"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}