Thread: Porting from Ms srvr2K to PostgreSQL

We're evaluating the possibility of porting our MS SQL server 2000 databases
over to PostgreSQL.

One of the things we do currently is replicate the database content in real
time accross a WAN connection between two separate sites. The master
database resides in one town and every time a change is made to it the slave
is updated automatically at the other location, it happens in close to real
time. Our future needs include extending this capacity to several slave
sites that need to be replicated to. Some accross a WAN some on a LAN.

Is this possible with the current version of PostgrSQL (not interested in
knowing about vaporware, sorry ;-) )

We also have an application that connects to a MsSQl srvr database via the
internet to synchronize it's local databases directly with those on the SQL
server (one way - from server to end user client). It is not possible for us
to modify the client app. It has to use exactly the same code for now. The
client app uses Microsoft ADO commands and MS SQL net lib to connect to the
SQL server. I don't think it's possible to have that client connect to a
postgress database at the same IP address without changing the client code.
So I think we're stuck with a requirement to use the MS SQL database as a
database for that particular use and try to find a way to replicate from it
to PostgreSQL or from Postgres to it. I don't think that this is possible as
things stand now but I would like to be told I'm wrong and have an idea of
how to go about it.

Another thing we do in the current database is embed actual large binary
data fields in some fields in some of the tables. I did not find a type of
field that would be able to handle variable length binary data (these fields
are typically over 2MB in size). Is there a way to handle this in
PostgreSQL?

From reading the specs and documentation, PostgreSQL seems to be able to
handle all our other requirements. If we can get the above problems resolved
I'm quite sure we would do the switch.

Thank you for any information on these questions.

"Bob Dufour" <dufourr@sgiims.com> writes:
> One of the things we do currently is replicate the database content in real
> time accross a WAN connection between two separate sites.

contrib/rserv might handle your needs, although it's still in a fairly
primitive state.  (Better replication support is a couple releases away,
I think.)

> It is not possible for us
> to modify the client app. It has to use exactly the same code for now. The
> client app uses Microsoft ADO commands and MS SQL net lib to connect to the
> SQL server. I don't think it's possible to have that client connect to a
> postgress database at the same IP address without changing the client
> code.

I think you're stuck until you can modify that client :-(

> Another thing we do in the current database is embed actual large binary
> data fields in some fields in some of the tables. I did not find a type of
> field that would be able to handle variable length binary data (these fields
> are typically over 2MB in size). Is there a way to handle this in
> PostgreSQL?

In theory you can stuff a couple of meg into a "bytea" field, but it
might be more pleasant to handle this sort of thing as a large object
(a/k/a BLOB).  There are LO functions to read and write sections of an
LO, whereas a bytea field could only be stored or retrieved as a unit.
bytea also has some rather ugly quoting conventions that you'd have to
put up with if you use it.

            regards, tom lane