0

I have 150 databases in one Postgresql instance. I need to list object_type, count (*) belonging to these databases. And I have 6 instances, which means about 900 databases.

Since querying each of the hundreds of databases would not be feasible, I would like to check with you if there is a system view where I could list object_type and quantity in the instance level (one instance containing many databases).

Because the query I have is below, but it needs to perform on each of the 900 databases, cause it checks only the current DB, not the entire instance:

SELECT   c.relkind as type, count(1) as Qtty 
FROM pg_catalog.pg_class c 
   LEFT JOIN g_catalog.pg_namespace n ON n.oid = c.relnamespace 
WHERE n.nspname not in ('pg_catalog', 'information_schema', 'pg_toast') 
GROUP BY  c.relkind 
ORDER by 2 desc ;
kazyski
  • 1
  • 2
  • 1
    Does this answer your question? [Possible to perform cross-database queries with PostgreSQL?](https://stackoverflow.com/questions/46324/possible-to-perform-cross-database-queries-with-postgresql) – sticky bit Jan 04 '22 at 16:49
  • 1
    Unrelated, but: `count(1)` is actually slightly _slower_ than `count(*)` if you expected that to some magic thing to make the count faster. –  Jan 04 '22 at 16:53

0 Answers0