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

[Stephen - junkmail]

Hi,

I had another power flicker and the DB corrupted again. What's 
interesting is I'm also storing Nominatim in the same DB, yet it hasn't 
gotten corrupted.

Here's what my DB looks like:

  Schema |        Name        | Type  |  Owner   |  Size   | Description
--------+--------------------+-------+----------+---------+-------------
  public | geography_columns  | view  | postgres | 0 bytes |
  public | geometry_columns   | view  | render   | 0 bytes |
  public | planet_osm_line    | table | render   | 16 kB   |
  public | planet_osm_point   | table | render   | 17 GB   |
  public | planet_osm_polygon | table | render   | 16 kB   |
  public | planet_osm_roads   | table | render   | 10 GB   |
  public | raster_columns     | view  | postgres | 0 bytes |
  public | raster_overviews   | view  | postgres | 0 bytes |
  public | spatial_ref_sys    | table | render   | 4360 kB |
(9 rows)

I should also mention that during the import, I don't have enough space 
for indexes to be generated. Instead, the SSD fills up to 100% and 
osm2pgsql says it's done. I manually regenerate the indexes after this 
point, which seems to work fine, even if I reboot the server cleanly.

I also don't run osm updates so I never write to the database.

This might be important:

gis=# select relpersistence, relname from pg_class WHERE relpersistence 
!= 'p';
  relpersistence |          relname
----------------+----------------------------
  u              | planet_osm_polygon_way_idx
  u              | planet_osm_line_way_idx
  u              | pg_toast_37882878
  u              | pg_toast_37882878_index
  u              | planet_osm_line
  u              | pg_toast_37882884_index
  u              | planet_osm_polygon
  u              | pg_toast_37882884
(8 rows)

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?

Thanks,

Stephen

On 2019-08-01 4:49 p.m., Christian Quest wrote:
> Which tables have been truncated to a few KB/MB ?
>
> I've never lost data in PG... except when I used unlogged tables to 
> speed up the initial import, that's why I suggested to look at this.
>
> ALTER TABLE ... SET LOGGED was VERY slow the last time I tried it, so 
> this was not a interesting way to speed up the import and switch to 
> logged tables afterwards.
>
>
> Le jeu. 1 août 2019 à 13:11, Stephen D <junkmail at scd31.com 
> <mailto:junkmail at scd31.com>> a écrit :
>
>     Hi,
>
>     I definitely didn't use the --unlogged option.
>
>     Stephen
>
>     On 2019-07-31 7:35 p.m., Christian Quest wrote:
>>     Check if you have used the "--unlogged" option in osm2pgsql
>>     during your first import.
>>
>>     "--unlogged  Use PostgreSQL's unlogged tables for storing data.
>>     This requires PostgreSQL 9.1 or above. Data written to unlogged
>>     tables is not written to PostgreSQL's write-ahead log, which
>>     makes them considerably faster than ordinary tables. However,
>>     they are not crash-safe: an unlogged table is automatically
>>     truncated after a crash or unclean shutdown."
>>
>>     In case of db crash recovery postgres truncates these tables as
>>     no log journal has been kept for them allowing to make sure
>>     everything is fine.
>>     This option should really be avoided in production and be used
>>     only for a fast (and dirty) import.
>>
>>
>>     Le mer. 31 juil. 2019 à 22:40, Stephen D <junkmail at scd31.com
>>     <mailto:junkmail at scd31.com>> a écrit :
>>
>>         Hi,
>>
>>         I've been running a tileserver for about a month. It's
>>         running on
>>         Postgres, and it's the full planet database. I've had 2 power
>>         failures,
>>         and after both of them the database entered a very weird
>>         state. The hard
>>         drive shows as suddenly having a bunch of free space, and
>>         some of the
>>         tables drop from dozens/hundreds of GB to just a few KB/MB. I
>>         have fsync
>>         on so I'm not sure why this is happening. Any ideas?
>>
>>         Thanks!
>>
>>
>>         _______________________________________________
>>         Tile-serving mailing list
>>         Tile-serving at openstreetmap.org
>>         <mailto:Tile-serving at openstreetmap.org>
>>         https://lists.openstreetmap.org/listinfo/tile-serving
>>
>>
>>
>>     -- 
>>     Christian Quest - OpenStreetMap France
>
>
>
> -- 
> Christian Quest - OpenStreetMap France
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20190804/51041c8e/attachment.html>