PostgreSQL – Best way to incremental export 1000+ tables every 5 mins

bulkcopypostgresql

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.

Best Answer

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 > ...
ON CONFLICT ON (...)
   DO UPDATE ...
Related Question