Thread: export a schema / import as new schema
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? 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
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: > 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 > >
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: > Look here: > http://sqlmanager.net/en/products/postgresql > They aren't cheap, but they seem to work well. > > Thanks, > > Craig Boyd > > David Kerr 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. >> >> Thanks >> >> Dave >> >> >
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 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