I found that sooner or later one ends up with a few records in an error state, they did not sync up. Most of the time the reason is that a change was made in the connected Salesforce instance and suddenly the written data is not valid anymore. In this case the field _hc_lastop will be set to the value ERROR and the field _hc_err will contain further details about the error.
After the problem is understood and measures have been taken, the data needs to be synced again. For me the following two options worked out quite well so far.
Trigger an update in the affected row
This works by updating something in the affected row, so that the sync will be triggered again. It seems to be enough to just set an attribute to its current value, like in the following example:
UPDATE salesforce.custom_object__c SET name = name WHERE id = 123;
In many cases I got quite good results this way. Make sure that you have a recent backup of your database though.
Update the trigger log
Heroku Connect maintains a log of triggered items, it can be updated as well to achieve the same. I assume though that the sync will not happen as fast as the first way.
Here is how it works in principle:
UPDATE salesforce._trigger_log SET state = 'NEW' WHERE state = 'FAILED' AND table_name = 'custom_object__c';
It's best to inspect this table first with a few SELECT queries to avoid that you change more things than you want.
These two snippets are only two options to get your data into a flow again. I am currently thinking of how to use the failure information for building a small dashboard so that I could monitor easily my data and maybe trigger a sync from there again. This is not useful for all cases though, make sure to always understand what caused the issue and what is the right fix for it before just trying to trigger a sync of invalid data.