22

I am getting space issue while running a batch process on PostgreSQL database.

However, df -h command shows that machine has enough space

enter image description here

below is the exact error 

org.springframework.dao.DataAccessResourceFailureException: PreparedStatementCallback; SQL [INSERT into BATCH_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION) values (?, ?, ?, ?)]; ERROR: could not extend file "base/16388/16452": No space left on device
  Hint: Check free disk space.

What is causing this issue?

EDIT

postgres data directory is /var/opt/rh/rh-postgresql96/lib/pgsql/data

df -h /var/opt/rh/rh-postgresql96/lib/pgsql/data
Filesystem      Size  Used Avail Use% Mounted on
/dev/xvda2      100G   63G   38G  63% /
YogeshR
  • 1,606
  • 2
  • 22
  • 43
  • 2
    Where is your data directory located? (`show data_directory;` in psql will tell you) –  Jun 13 '18 at 09:56
  • data directory is /var/opt/rh/rh-postgresql96/lib/pgsql/data. data directory's current size is 15GB – YogeshR Jun 13 '18 at 10:01
  • You should include _that_ directory in the output of `df -h` (btw: you can copy & paste **text** from the console as well. No need to use images) –  Jun 13 '18 at 10:03
  • @a_horse_with_no_name : edited post and added data directory's space – YogeshR Jun 13 '18 at 10:09
  • And, since you are using an ORM framework, the temptable-explosion is probably the result of some Carthesian product, caused by a logical error in your query. – wildplasser Jun 13 '18 at 13:01

1 Answers1

26

Most likely there are some queries that create large temporary files which fill up your hard disk temporarily. These files will be deleted as soon as the query is done (or has failed), so the file system has enough free space when you look.

Set log_temp_files = 10240 in postgresql.conf (and reload) to log all temporary files exceeding 10 MB, then you can check the log file to see if this is indeed the reason.

Try to identify the bad queries and fix them.

If temporary files are not the problem, maybe temporary tables are. They are dropped automatically when the database session ends. Does your application use temporary tables?

Another possibility might be files created by something else than the database.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • will log_temp_files works if log_statement has been set to none? currently, I am logging errors only. – YogeshR Jun 13 '18 at 11:52
  • Yes, that setting is independent from `log_statement`. – Laurenz Albe Jun 13 '18 at 12:04
  • I set log_temp_files to 10240 but it didn't work. Then I set it to 0 and detected 3-4 MBs of temp files. 5-6 files are getting generated every second. – YogeshR Jun 13 '18 at 18:22
  • Hmm. That doesn't explain your out of space condition (but it might be a good idea to raise `work_mem` if your memory situation allows it). Perhaps temporary tables? I'll update the answer. – Laurenz Albe Jun 14 '18 at 05:45
  • 5
    @LaurenzAlbe I'm using a dockerised instance of `postgres` how could this work in my case? – arilwan Jun 25 '19 at 16:48
  • @arilwan You'll need to use a volume or host map for the postgresql.conf file so that you can edit it. (Hint: you should be doing that anyway.) Alternatively, build a new image with an edited conf. – duct_tape_coder Aug 23 '22 at 20:34
  • 1
    @arilwan Weirdly enough, `docker system prune` fixed the issue for me. – Alex Nault Nov 07 '22 at 19:21