Database 如何使用PostgreSQL列出特定数据库的表并按大小排序

1. 通过添加LIMIT 10，您已经将结果限制在前10行.复制相同的10行的次数与目录中的数据库一样多，但这些仍然是您连接到的当前数据库的结果.

2. 您可能对所有Database Object Size Functions个字节都很熟悉，但是虽然按原始字节大小进行排序很好，但在SELECT部分中遍历pg_size_pretty()以使其具有可读性.

3. 别忘了schemas/namespaces块钱.您可以在一个数据库中拥有多个这样的数据库，并且每个数据库中都有一个同名的表.pg_tables年低于schemaname,information_schema.tables年低于table_schema.The recommendation是

   因为信息模式是SQL标准的，而这里描述的视图是特定于PostgreSQL的，所以如果信息模式提供了您需要的所有信息，那么使用它通常会更好.

4. 虽然您可以在系统目录中列出集群上可用的所有数据库、角色和表空间，但是没有集群范围的名称空间和表目录.在PostgreSQL中，没有本机/内置/标准的方法来从会话内部切换数据库，也没有方法引用其他数据库中的对象，无论是在同一集群上还是在其他地方.为了能够在您的会话中执行这些操作，您需要postgres_fdw或dblink个实际启动的客户端和代表您的附加客户端.

   Off-db，您只需指示您的客户端随时随地打开和关闭连接(这是psql中\c or \connect -reuse-previous=on的 idea )，只要您自己铲数据并且必须维护单独的实用程序即可.

5. 您可以使用预置的external tools个来监控PostgreSQL.

6. CREATE VIEW v_pg_total_relation_sizes在您的每个数据库上(here是您可以在前面加上create view ... as的示例).要使其默认显示在将来创建的数据库中，请在template1 database中也创建一个.使它们在postgres_fdw中可见，然后创建一个包含本地视图和所有链接视图的union视图.根据您的数据库有多大，如果速度变慢，您可能会考虑将其缓存为materialized view.

下面是上面第5点的演示:(db<>fiddle)

create table local_table as
select generate_series(1,2e5,1), (gen_random_uuid())::text;

create view v_pg_total_relation_sizes as 
SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "readable_size",
    pg_total_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', 'pg_toast')
ORDER BY pg_total_relation_size(C.oid) DESC;

连接到您的template1数据库(它始终是在初始PostgreSQL设置期间创建的，因为它就是这样:一个用于所有新数据库的模板)，并创建类似的对象:

create schema neighbour_schema;

create table neighbour_schema.neighbour_table as
select generate_series(1,3e5,1), (gen_random_uuid())::text;

create view neighbour_schema.v_pg_total_relation_sizes as 
SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "readable_size",
    pg_total_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', 'pg_toast')
  ORDER BY pg_total_relation_size(C.oid) DESC;

create database neighbour_db;

现在它们在template1中，所有使用create database创建的新数据库都将从一开始就具有它.

在设置postgres_fdw(和/或dblink)时，请注意，如果您不提供主机和用户信息，则默认情况下它将使用local Unix domain socket.对于不同的用户和主机，确保您连接/链接到的所有实例上的pg_hba.conf也是最新的.

在您的主数据库上:

create extension postgres_fdw;

CREATE SERVER foreign_server
        FOREIGN DATA WRAPPER postgres_fdw
        OPTIONS (dbname 'neighbour_db');
CREATE USER MAPPING FOR current_user
        SERVER foreign_server
        OPTIONS (user 'postgres');
CREATE FOREIGN TABLE fdw_v_pg_total_relation_sizes (
        relation text,
        readable_size text,
        size bigint )
  SERVER foreign_server
  OPTIONS (schema_name 'neighbour_schema', 
           table_name 'v_pg_total_relation_sizes');

设置实体化视图以及UNION ALL个本地和链接视图:

create materialized view f_v_cluster_pg_total_relation_sizes as 
select current_database() as dbname,* from v_pg_total_relation_sizes
union all
select 'neighbour_db',* from fdw_v_pg_total_relation_sizes
order by size desc;

refresh materialized view f_v_cluster_pg_total_relation_sizes;

select * from f_v_cluster_pg_total_relation_sizes;

请注意，每当添加新数据库时都需要重新定义该视图，这可以在PL/pgSQL routine 中通过dynamic SQL自动完成，尽管不是完全的，因为既不能在pg_databse上创建常规触发器，也不能在create database上触发event trigger on ddl_statement_start/_end.