From: Kevin Bednar <kevin@stockwelldesigngroup.com>
To: Aaron Bono <postgresql@aranya.com>
To: pgsql-sql@postgresql.org
Subject: Re: MS-SQL<->Postgres sync
Date: Mon, 10 Jul 2006 13:20:52 -0400
Message-ID: <WorldClient-F200607101320.AA20520009@stockwelldesigngroup.com> (raw)
In-Reply-To: <bf05e51c0607101015y20b200e4o26c7c0aee1e8487f@mail.gmail.com>
References: <WorldClient-F200607101127.AA27520002@stockwelldesigngroup.com>
<bf05e51c0607100948x5c684404x704a4bd3a3a7446e@mail.gmail.com>
<WorldClient-F200607101306.AA06070007@stockwelldesigngroup.com>
<bf05e51c0607101015y20b200e4o26c7c0aee1e8487f@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: kevin@stockwelldesigngroup.com, postgresql@aranya.com
Subject: Re: MS-SQL<->Postgres sync
In-Reply-To: <WorldClient-F200607101320.AA20520009@stockwelldesigngroup.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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