After the daily database update, random OSM objects are not accessible via the Nominatim endpoints

[vricciardulli]

Describe the bug

After the daily database update, some random OSM objects are not accessible via the Nominatim endpoints and I have to re-import them using commands:

nominatim add-data

or

nominatim refresh

For example, a couple days ago and after the daily update, requesting on my local /details?osmtype=R&osmid=1827125&class=boundary would return a 404. After re-importing it with nominatim add-data --relation 1827125 I would get the correct response and data.

I also noticed that, while this object was available in table planet_osm_rels it was not present in table place anymore.

I'm not sure this is a bug, most likely there is something wrong with my setup. Let me know if I should move this to "discussions" instead. I've also included more context below, I hope it's helpful.

Greatly appreciate your help, thank you! 🙏

To Reproduce

I don't know how to reproduce this, it seems like objects disappear randomly after an import.

Software Environment (please complete the following information):

• Nominatim version: 4.2.3-0
• Postgresql version: 14.7
• Postgis version: 3.2
• OS: Ubuntu 22.04.2 LTS

Hardware Configuration (please complete the following information):

• RAM: 17.3 GB
• number of CPUs: 4
• type and size of disks: 2048GB SSD
• bare metal/AWS/other cloud service: GCP

Postgresql Configuration:

max_connections = 300
shared_buffers = 4GB
effective_cache_size = 12GB
maintenance_work_mem = 1GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 16MB
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4

autovacuum_max_workers = 3
vacuum_cost_limit = 2000
autovacuum_vacuum_scale_factor = 0.01

Additional context

In a previous issue that I reported #2989 (comment), it was suggested that my setup may be incorrect. Here is more info regarding that.

Nominatim initial import

The initial planet import was performed by downloading the continents extracts from https://download.geofabrik.de/ and starting the import with command:

nominatim import --osm-file africa-latest.osm.pbf --osm-file antarctica-latest.osm.pbf --osm-file asia-latest.osm.pbf --osm-file australia-oceania-latest.osm.pbf --osm-file central-america-latest.osm.pbf --osm-file europe-latest.osm.pbf --osm-file north-america-latest.osm.pbf --osm-file south-america-latest.osm.pbf

Nominatim updates

I have set up Nominatim updates as per the docs and I'm using systemd to run it continuously. Relevant parameters are:

NOMINATIM_REPLICATION_URL="https://ftp5.gwdg.de/pub/misc/openstreetmap/planet.openstreetmap.org/replication/day/"
NOMINATIM_REPLICATION_MAX_DIFF=250
NOMINATIM_REPLICATION_UPDATE_INTERVAL=87300
NOMINATIM_REPLICATION_RECHECK_INTERVAL=3600

Flatnode file

I am using a flatnode file. It was created during import and I have not tampered with it since. It is currently about 80GB in size.

[lonvia]

As already mentioned in #2989, this is likely because you have not configured the flatnode file for the updates. NOMINATIM_FLATNODE_FILE=... needs to be present in the .env file in your project directory, i.e. in the same file where you have also configured the replication settings.

[vricciardulli]

This is what I see in the logs, right at the start of the update and before the indexing:

2023-04-26 03:15:01: No new changes. Sleeping for 3600 sec.

2023-04-26 04:15:23  osm2pgsql version 1.7.2
2023-04-26 04:15:23  Database version: 14.7 (Ubuntu 14.7-0ubuntu0.22.04.1)
2023-04-26 04:15:23  PostGIS version: 3.2
2023-04-26 04:15:23  Parsing gazetteer style file '/usr/local/etc/nominatim/import-extratags.style'.
2023-04-26 04:28:33  Reading input files done in 790s (13m 10s).
2023-04-26 04:28:33    Processed 2805556 nodes in 398s (6m 38s) - 7k/s
2023-04-26 04:28:33    Processed 427480 ways in 233s (3m 53s) - 2k/s
2023-04-26 04:28:33    Processed 9543 relations in 159s (2m 39s) - 60/s
2023-04-26 04:50:59  Done postprocessing on table 'planet_osm_nodes' in 0s
2023-04-26 04:50:59  Done postprocessing on table 'planet_osm_ways' in 0s
2023-04-26 04:50:59  Done postprocessing on table 'planet_osm_rels' in 0s
2023-04-26 04:50:59  osm2pgsql took 2136s (35m 36s) overall.

2023-04-26 04:54:54: Starting indexing rank (0 to 4) using 20 threads

[lonvia]

Sadly, osm2pgsql won't tell you if it is using a flatnode file or not. So lets check the other way around first. Does your database have a table planet_osm_nodes?

[vricciardulli]

Yes. These are the stats:

SELECT * FROM pg_stat_all_tables WHERE relname = 'planet_osm_nodes';

 relid | schemaname |     relname      | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins  | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | n_ins_since_vacuum | last_vacuum |        last_autovacuum        |         last_analyze         | last_autoanalyze | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count
-------+------------+------------------+----------+--------------+----------+---------------+------------+-----------+-----------+---------------+------------+------------+---------------------+--------------------+-------------+-------------------------------+------------------------------+------------------+--------------+------------------+---------------+-------------------
 80380 | public     | planet_osm_nodes |        3 |   8206011785 |        0 |             0 | 8206011775 |         0 |         0 |             0 | 8206011789 |          0 |                   0 |                  0 |             | 2023-02-11 22:47:37.632032+00 | 2023-02-11 08:56:45.70436+00 |                  |            0 |                2 |             7 |                 0
(1 row)

The database also contains tables planet_osm_rels and planet_osm_ways.

I don't know if this is useful, but I noticed that these tables may contain objects that do not exist anymore on the Nominatim server, for example W5790116

[lonvia]

There is your problem then. The flatnode file was not used during the import for whatever reason. Now it reads from a mostly empty file during updates.

[vricciardulli]

I see. So the only solution is to start from scratch and re-do the installation + import?

[lonvia]

You could also simply keep the database and remove the flatnode file option. Then it goes back to using the node coordinates from the planet_osm_nodes table. It needs more disk space and is a bit slower. If that is not an issue for you, you should be fine.

There is of course the other problem that you have already run lots of updates with the flanode file option which have destroyed quite a bit of data. If we are talking about only a couple of days, you could reapply the updates but if this has been going on for weeks, a reimport is the only realistic option.

[vricciardulli]

It's been going on for a while so I'm starting from scratch. Thank you very much for the help, as usual 🙏

I don't know how common this mistake is, but I wonder if any of the following could be considered as improvements:

• use a flatnode file as default
• if there is a way for you to check if a flatnode file was used during the initial import, then any subsequent imports and/or the update process would automatically adapt. Essentially any subsequent import or update would never use/create a flatnode file if it was not created during the initial import
• improve the documentation of the database layout, specifying that table planet_osm_nodes is not in the database if a flatnode file is used