Replication on postgres database to a target schema

More
1 year 2 months ago #13907 by bgautard
Hi everyone !

I'm using the pgnp provider for few months now since my group bought a license and it's fast and performing!
But I still don't manage to replicate an sql server table (from a dbo schema for instance) to a specific schema on a postgres db. It's always targeting the "public" schema on postgres.
I tried to use the sql server option 'Destination objet Owner' (in the article properties of the publication) with the name of my target schema (existing or not) but it's not targeting the right schema.

Do you know a way to do this ?

Thanks you in advance !

Benjamin

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

More
1 year 2 months ago #13908 by Moderator
Hi,
according to the Dev Manual (page 35), destination owner can be chosen:
For each table <table-name> execute following command:
exec sp_addarticle @publication = N'pgnpsnap1', @article = N'<table-name>',
 @source_owner = N'dbo', @source_object = N'<table-name>', @type = N'logbased',
 @description = null, @creation_script = null, @pre_creation_cmd = N'none', @schema_option = 0x000000000803509D, 
@identityrangemanagementoption = N'manual', @destination_table = N'<table-name>', @destination_owner = N'dbo',
 @vertical_partition = N'false'
GO

Could you try that and let us know if it worked for you?

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

More
1 year 2 months ago #13909 by bgautard
Hi !

Thank you for your fast reply.

I tried it before and again but even qith the destination_owner parameter to an other schema, it's pointing and creating the table in the public schema...
I get no errors in this case.

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

More
1 year 2 months ago #13910 by Moderator
Yes, I reproduced the issue in SQL Server 2014. I am still looking on why the destination schema is not passed to the provider (either subscription is not configured properly, or the OLEDB provider must support additional properties).

In the meantime please use the workaround: add SEARCH_PATH=mysnap; to the Extended Properties as shown below (where mysnap is your desired schema, different from public). It does the trick.
exec sp_addsubscription @publication = N'pgnpsnap1', @subscriber = N'localhost', @destination_db = N'postgres', 
  @subscription_type = N'Push', 
  @sync_type = N'automatic', 
  @article = N'all', @update_mode = N'read only', 
  -- type 3 is MUST for subscribers not oracle and ibmdb2 i.e. postgres
  @subscriber_type = 3
. . .
exec sp_changesubscription @publication = N'pgnpsnap1', @subscriber = N'localhost',
  @destination_db = N'postgres', @article = N'all',
  @property=N'subscriber_providerstring', 
  @value=N'PORT=5444;BULK_INSERT=1000;BULK_METHOD=COPY;SEARCH_PATH=mysnap;'
. . .
exec sp_addpushsubscription_agent @publication = N'pgnpsnap1', 
  @subscriber = N'localhost', @subscriber_db = N'postgres',
  @job_login = null, @job_password = null, @subscriber_security_mode = 0, 
  @subscriber_provider = N'PGNP', 
  @subscriber_datasrc = N'localhost',
  @subscriber_location =N'',
  @subscriber_provider_string = N'PORT=5444;BULK_INSERT=1000;BULK_METHOD=COPY;SEARCH_PATH=mysnap;',
  @subscriber_catalog = N'postgres', 
  @subscriber_login = N'postgres', @subscriber_password =N'12345', 
  @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'

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

More
1 year 2 months ago #13911 by bgautard
It's working perfectly with the parameter SEARCH_PATH

Thank you so much !

Let's replicate Gigas :)

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

Time to create page: 0.139 seconds
Powered by Kunena Forum