主页 / dba / 问题 / 194979
Accepted
Tombart
Asked: 2018-01-10 05:35:34 +0800 CST

PostgreSQL 数据库大小与磁盘上已用空间不匹配

  • 772

我有一个流量很大的 PostgreSQL 9.6 数据库。我定期运行pg_repack回收表/索引中未使用的空间。在较大的表上，重新打包有时无法完成导致使用 PostgreSQL 报告数据库正在使用的更多磁盘空间的过程。

我使用以下查询来报告每个数据库的大小：

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

这给出了一个想法，占用的空间应该在750GB. 然而实际上 PostgreSQL 的使用量几乎是原来的两倍：

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

问题的一部分是pgsql_tmp，那就是占领349GB。有没有一种安全的方法可以从中删除未使用的文件pgsql_tmp？

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

我已经尝试过VACUUM FULL并pg_repack在最大的桌子上没有任何成功。如何摆脱浪费的磁盘空间的唯一方法似乎是将表转储到 SQL 并重新导入到干净的服务器中。

postgresql postgresql-9.6

1 个回答

  1. Best Answer

    我更新了计算数据库大小的查询（包括索引）：

    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;

    至于 tmp 文件，我已经删除了所有超过 1 天的文件：

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

    至少在我们的设置中，查询通常需要几分钟，最多几个小时。因此，超过 1 天的临时文件可能是某个已崩溃的 postgresql 进程留下的。临时文件以进程的 PID 为后缀，例如postgresql 进程的 PID 应该在pgsql_tmp13774.1哪里。13774

    • 3

相关问题