From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Nature Conservation Geovista Space <conservation(dot)by(dot)geovista(dot)space(at)gmail(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Even if I have a lot of free space PgSQL returns "ERROR: could not extend file - No space left on device" |
Date: | 2023-09-08 15:27:45 |
Message-ID: | cfb8f743-f0c8-ad33-5606-f8da6b3f055e@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 9/8/23 04:25, Nature Conservation Geovista Space wrote:
> 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.
Postgres version?
PostGIS version?
OS and version?
Is this a physical machine or a virtual one?
Hosted or local?
'Hardware' specifications?
>
> After hours running a query to Subdivide, I get this Postgres error
How does CREATE TABLE enter into the query?
What is the query?
>
> *2023-09-08 02:11:23.745 BST [328594] postgres(at)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(at)database HINT: Check
> free disk space.*
>
> 2023-09-08 02:11:23.745 BST [328594] postgres(at)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
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2023-09-08 15:40:01 | Re: pg_restore unexpected end of file |
Previous Message | Tom Lane | 2023-09-08 13:53:33 | Re: ERROR: stack depth limit exceeded |