PostgreSQL - Could not extend file No space left on device. HINT: Check free disk space

I've been inserting data into PostgreSQL DB, and got the error message:

psycopg2.OperationalError: could not extend file "base/16384/61892": No space left on device
HINT: Check free disk space

I am using CentOS 7, and I've checked some of my status on my linux machine:

df -h

I'm pretty new to Linux, and based on the result of my df -h command, I could see that Linux storage system works differently than Windows.

It looks like PostgreSQL is pushing to /dev/mapper/centos-root, but it has only 50 GB available.

Question 1: How can I change it? How can I assign the extra free space that's available in /dev/mapper/centos-home to my ~/centos-root directory?

Question 2: My machine has two Hard disk, 200 GB each. But according to my df-h command, it looked like I only have 200GB available. Why is this the case? How can I take advantage of full 400GB space?

It looks like the problem is that my /dev/mapper/centos-root is all filled up. I have inserted nearly 10,000,000 rows of data with 12 columns in PostgreSQL.

Question 3: Is this big enough to take up 50GB of space?

Thanks!

Edit: output of pvs, vgs, lvs

Solution 1:

Your server is badly partitioned. This is not entirely your fault; rather, the default RHEL/CentOS settings are quite misleading in my opinion.

Let's start from the description on the pvs; vgs; lvs command entered above. They are all related to LVM2, a Logical Volume Manager which, in short, enable you to create "flexible", resizeable partitions:

• pvs shows how the physical disks are attached to the LVM system. You had 2x 200 GB disks, but pvs only shows 1x200 GB physical device. This means your disks are in RAID1 (mirroring) and they are wholly assigned to LVM (ie: no physical space is unassigned);

• vgs shows the Volume Group assigned to your physical devices and any usable free space. In your case free space is <4 MB, so basically you have no space free space. The next command explains what is using your space...

• lvs shows you have two Logical Volumes (think them as partitions): a 50 GB root volume and a ~145 GB home volume. In short, your root volume (where PostgreSQL resides) is way smaller than your needs; rather, your home volume is very large considering how few data (< 5GB) it contains.

To solve the problem, you have two options:

• relocate PostgreSQL under the home volume, for example under /home/pgsl. I would avoid this because /home is not the right place for a database and it will cause an administrative burden in the future. Moreover, you need to put SELinux into permissive mode (setenforce 0 + /etc/selinux/config editing) to let the relocation work;

• shrink the home volume and enlarge root. This is easier said than done, especially if you are using XFS (default RHEL/CentOS filesystem), which does not support shrinking.

If and only if your data under home can be trashed / reloaded, you can follow the following steps to DELETE the home volume and enlarging root. Please note that a small error (both mine and your) WILL destroy your data (killing any cats in a ~100 KM range), and so I take NO RESPONSABILITY for the final outcome. If you are unsure, stop here and call a professional sysadmin for support. Ok, let's go ahead; I'll post a command sequence with the relative description:

• umount /home: this unmount (ie "disable") the home volume;
• lvremove centos/home: this permanently destroy /home and all its content;
• lvextend centos/root -L +100G: this expand the root volume by 100 GB;
• xfs_growfs /: assuming you are using XFS, this expand the root filesystem to match the underlying, extended volume. If you are using ext4 (which is improbable), you need to issue resize2fs /dev/centos/root.