How do I find all the databases and related schemas on a Postgres cluster?

Question

I want to list out all the databases and schemas within a Postgres cluster, how do I get that list?

I have run below queries (I am using pgAdmin)

SELECT * FROM pg_database; --This lists all the databases in the cluster

SELECT distinct table_catalog, table_schema
FROM information_schema.tables
ORDER BY table_schema; --This lists all the schemas in the current database I am in.

I tried to combine them (below query) but it just gives result from information_schema and is limited to one database only.

select distinct db.datname, t.table_schema from pg_database db
inner join information_schema.tables t on db.datname = t.table_catalog
order by db.datname, t.table_schema

Answer 1

Databases are (logically) strictly separated in PostgreSQL; you cannot get information about objects in one database (e.g. schemas) with queries in another database.

You'll have to connect to all databases in turn and query each for its schemas.