I have spatial tables in ArcSDE 10.1 (MSSQL 2008 R2) geodatabases and need to update the same spatial tables in PostgreSQL 9.1(PostGIS 2.0.1) geodatabases. Basically I use my ArcSDE environment for editing and geoprocessing, but my final output needs to be in Postgres(PostGIS) data store for geoserver. The main hurdle for me is keeping my data updated and in sync (no pun intended) between databases.
Using the Data Interoperability extension I set up a spatial ETL model using the "quick export" toolset and can effortlessly move my spatial table(s) from an ArcSDE geodatabase to a PostGIS geodatabase. This is only a one time dump though. How can I regularly keep my table(s) updated in PostGIS as I make update/insert/delete changes in my ArcSDE geodatabases. I know with ArcGIS 10.1 SP1 you should be able to connect, view and edit spatial data in PostGIS geodatabases if you are using ArcSDE for PostgreSQL. I have ArcSDE 10.1 over MSSQL 2008. Ideally I would need to update my Postgres databases tables once a day to reflect changes made in the ArcSDE geodatabases tables. I am banging my head trying to explore triggers, stored procedures, SQL "magic" and even python scripting.