T283301 also mentions (quite misteriously) that the instances hosting osmdb.eqiad.wmnet (clouddb1003/clouddb1004) contain "hard-to-replace data for a few tools that are served on wiki". 🤔

After a brief discussion with @Andrew we decided to enable query logging on the Postgres db and check in a week if there is any activity and what kind of activity on that db instance (clouddb1003/1004).

<wrong ticket>

I enabled query logging with the following commands on clouddb1003:

postgres=# ALTER SYSTEM SET logging_collector TO on;
ALTER SYSTEM
postgres=# ALTER SYSTEM SET log_statement TO 'all';
ALTER SYSTEM
postgres=# SELECT pg_reload_conf();

And I see there are a few queries hitting the database:

[...]
2022-11-18 18:38:33 GMT LOG:  statement: (SELECT "T_es" as title, pop,lon,lat,style,lang,image,imagejpg,name,arms FROM wp_coords_red3  WHERE the_geom &&
        ST_SetSRID(ST_MakeBox2D(ST_Point(-122.09088436049,23.98954138585),
                                ST_Point(-86.754458579235,37.72014001038)),4326) and "T_es" IS NOT NULL  ORDER BY psize DESC LIMIT 80)
2022-11-18 18:38:33 GMT LOG:  statement: (SELECT "T_es" as title, pop,lon,lat,style,lang,image,imagejpg,name,arms FROM wp_coords_red2  WHERE the_geom &&
        ST_SetSRID(ST_MakeBox2D(ST_Point(-107.9943267433,26.554213963819),
                                ST_Point(-90.326113852672,33.495941776727)),4326) and "T_es" IS NOT NULL  ORDER BY psize DESC LIMIT 80)
2022-11-18 18:38:33 GMT LOG:  statement: (SELECT "T_es" as title, pop,lon,lat,style,lang,image,imagejpg,name,arms FROM wp_coords_red3  WHERE the_geom &&
        ST_SetSRID(ST_MakeBox2D(ST_Point(-122.09088436049,23.98954138585),
                                ST_Point(-86.754458579235,37.72014001038)),4326) and "T_es" IS NOT NULL  ORDER BY psize DESC LIMIT 80)
root@clouddb1003:~# tail -f /var/log/postgresql/postgresql-9.6-main.log
                                ST_Point(-90.326113852672,33.495941776727)),4326) and "T_es" IS NOT NULL  ORDER BY psize DESC LIMIT 80)
2022-11-18 18:38:33 GMT LOG:  statement: (SELECT "T_es" as title, pop,lon,lat,style,lang,image,imagejpg,name,arms FROM wp_coords_red3  WHERE the_geom &&
        ST_SetSRID(ST_MakeBox2D(ST_Point(-122.09088436049,23.98954138585),
                                ST_Point(-86.754458579235,37.72014001038)),4326) and "T_es" IS NOT NULL  ORDER BY psize DESC LIMIT 80)
2022-11-18 18:38:38 GMT LOG:  statement: (SELECT "T_es" as title, pop,lon,lat,style,lang,image,imagejpg,name,arms FROM wp_coords_red3  WHERE the_geom &&
        ST_SetSRID(ST_MakeBox2D(ST_Point(-151.16290584486,19.137648954059),
                                ST_Point(-80.490054282359,45.840687794701)),4326) and "T_es" IS NOT NULL  ORDER BY psize DESC LIMIT 80)
2022-11-18 18:38:38 GMT LOG:  statement: (SELECT "T_es" as title, pop,lon,lat,style,lang,image,imagejpg,name,arms FROM wp_coords_red3  WHERE the_geom &&
        ST_SetSRID(ST_MakeBox2D(ST_Point(-151.16290584486,19.137648954059),
                                ST_Point(-80.490054282359,45.840687794701)),4326) and "T_es" IS NOT NULL  ORDER BY psize DESC LIMIT 80)

2022-11-18 18:38:41 GMT LOG:  statement: (SELECT "T_es" as title, pop,lon,lat,style,lang,image,imagejpg,name,arms FROM wp_coords_red3  WHERE the_geom &&
        ST_SetSRID(ST_MakeBox2D(ST_Point(-134.28790584487,19.720370046361),
                                ST_Point(-63.615054282364,46.269919422411)),4326) and "T_es" IS NOT NULL  ORDER BY psize DESC LIMIT 80)
