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 dumpsqlRegards,
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
