I've search PostgressSql mailing list archives and forums but I'm left with more questions than answer. I'm trying to completely migrate the perfectly running data transfer process from M$ to LINUX bacause of COST issues.
Here's my current process.
DBMS : MS SQL server 7.0 ( server A)
OS : WIN NT 4.0
MS ACCESS FILE (server B)
data is inserted into a table in server A and a trigger is fired to populate data into MS ACCESS file residing on server B via a linked DB defined in SQL server(on server A).
okay now the qs :
How do I do this on PostgreSQL on red hat LINUX 9.
Specificcly how do I linked up Postgresql on a LINUX machine to MS acess DB on a windows machine ?
Is it possible to establish the link-up using SAMBA ?
Is there a ODBC thingy version equavalent to the ones in MS Windows that I can defined under Postgressql on LINUX so that it can link to other DB ?
I know I can run Postgresql on MS Windows using cygwin and that will make things less complicated but there will be an extra cost for the OS (M$).
Are you planning on migrating the MS ACCESS database to PostgreSQL aswell? If so you should be looking for some form of replication feature or possible the CONTRIB/dblink
If you plan to keep your MS ACCESS database then you can have ACCESS pull the data from PostgreSQL via PostgreSQL ODBC.
Thanks for the reply John. MS ACESS DB will not be migrated, only the SQL server DB portion. How do I automatically pulled data in MS Access from postgresql on LINUX. It has to be done only if there's data change (insert or update) on Postgresql table.
Does the contrib/dblink work across plaform (from LINUX to MS WIndows) ?
The contrib/dblink allows two PostgreSQL RDBMS's to exchange data. Since you're not going to migrate your ACCESS DB this wan't help you.
By use of PostgreSQL http://gborg.postgresql.org/project/psqlodbc you can have ACCESS either link in or copy views and/or tables from PostgreSQL. All you have to do is to find a way to get only changed data.
This can be done by a trigger on your PG-table(s) that will insert a row in a "transaction" table (also in PG). Then defining a view in PG joining these two tables will give you only the rows that have been updated. You probably need to do some programming (stored procedure) on PostgreSQL to cleanup the "transaction" table.
You can then link in the view in Access (Menu: File/Get External Data/Link Tables)
Good luck with your project!