Postgres sync

[abalmos]

Postgres/timescaledb is becoming our primary storage layer for data logs. We should consider some schemes for getting this data back to a remote postgres for permanent storage.

This is an interesting project that we may be able to use and/or learn from: https://bucardo.org/Bucardo/

[facastiblancor]

I like the way they handle the database replication. Though, my only concern is the control the sync frequency, which (I think I did not understand on their wiki) seems to depend on several variables of our environment.

[abalmos]

Yeah and we likely only want ISOBlueN -> cloud sync, not ISOBlue1 <-> ISOBlue2 <-> ... <-> cloud sync

[abalmos]

I'm thinking a "first" cut at this could be something like every hour:

1. get a "last" timestamp for each table from the server
2. export data from the ISOBlue from those timestamps forward (SQL dump ?)
3. compress all exports into one thing
4. send to server
5. decompress
6. INSERT dumps into the same table schema with one additional column of node or something like that per row.

might be the easiest thing moving forward.

That would hopefully play nice with timescaledb's partitions?