NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum

Complete import job after disk out of space - indexes

1

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's gravatar image

Palalet
31●1●1●5
accept rate: 0%

One Answer:
active answersoldest answersnewest answerspopular answers
2

A typical osm2pgsql 1.4 run would create these indexes:

CREATE INDEX planet_osm_line_osm_id_idx ON public.planet_osm_line USING btree (osm_id); CREATE INDEX planet_osm_line_way_idx ON public.planet_osm_line USING gist (way); CREATE INDEX planet_osm_point_osm_id_idx ON public.planet_osm_point USING btree (osm_id); CREATE INDEX planet_osm_point_way_idx ON public.planet_osm_point USING gist (way); CREATE INDEX planet_osm_polygon_osm_id_idx ON public.planet_osm_polygon USING btree (osm_id); CREATE INDEX planet_osm_polygon_way_idx ON public.planet_osm_polygon USING gist (way); CREATE INDEX planet_osm_rels_parts_idx ON public.planet_osm_rels USING gin (parts) WITH (fastupdate=off); CREATE INDEX planet_osm_roads_osm_id_idx ON public.planet_osm_roads USING btree (osm_id); CREATE INDEX planet_osm_roads_way_idx ON public.planet_osm_roads USING gist (way); CREATE INDEX planet_osm_ways_nodes_idx ON public.planet_osm_ways USING gin (nodes) WITH (fastupdate=off);

You can probably just run these commands and they will simply fail where the index already exists. After that your database should be good to go, but remember that many map styles will require the creation of additional indexes for performance.

You can switch on autovacuum before running the above commands if you want. It is unlikely to save space though. If you have a world-wide database, the planet_osm_ways_nodes index can easily use 200 GB of space. osm2pgsql 1.4 has a somewhat experimental feature where it allows you to reduce the size of that index at the cost of a little performance degradation during data updates (see option --middle-way-node-index-id-shift). Note that your database is likely going to grow by 30% within a few weeks if you run continuous updates due to database and index bloat, so be sure to have enough room.

Of course if you don't need data updates at all then you can save a lot of space by dropping the tables planet_osm_ways and planet_osm_rels and omitting their indexes from the list, and you can also omit the four "btree(osm_id)" indexes on the other tables and delete the flatnodes file.

permanent link

answered 08 Feb '21, 13:45

Frederik%20Ramm's gravatar image

Frederik Ramm ♦
82.5k●92●720●1273
accept rate: 23%

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!

(08 Feb '21, 14:12) Palalet

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 :(

(13 Feb '21, 20:20) Palalet

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.)

(14 Feb '21, 11:41) Frederik Ramm ♦

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

(15 Feb '21, 06:47) Palalet

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?

(15 Feb '21, 07:57) Palalet

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

learn more about Markdown

Question tags:

×263

question asked: 08 Feb '21, 13:32

question was seen: 1,702 times

last updated: 15 Feb '21, 07:58

Related questions

How long should minutely and hourly diff take with 8GB memory?

Is there a reason why osm2pgsql produces negative osm_id values?

How to configure postgresql for mapnik hourly updates?

Missing tables during osm2pgsql import

Merging 2 countries with osm2pgsql

How much RAM does osm2pgsql need?

Why do osm2pgsql and Mapnik use spherical projection 900913?

osm databases

osm2pgsql import getting killed as relations are processed

How do you make the osm2pgsql diff imports run faster than molasses on postgres 8.4?

NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum