Message-Id: <1450801154.79237.474120345.758EF3FA@webmail.messagingengine.com>
From: CN <cnliou9@fastmail.fm>
To: pgsql-admin@postgresql.org
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: multipart/alternative; boundary="_----------=_1450801154792372";
 charset="utf-8"
In-Reply-To: 
 <1450369108.2740801.470182945.59E4E975@webmail.messagingengine.com>
References: 
 <1450338245.2626908.469822713.6C14EE8F@webmail.messagingengine.com>
 <CAGDYbUNK2tQZz4SfFBZit8o5wqHgy3Hz3SOgVhxkHh3aZ1DOwg@mail.gmail.com>
 <1450365586.2726209.470114761.326B7E39@webmail.messagingengine.com>
 <CABzCKRCYkP400=0ZqOKPsYEb7twKjX8fcQ96hFMGf+6ZsGys=A@mail.gmail.com>
 <1450369108.2740801.470182945.59E4E975@webmail.messagingengine.com>
Subject: Re: feature request: pg_restore renaming target schema
Date: Wed, 23 Dec 2015 00:19:14 +0800
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

This is a multi-part message in MIME format.

--_----------=_1450801154792372
Content-Transfer-Encoding: 7bit
Content-Type: text/plain

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


--_----------=_1450801154792372
Content-Transfer-Encoding: 7bit
Content-Type: text/html

<!DOCTYPE html>
<html>
<head>
<title></title>
</head>
<body><div>I am pleased to revoke this request.</div>
<div>&nbsp;</div>
<div>Once option -O is applied to pg_dump, only two lines in the dumped file need to be edited.</div>
<div>&nbsp;</div>
<div>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.</div>
<div>&nbsp;</div>
<div>#!/bin/sh</div>
<div>SOURCE_DATABASE=sd</div>
<div>TARGET_DATABASE=td</div>
<div>TEMPLATE_SCHEMA=ss</div>
<div>NEW_SCHEMA=ts</div>
<div>pg_dump -O -E utf8 -n $TEMPLATE_SCHEMA -w -U postgres $SOURCE_DATABASE 2&gt;&amp;1 |</div>
<div>awk -v source="$TEMPLATE_SCHEMA" -v target="$NEW_SCHEMA" '</div>
<div>BEGIN {</div>
<div>	pattern1="CREATE SCHEMA " source ";";</div>
<div>	pattern2="SET search_path = " source ", pg_catalog;";</div>
<div>	pattern1_is_done=0</div>
<div>	pattern2_is_done=0</div>
<div>	is_data=0;</div>
<div>	print "BEGIN;"; #Wrap all statements in single transaction.</div>
<div>}</div>
<div>{</div>
<div>	if(!is_data){</div>
<div>		if(substr($0,1,2) == "--")</div>
<div>			$0="";</div>
<div>		else if(!pattern1_is_done){</div>
<div>			if($0 == pattern1){</div>
<div>				$0="CREATE SCHEMA " target ";";</div>
<div>				pattern1_is_done=1;</div>
<div>			}</div>
<div>		}</div>
<div>		else if(!pattern2_is_done){</div>
<div>			if($0 == pattern2){</div>
<div>				$0="SET search_path TO " target ";";</div>
<div>				pattern2_is_done=1;</div>
<div>			}</div>
<div>		}</div>
<div>		else if(match($0,/^COPY .+ FROM stdin;$/) &gt; 0)</div>
<div>				is_data=1;</div>
<div>	}	</div>
<div>	print</div>
<div>}</div>
<div>END{</div>
<div>	print "COMMIT;"; #Wrap all statements in single transaction.</div>
<div>}</div>
<div>' |</div>
<div>psql -q -o /dev/null -w -U postgres $TARGET_DATABASE 2&gt;&amp;1</div>
<div>vacuumdb -q -Z -w -U postgres $TARGET_DATABASE 2&gt;&amp;1</div>
<blockquote type="cite"><br></blockquote><div>&nbsp;</div>
<pre>
-- 
http://www.fastmail.com - The professional email service
</pre>
</body>
</html>

--_----------=_1450801154792372--