2

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.

6
  • Check where you have data from postgress and resize (grow) this partition. Other way is to move the postgress data directory to other partition.
    – mariaczi
    Jul 2, 2019 at 7:05
  • Who created this partition scheme? It's probably unnecessary and most certainly a problem to have a separate /home partition. Now you can't use that space for what you actually need it for. Jul 2, 2019 at 7:07
  • It's the " default " when i've chosen ubuntu on my server provider interface.. i'm didn't realize that could be an issue and wasn't really offered the choice
    – jeydan12
    Jul 2, 2019 at 7:11
  • Can you expend on the /home partition issue ? I'm thinking on moving psql data dir to /home/psql/data . what would be " wrong " with this ?
    – jeydan12
    Jul 2, 2019 at 10:17
  • Had the same problem with default partitioning in CentOS 7, I don't know why would anyone want to have 20GB root partition and the rest of the disk space allocated to home partition.. The defaults are really strange. Anyways, you should follow the suggestions in the answer for the question that you linked yourself - there are basically 2 options, either shrink the home partition and extend the root partition, or move the PostgreSQL data directory to the home partition. Jul 2, 2019 at 10:20

2 Answers 2

0

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

-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.

2
  • This is certainly not going to help. Based on the error (no space left on device) the issue clearly is in lack of free space on the partition where PostgreSQL data directory is located. Jul 2, 2019 at 10:08
  • "could not create temporary file" this is why I given above solution. Temp file/dir need enough temp_tablespaces.
    – asktyagi
    Jul 2, 2019 at 10:25

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.