Question: could not extend file "base/361191441/368615606.137": No space left on device

[Klaus-Tockloth]

On an HDD-based Ubuntu system, I fully imported the planet file monthly. However, the last import failed due to lack of disk space.

2023-10-15 20:58:04  ERROR: Database error: ERROR:  could not extend file "base/361191441/368615606.137": No space left on device

Questions:

• Who creates the file (PostgreSQL or osm2pgsql)?
• Where is the file located/created?
• What could I do about the problem?

Additional question:

• Is it possible to run only the "post processing" step?

Import command:

nohup ./osm2pgsql-1.9.2 --username postgres --multi-geometry --hstore --slim --drop --flat-nodes nodes.bin --create --cache 24000 --number-processes 10 --database osmplanet --style openstreetmap-carto.style --tag-transform-script openstreetmap-carto.lua --input-reader=pbf planet-latest.osm.pbf >load_osmplanet.out 2>&1 &

osm2pgsql output:

$ cat load_osmplanet.out
nohup: ignoring input
2023-10-13 08:46:07  osm2pgsql version 1.9.2 (1.9.2)
2023-10-13 08:46:07  Database version: 12.16 (Ubuntu 12.16-0ubuntu0.20.04.1)
2023-10-13 08:46:07  PostGIS version: 3.0
2023-10-13 08:46:07  Storing properties to table '"public"."osm2pgsql_properties"'.
2023-10-13 08:46:07  Setting up table 'planet_osm_point'
2023-10-13 08:46:07  Setting up table 'planet_osm_line'
2023-10-13 08:46:08  Setting up table 'planet_osm_polygon'
2023-10-13 08:46:08  Setting up table 'planet_osm_roads'
2023-10-15 15:43:05  Reading input files done in 197817s (54h 56m 57s).                   
2023-10-15 15:43:05    Processed 8658483163 nodes in 3760s (1h 2m 40s) - 2303k/s
2023-10-15 15:43:05    Processed 971692934 ways in 30307s (8h 25m 7s) - 32k/s
2023-10-15 15:43:05    Processed 11340554 relations in 163750s (45h 29m 10s) - 69/s
2023-10-15 15:43:40  Dropping table 'planet_osm_nodes'
2023-10-15 15:43:41  Table 'planet_osm_nodes' dropped in 0s
2023-10-15 15:43:41  Dropping table 'planet_osm_ways'
2023-10-15 15:44:48  Table 'planet_osm_ways' dropped in 67s (1m 7s)
2023-10-15 15:44:48  Dropping table 'planet_osm_rels'
2023-10-15 15:44:50  Table 'planet_osm_rels' dropped in 2s
2023-10-15 15:44:50  Done postprocessing on table 'planet_osm_nodes' in 0s
2023-10-15 15:44:50  Done postprocessing on table 'planet_osm_ways' in 0s
2023-10-15 15:44:50  Done postprocessing on table 'planet_osm_rels' in 0s
2023-10-15 15:44:51  Clustering table 'planet_osm_roads' by geometry...
2023-10-15 15:44:51  Clustering table 'planet_osm_polygon' by geometry...
2023-10-15 15:44:51  Clustering table 'planet_osm_line' by geometry...
2023-10-15 15:44:51  Clustering table 'planet_osm_point' by geometry...
2023-10-15 16:16:22  Creating geometry index on table 'planet_osm_roads'...
2023-10-15 16:34:10  Analyzing table 'planet_osm_roads'...
2023-10-15 16:34:14  Creating geometry index on table 'planet_osm_point'...
2023-10-15 18:01:46  Analyzing table 'planet_osm_point'...
2023-10-15 18:09:03  All postprocessing on table 'planet_osm_point' done in 8653s (2h 24m 13s).
2023-10-15 20:58:04  All postprocessing on table 'planet_osm_roads' done in 3004s (50m 4s).
2023-10-15 20:58:04  ERROR: Database error: ERROR:  could not extend file "base/361191441/368615606.137": No space left on device
HINT:  Check free disk space.

HDD space:

$ df -h
Filesystem      Size  Used Avail Use% Mounted on
udev             63G     0   63G   0% /dev
tmpfs            13G  1.2M   13G   1% /run
/dev/md2        2.0T  1.4T  471G  76% /                    <- database
tmpfs            63G   16K   63G   1% /dev/shm
tmpfs           5.0M     0  5.0M   0% /run/lock
tmpfs            63G     0   63G   0% /sys/fs/cgroup
/dev/loop1       41M   41M     0 100% /snap/snapd/20092
/dev/loop0       62M   62M     0 100% /snap/zig/8241
/dev/loop3      128K  128K     0 100% /snap/bare/5
/dev/loop2       41M   41M     0 100% /snap/snapd/20290
/dev/md3        5.3T  727G  4.3T  15% /home                <- osm2pgsql
/dev/md1        485M  161M  299M  35% /boot
tmpfs            13G     0   13G   0% /run/user/1000
tmpfs            13G     0   13G   0% /run/user/1004

