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

 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;
1
2
3
4
5
6
7
8
9
10
11
12

测试结果:

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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20