Re: remapping schema names with pg_restore?

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