[joto]

Who creates the file (PostgreSQL or osm2pgsql)?

PostgreSQL

What could I do about the problem?

Buy a bigger hard disk. Or run without --slim but I assume you are using that because you don't have enough RAM (64GB at least, 128GB is better). You can also try to run with --disable-parallel-indexing, this might or might not help.

Is it possible to run only the "post processing" step?

https://osm2pgsql.org/doc/faq.html#can-osm2pgsql-resume-after-a-failed-import

[Klaus-Tockloth]

Update 1:

The server used has 128728.9 MiB of RAM. The import without the options '--slim, --drop, --cache' was not successful. All the free disk space was used up by the (parallel) indexing operations:

• Estimated size of database: about 510 GB
• Free and available disk space: 705 GB

...
2023-10-18 18:09:27  Analyzing table 'planet_osm_roads'...
2023-10-18 18:37:32  Analyzing table 'planet_osm_point'...
2023-10-18 18:48:51  All postprocessing on table 'planet_osm_point' done in 8625s (2h 23m 45s).
2023-10-18 20:16:14  All postprocessing on table 'planet_osm_roads' done in 6266s (1h 44m 26s).
2023-10-18 20:16:14  ERROR: Database error: ERROR:  could not extend file "base/374190716/380929184.70": No space left on device
...

$ df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/md2        2.0T  1.2T  705G  63% /               <- Postgres
/dev/md3        5.3T  797G  4.3T  16% /home           <- osm2pgsql
...

However, omitting the '--slim, --drop, --cache' options significantly sped up the 'pure' import of raw data (6h 25m 8s versus 55h 47m 4s):

With '--slim, --drop, --cache':

...
2023-10-12 04:23:58  Reading input files done in 200824s (55h 47m 4s).                    
2023-10-12 04:23:58    Processed 8658483163 nodes in 3559s (59m 19s) - 2433k/s
2023-10-12 04:23:58    Processed 971692934 ways in 30443s (8h 27m 23s) - 32k/s
2023-10-12 04:23:58    Processed 11340554 relations in 166822s (46h 20m 22s) - 68/s
...

Without '--slim, --drop, --cache':

...
2023-10-18 16:25:02  Reading input files done in 23108s (6h 25m 8s).                      
2023-10-18 16:25:02    Processed 8672123611 nodes in 3183s (53m 3s) - 2725k/s
2023-10-18 16:25:02    Processed 973116158 ways in 16880s (4h 41m 20s) - 58k/s
2023-10-18 16:25:02    Processed 11372514 relations in 3045s (50m 45s) - 4k/s
...

Import command:

nohup ./osm2pgsql-1.9.2 --username postgres --multi-geometry --hstore --flat-nodes nodes.bin --create --number-processes 10 --database osmplanet --style openstreetmap-carto.style --tag-transform-script openstreetmap-carto.lua --input-reader=pbf planet-latest.osm.pbf >load_osmplanet.out 2>&1 &

Next step:

• Import command (as above) with additional option '--disable-parallel-indexing'.

[lonvia]

You should move your database to the bigger disk at /dev/md3. Either use tablespaces or move the entire PostgreSQL cluster over.

[Klaus-Tockloth]

Even with the option '--disable-parallel-indexing' the import of the planet file was not successful. 705 GB of free disk space is not enough for this.

$ df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/md2        2.0T  1.2T  705G  63% /               <- database
/dev/md3        5.3T  797G  4.3T  16% /home           <- osm2pgsql
...

nohup ./osm2pgsql-1.9.2 --username postgres --disable-parallel-indexing --multi-geometry --hstore --flat-nodes nodes.bin --create --number-processes 10 --database osmplanet --style openstreetmap-carto.style --tag-transform-script openstreetmap-carto.lua --input-reader=pbf planet-latest.osm.pbf >load_osmplanet.out 2>&1 &

...
2023-10-19 23:54:30  Creating geometry index on table 'planet_osm_roads'...
2023-10-19 23:57:16  Analyzing table 'planet_osm_roads'...
2023-10-19 23:57:19  All postprocessing on table 'planet_osm_roads' done in 702s (11m 42s).
2023-10-19 23:57:19  ERROR: Database error: ERROR:  could not extend file "base/384221473/394747050.145": No space left on device
...

Next step:

• Moving the database cluster to the larger partition (as suggested).
• This guide for this seems helpful with: How To Move a PostgreSQL Data Directory to a New Location on Ubuntu 20.04

[Klaus-Tockloth]

I have moved the database data directory to a bigger partion. This is what I have done (maybe helpful for others):

Preparation (identification of required information):

sudo -u postgres psql -c 'SHOW data_directory;'
       data_directory        
-----------------------------
 /var/lib/postgresql/12/main

sudo -u postgres psql -c 'SHOW config_file;'
               config_file               
-----------------------------------------
 /etc/postgresql/12/main/postgresql.conf

