Обсуждение: Re: Replication MSSQL-PostgreSQL - any success?

Hi, actually what I was after was a continuous way to keep a
PostgreSQL db in sync with a changing MSSQL db.  Migrating
data from MSSQL to PostgreSQL is pretty easy.  The method
you described is one way.  The way we usually use is with
PgAdmin (a great tool) which has a Migration Wizard plugin
to automatically migrate a database from any ODBC source to
PostgreSQL (including tables and all).

Once the data is migrated, a method of syncing any updates
in the MSSQL db to the PostgreSQL db is what we require.
I haven't discovered a nice way of doing that yet.

Cheers,
Chris.

ps: cc'd the pgsql-admin list back in to generate any
further discussion and ideas.

On Thu, Nov 14, 2002 at 01:11:22AM +0100, Ragnvald Larsen wrote:
> Hi Chris,
>       you wrote as follows:
>
> >Hi, sorry no, I got no responses unfortunately.
> >
> >If you find some information, please let me know.
>
> I have been thinking. Wouldn't a ODBC-call do the works? I mean a PHP-page
> with the correct code should be able to:
>
> o empty data in target table
> o query data from MSSQL-server table
> o insert it into target PostgreSQL-server table.
>
> The solution is a bit dirty, but would most certainly work.
> updatepostgresql.php and some crontab-solution should do it. Perhaps Perl?
> Shouldn't be difficult, and with some effort one might be able to make a
> more dynamic solution too... That, of course, would be an
> OpenSource-project in its own...
>
>
> Ragnvald

--
Chris Miles

> Hi, actually what I was after was a continuous way to keep a
> PostgreSQL db in sync with a changing MSSQL db.

> Once the data is migrated, a method of syncing any updates
> in the MSSQL db to the PostgreSQL db is what we require.
> I haven't discovered a nice way of doing that yet.

I've setup an active sync between an AS400->MSSQL which would work for
MSSQL->Postgres.

What you need to to is create a trigger on the MSSQL side which writes to a
separate table the key fields from the changed table. You then write a
program (mine's in Java on the AS400) which checks the tables the trigger
wrote, fetches the new data, and updates the target database (postgres).
Simply keep the check in a continuous loop (I actually wait a few minutes
between checks) and all your data is kept in sync almost realtime.

If you do use Java for this, the advantage is you can sync anything with a
JDBC driver, although it's probably not the fastest way to do it.