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.

jeydan12
  • 21
  • 1
  • 1
  • 2
  • 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 02 '19 at 07: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. – Michael Hampton Jul 02 '19 at 07: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 02 '19 at 07: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 02 '19 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](https://serverfault.com/questions/933401/postgresql-could-not-extend-file-no-space-left-on-device-hint-check-free-dis) - 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. – Jiri Valenta Jul 02 '19 at 10:20
  • Conservative sysadmins will probably tell you that you should not have the database data dir on a /home partition, but technically there is no problem with it and it will work. – Jiri Valenta Jul 02 '19 at 10:27

2 Answers2

0

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

jeydan12
  • 21
  • 1
  • 1
  • 2
-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.

asktyagi
  • 2,401
  • 1
  • 5
  • 19
  • 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. – Jiri Valenta Jul 02 '19 at 10:08
  • "could not create temporary file" this is why I given above solution. Temp file/dir need enough temp_tablespaces. – asktyagi Jul 02 '19 at 10:25