PostgreSQL - Best way to incremental export 1000+ tables every 5 mins I have 6 PostgreSQL database servers(v11) hosted on some third-party vendor. I don't have access to setup pg_logical. Each server has 1 database but 1000+ tables.
So I want to get the data from these 6 servers to my central PostgreSQL database. The sources tables are having PK and last_updated_timestamp columns.
Im just trying to find the best approach to get the data at every 5 or 15mins interval from these tables to my central database server.
My goal is to sync the whole database to Report database server with every5mins interval.
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 ...