So I have multiple child gateways with their own database(PostgreSQL) and a hub gateway where I want all the spoke's data centralized on master gateway database.
How can I achieve this , Does ignition have capability to centralize the data from multiple spokes to central hub? Apart from manually Fetching data with select statements and inserting the data using insert statements.
You should use PostgreSQL's replication technology to push changes in the spoke databases to read-only replicas in the hub.
For complete data transfer, include all schema changes as they happen, use Write-Ahead-Log based replication, described here:
With this approach, you end up with a replica in the hub for each spoke.
For partial data transfer, for selected tables and groups of tables, use Logical replication, described here:
With this approach, you can merge data from all of your spokes into a single database in your hub.
Once you have done this, you can arrange for your history queries in the hub to use their connection(s) to hub-local databases, greatly relieving the workload on the spokes.
Yes. You can set up a Remote History Provider on each spoke pointing at the hub. Then configure a Tag History Splitter on each spoke, that points at both your local historian and at the Remote History Provider. For each tag that you want available in both servers, then change its storage provider to the splitter.