Thread
-
Even if I have a lot of free space PgSQL returns "ERROR: could not extend file - No space left on device"
Nature Conservation Geovista Space <conservation.by.geovista.space@gmail.com> — 2023-09-08T11:25:26Z
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 -
Re: Even if I have a lot of free space PgSQL returns "ERROR: could not extend file - No space left on device"
Adrian Klaver <adrian.klaver@aklaver.com> — 2023-09-08T15:27:45Z
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@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 > -- Adrian Klaver adrian.klaver@aklaver.com
-
Re: Even if I have a lot of free space PgSQL returns "ERROR: could not extend file - No space left on device"
Alban Hertroys <haramrae@gmail.com> — 2023-09-09T09:33:57Z
> On 8 Sep 2023, at 13:25, Nature Conservation Geovista Space <conservation.by.geovista.space@gmail.com> 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. So a lot of geometry going on then… That seems important in this case. > 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 > ); That _looks_ like a query that could blow through space rather quickly. How large is gaul_administrative in GB? In rows? How many subdivisions does this generate per row on average? How many bytes are those subdivisions on average? Multiply those numbers, and you get a fair indication of how much space that table requires. Does that fit in 1.1TB? Frankly, I don’t see the point of repeating the geom column in that table after dividing it up, aren’t you just wasting space there? The original is still available in the source table, after all. And what about the other columns that you clone into this new table? My suggestion would be to minimise that with an operation like the above. This would be one of those situations where I’d introduce a (bigint) surrogate key and use that to link the two tables together, even though I’m a natural key person. Something like this (I had to make a few assumptions about your source table): CREATE TABLE _gaul_administrative_subdivided100 AS ( id bigint NOT NULL, geomid bigserial NOT NULL, geom_subdivided100 geometry NOT NULL ); -- If you care about referential integrity here ALTER TABLE _gaul_administrative_subdivided100 ADD FOREIGN KEY (id) REFERENCES gaul_administrative (id) ON DELETE CASCADE ON UPDATE RESTRICT; INSERT INTO _gaul_administrative_subdivided100 (id, geom_subdivided100) SELECT id, st_subdivide(geom,100) FROM gaul_administrative; (…) > It seems that it is not a problem of space. I wouldn’t be too sure of that, but I don’t have the numbers. > 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 Is this after the error and after PG finished rolling back? What does this show while that query is going on? If gaul_administrative takes up a large part of those 164G, then you probably don’t have enough space for a 10x multiplication in size from the original table to the new table. And that happening looks entirely possible from the information you provided. Regards, Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.