What does Syncari require to access data in tables?
To sync with database tables, Syncari needs to be able to identify each individual record and know if that record was modified since the last sync. To ensure that happens, every database table that you connect to Syncari must include:
- A primary key that uniquely identifies each record. The primary key must be required for each record.
- A DATETIME or TIMESTAMP field that identifies when the record was created or updated. Syncari uses the field as a watermark to identify when the record was last updated. This must be populated upon creation and must be populated at the current date and time of modification.
How can I fix this error - "canceling statement due to conflict with recovery"?
You may see this error when Syncari tries to query PostgreSQL table. This error occurs when long running queries executing on Standby/Secondary server are cancelled because changes from Primary server need to be applied to Standby/Secondary server. There are couple of options that can be explored for the fix.
- Increase the value of max_standby_archive_delay and max_standby_streaming_delay properties in postgresql.conf file on Secondary PostgreSQL server. You can contact your Database Administrator to make this change. This change ensures that the queries are allowed longer time to finish without being cancelled.
- Ensure that the table Syncari is querying has right database indexes. This is especially true if you are using a function like "Lookup External Record" and providing search condition. If the columns used in search condition are not indexed, queries can be slow and run for a long time causing cancellation. In fact it is always a good idea to check database indexes even if you don't see the above error. Right database indexes can lead to faster Pipeline execution.