1

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?

  • This shouldn't normally happen when running PostgreSQL. What OS are you using? What filesystem? What version of PostgreSQL? What were the circumstances that led to this problem? Can you give us the output of the queries you ran, as well as SELECT n.nspname AS tableschema, c.relname AS tablename FROM pg_class c INNER JOIN pg_namespace n on (c.relnamespace = n.oid) WHERE c.relfilenode = 121037129;? – Kassandry Feb 15 '18 at 5:48
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.