Data transfer between databases over the Internet

From: John McCawley <nospam(at)hardgeus(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Data transfer between databases over the Internet
Date: 2006-11-22 16:13:53
Message-ID: 45647741.5030405@hardgeus.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have found myself somewhat trapped in a project where the software
being used is essentially set in stone, and its limitations are
strangling progress. It is a client-side app written in VB with an
Access backend, and it has no reasonable data migration/synchronization
ability. It was designed for a single user system, and cannot even hook
up to a different backend. I have no confidence in the vendor's ability
to fix this situation in a timely fashion.

A lot of people are trying to tackle this (relatively simple) problem,
and trying to use bulldozers to hammer a nail. I am hearing "Active
Directory" and "Citrix" thrown around, and it scares me. I want to
offer a clean, quick solution to consolidating this data as our field
guys enter it without having to change the current work process (i.e.
while continuing to use this software...I only have so much pull!)

What I would like to do is set up a relational database at our site
(preferably Postgres) which would ultimately house all of the data from
the many different client machines. This database is extremely simple,
only consisting of 5 tables, and I have already written a simple VB tool
that is capable of merging these databases into one PostgreSQL backend,
so logically my task is simple.

What I need to do, however, is write a client-side app that can sit on
my users' computers and accomplish this task over the Internet. I want
to create a simple "check-in/check-out" type of system that my users can
use to do the data upload and download themselves. As I said before,
the database structure is exceedingly simple, so I will have no
difficulties writing the logic. I don't want to re-invent the wheel,
though, so I wanted to ask this list what would be the most sensible
transfer mechanism.

1) Simply expose the PostgreSQL database backend to the Internet over an
encrypted pipe and write my client-side app almost identical to my
current VB app. This seems to be the most logically simple solution,
however I question how fast the app will be since it has to do its work
through ODBC over the Internet. Also, is opening up a port like this,
even encrypted, considered a bad practice?

2) Write my own client and server program that manually transfer the
data themselves over my own encrypted pipe. The client side would
basically do queries to pull all of the data, I'd send it over the pipe
to the server program, and it would insert or update all of the
records. As far as the protocol for sending the data, I suppose I could
wrap all of the data in XML, but that's pretty verbose and may "bulk up"
the data. Is there any established wire protocol for doing this? This
approach seems pretty labor intensive, and I feel like I'd be
reinventing the wheel.

3) Perhaps there is some existing app, protocol, or combination of apps
that people use for this purpose that I've never heard of. Are there
any other approaches you guys have used when faced with this problem?

John

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Teodor Sigaev 2006-11-22 16:14:05 Re: Functional Index
Previous Message Jack Orenstein 2006-11-22 16:11:09 Buffer overflow in psql