To my knowledge, cross-DB querying is limited in Postgres. So I wonder if there is a way to do the following.
Lets say I have 3 Postgres DBs (A, B and C) with each having the same schema, but different data. They each have a users table. I want to query the count of users in each, grouped by DB.
Like such:
| DB | User Count |
|---|---|
| A | 90 |
| B | 12 |
| C | 59 |
Not necessarily looking for a solution within Postgres. An external tool will also suffice.
You could create another database. In that database, install postgres_fdw and create foreign servers that point to each of your original databases. Create a foreign table for each of your user tables, and create a view that is a UNION ALL of all these foreign tables. Then you can query that view, and from PostgreSQL v14 on, PostgreSQL should be able to query the foreign tables in parallel.
Don't forget to ANALYZE your foreign tables before using them.
