Tx Replication from SQL Server 2008 R2 to Postgres 9.1

More
12 years 2 months ago #4532 by sqlrepl
Hello,

I am trying to setup transactional replication between SQL Server 2008 R2 Publisher, SQL Server 2008 R2 Distributor and a Postgres 9.1 subscriber using Trial version of PGNP OLEDB Provider v1.4 for PostgreSQL, Server Edition. I have tried with 1 row in the table on the subscriber as well as > 1 rows. However, initialization fails and I get this error in the details panel for Replication Monitor(where 'replicated' is the name of the table) :
Code:
Command attempted: SELECT * FROM "replicated" WHERE 0 = 1 (Transaction sequence number: 0x00000046000001B0000300000000, Command ID: 1) Error messages: Undefined table replicated (Source: MSSQLServer, Error number: 2) Get help: http://help/2

I have a linked server setup on Distributor and Publisher to the postgres host using the PGNP OLEDB provider and they work fine.

These are the scripts I am using to setup Replication:
Code:
use [Replication_Test] GO exec sp_replicationdboption @dbname = N'Replication_Test', @optname = N'publish', @value = N'true' GO exec sp_addpublication @publication = N'pgnptrans1', @description = N'Transactional publication of database ''Replication_Test'' from Publisher ''Publisher_Host''.', @sync_method = N'native', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false' GO exec sp_addpublication_snapshot @publication = N'pgnptrans1', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1 GO exec sp_addarticle @publication = N'pgnptrans1', @article = N'replicated', @source_owner = N'dbo', @source_object = N'replicated', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'none', @schema_option = 0x000000000803509D, @identityrangemanagementoption = N'manual', @destination_table = N'replicated', @destination_owner = N'dbo', @vertical_partition = N'false' GO exec sp_changepublication N'pgnptrans1', N'enabled_for_het_sub', N'true', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1 ------------------------------------------------------------------------------ exec sp_addsubscription @publication = N'pgnptrans1', @subscriber = N'10.10.10.1', @destination_db = N'slave', @subscription_type = N'Push', @sync_type = N'none', @article = N'all', @update_mode = N'read only', @subscriber_type = 3 exec sp_changesubscription @publication = N'pgnptrans1', @subscriber = N'10.10.10.1', @destination_db = N'slave', @article = N'all', @property=N'subscriber_login', @value=N'sqlrepl' exec sp_changesubscription @publication = N'pgnptrans1', @subscriber = N'10.10.10.1', @destination_db = N'slave', @article = N'all', @property=N'subscriber_password', @value=N'sqlrepl' exec sp_changesubscription @publication = N'pgnptrans1', @subscriber = N'10.10.10.1', @destination_db = N'slave', @article = N'all', @property=N'subscriber_location', @value=N'' exec sp_changesubscription @publication = N'pgnptrans1', @subscriber = N'10.10.10.1', @destination_db = N'slave', @article = N'all', @property=N'subscriber_datasource', @value=N'10.10.10.1' exec sp_changesubscription @publication = N'pgnptrans1', @subscriber = N'10.10.10.1', @destination_db = N'slave', @article = N'all', @property=N'subscriber_provider', @value=N'PGNP' exec sp_changesubscription @publication = N'pgnptrans1', @subscriber = N'10.10.10.1', @destination_db = N'slave', @article = N'all', @property=N'subscriber_providerstring', @value=N'PORT=5432;CNV_SPECIAL_FLTVAL=ON;' exec sp_changesubscription @publication = N'pgnptrans1', @subscriber = N'10.10.10.1', @destination_db = N'slave', @article = N'all', @property=N'subscriber_catalog', @value=N'slave' exec sp_addpushsubscription_agent @publication = N'pgnptrans1', @subscriber = N'10.10.10.1', @subscriber_db = N'slave', @job_login = null, @job_password = null, @subscriber_security_mode = 0, @subscriber_provider = N'PGNP', @subscriber_datasrc = N'10.10.10.1', @subscriber_location =N'', @subscriber_provider_string = N'PORT=5432;CNV_SPECIAL_FLTVAL=ON;', @subscriber_catalog = N'slave', @subscriber_login = N'sqlrepl', @subscriber_password =N'sqlrepl', @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20090715, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor' GO


Is the error because of some limitation of the trial version or am I doing something wrong?

Thanks!

Please Log in or Create an account to join the conversation.

More
12 years 2 months ago #4533 by sqlrepl
The error msg above is for a table with 1 row.

If I try to replicate a table with > 1 rows, I get the following error msg:
ERROR: column "Col1" of relation "replicated" does not exist
LINE 1: INSERT INTO "replicated"("Col1","Col2") VALUES($1,$2)

Please Log in or Create an account to join the conversation.

More
12 years 2 months ago #5367 by Moderator
After setting transactional replication for the first time, we had to reinitialize subscription, then it works. We will repeat tests using your script, and we’ll let you know.
Thank you!

Please Log in or Create an account to join the conversation.

More
12 years 2 months ago #5490 by sqlrepl
Ok, I finally got the Tx Replication working between 2008 R2 and Postgres 9.1 with help from the Mods and Michael Giagnocavo. I will document the steps involved and gotchas shortly and post it.

Thanks Everyone!

Please Log in or Create an account to join the conversation.

More
12 years 1 month ago #6424 by Moderator
That would be great. Thank you!

Please Log in or Create an account to join the conversation.

More
12 years 1 month ago #6451 by sqlrepl
I have been running various scenarios for replication and I seem to be running into an issue when I need to change the schema of the table on postgres side after it has been created. I Drop/Create the table to change the schema. Once I create the table on Postgres side again, I get this error:

Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "PGNP" for linked server "PGNP_Samples" supplied inconsistent metadata for a column. The column "notifymebyemail" (compile-time ordinal 24) of object ""slave"."public"."Users"" was reported to have a "DBTYPE" of 11 at compile time and 130 at run time.

I tried to refresh the metadata for the PGNP Provider by running : EXEC('pgnp_refreshmetadata('''',''tablename'')') AT PGNP_SAMPLE and the error message changes to:

Msg 7355, Level 16, State 1, Line 1
The OLE DB provider "PGNP" for linked server "PGNP_Samples" supplied inconsistent metadata for a column. The name was changed at execution time.

Any ideas on how to refresh metadata if you Drop/Create a table?

Please Log in or Create an account to join the conversation.

Time to create page: 0.129 seconds
Powered by Kunena Forum