I am pleased to revoke this request.
Once option -O is applied to pg_dump, only two lines in the dumped file need to be edited.
In case it is also useful to anyone, here is the script I use to dump schema "ss" from source database "sd" and restore as new schema "ts" to database "td". Hard disk is not used as intermediate file is not created. I have a feeling that it is safe to run this script concurrently.
#!/bin/sh
SOURCE_DATABASE=sd
TARGET_DATABASE=td
TEMPLATE_SCHEMA=ss
NEW_SCHEMA=ts
pg_dump -O -E utf8 -n $TEMPLATE_SCHEMA -w -U postgres $SOURCE_DATABASE 2>&1 |
awk -v source="$TEMPLATE_SCHEMA" -v target="$NEW_SCHEMA" '
BEGIN {
pattern1="CREATE SCHEMA " source ";";
pattern2="SET search_path = " source ", pg_catalog;";
pattern1_is_done=0
pattern2_is_done=0
is_data=0;
print "BEGIN;"; #Wrap all statements in single transaction.
}
{
if(!is_data){
if(substr($0,1,2) == "--")
$0="";
else if(!pattern1_is_done){
if($0 == pattern1){
$0="CREATE SCHEMA " target ";";
pattern1_is_done=1;
}
}
else if(!pattern2_is_done){
if($0 == pattern2){
$0="SET search_path TO " target ";";
pattern2_is_done=1;
}
}
else if(match($0,/^COPY .+ FROM stdin;$/) > 0)
is_data=1;
}
print
}
END{
print "COMMIT;"; #Wrap all statements in single transaction.
}
' |
psql -q -o /dev/null -w -U postgres $TARGET_DATABASE 2>&1
vacuumdb -q -Z -w -U postgres $TARGET_DATABASE 2>&1
--
http://www.fastmail.com - The professional email service
