Thread: MS-SQL<->Postgres sync
Looking to keep 2 databases in sync, at least semi-realtime if possible, although running a batch update every x mins wouldn't be out of the question. One db is postgres and the other is ms-sql. It's to keep inventory in sync from 2 seperate locations, one being a brick and mortar store and the other an ecommerce system for a web site. Any and all help is appreciated since I can't find much of anything on syncing these 2 database systems!
We are looking for the exact thing but with two PostgreSQL databases
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Kevin Bednar
Sent: 10 July 2006 18:28
To: pgsql-sql@postgresql.org
Subject: [SQL] MS-SQL<->Postgres syncLooking to keep 2 databases in sync, at least semi-realtime if possible, although running a batch update every x mins wouldn't be out of the question. One db is postgres and the other is ms-sql. It's to keep inventory in sync from 2 seperate locations, one being a brick and mortar store and the other an ecommerce system for a web site. Any and all help is appreciated since I can't find much of anything on syncing these 2 database systems!
On Mon, Jul 10, 2006 at 11:27:52AM -0400, Kevin Bednar wrote: > Looking to keep 2 databases in sync, at least semi-realtime if possible, > although running a batch update every x mins wouldn't be out of the > question. One db is postgres and the other is ms-sql. It's to keep inventory > in sync from 2 seperate locations, one being a brick and mortar store and > the other an ecommerce system for a web site. Any and all help is > appreciated since I can't find much of anything on syncing these 2 database > systems! I don't think there's actually any product that does this right now, unless you're willing to use some sort of statement replica system (which means you have to deal with failures in one database and not another). Is the idea that this is multi-master? A -- Andrew Sullivan | ajs@crankycanuck.ca The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun
On 7/10/06, Kevin Bednar <kevin@stockwelldesigngroup.com> wrote:
Can you provide more information about what data is needing to be fed back and forth? Are both databases being updated or can you set one as the system of record? Why can't you have the web site read directly from MS SQL Server?
With a little more information we can provide more useful advice.
Thanks,
Aaron
Looking to keep 2 databases in sync, at least semi-realtime if possible, although running a batch update every x mins wouldn't be out of the question. One db is postgres and the other is ms-sql. It's to keep inventory in sync from 2 seperate locations, one being a brick and mortar store and the other an ecommerce system for a web site. Any and all help is appreciated since I can't find much of anything on syncing these 2 database systems!
Kevin Bednar
Can you provide more information about what data is needing to be fed back and forth? Are both databases being updated or can you set one as the system of record? Why can't you have the web site read directly from MS SQL Server?
With a little more information we can provide more useful advice.
Thanks,
Aaron
Please reply to all when replying on the list...
On 7/10/06, Kevin Bednar <kevin@stockwelldesigngroup.com > wrote:
For something this simple you are probably better off doing some custom coding.
If you have the ability to modify the databases, I would recommend putting a trigger on each database so when there is a product sold, that sale is recorded in a temp table (which serves as a queue of data that needs to be synched). Then have a process read from these temp tables and feed the data back to the other database. Of course, I am assuming you have full control to change the databases - some vendors do not allow that.
You may be able to connect the databases - MS SQL Server will definitely allow you to connect via ODBC to another database and feed data back and forth. I think there are add on modules for PostgreSQL but I have not tried to have PostgreSQL talk to other databases before.
-Aaron
Thanks Aron. What I'm actually trying to do is this:Postgress in physical store, being used by POS system as the back end.MS-SQL being used on web server by ecommerce system.Table structures are different of course, but some common fields. What I want to do is when an item is sold in the store, update the quantity field for that sku number on the web site and vice versa. Only 2 fields basically need to be updated on each side, the SKU number and quantity. This is to keep the product table in sync and try to avoid selling product that isnt in stock and setting a flag on the web system stating such. Thanks for your help.
If you have the ability to modify the databases, I would recommend putting a trigger on each database so when there is a product sold, that sale is recorded in a temp table (which serves as a queue of data that needs to be synched). Then have a process read from these temp tables and feed the data back to the other database. Of course, I am assuming you have full control to change the databases - some vendors do not allow that.
You may be able to connect the databases - MS SQL Server will definitely allow you to connect via ODBC to another database and feed data back and forth. I think there are add on modules for PostgreSQL but I have not tried to have PostgreSQL talk to other databases before.
-Aaron
Yeah, I was kind of thinking that myself. I do have control over both DB's, and postgres does have an ODBC connector available as well. Perhaps using a trigger to watch a temp table on each site and having it replicate to the opposite side and then remove the record from the temp table would work. I'll have to look into it. Thanks!
Kevin
-----Original Message-----
From: "Aaron Bono" <postgresql@aranya.com>
To: "Kevin Bednar" <kevin@stockwelldesigngroup.com>, pgsql-sql@postgresql.org
Date: Mon, 10 Jul 2006 12:15:34 -0500
Subject: Re: [SQL] MS-SQL<->Postgres sync
Please reply to all when replying on the list...On 7/10/06, Kevin Bednar <kevin@stockwelldesigngroup.com > wrote:Thanks Aron. What I'm actually trying to do is this:Postgress in physical store, being used by POS system as the back end.MS-SQL being used on web server by ecommerce system.Table structures are different of course, but some common fields. What I want to do is when an item is sold in the store, update the quantity field for that sku number on the web site and vice versa. Only 2 fields basically need to be updated on each side, the SKU number and quantity. This is to keep the product table in sync and try to avoid selling product that isnt in stock and setting a flag on the web system stating such. Thanks for your help.For something this simple you are probably better off doing some custom coding.
If you have the ability to modify the databases, I would recommend putting a trigger on each database so when there is a product sold, that sale is recorded in a temp table (which serves as a queue of data that needs to be synched). Then have a process read from these temp tables and feed the data back to the other database. Of course, I am assuming you have full control to change the databases - some vendors do not allow that.
You may be able to connect the databases - MS SQL Server will definitely allow you to connect via ODBC to another database and feed data back and forth. I think there are add on modules for PostgreSQL but I have not tried to have PostgreSQL talk to other databases before.
-Aaron
> On 7/10/06, Kevin Bednar <kevin@stockwelldesigngroup.com> wrote: > > > > Thanks Aron. What I'm actually trying to do is this: > > > > Postgress in physical store, being used by POS system as the back end. > > MS-SQL being used on web server by ecommerce system. > > > > Table structures are different of course, but some common fields. What I > > want to do is when an item is sold in the store, update the quantity field > > for that sku number on the web site and vice versa. Only 2 fields basically > > need to be updated on each side, the SKU number and quantity. This is to > > keep the product table in sync and try to avoid selling product that isnt in > > stock and setting a flag on the web system stating such. Thanks for your > > help. > > > > > For something this simple you are probably better off doing some custom > coding. > > If you have the ability to modify the databases, I would recommend putting a > trigger on each database so when there is a product sold, that sale is > recorded in a temp table (which serves as a queue of data that needs to be > synched). Then have a process read from these temp tables and feed the data > back to the other database. Of course, I am assuming you have full control > to change the databases - some vendors do not allow that. > > You may be able to connect the databases - MS SQL Server will definitely > allow you to connect via ODBC to another database and feed data back and > forth. I think there are add on modules for PostgreSQL but I have not tried > to have PostgreSQL talk to other databases before. I am not sure if this applys directly to the problem here, but this link my be useful also. http://archives.postgresql.org/pgsql-general/2006-07/msg00298.php Regards, Richard Broersma Jr.
Look at slony. On Mon, 2006-07-10 at 11:06, Forums @ Existanze wrote: > > We are looking for the exact thing but with two PostgreSQL databases > > > ______________________________________________________________ > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Kevin > Bednar > Sent: 10 July 2006 18:28 > To: pgsql-sql@postgresql.org > Subject: [SQL] MS-SQL<->Postgres sync > > > Looking to keep 2 databases in sync, at least semi-realtime if > possible, although running a batch update every x mins > wouldn't be out of the question. One db is postgres and the > other is ms-sql. It's to keep inventory in sync from 2 > seperate locations, one being a brick and mortar store and the > other an ecommerce system for a web site. Any and all help is > appreciated since I can't find much of anything on syncing > these 2 database systems! > > Kevin Bednar > Systems Support > Stockwell Design Group > http://www.stockwelldesigngroup.com > kevin@stockwelldesigngroup.com >
Took a quick glance but were running windows on both sides and it didnt look like that would work. Didnt look real hard though.
Kevin
-----Original Message-----
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: "Forums @ Existanze" <forums@existanze.com>
Cc: pgsql-sql@postgresql.org
Date: Mon, 10 Jul 2006 13:31:35 -0500
Subject: Re: [SQL] MS-SQL<->Postgres syncLook at slony.
On Mon, 2006-07-10 at 11:06, Forums @ Existanze wrote:
>
> We are looking for the exact thing but with two PostgreSQL databases
>
>
> ______________________________________________________________
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Kevin
> Bednar
> Sent: 10 July 2006 18:28
> To: pgsql-sql@postgresql.org
> Subject: [SQL] MS-SQL<->Postgres sync
>
>
> Looking to keep 2 databases in sync, at least semi-realtime if
> possible, although running a batch update every x mins
> wouldn't be out of the question. One db is postgres and the
> other is ms-sql. It's to keep inventory in sync from 2
> seperate locations, one being a brick and mortar store and the
> other an ecommerce system for a web site. Any and all help is
> appreciated since I can't find much of anything on syncing
> these 2 database systems!
>
> Kevin Bednar
> Systems Support
> Stockwell Design Group
> http://www.stockwelldesigngroup.com
> kevin@stockwelldesigngroup.com
>
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
On Mon, 2006-07-10 at 11:25, Andrew Sullivan wrote: > On Mon, Jul 10, 2006 at 11:27:52AM -0400, Kevin Bednar wrote: > > Looking to keep 2 databases in sync, at least semi-realtime if possible, > > although running a batch update every x mins wouldn't be out of the > > question. One db is postgres and the other is ms-sql. It's to keep inventory > > in sync from 2 seperate locations, one being a brick and mortar store and > > the other an ecommerce system for a web site. Any and all help is > > appreciated since I can't find much of anything on syncing these 2 database > > systems! > > I don't think there's actually any product that does this right now, > unless you're willing to use some sort of statement replica system > (which means you have to deal with failures in one database and not > another). Is the idea that this is multi-master? I wonder if it would be possible to write a set of triggers for MSSQL that would allow you to run slony daemons that connected to it and a postgresql server and did replication. Just mad scientisting for a second.
Kevin Bednar wrote: > Took a quick glance but were running windows on both sides and it didnt look > like that would work. Didnt look real hard though. I think the to-be-released stuff is supposed to have Windows support. They were supposed to release Real Soon Now some time ago, so I guess they are going to release Real Soon Now. Maybe you could help them with the beta testing if that's what they need. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
I think there's a version with windows support due out soon. Not sure, since I don't run windows... just remember seeing it mentioned. On Mon, 2006-07-10 at 13:59, Kevin Bednar wrote: > Took a quick glance but were running windows on both sides and it > didnt look like that would work. Didnt look real hard though. > > Kevin > > > -----Original Message----- > From: Scott Marlowe <smarlowe@g2switchworks.com> > To: "Forums @ Existanze" <forums@existanze.com> > Cc: pgsql-sql@postgresql.org > Date: Mon, 10 Jul 2006 13:31:35 -0500 > Subject: Re: [SQL] MS-SQL<->Postgres sync > > Look at slony. > > On Mon, 2006-07-10 at 11:06, Forums @ Existanze wrote: > > > > We are looking for the exact thing but with two PostgreSQL > databases > > > > > > > ______________________________________________________________ > > From: pgsql-sql-owner@postgresql.org > > [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of > Kevin > > Bednar > > Sent: 10 July 2006 18:28 > > To: pgsql-sql@postgresql.org > > Subject: [SQL] MS-SQL<->Postgres sync > > > > > > Looking to keep 2 databases in sync, at least > semi-realtime if > > possible, although running a batch update every x > mins > > wouldn't be out of the question. One db is postgres > and the > > other is ms-sql. It's to keep inventory in sync from > 2 > > seperate locations, one being a brick and mortar > store and the > > other an ecommerce system for a web site. Any and > all help is > > appreciated since I can't find much of anything on > syncing > > these 2 database systems! > > > > Kevin Bednar > > Systems Support > > Stockwell Design Group > > http://www.stockwelldesigngroup.com > > kevin@stockwelldesigngroup.com > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org >
> -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Scott Marlowe > Sent: 10 July 2006 20:03 > To: Kevin Bednar > Cc: Forums @ Existanze; pgsql-sql@postgresql.org > Subject: Re: [SQL] MS-SQL<->Postgres sync > > I think there's a version with windows support due out soon. > Not sure, > since I don't run windows... just remember seeing it mentioned. Yes, full support for windows has been written already for the upcoming 1.2 release. Regards, Dave
smarlowe@g2switchworks.com (Scott Marlowe) writes:
> On Mon, 2006-07-10 at 11:25, Andrew Sullivan wrote:
>> On Mon, Jul 10, 2006 at 11:27:52AM -0400, Kevin Bednar wrote:
>> > Looking to keep 2 databases in sync, at least semi-realtime if possible,
>> > although running a batch update every x mins wouldn't be out of the
>> > question. One db is postgres and the other is ms-sql. It's to keep inventory
>> > in sync from 2 seperate locations, one being a brick and mortar store and
>> > the other an ecommerce system for a web site. Any and all help is
>> > appreciated since I can't find much of anything on syncing these 2 database
>> > systems!
>>
>> I don't think there's actually any product that does this right now,
>> unless you're willing to use some sort of statement replica system
>> (which means you have to deal with failures in one database and not
>> another). Is the idea that this is multi-master?
>
> I wonder if it would be possible to write a set of triggers for MSSQL
> that would allow you to run slony daemons that connected to it and a
> postgresql server and did replication. Just mad scientisting for a
> second.
Someone went through the "mental gyrations" and figured it ought to be
able to work for Oracle...
It otta be able to work; the only thing is that I would think the only
nodes that could be origins would be PostgreSQL nodes, because the way
the data extraction scheme works is very much attuned to PostgreSQL's
MVCC model...
--
output = ("cbbrowne" "@" "cbbrowne.com")
http://cbbrowne.com/info/lisp.html
"I don't plan to maintain it, just to install it." -- Richard M. Stallman
On Mon, Jul 10, 2006 at 01:30:51PM -0500, Scott Marlowe wrote: > > (which means you have to deal with failures in one database and not > > another). Is the idea that this is multi-master? > > I wonder if it would be possible to write a set of triggers for MSSQL > that would allow you to run slony daemons that connected to it and a > postgresql server and did replication. Just mad scientisting for a > second. Well, yes, but that's why I asked whether it's multimaster. Because if so, it'll be at least tricky to do under Slony I. A -- Andrew Sullivan | ajs@crankycanuck.ca Information security isn't a technological problem. It's an economics problem. --Bruce Schneier