2

Every night we are truncating most of the tables in the DB and inserting data from remote databases. The total size of data that we are inserting every night is about 300 GB.

The problem is that DB size is much bigger:

SELECT pg_size_pretty( pg_database_size('comb') );

 pg_size_pretty 
----------------
 943 GB
(1 row)

Also, there are a lot of old files (from 2020 for example) in data/base directory that not appears in pg_class:

cd data/base
ll 935829*

-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.1
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.2
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.3
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.4
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.5
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.6
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.7
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.8
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.9
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.10
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.11
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.12
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.13
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.14
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.15
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.16
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.17
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.18
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.19
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.20
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.21
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.22
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.23
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.24
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.25
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.26
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.27
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.28
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.29
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.30
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.31
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.32
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.33
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.34
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.35
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.36
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.37
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.38
-rw------- 1 postgres postgres 1073741824 Sep  4  2020 935829.39




  SELECT relname, relnamespace::regnamespace, relkind FROM pg_class WHERE relfilenode =  935829;
     relname | relnamespace | relkind 
    ---------+--------------+---------
    (0 rows)
    
    Time: 8.156 ms

We tried to execute VACUUM FULL to release unused space to OS. It finished successfuly, but no space was freed to OS.

Last time this happend we performed logical backup (pg_dump) and restore to shrink this DB.

This time we would like to undestand the reason of this issue.

Please advise.

Anna Rybachkova
  • 21
  • 1

1 Answers1

0

First, find out is the files belong to an existing relation (assuming that the relation is in the default tablespace):

SELECT pg_filenode_relation(0, 935829);

If the result is NULL, and the files are old, you can delete them.

They must have been left behind after a crash.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263