主页 / dba / 问题 / 62969
Accepted
Brendan Hill
Asked: 2014-04-11 15:34:41 +0800 CST

删除后如何在不重建表的情况下回收磁盘空间？

  • 772

我们的 PostgreSQL 9.0 Windows 生产服务器空间不足。

在我们 100GB 的数据库中，我们有一个包含 TOASTed 二进制数据的大表。我们已经删除了一些行，需要将空间返回给 O/S。

我们没有足够的空间来完全重写表，而我所有的阅读，CLUSTER都是他们需要重写的表。到目前为止，我的 google-fu 还不足以找到其他任何东西。VACUUM FULLpg_repack

一些停机时间是可以接受的（约 2 小时），但是备份/恢复对于我们的目的来说太慢了，我也不热衷于在步骤之间删除数据库。

问题：如何在不进行全表重写的情况下将磁盘空间归还给操作系统？

（这是一个生产服务器，因此任何解决方案都必须是有信誉的/推荐的/支持的等）。

（PS 如果可以以某种方式在其上重建表，则可以使用单独的更大的磁盘。表空间？）

postgresql windows

3 个回答

  1. Best Answer

    如果您使用除CLUSTER//VACUUM FULL之外的任何东西pg_repack（所有这些都自动管理锁），您需要确保没有并发写入表。在表上获取排他锁并在单个事务中完成所有操作，或者更好的是，关闭所有连接以避免并发更改。

    TABLESPACE

    是的，你的最后一个想法可行。在另一个磁盘上创建一个新的表空间。

    CREATE TABLESPACE ts1 LOCATION '/data/disk2';

    然后在新表空间中创建表的优化副本：

    CREATE TABLE new_tbl 
TABLESPACE ts1 AS
SELECT * FROM tbl
ORDER BY ....  -- ORDER BY is optional

    副本将被紧紧地打包，没有死行。

    然后你可能只是删除旧表并重命名新表以继续使用新磁盘。

    或者，如果由于某种原因必须将其保留在旧磁盘上，请删除旧表，重命名新表并将其移回默认表空间。现在，这一步要快得多：

    ALTER TABLE tbl SET TABLESPACE pg_default

    根据文档：

    此表单将表的表空间更改为指定的表空间，并将与表关联的数据文件移动到新的表空间。表上的索引（如果有）不会移动；但可以使用其他SET TABLESPACE命令单独移动它们。另请参阅 CREATE TABLESPACE。

    无论哪种方式，不要忘记（重新）创建所有依赖对象。索引、外键、视图……

    好吧，您可以ALTER TABLE tbl SET TABLESPACE ...开始使用，但是表格不会按要求进行优化，只是按原样移动。但是您将有足够的回旋余地来运行 pg_repack 等。

    COPY

    完整的备份/恢复可能需要很长时间，但您可以只对有问题的表执行此操作。

    COPY tbl TO '/path/to/other/disk/tbl.pgsql';
TRUNCATE tbl;
COPY tbl FROM '/path/to/other/disk/tbl.pgsql';

    桌子现在已经挤得满满当当了。

    临时表

    如果您碰巧有足够的 RAM，您可以对 RAM 中的临时表执行类似的操作。会快很多。详细说明：

    • 5

  2. 如果您的环境的磁盘空间超过此表的“真实数据（减少后）”大小。您可以使用 pg_reorg 减少此表的膨胀空间。或者您可以使用 londiste3 增量复制此表，并使用更少的时间进行交换。但是如果你的环境没有比这张表更多的空间，你就不能减少。

    • 1

  3. 当您可以删除堆页面中的元组时，还有另一种方法可以减少膨胀空间，这种方法使用较少的添加空间来减少膨胀表。喜欢 exp：

    pg93@db-172-16-3-150-> psql
psql (9.3.3)
Type "help" for help.

digoal=# create table t_bloat(id int primary key, info text, crt_time timestamp);
CREATE TABLE
digoal=# insert into t_bloat select generate_series(1,100000),md5(random()::text),clock_timestamp();
INSERT 0 100000
digoal=# delete from t_bloat where id<>100000;
DELETE 99999
digoal=# vacuum t_bloat;
VACUUM
digoal=# vacuum t_bloat;
VACUUM
digoal=# select relpages from pg_class where relname='t_bloat';
 relpages 
----------
      233
(1 row)

digoal=# insert into t_bloat select generate_series(1,100),md5(random()::text),clock_timestamp();
INSERT 0 100
digoal=# select split_part(ctid::text,',',1) from t_bloat group by 1;
 split_part 
------------
 (0
 (232
(2 rows)

digoal=# begin;
BEGIN
digoal=# create temp table t_bloat_tmp(like t_bloat);
CREATE TABLE
digoal=# insert into t_bloat_tmp select * from t_bloat where split_part(ctid::text,',',1)='(232';
INSERT 0 1
digoal=# delete from t_bloat where split_part(ctid::text,',',1)='(232';
DELETE 1
digoal=# insert into t_bloat select * from t_bloat_tmp ;
INSERT 0 1
digoal=# end;
COMMIT
digoal=# vacuum t_bloat;
VACUUM
digoal=# select split_part(ctid::text,',',1) from t_bloat group by 1;
 split_part 
------------
 (0
 (1
(2 rows)

digoal=# select relpages from pg_class where relname='t_bloat';
 relpages 
----------
        2
(1 row)

    你注意锁定，这只是一个想法。

    • 1

相关问题