pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedreplicating postgresql database to ms-sql database
5+ messages / 5 participants
[nested] [flat]
* replicating postgresql database to ms-sql database
@ 2007-07-06 17:48 Mary Anderson <maryfran@demog.berkeley.edu>
0 siblings, 2 replies; 5+ messages in thread
From: Mary Anderson @ 2007-07-06 17:48 UTC (permalink / raw)
To: pgsql-admin
Hi list,
I have been asked if it is possible to asynchronously replicate a
postgresql database to ms-sql. My answer is yes, provided postgresql
ORM features aren't used in the design of the database. One does an
ascii dump of the tables and then bulk loads them into MSSQL. The
database in question will be about 10G. Is there a sane way to do this?
I am hoping the slony can be instructed to produce an ascii transaction
log which can then be massaged and applied to the MS-SQL database.
(This is a database of population statistics which is relatively static.
It will be read only on the ms-sql node)
I think it is much better to have both databases running postgres,
by the way.
mary anderson
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: replicating postgresql database to ms-sql database
@ 2007-07-06 18:15 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Mary Anderson <maryfran@demog.berkeley.edu>
1 sibling, 0 replies; 5+ messages in thread
From: Kevin Grittner @ 2007-07-06 18:15 UTC (permalink / raw)
To: Mary Anderson <maryfran@demog.berkeley.edu>; pgsql-admin
>>> On Fri, Jul 6, 2007 at 12:48 PM, in message
<468E8080.4030901@demog.berkeley.edu>, Mary Anderson
<maryfran@demog.berkeley.edu> wrote:
>
> I have been asked if it is possible to asynchronously replicate a
> postgresql database to ms-sql. My answer is yes, provided postgresql
> ORM features aren't used in the design of the database. One does an
> ascii dump of the tables and then bulk loads them into MSSQL. The
> database in question will be about 10G. Is there a sane way to do this?
Look at pg_dump and the switches to cause it to generate INSERT
statements instead of COPY statements. That has worked for me.
-Kevin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: replicating postgresql database to ms-sql database
@ 2007-07-06 19:11 Kenneth Marshall <ktm@rice.edu>
parent: Mary Anderson <maryfran@demog.berkeley.edu>
1 sibling, 1 reply; 5+ messages in thread
From: Kenneth Marshall @ 2007-07-06 19:11 UTC (permalink / raw)
To: Mary Anderson <maryfran@demog.berkeley.edu>; +Cc: pgsql-admin
Mary,
I believe that you can use FreeTDS with dblink to set up your
on replication process. I agree with you that having both databases
be PostgreSQL would be preferrable.
Ken
On Fri, Jul 06, 2007 at 10:48:48AM -0700, Mary Anderson wrote:
> Hi list,
> I have been asked if it is possible to asynchronously replicate a
> postgresql database to ms-sql. My answer is yes, provided postgresql
> ORM features aren't used in the design of the database. One does an
> ascii dump of the tables and then bulk loads them into MSSQL. The
> database in question will be about 10G. Is there a sane way to do this?
> I am hoping the slony can be instructed to produce an ascii transaction
> log which can then be massaged and applied to the MS-SQL database.
> (This is a database of population statistics which is relatively static.
> It will be read only on the ms-sql node)
>
> I think it is much better to have both databases running postgres,
> by the way.
>
> mary anderson
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: replicating postgresql database to ms-sql database
@ 2007-07-08 22:21 Ben Kim <bkim@tamu.edu>
parent: Kenneth Marshall <ktm@rice.edu>
0 siblings, 1 reply; 5+ messages in thread
From: Ben Kim @ 2007-07-08 22:21 UTC (permalink / raw)
To: pgsql-admin; +Cc: Mary Anderson <maryfran@demog.berkeley.edu>
On Fri, Jul 06, 2007 at 10:48:48AM -0700, Mary Anderson wrote:
> I have been asked if it is possible to asynchronously replicate a
> postgresql database to ms-sql. My answer is yes, provided postgresql
> ORM features aren't used in the design of the database. One does an
> ascii dump of the tables and then bulk loads them into MSSQL. The
> database in question will be about 10G. Is there a sane way to do this?
As a pull option, it's also possible to use MS SQL's DTS tasks. You can
define complex import tasks and (I believe) run the task regularly using
the windows scheduler.
Massaging can be done by custom conversion scripts, in perl or vb, within
the task definition.
My 2 cents.
Regards,
Ben K.
Developer
http://benix.tamu.edu
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: replicating postgresql database to ms-sql database
@ 2007-07-10 04:36 Kevin Kempter <kevin@kevinkempterllc.com>
parent: Ben Kim <bkim@tamu.edu>
0 siblings, 0 replies; 5+ messages in thread
From: Kevin Kempter @ 2007-07-10 04:36 UTC (permalink / raw)
To: pgsql-admin
On Sunday 08 July 2007 16:21:42 Ben Kim wrote:
> On Fri, Jul 06, 2007 at 10:48:48AM -0700, Mary Anderson wrote:
> > I have been asked if it is possible to asynchronously replicate a
> > postgresql database to ms-sql. My answer is yes, provided postgresql
> > ORM features aren't used in the design of the database. One does an
> > ascii dump of the tables and then bulk loads them into MSSQL. The
> > database in question will be about 10G. Is there a sane way to do this?
>
> As a pull option, it's also possible to use MS SQL's DTS tasks. You can
> define complex import tasks and (I believe) run the task regularly using
> the windows scheduler.
>
> Massaging can be done by custom conversion scripts, in perl or vb, within
> the task definition.
>
>
> My 2 cents.
>
>
>
> Regards,
>
> Ben K.
> Developer
> http://benix.tamu.edu
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
Have a look at dbi-link on the pg foundry
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-07-10 04:36 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-06 17:48 replicating postgresql database to ms-sql database Mary Anderson <maryfran@demog.berkeley.edu>
2007-07-06 18:15 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2007-07-06 19:11 ` Kenneth Marshall <ktm@rice.edu>
2007-07-08 22:21 ` Ben Kim <bkim@tamu.edu>
2007-07-10 04:36 ` Kevin Kempter <kevin@kevinkempterllc.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox