PostgreSQL High Disk Usage – Causes and Solutions

dockerpostgresql

I have a PostgreSQL database on a docker container with 50MB of data:

SELECT pg_size_pretty(pg_database_size('db_1'));
 pg_size_pretty 
------------------
 50 MB

but the volume size is 8.5GB :

sudo du -hd1 db_volume
8.5G    db_volume

and my docker-compose is like this:

version: '3.8'

services:
  r3-db:
    image: postgres:12
    restart: always
    ports:
      - 5432:5432
    environment:
      POSTGRES_PASSWORD: PASSWORD
      POSTGRES_USER: USER
      POSTGRES_DB: db_1
    volumes:
      - ./db_volume/:/var/lib/postgresql/data:Z

I try using VACUUM(FULL, ANALYZE, VERBOSE) on any table but the file size of the database doesn't change!

The volume contains many files named core.NUMBER with size of 145MB,

enter image description here

How can I reduce disk usage of the database?

Best Answer

Those are core dumps which are generated when PostgreSQL crashes. You can use them with gdb to perhaps figure out why it is crashing. You can delete them. You can save one to use it to figure out why PostgreSQL keeps crashing, as that should absolutely not be happening. Also look in the PostgreSQL log file for info on why it is crashing.

Related Question