msg: Cannot execute SQL 'COPY "my_table" FROM '/path/big_file.csv': ERROR: Could not extend file "base/A/B": only C bytes of D written in block E

Question

I am running an ansible playbook. The machine running ansible is localhost.

The playbook breaks during a task which loads data from a big csv (1.2 GB) into a postgres database table ( module community.postgresql.postgresql_copy ).

The task (running on localhost) breaking the execution is:

- name: Load data from CSV into table
  community.postgresql.postgresql_copy:
    login_host: '{{ db_host }}'
    login_user: '{{ db_username }}'
    login_password: '{{ db_password }}'
    db: '{{ db_database }}'
    port: '{{ db_database_port }}'

    copy_from: "{{ path }}/my_big_csv_file.csv"
    dst: "{{ my_table }}"

    options:
      format: csv
      delimiter: ';'
      header: yes

The error raised was:

msg: Cannot execute SQL 'COPY "my_table" FROM '/path/my_big_csv_file.csv' (format csv, delimiter ';', header True)': ERROR: Could not extend file "base/16385/45444.1": only 4096 bytes of 8192 written in block 165767

HINT: Check free disk space.

CONTEXT: COPY my_table, line 9615264

I have did some research I have read in this thread that this error shows up when there is lack of space on the disk.

However, I have run

 df -h --output=avail .

on localhost, in the folder where the source csv is located, and got 84G as output.

So what could be the problem?

Answer 1

You are running out of disk space on the database server, not the client. Your answer suggests that you figured that out. I wonder how that could happen, since COPY only writes data to a table, and there is no sorting or other memory-intense operations involved. But the diagnosis from your answer is correct.

However, creating a symbolic link for pgsql_tmp is not the proper solution. It will work, but in general, you should not manually mess with the data directory.

This is the recommended solution:

• create a directory on the database server on a file system with enough space:

  mkdir /home/myuser/tmpspace
  chown postgres:postgres /home/myuser/tmpspace
  

• define a tablespace:

  CREATE TABLESPACE tmpspace LOCATION '/home/myuser/tmpspace';
  

• set the configuration parameter temp_tablespaces to tmpspace

The parameter can be reset and the tablespace dropped when you are done.

Answer 2

I opened the postgresql logs to see in detail what went wrong at the moment when the query failed (I could track the time from the ansible playbook logs)

less /var/log/postgresql/postgresql-10-main.log.1

and I found this error

2023-07-04 02:37:30.175 CEST [26940] ERROR: Writing to file "base/pgsql_tmp/pgsql_tmp26940.121" failed: No space left on device

I did some research and turns out that when postgres has to run "operations such as sorting more data than can fit in memory", it stores temporary data in the default directory PGDATA/base/pgsql_tmp

So I opened my postgres configuration file to check out where this directory is located (i.e. what is the value of PGDATA)

less  /etc/postgresql/10/main/postgresql.conf

and I found this

#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------

# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.

data_directory = '/var/lib/postgresql/10/main' 

So I went to that directory to see what is inside

sudo -i -u postgres
cd /var/lib/postgresql/10/main
ls -al

and there I see the base directory indicated in the error message. So I get inside there to explore it

cd base
ls -al

and I see there is this pgsql_tmp also indicated in the error message in the postgres log.

So I go inside there and run

 postgres@mymachine:~/10/main/base/pgsql_tmp$ df -h --output=avail .

Avail
 5.8G

So the amount of free space in postgres temporary data directory is much smaller than in my user's directory.

The csv I am woking on is 1.2 GB large, so still smaller than the available space, but considering that postgres is also used by other processes, I want more free space for postgres temporary data directory.

So what I do is to create a softlink from the postgres temporary data folder to a folder in my user's space (as suggested here).

In another terminal (let's say t2), in my user directory:

cd /home/myuser/
mkdir pgsql_tmp

Then, in the previous terminal (let's say t1):

postgres@mymachine:~/10/main/base$ ln -s /home/myuser/pgsql_tmp 

postgres@umymachine:~/10/main/base$ ls -al
total 48
drwx------  6 postgres postgres  4096 Jul  6 18:02 .
drwx------ 19 postgres postgres  4096 Jul  6 16:22 ..
drwx------  2 postgres postgres 12288 Jul  6 16:22 1
drwx------  2 postgres postgres  4096 Oct  9  2020 13014
drwx------  2 postgres postgres 12288 Jun 29  2022 13015
drwx------  2 postgres postgres 12288 Jul  6 16:23 16385
lrwxrwxrwx  1 postgres postgres    20 Jul  6 18:02 pgsql_tmp -> /home/myuser/pgsql_tmp

Then back in t2:

chown -R postgres /home/orka/pgsql_tmp
chmod o+x /home/myuser/pgsql_tmp

restarted postgres

sudo service postgresql restart

and then it works.