Issue
Heroku Connect creates a function called get_xmlbinary in the public schema of your database, which is used as part of the sync process. If the public schema is removed from your search_path or if this function is deleted, Heroku Connect will not be able to find this function, which will in turn impair its ability to sync your data.
If this has happened within your database, you might see an error message in your logs that is similar to the following: function get_xmlbinary() does not exist at character 9.
Resolution
To troubleshoot this, start by ensuring that the get_xmlbinary function exists in the public schema of the database used by the Connect add-on:
=> \df get_xmlbinary
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+---------------+-------------------+---------------------+--------
public | get_xmlbinary | character varying | | func
If the function is not listed in the output, then the issue is likely with the search path. This can be confirmed by running \df public.get_xmlbinary and if it appears there but not in the previous query, the search_path does not include public. The current default search path can be seen by using the following query:
=> show search_path;
search_path
----------------
"$user",public
If public is listed in the search_path, it is likely that the search path has been altered using SET search_path TO my_schema; or something similar. Even if public is listed in your search_path here it is possible that your ORM is changing the search path when it connects to your database. Try to run the above SQL through your ORM and confirm that public is still in your search path.
If the public schema is not listed in your search_path, you'll need to adjust your search_path with the default value: SET search_path TO "$user", public;. This setting should persist across connections (existing connections may need to be restarted), but if it does not then your ORM may be overwriting the setting and you'll need to investigate that separately - a good place to start is to search Google for "search_path" and your ORM.
If the get_xmlbinary function cannot be found in the public schema, please reach out to our support team to have this resolved.