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 VACUUM FULL?
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