How to make JOIN for tables from different DB?

Question

PostgreSQL

Hello!
I have: about 10 tables in 2 different databases. One of the tables is large (hundreds of millions of records), the other has hundreds of thousands of records (all data in PostgreSQL).
It is necessary: ​​in fact to make JOIN on all 10 tables and to receive result for example group by for statistics.
Not necessarily in real time, you can, for example, a couple of times a day.
What is the best way to do this?
I mean that there must be some kind of software that, for example, takes a set of files in csv , and does whatever it wants with it.
PS My main language is PHP. But I think it does not matter, since the decisions most likely should be console.
I'd be glad for any tips, thanks!

Answer 1

You cannot directly access another database. But who said that it is impossible at all?
Set up Foreign Data Wrappers: https://www.postgresql.org/docs/current/static/pos...
Get access to both tables in the same database. On the current versions of PG, the scheduler can already push the aggregation to fdw on its own and calculate what is possible there, and only transfer the aggregated data back.
Older way: take the good old dblink: https://www.postgresql.org/docs/current/static/dbl... pull a query with join tables from one database, join the tables of the second base to the result.

Answer 2

I can be wrong, but this is not provided from different databases. Run the script via cron, it creates a table in some database, pulls the necessary data from all databases into it, makes the query you need from this one table. Everything. By cron, you can then drop this table if the result of the selection is stored somewhere and you do not need it.