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

[Sarah Hoffmann]

On Thu, Aug 08, 2019 at 08:31:28AM -0300, Stephen D wrote:
> 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)

Interesting. I would have expected that the dropped middle tables give
you enough space to do the copies. I trust that you are using the latest
version of osm2pgsql and run the import with --drop?

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

You could do it. The geographic sorting is really just an optimisation.
The original tables are otherwise fine. However, my understanding is that
tables can't just be converted and a 'SET LOGGED' internally also results
in making a copy of the table. In that case you likely run out of disk
space again.

At this point I would really recommend buying a larger disk. If that is
not an option, have a look at compressed zfs. People have reported that
it plays well with the osm2pgsql import.

Sarah

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