How to get all databse schemas from Postgresql cluster

1)  Configure the dblink contrib module to the cluster.

 And create the extension on all database.

Postgres=# create extension dblink ;
CREATE EXTENSION

DB1=# create extension dblink ;
CREATE EXTENSION

DB2=# create extension dblink ;
CREATE EXTENSION

2)  Create the table to store ,output of your function.
postgres=# create table all_db_schemas(databasename name,schemaname name);
CREATE TABLE


3) Create the function on Postgres database.

create or replace function get_all_db_schemas() returns text as $$
declare
r record;
r1 record;
dbname1 name;
schemaname1 name;
done text default 'successes';
stmt1 text;
stmt3 text;
begin
for r in select datname from pg_stat_database
loop
dbname1 :=r.datname;
CONTINUE WHEN dbname1='template0';
stmt1 :='select * from dblink('
|| ''''
|| 'dbname='
|| dbname1
|| ''''
|| ','
|| ''''
|| 'select distinct schemaname from pg_statio_user_tables'
|| ''''
|| ') as t1(schemaname name)'
|| ';';
for r1 in execute stmt1
loop
schemaname1 :=r1.schemaname;
stmt3 := 'insert into all_db_schemas(databasename,schemaname) values ('
|| ''''
||dbname1
|| ''''
|| ','
|| ''''
|| schemaname1
|| ''''
|| ')'
|| ';' ;
execute stmt3;
end loop;
end loop;
return done;
end;
$$ language plpgsql;


4) Execute the function to get to collect  all the schemas from all the databases on cluster.

postgres=# select get_all_db_schemas();
 get_all_db_schemas
--------------------
 successes
(1 row)

5) Check the data got inserted into table or not?
postgres=# select * from all_db_schemas;
 databasename | schemaname
--------------+------------
 postgres     | public
 postgres     | s1
 db1            | public
 db2            | public
(4 rows)