CODEWITHSUNDEEP
linuxpostgresqlubuntu
Marcin Zdunek
Marcin Zdunek

Reputation: 53

PostgreSQL 12 - large cache files in "base" directory

I have noticed that my small 8MB database is cached by PostgreSQL 12 in this folder:

/var/lib/postgresql/12/main/base/16384

This folder contains multiple 1GB files for example "16417" that weighs 1073741824 bytes.

How to remove these files and limit cache file space to max 100 GB? Now it uses as much space as it can and crashes my disk (no space left).

In postgresql.conf file i have changed these options:

temp_file_limit = 10000000 
shared_buffers = 128MB 
checkpoint_timeout = 12h
max_wal_size = 100MB
min_wal_size = 80MB

but unfortunately it did not help.

What else can I do to resolve this issue? In one our these files grew up to 80 GB...

EDIT: This issue occurs even with default settings. My system is Ubuntu 18.04.4.

Upvotes: 3

Views: 11487

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246383

This is not a cache, these are the actual tables and indexes. If you mess with these files, you will break your database and lose data.

Figure out what database 16384 is:

SELECT datname FROM pg_database WHERE oid = 16384;

Then connect to that database and figure out what 16417 is:

SELECT relname, relnamespace::regnamespace, relkind
FROM pg_class WHERE relfilenode = 16417;

If the size of that object is bigger than it should be, perhaps you have a bloated table or index, and VACUUM (FULL) tab can make it smaller (but don't forget that the table is inaccessible while it is rewritten!).

Again, make sure you don't manipulate any of those files yourself.

Upvotes: 8

Related Questions