Bi-directional synchronizing Salesforce with PostgreSQL

  • 31Views
  • Last Post 4 days ago
0
votes
Jacob Widmer posted this 2 weeks ago

Hey there,

I am attempting to create a "clone" more or less of a few different tables from Salesforce in an external Postgres database. The synchronization tool sounds like it's exactly what I need.

I created a new Synchronization Integration package, source of SFDC and target the Postgres db. I went to add a task and quickly found out that I need to set up the tables prior in the Postgres side, so I went ahead and did that (through an Export). Deleted all rows, and ran the Sync job. 

I am receiving the error "null value in column "Id" voilates not-null constraint". Not entirely sure why the `Id` field would be null, as the records (if directly expored via Export package) all have unique ids (generated in SFDC). None of the records succeeded in syncing. Any ideas? Is there anything special we need to link to the 'Id' field on records we want to Sync in Salesforce since they all have values?

I've also ran the Synchronization package with records in the Postgres database (from Export), and have no immediate errors until I change a record/row in Salesforce or the Postgres db and try to resync. This will then either show no records were updated/synced if the change was in Postgres, or it'll show an error if the change was on the Salesforce side (error is "null value in column "Id" voilates not-null constraint")

Order By: Standard | Newest | Votes
1
votes
Mariia Zaharova posted this 2 weeks ago

Hi Jacob!

The main requirement for synchronizing database data is that the primary key columns of the database tables must be autogenerated.

Thus, in order to synchronize the tables properly, you should create an extra column in PostgreSQL table, set it as an autogenerated and make it the primary key of the table.  At the moment, it is impossible to specify any key for mapping existing records in the synchronized sources to each other for the first synchronization.

When synchronization is performed for the first time, it does not check whether the records in sources are identical and simply copies data from one side to another and vice versa, and "maps" the original records to their copies in another source. When performing synchronization repeatedly, Skyvia synchronizes only data that was changed since the previous synchronization. It uses the fields storing information about when a record was created and modified in cloud applications and creates special tracking tables and triggers in relational databases for data modification tracking.

 

Please refer to: 

https://skyvia.com/resources/docs/index.html?synchronization_overview.htm

https://skyvia.com/resources/docs/index.html?synchronizing_product_data.htm

  • Supported by
  • Jacob Widmer
0
votes
Jacob Widmer posted this 2 weeks ago

Thanks for the insight, I missed the "create an extra column in the table and set as autogenerated primary key" part of setup. 

After creating a column "_id" and setting that as the table's primary key value, I am now getting the error:

"column store._id does not exist"

The database does fill up with the records properly if I manually insert them using SQL query commands, filling in the new autogenerated "_id" column, however this error is thrown when running sync.

1
votes
Mariia Zaharova posted this 2 weeks ago

After creating a column "_id" and setting that as the table's primary key value, I am now getting the error:

"column store._id does not exist"

Please try creating new synchronization package from the scratch and notify me about the results.

  • Supported by
  • Jacob Widmer
0
votes
Jacob Widmer posted this 2 weeks ago

I created a new package and was successfully able to sync one single table. Thanks for your help. A few other questions.

Regarding adding new tables to sync - would you suggest adding a package per table or adding those tables into the same package and resetting the "initTrackingObjects" flag? 

Additionally, I've been using the "Replicate" package to get the initial table structure set in place, however this creates tables in the Postgres database that contain primary keys that are not autogenerated (required for syncing package). Is there a better way to setup the new tables for Synchronization with keeping the foreign key relationships intact? 

And lastly, I am having an issue with foreign key constraints. I am trying to sync the Opportunity records as well as the Contact records. They have a foreign key relationship. I am receiving this error for about half of the records -
"insert or update on table "Opportunity" violates foreign key constraint "FK_Opportunity_Contact_Contact__c"". Any ideas on what that constraint is tripping up?

1
votes
Mariia Zaharova posted this 6 days ago

Regarding adding new tables to sync - would you suggest adding a package per table or adding those tables into the same package and resetting the "initTrackingObjects" flag? 

 

The recommended option is to use separate packages for new tasks or re-create the package with several tasks (new tasks and tasks that you have in an existing package).

 

Additionally, I've been using the "Replicate" package to get the initial table structure set in place, however this creates tables in the Postgres database that contain primary keys that are not autogenerated (required for syncing package). Is there a better way to setup the new tables for Synchronization with keeping the foreign key relationships intact? 

 

Replication package creates identical tables according to the source tables that is necessary for replication process. Due to the fact that synchronization package requires autogenerated primary key column, you need to create it manually. At the moment, Skyvia does not provide functionality for initial creating tables for sync packages.

 

"insert or update on table "Opportunity" violates foreign key constraint "FK_Opportunity_Contact_Contact__c"". Any ideas on what that constraint is tripping up?

Please specify the package (its number or name) with which this issue occurs.

  • Supported by
  • Jacob Widmer
0
votes
Jacob Widmer posted this 6 days ago

Thanks for the help. 

The package number is #52711, run #14025316.  The most recent run I toggled off "Create Foreign Keys" and removed two Objects (to speed up the run time), which is why that one passed. But if you could look into the broken build (#14025316) I'd appreciate it.

Thanks

1
votes
Mariia Zaharova posted this 5 days ago

Thanks. I will check it and reply you back with possible reasons and solutions as soon as possible.

  • Supported by
  • Jacob Widmer
0
votes
API User Reporting posted this 4 days ago

You can't use the foreign key constraints because SkyVia does not replicate in the context of source database transactions so the order in which the data is updated in the target environment may violate referential integrity. 

SkyVia should be defining unenforced (not enabled) foreign key constraints in the target system so you have this information as metadata in the target database which would be useful in modeling and ER diagrams.

Most replication frameworks that are external to the source system's transaction context have this problem. It is not unique to SkyVia.

Close