Hi, I started the full planet import, and after a week of work the job was ALMOST done, failed during building indexes after the import was probably done, but the harddrive run out of space.
1) Is there a way how I can just rebuild the indexes without reimporting the whole set? By running commands directly in PGSQL I guess? Where? How? (i know how to use command line sql tool, but not skilled in PGSQL, just MySQL)
2) I did the import with autovacuum=off. Is there any way I can run it now to save some additional space?
Here is the docker log.
sudo -u renderer osm2pgsql -d gis --create --slim -G --hstore --tag-transform-script /home/renderer/src/openstreetm ap-carto/openstreetmap-carto.lua --number-processes 8 -S /home/renderer/src/openstreetmap-carto/openstreetmap-carto.s tyle /data.osm.pbf -C 32000 --flat-nodes /nodes/flat_nodes.bin
2021-01-30 17:39:17 osm2pgsql version 1.4.0
2021-01-30 17:39:17 Database version: 12.5 (Ubuntu 12.5-1.pgdg18.04+1)
2021-01-30 17:39:17 PostGIS version: 3.0
2021-01-30 17:39:17 Node-cache: cache=32000MB, maxblocks=512000*65536, allocation method=11
2021-01-30 17:39:17 Setting up table 'planet_osm_point'
2021-01-30 17:39:17 Setting up table 'planet_osm_line'
2021-01-30 17:39:17 Setting up table 'planet_osm_polygon'
2021-01-30 17:39:17 Setting up table 'planet_osm_roads'
2021-02-07 22:53:59 Reading input files done in 710082s (197h 14m 42s). 4/s)
2021-02-07 22:53:59 Processed 6634698177 nodes in 5524s (1h 32m 4s) - 1201k/s
2021-02-07 22:53:59 Processed 733743019 ways in 303789s (84h 23m 9s) - 2k/s
2021-02-07 22:53:59 Processed 8593984 relations in 400769s (111h 19m 29s) - 21/s
2021-02-07 22:54:00 Clustering table 'planet_osm_polygon' by geometry...
2021-02-07 22:54:00 Clustering table 'planet_osm_point' by geometry...
2021-02-07 22:54:00 Clustering table 'planet_osm_line' by geometry...
2021-02-07 22:54:00 Clustering table 'planet_osm_roads' by geometry...
2021-02-07 22:54:01 Building index on table 'planet_osm_ways'
2021-02-07 22:54:01 Building index on table 'planet_osm_rels'
2021-02-08 05:14:21 node cache: stored: 3812666007(57.47%), storage efficiency: 90.90% (dense blocks: 472519, sparse nodes: 161717496), hit rate: 56.10%
2021-02-08 05:14:24 ERROR: Database error: ERROR: could not extend file "base/16385/5832519.8": No space left on device
HINT: Check free disk space.
Postgres log from within the docker:
2021-01-30 17:39:17.177 UTC [75] postgres@gis STATEMENT: CREATE EXTENSION hstore;
2021-02-08 01:45:40.776 UTC [161] renderer@gis ERROR: could not extend file "base/16385/5832515.2": No space left on device
2021-02-08 01:45:40.776 UTC [161] renderer@gis HINT: Check free disk space.
2021-02-08 01:45:40.776 UTC [161] renderer@gis STATEMENT: CREATE INDEX ON planet_osm_rels USING GIN (parts) WITH (fastupdate = off) ;
2021-02-08 01:45:40.776 UTC [127] renderer@gis ERROR: could not extend file "base/16385/5832517": No space left on device
2021-02-08 01:45:40.776 UTC [127] renderer@gis HINT: Check free disk space.
2021-02-08 01:45:40.776 UTC [127] renderer@gis STATEMENT: CREATE TABLE "planet_osm_roads_tmp" AS SELECT * FROM "planet_osm_roads" WHERE ST_IsValid(way) ORDER BY way
2021-02-08 01:45:40.776 UTC [124] renderer@gis ERROR: could not extend file "base/16385/5832519.8": No space left on device
2021-02-08 01:45:40.776 UTC [124] renderer@gis HINT: Check free disk space.
2021-02-08 01:45:40.776 UTC [124] renderer@gis STATEMENT: CREATE TABLE "planet_osm_point_tmp" AS SELECT * FROM "planet_osm_point" WHERE ST_IsValid(way) ORDER BY way
2021-02-08 01:45:40.776 UTC [125] renderer@gis ERROR: could not write to file "base/pgsql_tmp/pgsql_tmp125.10": No space left on device
2021-02-08 01:45:40.776 UTC [125] renderer@gis STATEMENT: CREATE TABLE "planet_osm_line_tmp" AS SELECT * FROM "planet_osm_line" WHERE ST_IsValid(way) ORDER BY way
2021-02-08 01:45:41.541 UTC [167] FATAL: terminating connection due to administrator command
2021-02-08 01:45:41.541 UTC [167] STATEMENT: CREATE TABLE "planet_osm_point_tmp" AS SELECT * FROM "planet_osm_point" WHERE ST_IsValid(way) ORDER BY way
2021-02-08 01:45:41.542 UTC [168] FATAL: terminating connection due to administrator command
2021-02-08 01:45:41.542 UTC [168] STATEMENT: CREATE TABLE "planet_osm_roads_tmp" AS SELECT * FROM "planet_osm_roads" WHERE ST_IsValid(way) ORDER BY way
2021-02-08 01:45:41.570 UTC [165] FATAL: terminating connection due to administrator command
2021-02-08 01:45:41.570 UTC [165] STATEMENT: CREATE TABLE "planet_osm_line_tmp" AS SELECT * FROM "planet_osm_line" WHERE ST_IsValid(way) ORDER BY way
2021-02-08 01:45:41.588 UTC [166] FATAL: terminating connection due to administrator command
2021-02-08 01:45:41.588 UTC [166] STATEMENT: CREATE TABLE "planet_osm_point_tmp" AS SELECT * FROM "planet_osm_point" WHERE ST_IsValid(way) ORDER BY way
2021-02-08 01:45:41.589 UTC [164] FATAL: terminating connection due to administrator command
2021-02-08 01:45:41.589 UTC [164] STATEMENT: CREATE TABLE "planet_osm_line_tmp" AS SELECT * FROM "planet_osm_line" WHERE ST_IsValid(way) ORDER BY way
2021-02-08 01:45:44.565 UTC [27] LOG: background worker "parallel worker" (PID 167) exited with exit code 1
2021-02-08 01:45:44.565 UTC [27] LOG: background worker "parallel worker" (PID 166) exited with exit code 1
2021-02-08 01:45:44.570 UTC [27] LOG: background worker "parallel worker" (PID 168) exited with exit code 1
2021-02-08 01:45:44.683 UTC [27] LOG: background worker "parallel worker" (PID 165) exited with exit code 1
2021-02-08 01:45:45.182 UTC [27] LOG: background worker "parallel worker" (PID 164) exited with exit code 1
2021-02-08 05:12:16.096 UTC [160] renderer@gis ERROR: could not extend file "base/16385/5832514.10": wrote only 4096 of 8192 bytes at block 1441264
2021-02-08 05:12:16.096 UTC [160] renderer@gis HINT: Check free disk space.
2021-02-08 05:12:16.096 UTC [160] renderer@gis STATEMENT: CREATE INDEX ON planet_osm_ways USING GIN (nodes) WITH (fastupdate = off) ;
2021-02-08 05:14:15.671 UTC [126] renderer@gis ERROR: could not write to file "base/pgsql_tmp/pgsql_tmp126.30": No space left on device
2021-02-08 05:14:15.671 UTC [126] renderer@gis STATEMENT: CREATE TABLE "planet_osm_polygon_tmp" AS SELECT * FROM "planet_osm_polygon" WHERE ST_IsValid(way) ORDER BY way
2021-02-08 05:14:15.672 UTC [163] FATAL: terminating connection due to administrator command
2021-02-08 05:14:15.672 UTC [163] STATEMENT: CREATE TABLE "planet_osm_polygon_tmp" AS SELECT * FROM "planet_osm_polygon" WHERE ST_IsValid(way) ORDER BY way
2021-02-08 05:14:15.699 UTC [162] ERROR: could not write to file "base/pgsql_tmp/pgsql_tmp162.29": No space left on device
2021-02-08 05:14:15.699 UTC [162] STATEMENT: CREATE TABLE "planet_osm_polygon_tmp" AS SELECT * FROM "planet_osm_polygon" WHERE ST_IsValid(way) ORDER BY way
2021-02-08 05:14:18.877 UTC [27] LOG: background worker "parallel worker" (PID 163) exited with exit code 1
2021-02-08 05:14:19.022 UTC [27] LOG: background worker "parallel worker" (PID 162) exited with exit code 1
Thank you! Radek
asked 08 Feb '21, 13:32

