PostgreSQL database size doesn't match used space on disk

Question

I have a PostgreSQL 9.6 database with high-volume traffic. I run periodically pg_repack reclaim unused space in tables/indexes. On larger tables repack sometimes fails to complete the process which results in using more disk space that PostgreSQL reports the DB is using.

I use following query to report size of each database:

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

this gives an idea that the occupied space should be around 750GB. However in reality PostgreSQL is using almost twice as much:

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

Part of the problem is pgsql_tmp, that is occupying 349GB. Is there a safe way how to remove unused files from pgsql_tmp?

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

I've already tried VACUUM FULL and pg_repack on largest tables without any success. The only way how to get rid of wasted disk space seems to be dumping tables to SQL and reimporting into a clean server.

Answer 1

I've updated query for computing database sizes (indexes included):

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;

As for the tmp files, I've deleted all files older than 1 day:

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

at least in our setup queries typically takes minutes, at most hours. So, temporary files older than one day were probably left by some postgresql process that had crashed. Temp files are suffixed with process's PID, like pgsql_tmp13774.1 where 13774 should be PID of postgresql process.