Migration (running 5 hours):

sudo systemctl stop postgresql.service
sudo mkdir /home/pgdata
sudo chown postgres:postgres /home/pgdata
sudo chmod 700 /home/pgdata
sudo rsync -av /var/lib/postgresql /home/pgdata

cd /etc/postgresql/12/main/
sudo nano postgresql.conf
old: data_directory = '/var/lib/postgresql/12/main'
new: data_directory = '/home/pgdata/postgresql/12/main'

sudo systemctl start postgresql.service
sudo systemctl status postgresql.service

(Helpful) Links concerning migration:
https://fitodic.github.io/how-to-change-postgresql-data-directory-on-linux
https://www.digitalocean.com/community/tutorials/how-to-move-a-postgresql-data-directory-to-a-new-location-on-ubuntu-20-04

Old situation:

$ df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/md2        2.0T  1.2T  705G  63% /             <- Postgres (free space insufficient for planet import)
/dev/md3        5.3T  797G  4.3T  16% /home         <- osm2pgsql
...

New situation:

$ df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/md2        2.0T  1.2T  705G  63% /             <- no longer used by Postgres
/dev/md3        5.3T  1.8T  3.3T  36% /home         <- Postgres, osm2pgsql
...

After migration I was able to import the planet file:

2023-10-20 14:08:13  osm2pgsql version 1.9.2 (1.9.2)
2023-10-20 14:08:13  Database version: 12.16 (Ubuntu 12.16-0ubuntu0.20.04.1)
2023-10-20 14:08:13  PostGIS version: 3.0
2023-10-20 14:08:13  Storing properties to table '"public"."osm2pgsql_properties"'.
2023-10-20 14:08:13  Setting up table 'planet_osm_point'
2023-10-20 14:08:13  Setting up table 'planet_osm_line'
2023-10-20 14:08:13  Setting up table 'planet_osm_polygon'
2023-10-20 14:08:13  Setting up table 'planet_osm_roads'
2023-10-20 20:37:58  Reading input files done in 23385s (6h 29m 45s).                     
2023-10-20 20:37:58    Processed 8672123611 nodes in 3214s (53m 34s) - 2698k/s
2023-10-20 20:37:58    Processed 973116158 ways in 17248s (4h 47m 28s) - 56k/s
2023-10-20 20:37:58    Processed 11372514 relations in 2923s (48m 43s) - 4k/s
2023-10-20 20:38:03  Clustering table 'planet_osm_roads' by geometry...
2023-10-20 20:38:03  Clustering table 'planet_osm_polygon' by geometry...
2023-10-20 20:38:03  Clustering table 'planet_osm_point' by geometry...
2023-10-20 20:38:03  Clustering table 'planet_osm_line' by geometry...
2023-10-20 21:36:32  Creating geometry index on table 'planet_osm_roads'...
2023-10-20 21:45:48  Creating geometry index on table 'planet_osm_point'...
2023-10-20 21:58:13  Analyzing table 'planet_osm_roads'...
2023-10-20 23:14:11  Analyzing table 'planet_osm_point'...
2023-10-20 23:39:24  All postprocessing on table 'planet_osm_point' done in 10883s (3h 1m 23s).
2023-10-21 04:46:52  Creating geometry index on table 'planet_osm_line'...
2023-10-21 05:45:56  Creating geometry index on table 'planet_osm_polygon'...
2023-10-21 07:00:04  Analyzing table 'planet_osm_line'...
2023-10-21 07:12:04  All postprocessing on table 'planet_osm_line' done in 38042s (10h 34m 2s).
2023-10-21 10:21:25  Analyzing table 'planet_osm_polygon'...
2023-10-21 10:36:22  All postprocessing on table 'planet_osm_polygon' done in 50300s (13h 58m 20s).
2023-10-21 10:36:22  All postprocessing on table 'planet_osm_roads' done in 4894s (1h 21m 34s).
2023-10-21 10:36:22  Storing properties to table '"public"."osm2pgsql_properties"'.
2023-10-21 10:36:22  osm2pgsql took 73688s (20h 28m 8s) overall.

The new import command without the options '--slim, --drop, --cache and --flat-nodes' is much faster (over 42 hours):
20h 28m versus 62h 47m

And this is my final import command:

nohup ./osm2pgsql-1.9.2 --username postgres --multi-geometry --hstore --create --number-processes 8 --database osmplanet --style openstreetmap-carto.style --tag-transform-script openstreetmap-carto.lua --input-reader=pbf planet-latest.osm.pbf >load_osmplanet.out 2>&1 &

BTW: The database is continuously growing by over 40 GB per year (database size after osm2pgsql step and before any additional indexing):

October 2021: 396 GB (planet file: 60.042 GB)
October 2022: 437 GB (planet file: 65.582 GB)
October 2023: 480 GB (planet file: 71.168 GB)

[Klaus-Tockloth]

Thanks for the support.