[...]

I will disable the query logging for now, to avoid filling the disk during the weekend (though probably we have enough space for a few days of logging).

I have enabled logging again for a few seconds, this time with ALTER SYSTEM SET log_line_prefix TO '%t %u %d %h'; to gather more information. All log lines have user osm, database gis and remote host 172.16.1.15.

After some digging with the help of @aborrero I tracked down that IP to the wp-world tool. T199652 seems to suggest the tool is not even working anymore.

I have enabled query logging again (same commands as above), this time both on clouddb1003 (primary) and clouddb1004 (replica). I will leave query logging on for 24 hours to double check if there are any other users connecting to them.

24 hours later:

• no queries at all on clouddb1004
• on clouddb1003
  • 19554 queries from IP 172.16.1.15 with user osm and database gis
  • 15 queries from IP 172.16.3.18 with user osm and database gis
  • a few queries with user osmupdater and database gis, where no IP was logged. I'm not sure where they're coming from, I'm pasting one example below:

2022-11-25 01:17:09 GMT osmupdater gis [local] LOG:  statement: SET synchronous_commit TO off;
2022-11-25 01:17:09 GMT osmupdater gis [local] LOG:  statement: SET client_min_messages = WARNING
2022-11-25 01:17:09 GMT osmupdater gis [local] LOG:  statement: DROP TABLE IF EXISTS planet_osm_point_tmp
2022-11-25 01:17:09 GMT osmupdater gis [local] LOG:  statement: RESET client_min_messages
2022-11-25 01:17:09 GMT osmupdater gis [local] LOG:  statement: BEGIN
2022-11-25 01:17:09 GMT osmupdater gis [local] LOG:  statement: SELECT * FROM planet_osm_point LIMIT 0
2022-11-25 01:17:09 GMT osmupdater gis [local] LOG:  statement: PREPARE get_wkb (int8) AS SELECT way FROM planet_osm_point WHERE osm_id = $1
2022-11-25 01:17:09 GMT osmupdater gis [local] LOG:  statement: COPY planet_osm_point (osm_id,"access","addr:housename","addr:housenumber","addr:interpolation","admin_level","aerialway","aeroway","amenity","area","barrier","bicycle","brand","bridge","boundary","building","capital","construction","covered","culvert","cutting","denomination","disused","ele","embankment","foot","generator:source","harbour","highway","historic","horse","intermittent","junction","landuse","layer","leisure","lock","man_made","military","motorcar","name","natural","office","oneway","operator","place","population","power","power_source","public_transport","railway","ref","religion","route","service","shop","sport","surface","toll","tourism","tower:type","tunnel","water","waterway","wetland","width","wood","z_order",tags,way) FROM STDIN

I will leave the query logging enabled during the weekend, as it's not using much disk space.

@Kolossos I see you are listed as a maintainer of wp-world, the tool is connecting regularly to osmdb.eqiad.wmnet (clouddb1003) and making queries such as

SELECT "T_hu" as title, pop,lon,lat,style,lang,image,imagejpg,name,arms FROM wp_coords_red3  WHERE the_geom &&
        ST_SetSRID(ST_MakeBox2D(ST_Point(-98.701171875003,16.922300248686),
                                ST_Point(-81.298828124999,32.662011729866)),4326) and "T_hu" IS NOT NULL  ORDER BY psize DESC LIMIT 80

We are planning to shut down this database completely as the hardware is due for decommission.

It looks like wp-world is the only tool that's currently connecting to the database. Is the wp-world tool still in use? Does it rely on this database? If yes, we can help you in migrating the data to a new server.

The only other activity on the database is:

