[Tile-serving] osm2pgsql DB keeps corrupting on power loss

[Christian Quest]

Reclustering the table geographically is not mandatory, it only reduces
I/Os necessary to read the data for givetn area (I explained this during a
session at SOTM 2014 in Buenos Aires).
For many years, osm2pgsql was not doing this reclustering, it could be done
manually after import or from time to time.
I'm still doing it around once a year.

Switching the table to LOGGED and creating the missing index will allow you
to use it and not loose its content anymore.
Getting a larger SSD is really the simpler option for the future.

Le jeu. 8 août 2019 à 13:32, Stephen D <junkmail at scd31.com> a écrit :

> Hi again,
>
> The import unfortunately still ran out of space, but it got farther. I'm
> missing 1 index, which I'm regenerating now, and 1 table is still
> unlogged (planet_osm_polygon)
>
> I'm regenerating the index now. Afterwards, can I simply run "ALTER
> TABLE planet_osm_polygon SET LOGGED"? I'm assuming no, since the data
> won't be sorted geographically that way.
>
> Thanks,
>
> Stephen
>
> On 2019-08-05 4:28 a.m., Sarah Hoffmann wrote:
> > Hi,
> >
> > On Sun, Aug 04, 2019 at 07:19:06PM -0400, Kevin Kenny wrote:
> >> On Sun, Aug 4, 2019 at 6:30 PM Stephen - junkmail <junkmail at scd31.com>
> wrote:
> >>> If I understand that right, it means planet_osm_polygon and
> planet_osm_line are unlogged. That makes sense as they're the ones being
> corrupted. I am absolutely positive I didn't use the --unlogged option,
> especially when I reimported after I received your email. Is there anything
> else that would cause the tables to be unlogged?
> >> If I recall correctly, the tables are unlogged during the import for
> >> speed. That's ordinarily not a risk, since osm2pgsql doesn't provide
> >> any method to restart an import in progress.
> >>
> >> But what you're doing is to build the indices manually, without
> >> turning logging on at the end, after osm2pgsql has aborted. That's
> >> certain to leave logging turned off, since neither you nor osm2pgsql
> >> has turned it on.
> > To elaborate on that: the data is initially imported into unlogged
> > tables and then copied over into normal tables after the import is
> > finished. This is done so that the final tables are sorted
> > geographically which increases speed for rendering later quite a bit.
> > The obvious disadvantage is that you need twice the disk space when
> > importing.
> >
> > If your import aborted for lack of disk space, then you are not only
> > missing the indexes but your table also wasn't copied into normal
> > tables.
> >
> > Try importing with the --disable-parallel-indexing. This will force
> > the tables to be copied one after another instead of in parallel and
> > should save a bit of space (at the expense of taking a bit longer).
> >
> > Sarah
>
> _______________________________________________
> Tile-serving mailing list
> Tile-serving at openstreetmap.org
> https://lists.openstreetmap.org/listinfo/tile-serving
>


-- 
Christian Quest - OpenStreetMap France
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20190809/68ce7510/attachment-0001.html>