Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qeZa6-0079ON-ST for pgsql-novice@arkaria.postgresql.org; Fri, 08 Sep 2023 11:23:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qeZa4-00A4pH-Qh for pgsql-novice@arkaria.postgresql.org; Fri, 08 Sep 2023 11:23:20 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qeZa4-00A4ot-DE for pgsql-novice@lists.postgresql.org; Fri, 08 Sep 2023 11:23:20 +0000 Received: from mail-lj1-x22c.google.com ([2a00:1450:4864:20::22c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qeZZw-003QOZ-VV for pgsql-novice@lists.postgresql.org; Fri, 08 Sep 2023 11:23:18 +0000 Received: by mail-lj1-x22c.google.com with SMTP id 38308e7fff4ca-2b703a0453fso32897951fa.3 for ; Fri, 08 Sep 2023 04:23:12 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1694172191; x=1694776991; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=WJ3pxH4hykkX+z5trlu0fsCIh3/+1XnbTpVJLJu55Ns=; b=KFhbVfLD8z+ZTj+B7QhlCr5VoNZwB30gBCSj5Cj0xbT81GG+AGJTJE8BMAzFo7Z63t cX1RIcyVWIZ8seoLwANTGB5yCJEUNattrJLTvgcJgV2mGqUW5M47HuhSLC2aVUfoUSIR uMB9Ay93DM8GXOGRlSl18arN/9073TTCMbNhL0Ioiv84QHCQmaQ9M0EKe602VUmXiPFF QD0saWnvGu3Yj1v9OnXiIdK31qnvtgJVK9JKJw5OTfwKJtw46sMQk70nYG5OHfHAfaYx mbxuQC6IXto9kLgQDC1YF3vbvSxA2J/wD9E+c2PV5xGROOzL+WvB6f9HSM5BTC2AJPWw yIpQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1694172191; x=1694776991; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=WJ3pxH4hykkX+z5trlu0fsCIh3/+1XnbTpVJLJu55Ns=; b=ZDbrwzhbYStU3CUXrru2mXMvW57Qqynp/S81+VInBbaHOt7sCmHZYwpHSpbDLhHWgz RaEZlBYFGqtU/G+e8/toMeRJojRMw77zffuevsIRyfYdkpiNUtP12jfqBMfAHV9nBFcf XwXkGSYuVJA/XOJYYTbBh831t5juM/vvjKkwPtPHIqvfhbLN0K3RsPv8uTIy2hMQOU7N lUPLruKyP3x/RRSIWzH7X0/la35JWFtQun5uAw7jeUDNN4UM+UIMijkYHIPqezQp+VOb Z7q/C78i+gREtxHvN8vqXwJcHQFBl7ZTO1zZ3vhsNRcj+nccEilrydq9Nue1a8LBgiNf lNdQ== X-Gm-Message-State: AOJu0YyMWINiDVJ4s8hqXsM6GkuMne+x0XH5hRbaLwk+6wDGGKYKX8l8 60mxPK5pUs/N1ebn0FthAx29dvq7Cu5ZzeJLNbyhTBIybDpe/A== X-Google-Smtp-Source: AGHT+IGpwvVYaoNqncBcUI8Wt0/sUUdo7G1lbKEVUSSs3owOhJNcMwtCOpal411Y4X35R6lTWk4Lzywj9JmqscsxsHA= X-Received: by 2002:a05:6512:3a87:b0:4fb:8bab:48b6 with SMTP id q7-20020a0565123a8700b004fb8bab48b6mr2105132lfu.52.1694172190807; Fri, 08 Sep 2023 04:23:10 -0700 (PDT) MIME-Version: 1.0 From: Nature Conservation Geovista Space Date: Fri, 8 Sep 2023 12:22:58 +0100 Message-ID: Subject: Even if I have a lot of free space PgSQL returns "ERROR: could not extend file - No space left on device" To: pgsql-novice@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000009f1b680604d73306" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000009f1b680604d73306 Content-Type: text/plain; charset="UTF-8" Dear Pg-users, I am coming back to Postgres/PostGIS after a few years. I am dealing with a big database with a lot of geometries and too many vertices. After hours running a query to Subdivide, I get this Postgres error *2023-09-08 02:11:23.745 BST [328594] postgres@database ERROR: could not extend file "base/16388/7985375.1020": No space left on device 2023-09-08 02:11:23.745 BST [328594] postgres@database HINT: Check free disk space.* 2023-09-08 02:11:23.745 BST [328594] postgres@database STATEMENT: CREATE TABLE _gaul_administrative_subdivided100 AS ( SELECT *, st_subdivide(geom,100) AS geom_subdivided100 FROM gaul_administrative ); *2023-09-08 02:15:38.251 BST [313729] LOG: checkpoint complete: wrote 81956 buffers (1.6%); 0 WAL file(s) added, 0 removed, 608 recycled; write=269.414 s, sync=0.001 s, total=269.634 s; sync files=1, longest=0.001 s, average=0.001 s; distance=9962549 kB, estimate=9980351 kB; lsn=291/BF46ABE8, redo lsn=291/A0FB7D98* It seems that it is not a problem of space. Command *df -h* returns: Filesystem Size Used Avail Use% Mounted on tmpfs 6.3G 1.1M 6.3G 1% /run /dev/sda 1.3T 164G 1.1T 14% / tmpfs 32G 3.2M 32G 1% /dev/shm tmpfs 5.0M 0 5.0M 0% /run/lock tmpfs 6.3G 4.0K 6.3G 1% /run/user/1000 Command *df -ih* returns: Filesystem Inodes IUsed IFree IUse% Mounted on tmpfs 7.9M 724 7.9M 1% /run /dev/sda 80M 179K 80M 1% / tmpfs 7.9M 4 7.9M 1% /dev/shm tmpfs 7.9M 3 7.9M 1% /run/lock tmpfs 1.6M 28 1.6M 1% /run/user/1000 I suppose it is an issue with temporary table, here my present configuration in *postgresql.conf* #temp_tablespaces = '' # a list of tablespace names, '' uses # only default tablespace #temp_file_limit = -1 # limits per-process temp file space # in kilobytes, or -1 for no limit What do you suggest? cheers, Enzopolo --0000000000009f1b680604d73306 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Dear Pg-users,

I am coming back to Postgres/PostGIS after a few years. I am dealing=20 with a big database with a lot of geometries and too many vertices.

After hours running a query to Subdivide, I get this Postgres error

2023-09-08 02:11:23.745 BST=20 [328594] postgres@database ERROR:=C2=A0 could not extend file=20 "base/16388/7985375.1020": No space left on device
2023-09-08 02:11:23.745 BST [328594] postgres@database HINT:=C2=A0 Check fr= ee disk space.

2023-09-08 02:11:23.745 BST [32859= 4] postgres@database=C2=A0 STATEMENT:=C2=A0 CREATE TABLE _gaul_administrati= ve_subdivided100 AS (
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 SELECT *, st_subdivide(geom,100) AS geom_subdivided100
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 FROM gaul_a= dministrative
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 );

2023-09-08 02:15:38.251 BST=20 [313729] LOG:=C2=A0 checkpoint complete: wrote 81956 buffers (1.6%); 0 WAL= =20 file(s) added, 0 removed, 608 recycled; write=3D269.414 s, sync=3D0.001 s,= =20 total=3D269.634 s; sync files=3D1, longest=3D0.001 s, average=3D0.001 s;=20 distance=3D9962549 kB, estimate=3D9980351 kB; lsn=3D291/BF46ABE8, redo=20 lsn=3D291/A0FB7D98

It seems that it is not a problem of space.

Command df -h returns:

Filesystem=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 Size=C2=A0 Used Avail Use% Mounted on
tmpfs=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 6.3G=C2= =A0 1.1M=C2=A0 6.3G=C2=A0=C2=A0 1% /run
/dev/sda=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 1.3T=C2=A0 164G=C2=A0 1.= 1T=C2=A0 14% /
tmpfs=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 32G= =C2=A0 3.2M=C2=A0=C2=A0 32G=C2=A0=C2=A0 1% /dev/shm
tmpfs=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 5.0M=C2= =A0=C2=A0=C2=A0=C2=A0 0=C2=A0 5.0M=C2=A0=C2=A0 0% /run/lock
tmpfs=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 6.3G=C2= =A0 4.0K=C2=A0 6.3G=C2=A0=C2=A0 1% /run/user/1000

Command df -ih returns:

Filesystem     Inodes IUsed IFree IUse=
% Mounted on                                                               =
                 
tmpfs           =
 7.9M   724  7.9M    1% /run                                               =
                                       
/dev/sda          80M  179K   80M    1% /                            =
                                                             
<=
div>tmpfs            7.9M     4  7.9M    1% /dev/sh=
m                                                                          =
        
tmpfs            7.9M    =
 3  7.9M    1% /run/lock                                                   =
                              
tmp=
fs            1.6M    28  1.6M    1% /run/user/1000

I suppose it is an issue with temporary table, here my present configura= tion in postgresql.conf 

#temp_tablespaces =3D ''        =
          # a list of tablespace names, '' uses                    =
                                 
=
                                        # only default tablespace   =


#temp_file_limit =3D -1                 =
  # limits per-process te=
mp file space                                                      <=
/font>
                                  =
      # in kilobytes, or -1 for no limit

What do you suggest?

cheers,

Enzopolo

--0000000000009f1b680604d73306--