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.
- 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;
- 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;
- With the OID of these files 21037129 and using
oi2dnameI 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