= Background
Since this task was created, we've been experiencing an OSM database degradation happening over time.
At first, we had some issues with `osm2pgsql` failing to complete a daily OSM sync due to corrupted input data but we couldn't correlate the problems due to the lack of information on the logs.
We now have `imposm3` as the ETL engine behind OSM DB and the problem continues to happen and we still don't know the cause.
**What we know:**
- it's unrelated to specific OSM edits, some missing polygons hasn't received an edit for months and still disappear
- it's unrelated to the tool that imports the database
== How to mitigate that
While we don't have a solution for the root cause we have two maintenance procedures to restore the DB state:
- Full planet re-import
- Re-import the missing features one by one
These procedures helped to fix some issues raised by the community but it has become a burden to keep up with the degradation rate, that increased since we introduced Tegola
== The current hypothesis to explore
There have been some PostgreSQL errors caused by connection slots starvation recently. The hypothesis is that, if we can correlate the OSM sync failure with the PG errors, we can find the root cause and act promptly to fix it.
Therefore we will be monitoring the database after every OSM change file is applied to it and look for the causes of the DB degradation. (upcoming patch)
== What to do meanwhile?
For now, we will keep restoring the DB with the maintenance scripts, and to make it easier for us to keep up with the bugs, we will suggest a Phabricator template task to raise these types of issues with the needed information to apply the fix for specific geometries.
- Template (TBD)
**We have a scheduled maintenance script to re-import the planet in order to fix the current open tasks in the next week**
= Old task description kept for history context
>
> Recently the community have spotted some weird behavior with geoshapes service. Some Wikidata items are not available through the service and some are, see example below:
> - Works fine: https://maps.wikimedia.org/geoshape?getgeojson=1&ids=Q3145754
> - Returns empty array: https://maps.wikimedia.org/geoshape?getgeojson=1&ids=Q6414
>
> It seemed that something was wrong with the OSM replication script or the initial import script failed at some point.
>
> === Initial import script and Steps to reproduce ===
> The log doesn't show any errors or warnings and they are available at `/home/mbsantos/osm-initial-import.log`
>
> === OSM replication script ===
> The logs doesn't show any errors or warnings and they are available at `/var/log/osmosis`
>
> = Further investigation and steps to reproduce the actual results =
> With no related track from the logs I started to investigate the data stored in the PostgreSQL DB. I started with the relation [[ https://www.openstreetmap.org/relation/8569 | Lake Garda (8569) ]] from the example above. The problem is not with the relation, because it is stored in the table `planet_osm_rels`:
> ```
> gis=> SELECT id FROM planet_osm_rels WHERE id = 8569;
> id
> ------
> 8569
> (1 row)
> ```
> But performing a [[ https://github.com/kartotherian/geoshapes/blob/master/geoshapes.js#L64 geoshapes similar query ]], it returns nothing because the OSM relation didn't become a polygon at `osm_planet_polygon`:
> ```
> gis=> SELECT tags->'wikidata' AS id, osm_id
> gis-> FROM planet_osm_polygon
> gis-> WHERE tags ? 'wikidata' AND tags->'wikidata' IN ('Q6414');
> id | osm_id
> ----+--------
> (0 rows)
> ```
> = Expected Results
> Request existent Wikidata-OSM link and got the GeoJSON through geoshapes service. See the same SQL queries for the [[ https://www.openstreetmap.org/relation/311766 | Hôtel de Blossac (311766) ]]:
> - [[ https://www.wikidata.org/wiki/Q3145754 | Q3145754]]
> - https://maps.wikimedia.org/geoshape?getgeojson=1&ids=Q3145754
> ```
> gis=> SELECT id FROM planet_osm_rels WHERE id = 3117766;
> id
> ---------
> 3117766
> (1 row)
> ```
>
> ```
> gis=> SELECT tags->'wikidata' AS id, osm_id
> FROM planet_osm_polygon
> WHERE tags ? 'wikidata' AND tags->'wikidata' IN ('Q3145754');
> id | osm_id
> ----------+---------
> Q3145754 | -311766
> (1 row)
> ```
>
> = Environments Observed
> - All master machines:
> - map1004.eqiad.wmnet
> - map2004.codfw.wmnet
>
> = Testing Environment for QA
> - Beta Cluster environment will be set to explore the issue
>
> = Additional notes
> Some references report a similar issue but the outcome doesn't apply to our infrastructure
> https://help.openstreetmap.org/questions/28563/missing-polygons-or-rendering-issue
> https://help.openstreetmap.org/questions/68009/osm2pgsql-how-import-relations-into-polygon-table