5 ms·

Piggybacking for a Postgres question: I have thousands of legacy software instances all around the world, running Postgres with very marginal internet connectio

Piggybacking for a Postgres question: I have thousands of legacy software instances all around the world, running Postgres with very marginal internet connections (think IoT). I’d like to continuously get all their data into my cloud. Should I use Postgres built in async replication for that?

_verandaguy 8y ago
If you added your design goals and constraints (do you want it to be fast? frequent? power-efficient? which ones can you trade off?), this would make a good question for the DBA stackexchange.
jeltz 8y ago
Hard to say for sure without knowing more but I would probably build my own custom protocol for syncing this, because the replication (both binary and logical) is built with the assumption that you have a reasonably constant connection from the master to the replica. If not you will start to build up large amounts of write-ahead logs.
- Cthulhu_ 8y ago
  Never invent the wheel if you haven't done your research yet. Colleagues of mine successfully employed CouchDB in a situation where devices were offline for an x amount of time (running on tablets in airplanes), once they had internet again they would reliably start syncing data with the main database again. This was a number of years ago though, I haven't heard anything about CouchDB since.
  - megous 8y ago
    It works perfectly with intermittent connectivity, restarts, etc. That will not be a problem. I'm not sure about thousands of nodes syncing to a single replica though. That may be a bit over the top.
    - pgaddict 8y ago
      Who says you need to feed it directly to the replica. Logical replication provides infrastructure for decoding changes, and you may fetch it any way you want / how often you want / feed it wherever you want (file, another database, ...). It would be trivial to write something that connects to a bunch of machines regularly, fetches the decoded increment and feed it somewhere (say, to a single database over a shared connection). See test_decoding / pg_recvlogical for examples.
- pgaddict 8y ago
  You need to accumulate the data somewhere. If you don't fetch if from the node, it has to accumulate there. Custom sync protocol does not eliminate this.
sroussey 8y ago
Might look into https://gun.eco/ https://gun.eco/
ibotty 8y ago
I would occasionally send the WAL-log from the client to the cloud. pg_basebackup, etc.

_verandaguy 8y ago

If you added your design goals and constraints (do you want it to be fast? frequent? power-efficient? which ones can you trade off?), this would make a good question for the DBA stackexchange.

jeltz 8y ago

Hard to say for sure without knowing more but I would probably build my own custom protocol for syncing this, because the replication (both binary and logical) is built with the assumption that you have a reasonably constant connection from the master to the replica. If not you will start to build up large amounts of write-ahead logs.

Cthulhu_ 8y ago
Never invent the wheel if you haven't done your research yet. Colleagues of mine successfully employed CouchDB in a situation where devices were offline for an x amount of time (running on tablets in airplanes), once they had internet again they would reliably start syncing data with the main database again. This was a number of years ago though, I haven't heard anything about CouchDB since.
- megous 8y ago
  It works perfectly with intermittent connectivity, restarts, etc. That will not be a problem. I'm not sure about thousands of nodes syncing to a single replica though. That may be a bit over the top.
  - pgaddict 8y ago
    Who says you need to feed it directly to the replica. Logical replication provides infrastructure for decoding changes, and you may fetch it any way you want / how often you want / feed it wherever you want (file, another database, ...). It would be trivial to write something that connects to a bunch of machines regularly, fetches the decoded increment and feed it somewhere (say, to a single database over a shared connection). See test_decoding / pg_recvlogical for examples.
pgaddict 8y ago
You need to accumulate the data somewhere. If you don't fetch if from the node, it has to accumulate there. Custom sync protocol does not eliminate this.

sroussey 8y ago

Might look into https://gun.eco/ https://gun.eco/

ibotty 8y ago

I would occasionally send the WAL-log from the client to the cloud. pg_basebackup, etc.