如何在PostgreSQL中列出所有数据库及其架构? [英] How to list all databases and their schemas in postgresql?

问题描述

我正在寻找一种列出OpenSuse postgresql服务器上的所有数据库和所有模式的方法.我知道我可以使用诸如 \ l + 之类的meta命令列出所有数据库，但是有什么办法列出所有数据库的所有信息，包括数据库，架构和大小信息?

I am looking for a way to list all databases and all schemas on an OpenSuse postgresql server. I know that I can use the meta commands such as \l+ to list all of the databases, but is there any way to list all information for all databases including databases, schema and size information?

我希望有一种方法可以全面了解所有内容.

I'd like a way to look at everything overall.

谢谢！

推荐答案

我看不到没有先连接数据库就可以获取数据库模式列表的方法.基于此，我认为您可以使用

I don't see a way to get the list of databases's schemas without connecting to it first. Based on this, i think you can get it using db_link with something like:

CREATE OR replace FUNCTION databases_schemas_size 
  () returns setof RECORD 
AS 
  $body$ 

  DECLARE 

    connection_info CHARACTER varying; 
    i RECORD; 
    r RECORD; 

  BEGIN 

    FOR r IN SELECT cast(datname AS CHARACTER varying) FROM  pg_database  WHERE  datistemplate = FALSE LOOP 

    SELECT format ('dbname=%s user=%s password=%s', r.datname, 'YOUR_ALLOWED_USERNAME', 'PASSWORD') INTO   connection_info; 

    perform dblink_connect(connection_info); 
    FOR i IN SELECT r.datname, * 
    FROM   dblink('SELECT nspname , pg_size_pretty(sum(pg_relation_size(C.oid))) AS size FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN (''pg_catalog'', ''information_schema'') group by nspname') 
    AS t(schema_name CHARACTER varying, schema_size text) 

    LOOP 
    RETURN NEXT i; 
    END LOOP; 

  perform dblink_disconnect(); 
END LOOP; 

RETURN; 

END; 
$body$ LANGUAGE plpgsql volatile cost 100; 


SELECT * FROM  databases_schemas_size() 
AS (datname CHARACTER varying, schema_name CHARACTER varying, schema_size text);

您可以通过使用

You can improve it to not hard code the login/password by using the FOREIGN DATA WRAPPER functionality

这篇关于如何在PostgreSQL中列出所有数据库及其架构?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！