I have an application that consists of a master application+DB and a bunch of edge servers. Each edge server syncs a subset of the master data via custom API calls. I would like to simplify this process by implementing some existing solution for sharding/replication. Some considerations:
- The edge servers have bad/unstable connectivity to master and no interconnection is possible between the edge servers.
- Can’t 100% trust the edge servers, so I don’t want to give them full access to master for replicating whatever they want.
- Fancy features like multi-master writes or distributed execution of queries between shards are not needed. Each shard should be a fully redundant read-only subset of master.
I’ve thought about using builtin pg logical replication but am not sure how much work it will be create publications for all the tables with appropriate filters. And it’s really too bad that replication doesn’t copy table structures, so I would have to keep the structure in sync manually somehow.
The 3rd party solutions I’ve looked at (Citus, repmgr) don’t seem quite right either, considering my 3 points above.
Is there something I’m missing?