PSQL : ERROR: could not extend file No space left on device HINT: Check free disk space

Question

It's my first time working with a dedicated server of my own and i'm running into issue when trying to important a large ( 10gb , millions of rows ) postgresql backup file into a new table. Running ubuntu 18.04 LTS.

I've installed postgresql with apt-get then logged in as root and created a database.

I then ran

sudo -u postgres psql mytable < mybackupfile.bak in /root

appart from a missing role error it started running until i started to get errors such as :

ERROR: could not extend file "base/16384/16472.4": wrote only 4096 of 8192 bytes at block 635129 HINT: Check free disk space. CONTEXT: COPY stock_prices, line 28568936 ERROR: could not extend file "base/16384/16480": No space left on device HINT: Check free disk space. CONTEXT: COPY stocks, line 99 

it continued to run " normally " afterwards :

 ... setval --------- 1864218 (1 row) setval --------- 1356711 (1 row) setval -------- 478761 (1 row) ... 

until it was just a bunch of :

ERROR: could not create temporary file "base/pgsql_tmp/pgsql_tmp3458.0": No such file or directory ERROR: could not extend file "base/16384/16503": No space left on device 

my file system looks like this :

 Filesystem Size Used Avail Use% Mounted on udev 16G 0 16G 0% /dev tmpfs 3.2G 1000K 3.2G 1% /run /dev/md2 20G 12G 6.2G 67% / tmpfs 16G 8.0K 16G 1% /dev/shm tmpfs 5.0M 0 5.0M 0% /run/lock tmpfs 16G 0 16G 0% /sys/fs/cgroup /dev/md3 420G 9.3G 390G 3% /home /dev/md1 487M 146M 312M 32% /boot tmpfs 3.2G 0 3.2G 0% /run/user/0 

From reading a few related question some people say there is a problem in the partitions of the server here, other people say to increase the size of the root partition other that it might be only be an issue with the temp memory required during import and to change the psql config file.

I'm a bit lost, there is nothing on the server but the basic security configuration and PSQL + the backup file so if changes should be made in partition size it's the right time but i don't know what's happening in my case and don't want to mess everything up if that doesn't fix anything.

Answer 1

So i've followed this tutorial to move the datadir to /home/postgresql/ and it's working.

Answer 2

Try to increase the temp_tablespaces in postgresql.conf file reference is : https://www.postgresql.org/docs/10/runtime-config-client.html#GUC-TEMP-TABLESPACES. By default it is empty string.