Thread: Append only replication over intermittent links (with local only delete?)
Hi,
I'm looking to run Postgres -- flexible on exact version -- on some devices installed in cars and replicated to a central server over cell phone modems. I expect dropped connections due to: lack of coverage (remote areas), dead spots, tunnels, cars parked in garages, device power disconnected, etc. I expect all data to be generated on the car devices and want it to eventually be copied over to the central server. I would also like to be able to delete old data -- say months old -- on the car equipment and NOT have those deletes propagate to the central server.
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.
Thank you!
Uri Braun
On Thu, Jun 14, 2018 at 8:04 AM, Uri Braun <uri@braunnet.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).
Re: Append only replication over intermittent links (with local onlydelete?)
From
Andreas Kretschmer
Date:
Am 14.06.2018 um 14:04 schrieb Uri Braun: > Hi, > > I'm looking to run Postgres -- flexible on exact version -- on some > devices installed in cars and replicated to a central server over cell > phone modems. I expect dropped connections due to: lack of coverage > (remote areas), dead spots, tunnels, cars parked in garages, device > power disconnected, etc. I expect all data to be generated on the car > devices and want it to eventually be copied over to the central > server. I would also like to be able to delete old data -- say months > old -- on the car equipment and NOT have those deletes propagate to > the central server. > > 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 think pglogical can fit your requirements, please take a look: https://www.2ndquadrant.com/en/resources/pglogical/ Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com