PostgreSQL drop table 空间不释放的问题解决
PostgreSQL drop table 空间不释放的问题解决
先上结论： 有连接占用这个表(会话没提交)，kill掉相关连接即可释放出磁盘空间。
背景：
1、生产环境，因为历史原因某个日志表体积超过150GB，经与研发沟通后，确定处理策略是保留3个月的数据即可，其余历史数据可丢弃。
3、日志表，经业务方确认后，允许在割接期间有5分钟以内丢数据的情况发生。
说下大致处理的过程如下：
0、因为日志表，只有插入操作，我们可以根据自增id简单估算了下需要保留的数据范围。后续处理的时候，只要根据id范围来查询就行。
1、创建一个新表t_new，表结构与原先的表t保持一致。然后创建一个sequence，与t_new表关联起来。 记得给新表加下授权之类的玩意。
2、写个脚本，使用类似下面的方法，将t表的历史数据灌入t_new表中（脚本首次跑全量）
# 我这里的519999999是t表比较近的一个时刻的最大id，不需要很精准即可
while [[ $max_id -le 519999999 ]] ; do
# 先插一条数据到新表里面，用于检测max_id。 sql如下：insert into t_new select * from t order by id asc limit 1;
max_id=`psql -d db1 --no-align --tuples-only -c "select max(id) from t_new ;"`
echo "当前时间: $(date +%F_%T)" | tee -a /tmp/archive.log
echo "当前最大id:" ${max_id} | tee -a /tmp/archive.log
let max_id=max_id+1
psql -d db1 -c "insert into t_new select * from t where id>=${max_id} order by id asc limit 5000;"
done3、在step2首次全量补数据完成后，我们还需要定时追下增量。给step2的这个脚本要配个cronjob，每5分钟运行一次追增量数据。
4、晚上低峰期，正式切换前，修改下新表的sequence，将last_value调大些，防止切换时候id冲突了。我这里类似这样：
alter sequence seq_t_new_id RESTART WITH 518500000;5、低峰期切换表名
ALTER TABLE t RENAME TO t_old;
ALTER TABLE t_new RENAME TO t;6、再补下数据（如果允许丢失些数据，这一步也可以不用进行）
我们上面最后切换的时候，可能还会有数据不平的情况，我们可以再查下 t_old表最近5分钟的id情况，和新的t表做下比较，补到t表里面即可。
7、删除老表
truncate table xxx;
drop table xxx;8、执行完上面的删表操作后，我们看看磁盘空间释放出来没有？
很明显，/data 路径还是占用了87%的空间，我们drop table 完全没卵用。。。
prod-pg-01 / # df -lhP
Filesystem Size Used Avail Use% Mounted on
devtmpfs 3.7G 0 3.7G 0% /dev
tmpfs 3.7G 20K 3.7G 1% /dev/shm
tmpfs 3.7G 556K 3.7G 1% /run
tmpfs 3.7G 0 3.7G 0% /sys/fs/cgroup
/dev/vda1 50G 3.1G 44G 7% /
tmpfs 756M 0 756M 0% /run/user/0
/dev/mapper/VolGroup1-LogVol01 591G 489G 75G 87% /data
这种情况，很简单，通常是文件句柄（这里的话，就是有连接没释放）占用导致的。lsof看一下：
prod-pg-01 / # lsof | grep delete 结果如下：
postgres 24550 postgres 254u REG 252,0 987389952 10224458 /data/base/16385/587704.7 (deleted)
postgres 24550 postgres 255u REG 252,0 0 10224200 /data/base/16385/587708 (deleted)
postgres 24550 postgres 256u REG 252,0 1073741824 10224234 /data/base/16385/587708.1 (deleted)
postgres 24550 postgres 257u REG 252,0 1073741824 10223874 /data/base/16385/587708.2 (deleted)
postgres 24550 postgres 258u REG 252,0 1073741824 10224205 /data/base/16385/587708.3 (deleted)
postgres 24550 postgres 259u REG 252,0 1073741824 10224221 /data/base/16385/587708.4 (deleted)
postgres 24550 postgres 260u REG 252,0 1073741824 10223748 /data/base/16385/587708.5 (deleted)
postgres 24550 postgres 261u REG 252,0 1073741824 10224317 /data/base/16385/587708.6 (deleted)
postgres 24550 postgres 262u REG 252,0 1073741824 10224367 /data/base/16385/587708.7 (deleted)
postgres 24550 postgres 263u REG 252,0 1073741824 10224413 /data/base/16385/587708.8 (deleted)
postgres 24550 postgres 264u REG 252,0 1073741824 10224459 /data/base/16385/587708.9 (deleted)
postgres 24550 postgres 265u REG 252,0 0 10223673 /data/base/16385/587712 (deleted)
postgres 24550 postgres 266u REG 252,0 1073741824 10223725 /data/base/16385/587712.1 (deleted)
postgres 24550 postgres 267u REG 252,0 1073741824 10224203 /data/base/16385/587712.2 (deleted)
postgres 24550 postgres 268u REG 252,0 1073741824 10224056 /data/base/16385/587712.3 (deleted)
postgres 24550 postgres 269u REG 252,0 1073741824 10223755 /data/base/16385/587712.4 (deleted)
postgres 24550 postgres 270u REG 252,0 1073741824 10224335 /data/base/16385/587712.5 (deleted)找到pid后，到pg里面，干掉它：
db1=# select pg_terminate_backend(24550);
pg_terminate_backend
----------------------
t
(1 row)
如果上面的losf有多个进程占用的已删除的文件的话，我们这里多次执行 pg_terminate_backend即可。再次查看，磁盘空间已经释放出来了：
prod-pg-01 / # df -lhP
Filesystem Size Used Avail Use% Mounted on
devtmpfs 3.7G 0 3.7G 0% /dev
tmpfs 3.7G 20K 3.7G 1% /dev/shm
tmpfs 3.7G 556K 3.7G 1% /run
tmpfs 3.7G 0 3.7G 0% /sys/fs/cgroup
/dev/vda1 50G 3.1G 44G 7% /
tmpfs 756M 0 756M 0% /run/user/0
/dev/mapper/VolGroup1-LogVol01 591G 242G 322G 43% /data