Início / dba / Perguntas / 62969
Accepted
Brendan Hill
Asked: 2014-04-11 15:34:41 +0800 CST

Como recuperar espaço em disco após excluir sem reconstruir a tabela?

  • 772

Nosso servidor de produção Windows PostgreSQL 9.0 está com pouco espaço.

Em nosso banco de dados de 100 GB, temos uma grande tabela contendo dados binários TOASTed. Excluímos algumas linhas e precisamos devolver o espaço ao O/S.

Não temos espaço suficiente para fazer uma reescrita completa da tabela e todas as minhas leituras de CLUSTER, VACUUM FULLe pg_repacké necessário reescrever a tabela. Meu google-fu tem sido insuficiente até agora para encontrar qualquer outra coisa.

Algum tempo de inatividade é aceitável (~ 2 horas), no entanto, o backup/restauração é muito lento para nossos propósitos, e não estou interessado em descartar o banco de dados entre as etapas.

PERGUNTA: Como posso devolver o espaço em disco ao sistema operacional sem fazer uma reescrita completa da tabela?

(Este é um servidor de produção, portanto, todas as soluções devem ser respeitáveis/recomendadas/suportadas, etc.).

(PS Um disco separado muito maior está disponível e pode ser usado, se a tabela puder ser reconstruída de alguma forma. Tablespaces?)

postgresql windows

3 respostas

  1. Best Answer

    Se você usar qualquer coisa, exceto CLUSTER/ VACUUM FULL/ pg_repack(que gerencia bloqueios automaticamente), precisará garantir que não haja gravações simultâneas na tabela. Pegue um bloqueio exclusivo na tabela e faça tudo em uma única transação ou, melhor ainda, feche todas as conexões para evitar alterações simultâneas.

    TABLESPACE

    Sim, sua última ideia pode funcionar. Crie um novo tablespace no outro disco.

    CREATE TABLESPACE ts1 LOCATION '/data/disk2';

    Em seguida, crie uma cópia otimizada da tabela no novo tablespace:

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

    A cópia será empacotada firmemente sem linhas mortas.

    Em seguida, você pode simplesmente descartar a tabela antiga e renomear a nova para continuar usando o novo disco.

    Ou , se você tiver que mantê-lo no disco antigo por algum motivo, exclua a tabela antiga, renomeie a nova e mova-a de volta para o tablespace padrão. Esta etapa é muito mais rápida agora com:

    ALTER TABLE tbl SET TABLESPACE pg_default

    Por documentação:

    Este formulário altera o tablespace da tabela para o tablespace especificado e move o(s) arquivo(s) de dados associado(s) à tabela para o novo tablespace. Índices na tabela, se houver, não são movidos; mas eles podem ser movidos separadamente com SET TABLESPACEcomandos adicionais. Veja também CREATE TABLESPACE.

    De qualquer forma, não se esqueça de (re)criar todos os objetos dependentes. Índices, chaves estrangeiras, visualizações, ...

    Bem, você poderia usar ALTER TABLE tbl SET TABLESPACE ...para começar, mas a tabela não seria otimizada conforme solicitado, apenas movida como está. Mas você teria espaço de manobra suficiente para executar pg_repack etc.

    COPY

    Um backup / restauração completo pode demorar muito, mas você pode fazer isso apenas para a tabela em questão.

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

    A mesa agora está bem embalada.

    tabela temporária

    Se você tiver RAM suficiente, poderá fazer algo semelhante com uma tabela temporária na RAM. Seria muito mais rápido. Instruções detalhadas:

    • 5

  2. Se o seu ambiente tiver mais espaço em disco do que o tamanho dos "dados reais (após reduzidos)" desta tabela. você pode usar pg_reorg para reduzir o espaço de inchaço desta tabela. ou você pode usar o incremento londiste3 para copiar esta tabela e usar menos tempo para trocar. mas se seu env não tiver mais espaço que essa tabela, não pode reduzir.

    • 1

  3. existe outra maneira de reduzir o espaço de inchaço quando você pode remover tuplas em páginas de heap; esse método usa menos espaço de adição para reduzir a tabela de inchaço. COMO 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)

    você deve manter bloqueios de atenção, isso é apenas um pensamento.

    • 1

relate perguntas