[Tile-serving] [osm2pgsql-dev/osm2pgsql] Question: could not extend file "base/361191441/368615606.137": No space left on device (Discussion #2093)

[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)
```


-- 
Reply to this email directly or view it on GitHub:
https://github.com/osm2pgsql-dev/osm2pgsql/discussions/2093#discussioncomment-7349696
You are receiving this because you are subscribed to this thread.

Message ID: <osm2pgsql-dev/osm2pgsql/repo-discussions/2093/comments/7349696 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20231022/bf61635d/attachment.htm>