• nightly imports with queries like COPY planet_osm_point [...] FROM STDIN, the user is osmupdater but I haven't identified the process
• very infrequent queries (between 10 and 20 queries per day) from 172.16.3.18 (tools-sgeweblight-10-32), the user is osm but I haven't identified the process. some example queries are:
  • select distinct name from planet_osm_line where way && ST_Transform(ST_SetSRID(ST_MakeBox2D(ST_Point(13.54,50.95),ST_Point(13.94,51.15)),4326),900913) AND "highway" is not NULL AND name is not NULL order by name LIMIT 10000;
  • select osm_id,name,ST_asKML(ST_Transform(way,4326)) from planet_osm_point where way && ST_Transform(ST_SetSRID(ST_MakeBox2D(ST_Point(14.49,46.05),ST_Point(14.50,46.05)),4326),900913) AND name like 'Petrol' order by name LIMIT 1000;

Thanks for doing this research! I guess the remaining question is if all of this traffic can be moved to the in-project database running on maps-osmdb. @dschwen, is it your understanding that the new DB (that you built) contains the same data as this database?

is it your understanding that the new DB (that you built) contains the same data as this database?

Yeah, it should contain the same data. Before we migrate other tools to the project internal db I'd like some advice on how to set up individual accounts for each connecting tool.

@dschwen, the in-project database is also doing whatever that 'COPY planet_osm_point [...] FROM STDIN' maintenance is in the other database?

Also, can you tell me more about what you mean about setting up individual accounts? Do you just need @fnegri's help creating postgres accounts and setting up grants? Is it enough to just duplicate whatever is present on osmdb.eqiad.wmnet?

It looks like the only two Postgres accounts being used on the old DB are osm and osmupdater, the second one is only used for the nightly imports so it would be unnecessary if you're already handling the imports in the new database. I can assist in creating a osm user with the correct read-only grants on the required tables.

I would also like to hear from @Kolossos to understand if the wp-world tool is still in use or not.

@fnegri my suggestion would be this:

• Leave the current osm account as is. It will be my "admin" account
• Create read-only accounts for each tool. E.g. osm_wma for my wikiminiatlas, osm_wp-world if needed and so on.

ideally if you could just document the account creation (or make it a small shell script that I can check into the https://github.com/Commonists/maps-osmdb repo) that would allow me to add accounts on demand without bothering you.

Primarily I want to avoid teh situation, where every tool just uses the same account and we have no way of easily throttling / disabling broken tools, and figuring out who uses the db how much.

@dschwen sounds good, having one account per tool is definitely a good idea. Maybe we could make the names more explicit, like osm_admin, osm_ro_wma, etc.? I'll draft the actual SQL commands next week.

Maybe we could make the names more explicit, like osm_admin, osm_ro_wma, etc.? I'll draft the actual SQL commands next week.

Yea, good idea, just make sure though not to remove/rename the osm account before I get around to changing the db config for my tool, please :)

Quick drive-by: Francesco, once you make the account changes can you also leave instructions for @dschwen so that he can make similar followup changes? I'm loving the idea of this DB being primarily volunteer-maintained :)

Yes wp-world is still in use and included in tons of Wikipedia-article.
You can find the help page here:
https://de.wikipedia.org/wiki/Wikipedia:WikiProjekt_Georeferenzierung/Anwendungen/OpenStreetMap/en#Open_map_view

If dschwen had copy the data and could show me how to access the other database I could maybe move to there.
(@Nux is also a project maintainer of wp-world.)

@Kolossos what's the status of the WIWOSM project? I'm using it from WMA. Is it maintained? It would be another candidate to use the new OSM DB.

@dschwen, WIWOSM also still running but it's afaik not updated.
For this it seems possible to switch to kartographers data like
https://maps.wikimedia.org/geoshape?getgeojson=1&ids=Q158158 but some adoptions seems necessary.

If I remember correctly the data for WIWOSM are cached in the file system.

possible to switch to kartographers data like

Ah, right, I remember seeng that. I'll make that change. IIRC the wikidata Q number is available in the article JS.

@Kolossos thanks for the info! I have a few followup questions:

