export a schema / import as new schema

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

I use similar functionality in oracle all the time and it's great for
development environments when you're making schema changes or updating a
lot of data. You can mess up schema A and load a backup into schema B
and fix the data (or whatever).

Thanks

Dave

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering

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.

Thanks

Dave

Look here:
http://sqlmanager.net/en/products/postgresql
They aren't cheap, but they seem to work well.

Thanks,

Craig Boyd

David Kerr wrote:

Thanks.

Yeah, if it's not free i'll just write my own if it becomes too much of
a pain =)

Dave

Boyd, Craig wrote:

On 2009-08-21, David Kerr <dmk@mr-paradox.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