My database tables on Postgres -OSM Server are away
Closed, ResolvedPublic

Description

Until some days ago, I had some important data extracts in postgres under:
host: osmdb.eqiad.wmnet
database: gis
User: osm
Tables (relations) like: "wp_coords_red0"

This table are important for maps that are running live in different Wikipedia languages in the project wp-world:
https://de.wikipedia.org/wiki/Wikipedia:WikiProjekt_Georeferenzierung/Anwendungen/OpenStreetMap/en#Open_map_view

Perhaps the data are now in the database "u_kolossos", but this database is not accessable for me.( I need a password that I don't have.

tools.wp-world@tools-bastion-03:~$ psql -h osmdb.eqiad.wmnet -d u_kolossos -U osm
psql: FATAL:  no pg_hba.conf entry for host "10.68.23.58", user "osm", database "u_kolossos", SSL off

Please help fast so that the service can be back soon.

Thanks

Kolossos created this task.Aug 8 2018, 7:07 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptAug 8 2018, 7:07 PM
TheDJ added a subscriber: TheDJ.Aug 9 2018, 6:35 PM
bd808 added a subscriber: bd808.Aug 9 2018, 11:30 PM

@akosiaris is this something that you could help @Kolossos with? It sounds like something that may have not been synced between the postgres boxes as things were moved back and forth.

TheDJ added a comment.EditedAug 24 2018, 8:42 AM

I'm a bit surprised that a database disappeared, and that no one thinks that should be explained/investigated ....
What's going on here ?

If this had been a WMF database, it would have taken 2 hours. Now it takes over a month ?

MSantos moved this task from All map-related tasks to Tracking on the Maps board.Aug 24 2018, 8:47 AM

Looking at dates, this clearly relates to T197246. I'm not 100% sure when the issue existed and didn't exist, but I can say that the server should not have that issue now, I don't think (at least on a quick check of things). @Kolossos could you please confirm if this ticket is current?

@Bstorm : Yes the ticket is still a problem. You can self see that under database "gis" that my tables "wp_coords.." are still missing. This is also the error report of my script[1].
If the data was moved to an other database like u_kolossos a hint would be nice and I would need help how to access the other database (see above).

[1] https://tools.wmflabs.org/wp-world/marks.php?LANG=de&coats=0&thumbs=0&bbox=4.5250842556804,48.722579839863,24.755308865056,53.288761352003

I'm looking through now, and I'm concerned the data is lost due to a misunderstanding during the re-images. I don't think the syncing was working properly at various points, and those need to be reconstructed. I'm sorry.

How can I help with that?

Luckily I have dumps (primarily as service for external users):
Dumps:
https://tools.wmflabs.org/wp-world/dumps/new_red0.gz
Database_Structure_postGIS:
https://de.wikipedia.org/wiki/Wikipedia_Diskussion:WikiProjekt_Georeferenzierung/Hauptseite/Wikipedia-World/news#Database_Structure_postGIS
Further personal SQL-Commands:
https://tools.wmflabs.org/wp-world/load-dispenser2015-2.php

If it's not possible to restore the data by admins, I would need is access to a schema to recreate the tables.

Brilliant! I'm sure we can figure it out.

I'm going through the table creation commands and so forth, and then I'll try to load the dump.

The pub_C_geo_id creation command looks like it's for mariadb/mysql. Does that apply to this database (which is postgres)?

@Kolossos I've created the table in the gis database (with indexes from your page). Haven't quite sorted out how to load the dump, but you might be able to at this point?

I just saw this. Seems like the mistake is mine. I am sorry about that, somehow I missed those tables. Let me know if I can do anything to help.

Kolossos added a comment.EditedAug 29 2018, 11:01 PM

@Bstorm: I think "copy" should import the unpacked file:

copy wp_coords_red0 from '/home/wp-world/new_red0';

Than I can start some some other sql-commands to create the other tables *red2,*red3 and *red4

For me it was never necessary to use my own dumps, so I don't if it's fast to create the indexes after import.

(Or @akosiaris has some backups of the tables. ???)

@Bstorm: I think "copy" should import the unpacked file:

copy wp_coords_red0 from '/home/wp-world/new_red0';

Than I can start some some other sql-commands to create the other tables *red2,*red3 and *red4

For me it was never necessary to use my own dumps, so I don't if it's fast to create the indexes after import.

(Or @akosiaris has some backups of the tables. ???)

No I definitely do not.

@Kolossos I get

ERROR:  value too long for type character varying(40)
CONTEXT:  COPY wp_coords_red0, line 3225, column instance: "Coat of Arms of Tumanny (Murmansk oblast) proposal - 2.png"

So, I figure the original table definitions in the links above may have changed somewhat? That strikes me as the 'arms' column rather than the 'instance' column. huh.

The row is:

fi      Tumannyi        68.88361111     35.69472222     city    \N      \N                              \N      7005    city            Location_of_Kolsky_district_(Murmansk_Oblast).svg       Туманный_июнь_2010_г..jpg       \N      731392  Q1983688        \N      \N      \N      \N      \N \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N \N      Tumanny \N      \N      \N      \N      \N      \N      \N      \N      Tumannyi        \N      \N      \N      \N      \N      \N \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N \N      \N      \N      Տումաննիյ (քաղաքատիպ ավան, Կոլսկի շրջան)        \N      \N      \N      \N      \N      \N      \N      \N      \N \N      Tumannyj        \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N \N      \N      \N      \N      \N      \N      Туманный        \N      \N      \N      \N      \N      \N      \N      \N      \N      \N \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N      \N \N      \N      \N      \N      \N      \N      \N      0101000020E61000002E5A5FA8ECD8414044F59C158D385140      1983688 Coat of Arms of Tumanny (Murmansk oblast) proposal - 2.png      {2989457,192287}        {2989457,192287,15042037,486972,15916867}

The table is defined exactly as in the link. I wonder if I need to set an encoding or something.

Bstorm added a comment.Sep 5 2018, 9:52 PM

@akosiaris Do you have any thoughts on the above? I might just try downloading it again and trying again.

Sorry I was longer time AFK.

I believe in the table creation command the last lines have to be a little bit different:

 ...
the_geom geometry,
wd_id double precision,
arms character varying(255),
classes integer[],
superclasses integer[]

So the last columns should match.

P.S. For Monday to Wednesday I also traveling.

Ok, the wp_coords_red0 table is restored from that dump in the gis DB at this point. How for does that get us? @Kolossos

It works now fine high zoom levels. Thanks.

I started over night now some SQl-commands to generate the reduced tables for low zoom levels.

Kolossos closed this task as Resolved.Sep 13 2018, 7:21 AM

Fine it's working. Thanks.