Início / dba / Perguntas / 194979
Accepted
Tombart
Asked: 2018-01-10 05:35:34 +0800 CST

O tamanho do banco de dados PostgreSQL não corresponde ao espaço usado no disco

  • 772

Eu tenho um banco de dados PostgreSQL 9.6 com tráfego de alto volume. Eu corro periodicamente para pg_repackrecuperar espaço não utilizado em tabelas/índices. Em tabelas maiores, o repack às vezes falha ao completar o processo, o que resulta no uso de mais espaço em disco que o PostgreSQL relata que o banco de dados está usando.

Eu uso a seguinte consulta para relatar o tamanho de cada banco de dados:

SELECT schema_name, 
       pg_size_pretty(sum(table_size)::bigint),
       (sum(table_size) / pg_database_size(current_database())) * 100 as pct
FROM (
  SELECT pg_catalog.pg_namespace.nspname as schema_name,
         pg_relation_size(pg_catalog.pg_class.oid) as table_size
  FROM   pg_catalog.pg_class
     JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY pct DESC;

    schema_name     | pg_size_pretty |                pct                 
--------------------+----------------+------------------------------------
 production         | 605 GB         |            62.70818987165323895600
 dev                | 116 GB         |            12.05199834243206743500
 pg_toast           | 12 GB          |             1.26824870382580753200
 staging            | 12 GB          |             1.26031018275065892500
 test               | 1497 MB        |             0.15143744784303601600
 pg_catalog         | 26 MB          |         0.002621403693008641646300
 public             | 624 kB         |         0.000061661486144352849300
 information_schema | 96 kB          |         0.000009486382483746592200
 repack             | 0 bytes        | 0.00000000000000000000000000000000

isso dá uma ideia de que o espaço ocupado deve estar ao redor 750GB. No entanto, na realidade, o PostgreSQL está usando quase o dobro:

$ du -hs /var/lib/postgresql/9.6/main/base/
1.3T    /var/lib/postgresql/9.6/main/base/

Parte do problema é pgsql_tmpque isso é ocupar 349GB. Existe uma maneira segura de remover arquivos não utilizados pgsql_tmp?

349G    /var/lib/postgresql/9.6/main/base/pgsql_tmp/

Já tentei VACUUM FULLe pg_repacknas maiores mesas sem sucesso. A única maneira de se livrar do espaço em disco desperdiçado parece ser despejar tabelas no SQL e reimportar para um servidor limpo.

postgresql postgresql-9.6

1 respostas

  1. Best Answer

    Atualizei a consulta para calcular os tamanhos do banco de dados (índices incluídos):

    SELECT schema_name, 
       pg_size_pretty(sum(table_size)::bigint),
       (sum(table_size) / pg_database_size(current_database())) * 100 as pct
FROM (
  SELECT pg_catalog.pg_namespace.nspname as schema_name,
         pg_total_relation_size(pg_catalog.pg_class.oid) as table_size
  FROM   pg_catalog.pg_class
     JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY pct DESC;

    Quanto aos arquivos tmp, excluí todos os arquivos com mais de 1 dia:

    find /var/lib/postgresql/9.6/main/base/pgsql_tmp/ -type f -mtime +1 -delete

    pelo menos em nossas consultas de configuração normalmente leva minutos, no máximo horas. Portanto, arquivos temporários com mais de um dia provavelmente foram deixados por algum processo do postgresql que travou. Os arquivos temporários são sufixados com o PID do processo, como pgsql_tmp13774.1onde 13774deve ser o PID do processo postgresql.

    • 3

relate perguntas