Palalet
31●1●1●5
accept rate: 0%
thanks, will run the indexes and see what happens :) I do not plan to update it way too often, I rather need some basic map data source for my other work, no need to be 100% up to date. Didnt expect this to grow up to some 600Gb during the work, and I do not want to loose one week of procesing :) This is too much new technologies for me, appreciate your help!
well, I am unable to finish the task :( I did most of the indexes, but when I run the
CREATE INDEX planet_osm_ways_nodes_idx ON public.planet_osm_ways USING gin (nodes) WITH (fastupdate=off);
command, I get into trouble. The table is 160Gb, and running the index command takes up another 280Gb of space, before I had to kill it just before I run out of space again. Is this normal, that building the index takes x times more space than the original table? I tried to have them on separate tablespaces, one for data, one for index, but no help, it just eats up all available space :(
Yes, this is normal. I suggest to do what I said above under "of course if you don't need..." - just drop the tables required for updates, and don't create the planet_osm_ways_nodes index at all. You'll be fine for rendering, and you can worry about loading new data later. Your disk is too small for running a continuously updated planet. (Unless, perhaps, you try the experimental osm2pgsql option also mentioned above, or maybe try a zfs compressed file system.)
thanks, I will do it as you say, I cannot keep up with this space consumption :) - the table is 160Gb, while the complete index (yes, I made it after all) is 321Gb, which is utter nonsense :( so it would need some 800Gb+ to do the whole import
btw, is there a documentation which table contain what, what is their purpose? Where can I get the information which tables do I need just for the render and for the import? Also, not sure what role plays the flatnodes file, you mentioned I can delete it, so I guess is is some "temporary table" that is created during the transformation from the OSM file to final Postgres tables?