PostgreSQL查看各个数据库占用的物理空间大小

最新推荐文章于 2024-06-26 05:30:00 发布

清海风缘

最新推荐文章于 2024-06-26 05:30:00 发布

阅读量1.1k

点赞数

分类专栏： postgresql 文章标签： postgresql

本文链接：https://blog.csdn.net/liuhhaiffeng/article/details/106489562

版权

postgresql 专栏收录该内容

27 篇文章 3 订阅

订阅专栏

 SELECT d.datname AS Name,  pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
        ELSE 'No Access'
    END AS SIZE
FROM pg_catalog.pg_database d
    ORDER BY
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_database_size(d.datname)
        ELSE NULL
    END DESC -- nulls first;
    LIMIT 20;

测试结果:

postgres=# SELECT d.datname AS Name,  pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
postgres-#     CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
postgres-#         THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
postgres-#         ELSE 'No Access'
postgres-#     END AS SIZE
postgres-# FROM pg_catalog.pg_database d
postgres-#     ORDER BY
postgres-#     CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
postgres-#         THEN pg_catalog.pg_database_size(d.datname)
postgres-#         ELSE NULL
postgres-#     END DESC -- nulls first
postgres-#     LIMIT 20;
   name    |  owner   |  size   
-----------+----------+---------
 pg1       | postgres | 23 MB
 postgres  | postgres | 7631 kB
 template0 | postgres | 7481 kB
 template1 | postgres | 7481 kB
(4 rows)