We collect data in about 100 satallites postgres databases.
The data should be moved to a central postgres database.
What is the common way to handle this?
This site is in read only mode. Please continue to browse, but replying, likes,
and other actions are disabled for now.
@guettli,
So Im understanding you have 100 Postgres databases. Each of those databases standalone, each with different types of data. You want to synchronize to contents into a master database, effectively your 101st database. Am I understanding that correctly?
guettli
5
Hi openbride,
thank you for looking at my question.
The database schema is equal on all hosts. All database columns use primary-keys which don’t collide.
Rows in the databases get created on the satellites. The rows get never updated.
Yes, there are 100+1 databases.
My suggestion is that you have each DB replicate/copy its data to a central warehouse. This will ensure that there is a “truth” that reflects all the remote nodes, however many there may be. You can organize that data by remote source. For example, there might be a column which reflects a node ID to help you understand the source of the record. You might also want to create some meta data like a lookup table for all those remote databases. I assume there is some variations where this meta data would be useful. With data consolidated, it is a pretty simple process to use redash or any tool like it to query that unified warehouse.
guettli
7
OK, if I understood you correctly, then the easiest setup would be to sync the satalite databases to the central database with some tool (not with redash). Then query this central database with redash.
if I understood correctly redash is only for querying the db, it does not move data. Is this correct?
This is correct.