I was trying to write a 55GB SQL file to my database. It used to crash each time. I removed logs and did some cleaning to get the server running, but the transaction never used to complete. Latter, I emptied all the tables I was writing into (I had a backup), but even after that, when I tried writing the SQL file, it gave me this error after a series of successful inserts - Could not write to file "pg_subtrans/14C6" at offset 106496: No space left on device.

Is there a way out of this? Are there logs related to PGSQL that I should still clean? I've read that moving the pg_xlog might just help? Should I try this?

Any help will be appreciated.


Output of df -h reformatted from comment for readability.

Filesystem                      Size  Used Avail Use% Mounted on
/dev/mapper/vg_zarkin-lv_root   574G  540G 4.3G  100% /
/dev/sda1                       194M  35M  150M  19%  /boot
tmpfs                           5.9G  928K 5.9G  1%   /dev/shm
/dev/mapper/smeidc0-smeisky0    7.2T  5.2T 2.0T  73%  /smeidc/sky0
/dev/md0                        7.2T  6.5T 400G  95%  /smeidc/backup
  • 1
    Have you tried breaking the import into more manageable pieces? – DopeGhoti Jul 5 '17 at 22:39
  • @DopeGhoti - Yeah, I divided into 12 files each of a size around 5GB. Should I make them smaller? – Pushpak Raj Gautam Jul 5 '17 at 22:40
  • 1
    Output of df -h would likely be helpful here... Also indicate where your PGSQL data files are if they are not in the typical default location of /var/lib/pgsql. – Deathgrip Jul 5 '17 at 22:53
  • One should also note that there is not a one-to-one correlation between the size of the SQL file and the amount of disk space that will be consumed by loading it. Depending on many factors, the space used in the database could be larger or smaller than the size of the SQL file. Sometimes this difference can be quite large. – Deathgrip Jul 6 '17 at 0:06
  • @Deathgrip- Hey, they are at the default location - /var/lib/pgsql. Can I remove the logs (pg_xlog) to get more space? Otherwise it gives me the No space left error. – Pushpak Raj Gautam Jul 6 '17 at 7:15

You should provide data volume sufficient for your application.

But naturally there are some things to know which can help shrink database size in PostgreSQL:

  • If you want to purge whole tables, be aware that SQL DELETE does not always clean the files. Use TRUNCATE, not DELETE to remove table contents completely.

  • If you canot purge the table completely, and you need to remove a large subset of rows (50% or more), it is very useful to run VACUUM FULL or CLUSTER command after DELETE, to shrink the table.

  • Amount of data reserved for transaction logs (pg_xlog directory) can be controlled with postgres server settings

  • Unused databases, tables and indexes can be detected with help of system views (pg_stat_database, pg_stat_user_tables, pg_stat_user_indexes)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.