Обсуждение: replicating postgresql database to ms-sql database
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
>>> 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
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 >
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
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