@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.

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 ?

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.

@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. ???)

In T201544#4543960, @Kolossos wrote:

@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.

@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.