Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1a9aN6-0003O8-Ub for pgsql-admin@arkaria.postgresql.org; Thu, 17 Dec 2015 15:21:37 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1a9aN6-0003HY-Hc for pgsql-admin@arkaria.postgresql.org; Thu, 17 Dec 2015 15:21:36 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1a9aLP-0000Wz-AM for pgsql-admin@postgresql.org; Thu, 17 Dec 2015 15:19:51 +0000 Received: from out1-smtp.messagingengine.com ([66.111.4.25]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1a9aLM-0004Yi-Cc for pgsql-admin@postgresql.org; Thu, 17 Dec 2015 15:19:50 +0000 Received: from compute4.internal (compute4.nyi.internal [10.202.2.44]) by mailout.nyi.internal (Postfix) with ESMTP id 115D1208CE for ; Thu, 17 Dec 2015 10:19:47 -0500 (EST) Received: from web6 ([10.202.2.216]) by compute4.internal (MEProxy); Thu, 17 Dec 2015 10:19:47 -0500 DKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; d=fastmail.fm; h= content-transfer-encoding:content-type:date:from:in-reply-to :message-id:mime-version:references:subject:to:x-sasl-enc :x-sasl-enc; s=mesmtp; bh=OCoRo/1vmuUvET6Dn0Ef7a+JM2k=; b=WrKTUU YpvTJpUgVucJi3pVM6SezJQ3xPwCM/ArMpExBd6yj0KdeWYm6qqJbUEHZYHzXmmC 8rtALgPINYmFItPciBKWnDf4+zCJOCxje7E7pHC8vsslzSWpbvRPGrhyYlmitwd9 OiIg1y7MkKzDcdzlKdV8teQy4x2dSxFZcrByw= DKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; d= messagingengine.com; h=content-transfer-encoding:content-type :date:from:in-reply-to:message-id:mime-version:references :subject:to:x-sasl-enc:x-sasl-enc; s=smtpout; bh=OCoRo/1vmuUvET6 Dn0Ef7a+JM2k=; b=J6rUdFwIHzs+k1ueXhhAq5WILPfDpQQHXDIyJe6vQYQAXvp iQUNDOFcWEX2moYUQb4hUodaHhUkg1DVTeSSv0cR7ozHNXV1k9xqoKN0sGheZ/Xa VYWlr7eamtDs7vH3WYfS+wlvxshqig/wQJ1+h9PGK5P8Jx1WfsvPZXqVrWy8= Received: by web6.nyi.internal (Postfix, from userid 99) id C9A944352C; Thu, 17 Dec 2015 10:19:46 -0500 (EST) Message-Id: <1450365586.2726209.470114761.326B7E39@webmail.messagingengine.com> X-Sasl-Enc: 0r28gBlwgEbeZwBII+xuaMSW/jRbz2V0Ayghmd2BEYDc 1450365586 From: CN To: pgsql-admin@postgresql.org MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: multipart/alternative; boundary="_----------=_145036558627262097"; charset="utf-8" X-Mailer: MessagingEngine.com Webmail Interface - ajax-a93c17cb In-Reply-To: References: <1450338245.2626908.469822713.6C14EE8F@webmail.messagingengine.com> Subject: Re: feature request: pg_restore renaming target schema Date: Thu, 17 Dec 2015 23:19:46 +0800 X-Pg-Spam-Score: -2.5 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multi-part message in MIME format. --_----------=_145036558627262097 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset="utf-8" Thank you! Venkataramana, On Thu, Dec 17, 2015, at 09:20 PM, Shreeyansh Dba wrote: > But if you are trying to do with second option like from source > schema(s1) of db1 to target schema(t1) of db2,=C2=A0it is not possible wi= th > pg_restore because while restoring from backup dump, it follows the > sequence of commands like "CREATE SCHEMA s1" "SET SEARCH_PATH=3Ds1" > "CREATE TABLE table_name(id int)" "INSERT INTO table_name VALUES(1)" Indeed. I have just peeked the content of the dumped file in plain format and have noticed that all objects are prefixed with original schema name. This makes me feel that the task of adding the mentioned feature to pg_restore or pg_dump must be nontrivial :-( > 2: If you are taking the dump in plan text format you can edit the > plain text file and replace schema old name to new name and then > you can restore the file using psql -d -U -f plain text file>. > Manual editing is not quite feasible because I need to automate the sequence of jobs by executing pg_dump and pg_restore in program. Best Regards, CN --=20 http://www.fastmail.com - Accessible with your email software or over the web --_----------=_145036558627262097 Content-Transfer-Encoding: 7bit Content-Type: text/html; charset="utf-8"
Thank you! Venkataramana,
 
On Thu, Dec 17, 2015, at 09:20 PM, Shreeyansh Dba wrote:
But if you are trying to do with second option like from source schema(s1) of db1 to target schema(t1) of db2, it is not possible with pg_restore because while restoring from backup dump, it follows the sequence of commands like 
"CREATE SCHEMA s1"
"SET SEARCH_PATH=s1"
"CREATE TABLE table_name(id int)"
"INSERT INTO table_name VALUES(1)"
 
Indeed. I have just peeked the content of the dumped file in plain format and have noticed that all objects are prefixed with original schema name. This makes me feel that the task of adding the mentioned feature to pg_restore or pg_dump must be nontrivial :-(
 
2: If you are taking the dump in plan text format you can edit the plain text file and replace schema old name to new name and then you can restore the file using psql -d <db> -U <user> -f <path of plain text file>.
 
 
Manual editing is not quite feasible because I need to automate the sequence of jobs by executing pg_dump and pg_restore in program.
 
Best Regards,
CN
-- 
http://www.fastmail.com - Accessible with your email software
                          or over the web
--_----------=_145036558627262097--