I am trying to setup one-way, transactional replication from SQL Server 2008 R2 to PostgreSQL 9.1. I'm trying out the PGNP OLE DB provider.
I can successfully create a subscription, and transactions are indeed replicated to the PG server. I am using sync_type = 'none' on the subscription, because it seems that otherwise, the schema, including SQL Server specific options (SET ANSI_NULLS ON, etc.) get sent to PostgreSQL, which screws it up.
This is fine, I can use SSIS and copy my entire database over, including data. My question is, how can I do this in a safe way? Because if I just run a SSIS package to export the data, then add the subscription, transactions could get lost.
E.g. SSIS exports data to PG. Row X is updated. Replication starts. In this case, row X's update wouldn't be in my manual sync, but replication started after. And I can't have replication running, because other errors could happen.
What's the proper procedure here?