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

[Stephen D]

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