Which steps remain when osm2pgsql fails after the data input, and can I perform these directly against the database?

Help and support
1

Which steps remain when osm2pgsql fails after the data input, and can I perform these directly against the database?

My setup:

  • osm2pgsql version 1.6 running in an AWS EC2 instance (Instance type t2.small, 2 GB RAM, 1vCPU, 512 GB storage, running Ubuntu 22.04)
  • Aurora PostgreSQL 14.5 running in an AWS RDS cluster (instance class db.r6g.xlarge, 32 GB RAM, 4 vCPU, storage autoscaling)

I attempted to import the OSM dump of Europe of 2023-05-02T20:16:04Z. This ran for about 8 days almost until the end, with several “All done” messages shown, before terminating with an error message. The generated tables contain about 4 billion rows in all, so it looks like the import of the data was successful. Also, (some, maybe all?) indexes were created.

What I would like to know is whether I have a usable data import or whether some essential steps are still needed. Reading through the osm2pgsql source code did not get me further.

This is the command line I used (connection details redacted):

osm2pgsql --create --slim --hstore \
--style ${HOME}/etc/openstreetmap-carto.style --prefix europe_osm \
--database (...) --username (...) --host (...) --port (...) \
europe-latest.osm.pbf

This is the osm2pgsql output:

2023-05-03 20:27:01  osm2pgsql version 1.6.0
2023-05-03 20:27:01  Database version: 14.5
2023-05-03 20:27:01  PostGIS version: 3.2
2023-05-03 20:27:02  Setting up table 'europe_osm_point'
2023-05-03 20:27:02  Setting up table 'europe_osm_line'
2023-05-03 20:27:02  Setting up table 'europe_osm_polygon'
2023-05-03 20:27:02  Setting up table 'europe_osm_roads'
2023-05-10 18:19:44  Reading input files done in 597162s (165h 52m 42s).                  
2023-05-10 18:19:44    Processed 3164864349 nodes in 18501s (5h 8m 21s) - 171k/s
2023-05-10 18:19:44    Processed 383845957 ways in 431097s (119h 44m 57s) - 890/s
2023-05-10 18:19:44    Processed 6717149 relations in 147564s (40h 59m 24s) - 46/s
2023-05-10 18:19:46  Clustering table 'europe_osm_point' by geometry...
2023-05-10 18:41:33  Creating geometry index on table 'europe_osm_point'...
2023-05-10 19:19:32  Creating osm_id index on table 'europe_osm_point'...
2023-05-10 19:20:56  Analyzing table 'europe_osm_point'...
2023-05-10 19:21:15  Clustering table 'europe_osm_line' by geometry...
2023-05-10 19:51:11  Creating geometry index on table 'europe_osm_line'...
2023-05-10 20:38:50  Creating osm_id index on table 'europe_osm_line'...
2023-05-10 20:40:39  Analyzing table 'europe_osm_line'...
2023-05-10 20:41:09  Clustering table 'europe_osm_polygon' by geometry...
2023-05-10 20:52:56  Clustering table 'europe_osm_roads' by geometry...
2023-05-10 20:56:43  Creating geometry index on table 'europe_osm_roads'...
2023-05-10 20:59:12  Creating osm_id index on table 'europe_osm_roads'...
2023-05-10 20:59:16  Analyzing table 'europe_osm_roads'...
2023-05-10 20:59:16  Done postprocessing on table 'europe_osm_nodes' in 0s
2023-05-10 20:59:16  Building index on table 'europe_osm_ways'
2023-05-11 17:51:29  Done postprocessing on table 'europe_osm_ways' in 75132s (20h 52m 12s)
2023-05-11 17:51:29  Building index on table 'europe_osm_rels'
2023-05-11 17:57:53  Done postprocessing on table 'europe_osm_rels' in 383s (6m 23s)
2023-05-11 17:57:53  All postprocessing on table 'europe_osm_point' done in 3689s (1h 1m 29s).
2023-05-11 17:57:53  All postprocessing on table 'europe_osm_line' done in 4793s (1h 19m 53s).
2023-05-11 17:57:53  ERROR: Database error: ERROR:  could not write to file "base/pgsql_tmp/pgsql_tmp28765.19": No space left on device

I suspect that perhaps some temporary tables still remain, or some indexes were not created.

Currently I have these tables with the specified prefix europe_osm:

europe_osm_line
europe_osm_nodes
europe_osm_point
europe_osm_polygon
europe_osm_rels
europe_osm_roads
europe_osm_ways

and these are the existing indexes on these tables:

