remapping schema names with pg_restore?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

remapping schema names with pg_restore?

S. Bob

Hi All;


Is it possible to re-map / rename schemas when doing a schema based restore from a custom formatted dump file. I have a dump I created like this:


pg_dump  -Fc  --schema=prod_test  prod_db   >   prod_db.dmp

I want to restore  it into another cluster, into a db named integ_db but I want the schema in the restored db to be named integ_test instead of prod_test


Is there a way  to do this at  pg_restore time? without loading into a schema named prod_test and renaming the schema after the restore?


Thanks in advance



Reply | Threaded
Open this post in threaded view
|

Re: remapping schema names with pg_restore?

pavan95
Hi Bob,

As noted, there's no direct support in pg_dump, psql or pg_restore to change the schema name during a dump/restore process. But it's fairly straightforward to export using "plain" format then modify the .sql file. This Bash script does the basics:

rename_schema () {

  # Change search path so by default everything will go into the specified schema
  perl -pi -e "s/SET search_path = $2, pg_catalog/SET search_path = $3, pg_catalog, $2;/" "$1"

  # Change 'ALTER FUNCTION foo.' to 'ALTER FUNCTION bar.'
  perl -pi -e 's/^([A-Z]+ [A-Z]+) '$2'\./$1 '$3'./' "$1"

  # Change the final GRANT ALL ON SCHEMA foo TO PUBLIC
  perl -pi -e 's/SCHEMA '$2'/SCHEMA '$3'/' "$1"

}

Usage:

pg_dump --format plain --schema=foo --file dump.sql MYDB
rename_schema dump.sql foo bar
psql -d MYDB -c 'CREATE SCHEMA bar;'
psql -d MYDB -f dumpsql

Regards,
Pavanteja,
9841380956

On Wed, Feb 26, 2020, 4:49 AM S.Bob <[hidden email]> wrote:

Hi All;


Is it possible to re-map / rename schemas when doing a schema based restore from a custom formatted dump file. I have a dump I created like this:


pg_dump  -Fc  --schema=prod_test  prod_db   >   prod_db.dmp

I want to restore  it into another cluster, into a db named integ_db but I want the schema in the restored db to be named integ_test instead of prod_test


Is there a way  to do this at  pg_restore time? without loading into a schema named prod_test and renaming the schema after the restore?


Thanks in advance