• can you point me to an example of a wiki page that is using it? I tried finding a page with the "map" link as shown in the help page, but the ones I found are using WikiMiniAtlas instead.
• do you know where the queries against the tables planet_osm_line and planet_osm_point (see a few comments above) are coming from? I'm not finding them in the source code of the wp-world tool, maybe they're from a different tool (I see they're coming from the Grid engine, while wp-world is using the Kubernetes engine) but I'm struggling to find which tool.

• For a link to the map you can look at German Wikipedia. Nowadays we use the OSM-Icon instead of the "map"-link. Examples :
  • https://de.wikipedia.org/wiki/Berlin
  • https://es.wikipedia.org/wiki/Berl%C3%ADn (mapa)
  • https://pl.wikipedia.org/wiki/Berlin (mapa)
• If the queries to planet_osm_line and planet_osm_point are coming from OSM-updater, it seem to me that they are coming from OSM-to Pgsql Updating process https://osm2pgsql.org/

Thanks @Kolossos for the links.

The queries on planet_osm_line and planet_osm_point are coming both from osmupdater (like this one):

COPY planet_osm_point [...] FROM STDIN

But also from user osm (like these ones):

select distinct name from planet_osm_line where way && ST_Transform(ST_SetSRID(ST_MakeBox2D(ST_Point(13.54,50.95),ST_Point(13.94,51.15)),4326),900913)  AND "highway" is not NULL AND name is not NULL order by name LIMIT 10000;
select osm_id,name,ST_asKML(ST_Transform(way,4326)) from planet_osm_point where way && ST_Transform(ST_SetSRID(ST_MakeBox2D(ST_Point(14.49,46.05),ST_Point(14.50,46.05)),4326),900913)  AND name like 'Petrol'  order by name LIMIT 1000;

I'm trying to understand where the ones from user osm are coming from. It looks like it's a tool running on the Toolforge Grid engine. They are not very frequent (only 10-20 per day).

This one looks like it dumps all the names of roads/streets in a given region (here it is east Germany, around the city of Dresden)

select distinct name from planet_osm_line where way && ST_Transform(ST_SetSRID(ST_MakeBox2D(ST_Point(13.54,50.95),ST_Point(13.94,51.15)),4326),900913)  AND "highway" is not NULL AND name is not NULL order by name LIMIT 10000;

This one looks like it is getting locations of gas stations (in this case in the capital of Slovenia) in KML format

select osm_id,name,ST_asKML(ST_Transform(way,4326)) from planet_osm_point where way && ST_Transform(ST_SetSRID(ST_MakeBox2D(ST_Point(14.49,46.05),ST_Point(14.50,46.05)),4326),900913)  AND name like 'Petrol'  order by name LIMIT 1000;

I've made some progress in accessing data via WD, but didn't integrate it yet.

You can see a working version here for smaller items (island in Poland):
https://wiwosm.toolforge.org/test-osm-on-ol/kml-on-ol.php?lang=wikidata&title=Q3255

But larger items (or maybe just Alaska) seem act weird. Might be because of it being on the edge of the world ;-) (edge of coordinates)
https://wiwosm.toolforge.org/test-osm-on-ol/kml-on-ol.php?lang=wikidata&title=Q797

This is for drawing shapes only and with my changes it doesn't use any database (uses maps.wikimedia service). The original is using zipped files that on wiwosm disk space. Those files are currently out of date, but there are a lot of them. As I understand they were generated from some database, but haven't really looked into it. I know there were problems with updates of the files and (as mentioned above) it might be good to drop them in the long run.

Not sure if a database is used for anything else though.

Anyway I can make a more permanent solution for accessing shape data via WD id (Q123), but using it will require some changes on wikis. Which don't use Q ids AFAIK.

