Re: Append only replication over intermittent links (with local only delete?)

On Thu, Jun 14, 2018 at 8:04 AM, Uri Braun <uri(at)braunnet(dot)com> wrote:

> To be clear, the car device will surely add data -- append rows -- and may
> very occasionally add a new table. I would expect the only case where a
> delete may occur -- other than culling old data -- is during recovery of a
> partial write or transaction rollbacks. The time requirements are loose,
> but I would like replication consistency within hours (once connected).
>
> I'm wondering what replication scheme is appropriate for this use case and
> how to configure it appropriately.
>
>
I would recommend a trigger based approach where every table has an INSERT
trigger on it that adds the new data to a log table to store the data that
needs to be pushed up to the central server. When the system detects a
network connection, it sends the data and deletes them from the log table
in a transaction (begin, select, send, delete, get ack, commit).