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

[Sarah Hoffmann]

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