pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMS-SQL<->Postgres sync
15+ messages / 9 participants
[nested] [flat]
* MS-SQL<->Postgres sync
@ 2006-07-10 15:27 Kevin Bednar <kevin@stockwelldesigngroup.com>
2006-07-10 16:06 ` Re: MS-SQL<->Postgres sync Forums @ Existanze <forums@existanze.com>
2006-07-10 16:25 ` Re: MS-SQL<->Postgres sync Andrew Sullivan <ajs@crankycanuck.ca>
2006-07-10 16:48 ` Re: MS-SQL<->Postgres sync Aaron Bono <postgresql@aranya.com>
0 siblings, 3 replies; 15+ messages in thread
From: Kevin Bednar @ 2006-07-10 15:27 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: MS-SQL<->Postgres sync
2006-07-10 15:27 MS-SQL<->Postgres sync Kevin Bednar <kevin@stockwelldesigngroup.com>
@ 2006-07-10 16:06 ` Forums @ Existanze <forums@existanze.com>
2006-07-10 18:31 ` Re: MS-SQL<->Postgres sync Scott Marlowe <smarlowe@g2switchworks.com>
2 siblings, 1 reply; 15+ messages in thread
From: Forums @ Existanze @ 2006-07-10 16:06 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: MS-SQL<->Postgres sync
2006-07-10 15:27 MS-SQL<->Postgres sync Kevin Bednar <kevin@stockwelldesigngroup.com>
2006-07-10 16:06 ` Re: MS-SQL<->Postgres sync Forums @ Existanze <forums@existanze.com>
@ 2006-07-10 18:31 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-07-10 18:59 ` Re: MS-SQL<->Postgres sync Kevin Bednar <kevin@stockwelldesigngroup.com>
0 siblings, 1 reply; 15+ messages in thread
From: Scott Marlowe @ 2006-07-10 18:31 UTC (permalink / raw)
To: Forums @ Existanze <forums@existanze.com>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: MS-SQL<->Postgres sync
2006-07-10 15:27 MS-SQL<->Postgres sync Kevin Bednar <kevin@stockwelldesigngroup.com>
2006-07-10 16:06 ` Re: MS-SQL<->Postgres sync Forums @ Existanze <forums@existanze.com>
2006-07-10 18:31 ` Re: MS-SQL<->Postgres sync Scott Marlowe <smarlowe@g2switchworks.com>
@ 2006-07-10 18:59 ` Kevin Bednar <kevin@stockwelldesigngroup.com>
2006-07-10 19:02 ` Re: MS-SQL<->Postgres sync Scott Marlowe <smarlowe@g2switchworks.com>
2006-07-10 19:33 ` Re: MS-SQL<->Postgres sync Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 2 replies; 15+ messages in thread
From: Kevin Bednar @ 2006-07-10 18:59 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; Forums @ Existanze <forums@existanze.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: MS-SQL<->Postgres sync
2006-07-10 15:27 MS-SQL<->Postgres sync Kevin Bednar <kevin@stockwelldesigngroup.com>
2006-07-10 16:06 ` Re: MS-SQL<->Postgres sync Forums @ Existanze <forums@existanze.com>
2006-07-10 18:31 ` Re: MS-SQL<->Postgres sync Scott Marlowe <smarlowe@g2switchworks.com>
2006-07-10 18:59 ` Re: MS-SQL<->Postgres sync Kevin Bednar <kevin@stockwelldesigngroup.com>
@ 2006-07-10 19:02 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-07-10 21:34 ` Re: MS-SQL<->Postgres sync Dave Page <dpage@vale-housing.co.uk>
1 sibling, 1 reply; 15+ messages in thread
From: Scott Marlowe @ 2006-07-10 19:02 UTC (permalink / raw)
To: Kevin Bednar <kevin@stockwelldesigngroup.com>; +Cc: Forums @ Existanze <forums@existanze.com>; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: MS-SQL<->Postgres sync
2006-07-10 15:27 MS-SQL<->Postgres sync Kevin Bednar <kevin@stockwelldesigngroup.com>
2006-07-10 16:06 ` Re: MS-SQL<->Postgres sync Forums @ Existanze <forums@existanze.com>
2006-07-10 18:31 ` Re: MS-SQL<->Postgres sync Scott Marlowe <smarlowe@g2switchworks.com>
2006-07-10 18:59 ` Re: MS-SQL<->Postgres sync Kevin Bednar <kevin@stockwelldesigngroup.com>
2006-07-10 19:02 ` Re: MS-SQL<->Postgres sync Scott Marlowe <smarlowe@g2switchworks.com>
@ 2006-07-10 21:34 ` Dave Page <dpage@vale-housing.co.uk>
0 siblings, 0 replies; 15+ messages in thread
From: Dave Page @ 2006-07-10 21:34 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; Kevin Bednar <kevin@stockwelldesigngroup.com>; +Cc: Forums @ Existanze <forums@existanze.com>; pgsql-sql
> -----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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: MS-SQL<->Postgres sync
2006-07-10 15:27 MS-SQL<->Postgres sync Kevin Bednar <kevin@stockwelldesigngroup.com>
2006-07-10 16:06 ` Re: MS-SQL<->Postgres sync Forums @ Existanze <forums@existanze.com>
2006-07-10 18:31 ` Re: MS-SQL<->Postgres sync Scott Marlowe <smarlowe@g2switchworks.com>
2006-07-10 18:59 ` Re: MS-SQL<->Postgres sync Kevin Bednar <kevin@stockwelldesigngroup.com>
@ 2006-07-10 19:33 ` Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 0 replies; 15+ messages in thread
From: Alvaro Herrera @ 2006-07-10 19:33 UTC (permalink / raw)
To: Kevin Bednar <kevin@stockwelldesigngroup.com>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; Forums @ Existanze <forums@existanze.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: MS-SQL<->Postgres sync
2006-07-10 15:27 MS-SQL<->Postgres sync Kevin Bednar <kevin@stockwelldesigngroup.com>
@ 2006-07-10 16:25 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-07-10 18:30 ` Re: MS-SQL<->Postgres sync Scott Marlowe <smarlowe@g2switchworks.com>
2 siblings, 1 reply; 15+ messages in thread
From: Andrew Sullivan @ 2006-07-10 16:25 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: MS-SQL<->Postgres sync
2006-07-10 15:27 MS-SQL<->Postgres sync Kevin Bednar <kevin@stockwelldesigngroup.com>
2006-07-10 16:25 ` Re: MS-SQL<->Postgres sync Andrew Sullivan <ajs@crankycanuck.ca>
@ 2006-07-10 18:30 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-07-11 00:12 ` Re: MS-SQL<->Postgres sync Chris Browne <cbbrowne@acm.org>
2006-07-11 13:17 ` Re: MS-SQL<->Postgres sync Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 2 replies; 15+ messages in thread
From: Scott Marlowe @ 2006-07-10 18:30 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: MS-SQL<->Postgres sync
2006-07-10 15:27 MS-SQL<->Postgres sync Kevin Bednar <kevin@stockwelldesigngroup.com>
2006-07-10 16:25 ` Re: MS-SQL<->Postgres sync Andrew Sullivan <ajs@crankycanuck.ca>
2006-07-10 18:30 ` Re: MS-SQL<->Postgres sync Scott Marlowe <smarlowe@g2switchworks.com>
@ 2006-07-11 00:12 ` Chris Browne <cbbrowne@acm.org>
1 sibling, 0 replies; 15+ messages in thread
From: Chris Browne @ 2006-07-11 00:12 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: MS-SQL<->Postgres sync
2006-07-10 15:27 MS-SQL<->Postgres sync Kevin Bednar <kevin@stockwelldesigngroup.com>
2006-07-10 16:25 ` Re: MS-SQL<->Postgres sync Andrew Sullivan <ajs@crankycanuck.ca>
2006-07-10 18:30 ` Re: MS-SQL<->Postgres sync Scott Marlowe <smarlowe@g2switchworks.com>
@ 2006-07-11 13:17 ` Andrew Sullivan <ajs@crankycanuck.ca>
1 sibling, 0 replies; 15+ messages in thread
From: Andrew Sullivan @ 2006-07-11 13:17 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: MS-SQL<->Postgres sync
2006-07-10 15:27 MS-SQL<->Postgres sync Kevin Bednar <kevin@stockwelldesigngroup.com>
@ 2006-07-10 16:48 ` Aaron Bono <postgresql@aranya.com>
2006-07-10 17:15 ` Re: MS-SQL<->Postgres sync Aaron Bono <postgresql@aranya.com>
2 siblings, 1 reply; 15+ messages in thread
From: Aaron Bono @ 2006-07-10 16:48 UTC (permalink / raw)
To: Kevin Bednar <kevin@stockwelldesigngroup.com>; +Cc: pgsql-sql
On 7/10/06, Kevin Bednar <kevin@stockwelldesigngroup.com> 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!
>
>
> 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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: MS-SQL<->Postgres sync
2006-07-10 15:27 MS-SQL<->Postgres sync Kevin Bednar <kevin@stockwelldesigngroup.com>
2006-07-10 16:48 ` Re: MS-SQL<->Postgres sync Aaron Bono <postgresql@aranya.com>
@ 2006-07-10 17:15 ` Aaron Bono <postgresql@aranya.com>
2006-07-10 17:20 ` Re: MS-SQL<->Postgres sync Kevin Bednar <kevin@stockwelldesigngroup.com>
2006-07-10 17:57 ` Re: MS-SQL<->Postgres sync Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 2 replies; 15+ messages in thread
From: Aaron Bono @ 2006-07-10 17:15 UTC (permalink / raw)
To: Kevin Bednar <kevin@stockwelldesigngroup.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: MS-SQL<->Postgres sync
2006-07-10 15:27 MS-SQL<->Postgres sync Kevin Bednar <kevin@stockwelldesigngroup.com>
2006-07-10 16:48 ` Re: MS-SQL<->Postgres sync Aaron Bono <postgresql@aranya.com>
2006-07-10 17:15 ` Re: MS-SQL<->Postgres sync Aaron Bono <postgresql@aranya.com>
@ 2006-07-10 17:20 ` Kevin Bednar <kevin@stockwelldesigngroup.com>
1 sibling, 0 replies; 15+ messages in thread
From: Kevin Bednar @ 2006-07-10 17:20 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: MS-SQL<->Postgres sync
2006-07-10 15:27 MS-SQL<->Postgres sync Kevin Bednar <kevin@stockwelldesigngroup.com>
2006-07-10 16:48 ` Re: MS-SQL<->Postgres sync Aaron Bono <postgresql@aranya.com>
2006-07-10 17:15 ` Re: MS-SQL<->Postgres sync Aaron Bono <postgresql@aranya.com>
@ 2006-07-10 17:57 ` Richard Broersma Jr <rabroersma@yahoo.com>
1 sibling, 0 replies; 15+ messages in thread
From: Richard Broersma Jr @ 2006-07-10 17:57 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; Kevin Bednar <kevin@stockwelldesigngroup.com>; pgsql-sql
> 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.
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2006-07-11 13:17 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-10 15:27 MS-SQL<->Postgres sync Kevin Bednar <kevin@stockwelldesigngroup.com>
2006-07-10 16:06 ` Forums @ Existanze <forums@existanze.com>
2006-07-10 18:31 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-07-10 18:59 ` Kevin Bednar <kevin@stockwelldesigngroup.com>
2006-07-10 19:02 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-07-10 21:34 ` Dave Page <dpage@vale-housing.co.uk>
2006-07-10 19:33 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-07-10 16:25 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-07-10 18:30 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-07-11 00:12 ` Chris Browne <cbbrowne@acm.org>
2006-07-11 13:17 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-07-10 16:48 ` Aaron Bono <postgresql@aranya.com>
2006-07-10 17:15 ` Aaron Bono <postgresql@aranya.com>
2006-07-10 17:20 ` Kevin Bednar <kevin@stockwelldesigngroup.com>
2006-07-10 17:57 ` Richard Broersma Jr <rabroersma@yahoo.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