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

importpostgresqlubuntu-18.04

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.

Best Answer

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