do you know where the queries against the tables planet_osm_line and planet_osm_point (see a few comments above) are coming from? I'm not finding them in the source code of the wp-world tool, maybe they're from a different tool (I see they're coming from the Grid engine, while wp-world is using the Kubernetes engine) but I'm struggling to find which tool.

@fnegri AFAIK current source code is not fully public. I mean current versions of files on Toolforge differ a bit from current ones on Github. There are some test versions, which I'm not sure if they are used in practice in some way. The files you might want to look at are those:

class.Wiwosm.php
class.Wiwosm.dev.php
class.Wiwosm.dev_hstore.php

The first is only slightly different then the public version:
https://github.com/aiomaster/WIWOSM/blob/master/server/class.Wiwosm.php

I've migrate 2nd part of the project to a new organisation-repo for osm-on-ol (team project). But osm-on-ol does not contain any references to planet_osm_*.

BTW. @Kolossos @dschwen maybe you remember if I can just remove this dev versions? And also gen_json_files.dev.php. If this are only for shapes then I think in the long run we can switch to using maps.wikimedia service and don't host separate files. But keep old files (already generated) for backward compatibility.

@Nux: I would also say let's switch to maps.wikimedia service. With Kartographer the service should have long time support by WMF. It's better to have this, than an unmaintained project (I only managed the development of WIWOSM but doesn't write the code.) I hope maps.wikimedia service has a rapid update cycle.

Issues with objects that are running over the date line like Alaska are in my eyes acceptable.

We have different cases of geometries to test (group of points, lines , group of lines, Polygons and group of Polygons).

• group of points are not running in the moment (Example: "Stolpersteine" Q314003 Maybe not the best example, but it runs in parts in the map in Wikidata.)
• lines and group of lines are in the moment not running (Example: river in my hometown Chemnitz Q159630) but it is shown in the map in Wikidata
• polygons and group of polygons like Q159630 are running fine.

Additionally we have to check very large geometries like the polygon of Russia (Q159), it seem also in maps.wikimedia service polygons are simplifed and runs without craching the browser.

Just poking my head in to mention -- there is a database available that replaces osmdb.eqiad.wmnet, it's running on maps-osmdb.maps.eqiad.wikimedia.cloud. So you don't /have/ to refactor your code to not use a database, it would just be a different database.

@Andrew : For the WIWOSM part I still think a switch to maps.wikimedia service would make sense.

Other thing is what to do with the Wikipedia-coordinates on database: host=osmdb.eqiad.wmnet dbname=gis user=osm ?
Different questions there, as I was not active in this topic for so long:
Is there also something to change?
If so, could somebody please copy tables "wp_coords*" to some in future running database server and adjust "marks.php"?

Maybe between Christmas and New Year I find time to go deeper , but I'm not sure.

@dschwen : Could you please tell me where the new database is and if you copy perhaps the 4 tables "wp_coords_redX" ?
If so, a switch should be easy.

I could use a timeline for when we can safely switch off osmdb.eqiad.wmnet. End of January?

Mentioned in SAL (#wikimedia-cloud) [2023-01-05T09:12:46Z] <dhinus> disabled puppet checks on clouddb100[3-4] (touch /.no-puppet-checks) T323159

I disabled puppet checks because I was getting a daily email saying Puppet did not run in the last X seconds, and the last run was a failure.

Puppet stopped working on those hosts because Stretch is no longer supported by the class postgresql::postgis:

Jan  5 07:22:01 clouddb1004 puppet-agent[13155]: Could not retrieve catalog from remote server: Error 500 on SERVER: Server Error: Evaluation Error: Error while evaluating a Resource Statement, Evaluation Error: No matching entry for selector parameter with value 'stretch' (file: /etc/puppet/modules/postgresql/manifests/postgis.pp, line: 17, column: 35) on node clouddb1004.clouddb-services.eqiad.wmflabs

Stretch support was removed in https://gerrit.wikimedia.org/r/c/operations/puppet/+/812230 (merged Nov 15). If these hosts are going to be shut down soon I don't think we need to fix this and we can accept that Puppet is no longer running. If for any reason these hosts will still be running next month I would suggest to temporarily revert that patch.

I could really need help. For wp-world I would need a PostGIS database for my database of Wikipedia coordinates (4 static tables "wp_coords_redX" with "X"=[0,2,3,4] ) . The project runs in different Wikipedias in over 100.000 articles. It would be such a shame to stop the project after >15 years without replacement. The problem is that I never was an server admin or an database admin, so help would be neccesary.

Yeah, I was waiting for help with the account creation. I'm out skiing this weekend but I'll check where we are with that this week. I can copy the tables. Ideally you just dump them and I import them from the dump.

@dschwen: Sounds very good. Thanks in advanced.

The dumps are in plain text in the project "wp-world": ~/public_html/dumps/*.pgsql
I gave you access to the project, otherwise the files should be also available over the web.

@dschwen : As End of January comes closer, I want to ask if you had time to import my dumps from above? Thanks

Hey all, we now have a target date for this: We would like to shut this database down by February 13th because some network maintenance is happening that day on the switch hosting this server and we're hoping to just power down and ignore this server before that.

It seems to me that this task is no longer really about osmdb and instead is about replacing that workload with an unrelated service, is that right? If that turns out to be delayed, you should be able to move to the in-project database server with a minimum of friction.

@fnegri, I circled back with dschwen and now we both think that this remaining task is in your hands. Can you assist with the postgres wrangling? thx.

I created two users osm_ro_wma and osm_ro_wpworld, and granted them read-only access to all tables in the gis database.

I can connect successfully from the db instance itself maps-osmdb.maps.eqiad1.wikimedia.cloud, using

psql -h localhost -U osm_ro_wpworld gis

But I think we might need some extra configuration to make this database reachable by other tools. Are wikiminiatlas and wpworld going to connect over TCP? Can we open the Postgres port only for specific tools? I assume we could modify the instance security groups, but I'm not sure about the best practices here...

I would use a security group and open it either to all of cloud-vps or possibly to just the 'tools' project if there's a clear way to do that. Then rely on postgres grants to do the rest. Not the most elegant but similar to the limits on the DB we're replacing.

I attached a new security group to the maps-osmdb instance, allowing connections on port 5432 from 172.16.0.0/21 (all of Cloud VPS).

I can now connect successfully from the Toolforge bastion. I saved a copy of the password for the new Postgres users osm_ro_wma and osm_ro_wpworld in the home directories for the respective tools (/data/project/wikiminiatlas/.pgpass and /data/project/wp-world/.pgpass).

I can successfully connect with

become wikiminiatlas
psql -h maps-osmdb.maps.eqiad1.wikimedia.cloud -U osm_ro_wma gis

become wp-world
psql -h maps-osmdb.maps.eqiad1.wikimedia.cloud -U osm_ro_wpworld gis

For the record, I created the two new users and assigned them to a new gis_readonly group, which has read permission on new and existing tables in the gis database:

\c gis

CREATE ROLE gis_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO gis_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES to gis_readonly;

CREATE ROLE osm_ro_wma WITH LOGIN PASSWORD 'xxxxx';
CREATE ROLE osm_ro_wpworld WITH LOGIN PASSWORD 'yyyyy';
GRANT gis_readonly TO osm_ro_wma;
GRANT gis_readonly TO osm_ro_wpworld;

Thanks @fnegri . If you could copy also my 4 tables wp_coords_red* from the old gis-database to the new one, I would be absolutely fine with the read-only status of the new database. (This tables contains coordinates of Wikipedia-POIs for wp-world on different reduction levels. )

@Kolossos sure, I will do this on Monday!

Thanks. I'm importing the wp_coords_redX tables right now. For the record, the WikiMiniAtlas has already been connecting to this database. I hope that still works after these changes. I'll check this later and will change it to use the new account.

@Kolossos how old are these POI tables? I notice they use a curious design with a column for each language and most columns being empty for most POIs. I have a pretty robust extraction and reduction level rebuilding going on for the WMA. I think it would be a good idea to just use that dataset instead.

P.S.: the import of the wp_coords_redX tables is done.

I see that in the old db, there are also some other databases next to the gis one. From the logs, it seems that no one is connecting to those, but I wonder if we should make a backup before shutting down the server?

• u_aude
• u_kolossos
• wikimaps_atlas

From my side I don't need the u_kolossos db
@aude can maybe check if she needs here db.
I have no idea who is behind wikimaps-atlas.

In T323159#8588380, @dschwen wrote:

@Kolossos how old are these POI tables? ....

The POI tables are quite old (2015), afterwards all trys for an update were unsucessful and run into timeouts.
At this time Wikidata wasn't good enought and still objects are missing e.g. Lists of Wiki Loves Monuments. So I collected the data from all Wikipedias. I would like to leave the data as they are, until somebody else or I what revive the project.

According to phabricator wikimaps-atlas was by @Planemad

@Kolossos do you have everything you need to switch wp-world from osmdb.eqiad.wmnet to maps-osmdb.maps.eqiad.wikimedia.cloud?

The files in project wp-world are in the command line read only for me. I contacted @Nux yesterday how to solve this issue.
Than the change of one line code should be no problem.

@Kolossos have you already tried running become wp-world to switch to the tools.wp-world user? That user should have write access to the files.

Yes I did this. Some of the files have only write access for user kolossos but not for group tools.wp-world.

Hi. I don't think I used wp-world. I changed wiwosm and osm project so I know I have access to them, not sure about wp-world. I checked if I had any mental blackout 😉, but I don't see any ssh connections to wp-world in the history of the ssh shell.

@Kolossos I assume this is about /mnt/nfs/labstore-secondary-tools-project/wp-world, right? I can recommend WinSCP for editing files on Windows if you use that. It shows which files you own as kolossos at a glance, so that makes things a bit easier.

In any case I don't see .git folder there. I can add a repo for wp-world on our Github if you want.

@Nux the blackout was with high probability on my side. I think a repo on github would be very good, would be nice if you could do it.
(BTW: My Desktop is Linux, but i still know WinSCP from the good old times.)

@fnegri I changed the database connection to the new server and it seems to work fine. The numbers of request should now very reduced.

I can confirm wp-world works fine :-)

In T323159#8594794, @Kolossos wrote:

Yes I did this. Some of the files have only write access for user kolossos but not for group tools.wp-world.

Just double-checking on this... you aren't blocked by this, right? You can just chmod/chown as needed?

@fnegri I changed the database connection to the new server and it seems to work fine. The numbers of request should now very reduced.

I can confirm the numbers of queries to osmdb.eqiad.wmnet dropped around 21:00 UTC yesterday. There are very few queries after that time (11 queries in total) and they all look like the following:

select osm_id,name,ST_asKML(ST_Transform(way,4326)) from planet_osm_point where way &&
        ST_Transform(ST_SetSRID(ST_MakeBox2D(ST_Point(143.923645,-38.254358),ST_Point(145.997314,-37.493384)),4326),900913) AND "amenity" like 'bicycle_repair_station'   order by name LIMIT 1000;

select distinct name from planet_osm_line where way &&
        ST_Transform(ST_SetSRID(ST_MakeBox2D(ST_Point(13.5333,50.95),ST_Point(13.9333,51.15)),4326),900913)  AND "highway" is not NULL AND name is not NULL order by
name LIMIT 10000;

These remaining queries all come from 172.16.3.18 (tools-sgeweblight-10-32), so it looks like it's another tool running on the Grid. They are so few, that if we are unable to identify the tool, we can probably take the risk of shutting down the database anyway, and see if anyone complains. :)

Thanks largely to dschwen's hard work, we are about to move the long-neglected postgres osmdb to a volunteer-managed project. Most workloads have already moved to the new service. As far as anyone can tell there is only a single tool still hitting osmdb.eqiad.wmnet.

Later in the week, that tool will break when I finally shut down the eqiad.wmnet domain. If your tool is using that service, please refer to https://phabricator.wikimedia.org/T323159 to coordinate migration.

- Andrew + the WMCS team

I'm planning to stop postgres entirely next Tuesday.

Change 892901 had a related patch set uploaded (by Majavah; author: Majavah):

[operations/dns@master] Remove osmdb records

https://gerrit.wikimedia.org/r/892901

Change 892903 had a related patch set uploaded (by Majavah; author: Majavah):

[operations/puppet@production] openstack: remove osmdb dns records

https://gerrit.wikimedia.org/r/892903

Change 892904 had a related patch set uploaded (by Majavah; author: Majavah):

[operations/puppet@production] P:wmcs: remove osmdb classes

https://gerrit.wikimedia.org/r/892904

Mentioned in SAL (#wikimedia-cloud) [2023-04-04T08:35:44Z] <taavi> revoke puppet certs for now-deleted clouddb1003/4 (osmdb replicas) T323159

Change 892903 merged by Andrew Bogott:

[operations/puppet@production] openstack: remove osmdb dns records

https://gerrit.wikimedia.org/r/892903

Change 892904 merged by Andrew Bogott:

[operations/puppet@production] P:wmcs: remove osmdb classes

https://gerrit.wikimedia.org/r/892904

Change 892901 merged by FNegri:

[operations/dns@master] Remove osmdb records

https://gerrit.wikimedia.org/r/892901