这篇文章主要介绍了聊聊PostgreSql table和磁盘文件的映射关系，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧
在postgresql中 Drop table会不会释放磁盘空间，今日以实操来见证
--2019-01-11 09:49:21 drop table 会不会释放空间
create table tab_todrop(id int,cname varchar(50),remark text);
insert into tab_todrop select generate_series(1,10000000),'wx good boy',md5('wx good boy');
insert into tab_todrop select generate_series(1,10000000),'wx good boy',md5('wx good boy');
--查看表大小
qmstst=# select pg_size_pretty(pg_relation_size('tab_todrop'));
pg_size_pretty
----------------
2410 MB
(1 row)
qmstst=#
每张数据表放在datap*下。postgresql集群是通过hash分布到dataap*这种文件下。
[root@P1QMSTST01 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/rootvg-rootlv
35G 4.8G 28G 15% /
tmpfs 63G 0 63G 0% /dev/shm
/dev/sda2 477M 33M 419M 8% /boot
/dev/sda1 500M 272K 500M 1% /boot/efi
/dev/mapper/rootvg-homelv
4.8G 1.6G 3.1G 34% /home
/dev/mapper/rootvg-optlv
20G 8.4G 11G 46% /opt
/dev/mapper/rootvg-tmplv
4.8G 402M 4.2G 9% /tmp
/dev/mapper/rootvg-usrlv
9.8G 3.6G 5.8G 39% /usr
/dev/mapper/rootvg-locallv
52G 25G 25G 51% /usr/local
/dev/mapper/rootvg-varlv
15G 5.2G 8.8G 37% /var
/dev/mapper/datavg-gpmasterlv
100G 50G 51G 50% /gpmaster
/dev/mapper/datavg-datap1lv
150G 43G 108G 29% /datap1
/dev/mapper/datavg-datap2lv
150G 42G 109G 28% /datap2
/dev/mapper/datavg-datap3lv
150G 42G 109G 28% /datap3
/dev/mapper/datavg-datap4lv
150G 42G 109G 28% /datap4
/dev/mapper/datavg-datap5lv
150G 43G 108G 29% /datap5
/dev/mapper/datavg-datap6lv
150G 42G 108G 28% /datap6
/dev/mapper/rootvg-redislv
在dataap*下的base目录下存储的是数据表
select
relname, --表/视图/索引等的名字
relowner, --关系所有者
relfilenode --这个关系在磁盘上的文件的名称，如果没有则为0
from pg_class
where relname = 'tab_todrop';
qmstst=# select relname, relowner, relfilenode from pg_class where relname = 'tab_todrop';
relname | relowner | relfilenode
------------+----------+-------------
tab_todrop | 17088 | 15997062
(1 row)
ls -lh 17089/15997006*
-rw------- 1 gpadmin gpadmin 268M Jan 11 13:56 17089/15997006
[root@P1QMSTST01 base]# pwd
