Results 1 to 3 of 3
  1. #1
    Join Date
    May 2012
    Posts
    3

    Unanswered: Copying schema objects between schemas

    Hi,

    I'm new to this forum. Our software solutions trying to migrate from Oracle to Postgres due to several factors including licensing fees.

    However we've found several limiting factors for this migration.
    1. Is there any similar way to import/export data from one schema to another? For an example, in oracle I'd take a schema dump using expdp and import in to another user using impdp with remap_schema and remap_tablespace parameters. But I'm unable to find similar method in Postgres or at least a workaround. I can replace the schema name in pg database dump script but it may be risky because it might replace the table data also.

    Hoping to get a answer soon.

    Thanks,

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    There is no direct way of doing this.

    What could work is the following:

    1) run a schema-only dump to get the definition of the objects, something like:
    Code:
    pg_dump --schema=old_schema --schema-only your_db_name > schema.sql
    2) run a data-only dump to get the data only, something like:
    Code:
    pg_dump --schema=old_schema --data-only your_db_name > data.sql
    In the schema file rename the schema accordingly (there is a CREATE SHCEMA, and a SET search_path and all prefixed tables), then run that in the target database.

    In the data file change the line with "SET search_path = ..." and your new schema name to the front. All following statements use unqualified table names and setting the search path to the new schema will make the inserts go into there.

  3. #3
    Join Date
    May 2012
    Posts
    3
    Quote Originally Posted by shammat View Post
    There is no direct way of doing this.

    What could work is the following:

    1) run a schema-only dump to get the definition of the objects, something like:
    Code:
    pg_dump --schema=old_schema --schema-only your_db_name > schema.sql
    2) run a data-only dump to get the data only, something like:
    Code:
    pg_dump --schema=old_schema --data-only your_db_name > data.sql
    In the schema file rename the schema accordingly (there is a CREATE SHCEMA, and a SET search_path and all prefixed tables), then run that in the target database.

    In the data file change the line with "SET search_path = ..." and your new schema name to the front. All following statements use unqualified table names and setting the search path to the new schema will make the inserts go into there.
    Thanks for the reply. This is really helpful. Hoping to write a Perl module for this. Will post it once completed.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •