PostgreSQL查询表空间所在目录的磁盘使用率

脚本功能

查询表空间所在目录的磁盘使用率

脚本使用示例

该环境中表空间test_tsp和pg_default都在根目录下，所以显示了两次根目录的使用率和大小

[postgres@mydb1a tmp]$ psql
psql (12.3)
Type "help" for help.

postgres=# select path,
       pg_size_pretty(total_size*1024) total_size,
   pg_size_pretty(use_size*1024) use_size,
   use_rate
from get_disk_info() as aa(path varchar,total_size bigint,use_size bigint,use_rate varchar);
NOTICE:  table "temp_disk_usage_info" does not exist, skipping
 path | total_size |  use_size  | use_rate
------+------------+------------+----------
 /    | 17 GB      | 9695 MB    | 61%
 /    | 17 GB      | 9695 MB    | 61%
 /tmp | 1231 MB    | 4096 bytes | 1%
(3 rows)

postgres=# \db+
                                      List of tablespaces
    Name    |  Owner   |    Location    |  Access privileges  | Options |  Size   | Description
------------+----------+----------------+---------------------+---------+---------+-------------
 pg_default | postgres |                |                     |         | 429 MB  |
 pg_global  | postgres |                |                     |         | 1071 kB |
 test_tsp   | postgres | /app/pg12/tsp  | postgres=C/postgres |         | 8297 kB |
 test_tsp1  | postgres | /tmp/test_tsp1 |                     |         | 0 bytes |
(4 rows)

postgres=# \q
[postgres@mydb1a tmp]$ df -h
文件系统                    容量  已用  可用 已用% 挂载点
devtmpfs                    1.2G     0  1.2G    0% /dev
tmpfs                       1.3G   52K  1.3G    1% /dev/shm
tmpfs                       1.3G  804K  1.3G    1% /run
tmpfs                       1.3G     0  1.3G    0% /sys/fs/cgroup
/dev/mapper/openeuler-root   17G  9.5G  6.3G   61% /
tmpfs                       1.3G  4.0K  1.3G    1% /tmp
/dev/sda1                   976M  120M  789M   14% /boot
tmpfs                       247M     0  247M    0% /run/user/0

函数如下

create or replace function get_disk_info()
returns setof record
as 
$function$
declare
    tbs record;
    sql text;
    res record;
begin
    sql = 'drop table if exists temp_disk_usage_info';
	execute sql;
	sql = 'create temp  table temp_disk_usage_info(path varchar,total_size bigint,use_size bigint,use_rate varchar)';
	execute sql;
    sql = 'select spcname,case when spcname=''pg_default'' then current_setting(''data_directory'') else pg_tablespace_location(oid) end as path from pg_tablespace  where spcname<>''pg_global''';
	for tbs in execute sql
	loop
		sql = 'df '||tbs.path||'|grep -v Used|awk ''{printf"%s,%s,%s,%s\n",$6,$2,$3,$5}''';
		sql = 'copy temp_disk_usage_info from program '||quote_literal(sql)||' delimiter '',''';
		execute sql;
	end loop;
	return query select * from temp_disk_usage_info;
end;
$function$ language plpgsql SECURITY DEFINER;