PostgreSQL - Best way to incremental export 1000+ tables every 5 mins
In the central database, define foreign tables using postgres_fdw that point to the other databases. For each table, remember the timestamp from the last time you fetched data. Then regularly run
INSERT INTO localtable
SELECT * FROM foreigntable
WHERE last_upd_ts &gt; ...
ON CONFLICT ON (...)
   DO UPDATE ...