europe_osm_line: europe_osm_line_osm_id_idx, europe_osm_line_way_idx
europe_osm_nodes: europe_osm_nodes_pkey
europe_osm_point: europe_osm_point_osm_id_idx, europe_osm_point_way_idx
europe_osm_rels: europe_osm_rels_parts_idx, europe_osm_rels_pkey
europe_osm_roads: europe_osm_roads_osm_id_idx, europe_osm_roads_way_idx
europe_osm_ways: europe_osm_ways_nodes_idx

That is, all tables but europe_osm_polygon have indexes.

So the question: can I avoid a complete re-import of the data by simply performing some final steps manually? What steps remained after the point where the error occurred?

2

No. It said “ERROR: Database error: ERROR: could not write to file “base/pgsql_tmp/pgsql_tmp28765.19”: No space left on device”

If it took 6 days to get there you need to run it on a bigger machine or (as a test) load data for a smaller area or (if you want Europe but not all the detail) preprocess the file to have less data in it before loading.

1 Like
3

Many thanks for your answer and suggestion.

I would like to repeat the final question from my post:
What steps remained after the point where the error occurred?

4

You’ve just driven off a cliff. Asking “how do I drive to my destination from here” unfortunately is not meaningful :slight_smile:

3 Likes
5

You need more disk space to do anything, or eliminate some data - either filter something out (e.g. buildings) or remove areas. I see that you’re planning on updating the database because you have --slim without --drop, so if that plan changes, you could add --drop

Your ec2 instance is drastically undersized for dealing with all of Europe. You should be giving osm2pgsql --cache approximately equal to the PBF size, which you can’t do with only 2GB RAM.

I do not recommend attempting to run the clustering and indexing statements by hand. You will have to debug where it stopped, what needs to be done, and the statements remaining. I’m a maintainer of osm2pgsql, and if I try to do this by hand, there’s a good chance I will mess it up.

For the specific error you’re having, it’s not a PostgreSQL error, but an Aurora one. Aurora is not PostgreSQL, and is not supported by osm2pgsql. Running out of space indicates autoscaling is not working, which is purely an Aurora problem, so I suggest contacting AWS support.

1 Like
6

Recently had this issue, AWS said that it was likely due to the instance type not having enough local storage (this is not db scaling, but supposedly the node-local space used for temporary tables). I tried a couple times with larger instance types and had it fail the same way despite their analytics not showing local storage exhaustion.

In the end we went with an instance type that had a locally attached SSD, ran both postgresql and osm2pgsql on it, and did a db export TO a prepared RDS node.

7

Thanks for getting back with your comments. When we originally imported the OSM data 2 years ago, we had set up a much stronger EC2 instance. This time we thought we could go with our small jumpserver EC2 instance, as it seemed that most of the action happens inside the RDS instance. Apparently this is not the case. Unfortunately, the import, and the subsequent attempt to update the data using daily update files, burnt about $1000 in RDS I/O costs and so we have abandoned our efforts to get a successful import for now… no point throwing good money after bad money.

We did see, however, that most (all?) of the data we need is in the failed import. We use the OSM data for analytic purposes, not for serving map tiles or any such thing. The main table we use is europe_osm_roads, from which we extract railway lines, and then determine distances between coordinates from our IoT devices and the nearest railway track. The other thing we use is country outlines, which are in the europe_osm_polygon table. For these purposes it looks like the data is there and is complete.

By the way, we had been in touch with AWS support on a similar issue (“no space left on device”) in the past, and I know what their advice is: upgrade to a bigger instance type. This means double the cost with every step up the instance class ladder, and as you may have guessed, our company doesn’t like big spending increases.

8

osm2pgsql is not really restartable, and I would imagine would require considerable engineering effort to make it so. Trying to use a partial build with hand-crafted queries is not something I’d be willing to try.

To reduce data volumes I would advise filtering the data which you upload. Simple changes to the style file (default.style) can remove stuff which is not of interest without changing the target schema. For instance, if buildings are not of interest simply comment out the line in the style file.

2 Likes
9

Yep. osmium is good at filtering a pvf file based on OSM tags. Doing that first can vastly reduce the size of your data file to import. By keeping the old file around, and using osmium’s diff functionality, you can also do data updates.

1 Like
10

Osmium may be overkill for simple feature removal if one doesn’t mind starting with a complete planet or country dataset for the load. Judging by @ZeLonewolf’s recent write-up this is not an issue if using an AWS hosted planet to start with.

11

Just remember that the planet pbf is only updated weekly, so it may still be a valid use case to repeatedly patch a planet file depending on your needs.

12

Many thanks for the further suggestions. Once we get around to this next time, we will probably do it this way:

  1. Be very selective in the data we extract from the European dataset.
  2. Perform the import on a local development machine (to avoid AWS costs).
  3. Dump the finished tables, transfer them to the AWS server and restore these into the RDS instance.
1 Like