Skip site navigation (1) Skip section navigation (2)

Re: export a schema / import as new schema

From: Jasen Betts <jasen(at)xnet(dot)co(dot)nz>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: export a schema / import as new schema
Date: 2009-08-30 12:22:00
Message-ID: h7dqt8$84m$1@reversiblemaps.ath.cx (view raw or flat)
Thread:
Lists: pgsql-general
On 2009-08-21, David Kerr <dmk(at)mr-paradox(dot)net> wrote:
> On Fri, Aug 21, 2009 at 12:00:11PM -0700, Joshua D. Drake wrote:
> - On Fri, 2009-08-21 at 11:56 -0700, David Kerr wrote:
> - > Is there an easy way, that I'm missing, where I can export a schema from 
> - > database A and then rename it on load into database B?
> - 
> - pg_dump -s foo|psql bar
>
> Sorry, I wasn't clear.
>
> What I have is:
>
> [Database 1].[Schema 1]->[Data Set 1]
> [Database 2].[Schema 1]->[Data Set 2]
>
> What I want to do is:
>
> Export [Database 1].[Schema 1]->[Data Set 1]
> Import [Database 2].[Schema 2]->[Data Set 1]
>
> Leaving me with
> [Database 2].[Schema 1]-[Data Set 2]
> 	     [Schema 2]-[Data Set 1]
>
> So that i can now compare Data Set 1 and Data Set 2
>
> Currently, I'm creating the new schema in database 1 and then exporting that data
> into database 2, but i was hoping there was a better way.

you just need a little sed magic, (untested)

 pg_dump -s schema1 database1 |
 sed '/^COPY/ /\\./ { p;d } ;
     s/^SET search_path = schema1,/^SET search_path = schema2,/;
     s/ schema1\./ schema2\./;
     s/ SCHEMA schema1 / SCHEMA schema2 /;
     '|
 psql database2

if you need mixed case,spaces,punctuation,etc in the names it's a 
little harder

In response to

pgsql-general by date

Next:From: Jasen BettsDate: 2009-08-30 12:48:02
Subject: Re: Removing older versions
Previous:From: Stephen CuppettDate: 2009-08-30 12:11:13
Subject: Trouble using TG_TABLE_NAME in BEFORE INSERT OR UPDATE trigger

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group