Postgresql – Not find the table of the oid

disk-spacepostgresqlvacuum

I have a database running under PostgreSQL which normally has to have a size of 300 GB. Unfortunately it is now 3,5 TB in size.

I noticed that the directory of this database , the pgdata path is /base/45075466. There are about 3000 files in that directory each 1 GB in size.

  1. I launched a query to check on all the objects of the database, but I didn't detect any large objects: 
 SELECT *, pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS INDEX
    , pg_size_pretty(toast_bytes) AS toast
    , pg_size_pretty(table_bytes) AS TABLE
  FROM (
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
              , c.reltuples AS row_estimate
              , pg_total_relation_size(c.oid) AS total_bytes
              , pg_indexes_size(c.oid) AS index_bytes
              , pg_total_relation_size(reltoastrelid) AS toast_bytes
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE relkind = 'r'
  ) a
) a;
  1. I checked the size by schema, but didn't find any size irregularities:
    SELECT schemaname,
     sum(pg_total_relation_size(schemaname||'.'||tablename)) as size
     from pg_tables   group by schemaname
     order by size desc;
  1. With the OID of these files 21037129 and using oi2dname I did not find the name of the table:
-rw-------  1 999 docker  1,0G févr. 11 02:30 121037129.1045
-rw-------  1 999 docker  1,0G févr. 11 02:34 121037129.1046

I think it's orphaned files, because if I use another OID, the name of the table is detected.

I have only 350 GB space on my computer's drive and I think it's a risk to launch a VACUUM FULL (risk of having free space reduced to zero).

Before I launch the VACUUM FULL, is there any other method to resolve this problem? If I delete the database in question, is it possible that my orphaned files will be eliminated and what is the delay? Is there any other method to avoid having to launch VACUUM FULL?

Best Answer

Before launch the VACUUM FULL, is there a another method for the resolution of this problem?

VACUUM FULL does not solve that problem, in the sense that if the files you found inside the database directory do not corresponding to anything in the catalog, they will be ignored by VACUUM FULL and their space will not be reclaimed.

Such files are left out when a backend is stopped abrubtly (fast shutdown or crash) during the transaction that creates the objects, and the crash recovery sequence is not able to clean up. In that case, only manual deletion will reclaim that space.

Before removing the files you want to be absolutely sure of your query. Right now, it's based on relkind='r', and altough it gets back to indexes and toast tables indirectly, it seems to miss materialized views or sequences.

See How to reclaim space taken by an index that partially built and was terminated by a power outage for an approach that worked. That question was about an index, but it applies to other kind of objects.

Related Question