Thread: Regression from postgresql14 to postgresql17 with postgis (osm2pgsql)

Hi,

I have what seems to be a regression from postgresql14 to postgresql17

I hope I'm on the right bug tracking list, since I am unsure if bug is from postgresql or osm2pgsql.

/mnt/faststorage is a 2Tb NVME, which seems ok (recent, low write count, no checksum error from btrfs)

Error is repeatable

Error doesn't change if I use a different destination

Ram seem ok (tested with memtester)

System is Ubuntu 24.10

files from https://planet.openstreetmap.org/pbf/

Not working with postgresql17 :

Working with postgresql14

I'm happy to rerun the test if you can tell me what log to enable, my experience with postgresql is quite limited

Thanks and happy debugging :)

Elie

Hi Elie,

> System is Ubuntu 24.10
> [...]
> files from https://planet.openstreetmap.org/pbf/
> Not working with postgresql17 :
>
> [USER@HOST ~]$ sudo pg_createcluster 17 osmdata --datadir=/mnt/faststorage/postgres --port=54372 --pgoption
autovacuum_work_mem='2GB'--pgoption checkpoint_completion_target='0.9' --pgoption checkpoint_timeout='60min' --pgoption
default_statistics_target='500'--pgoption effective_cache_size='10GB' --pgoption effective_io_concurrency='200'
--pgoptionmaintenance_work_mem='10GB' --pgoption min_wal_size='4GB' --pgoption max_connections='40' --pgoption
max_wal_size='12GB'--pgoption max_worker_processes='10' --pgoption max_parallel_workers_per_gather='6' --pgoption
max_parallel_workers='10'--pgoption max_parallel_maintenance_workers='4' --pgoption max_wal_senders='0' --pgoption
random_page_cost='1.0'--pgoption shared_buffers='1GB' --pgoption wal_buffers='16MB' --pgoption wal_level='minimal'
--pgoptionwork_mem='50MB' -- --data-checksums --lc-messages=C --auth-host=scram-sha-256 --auth-local=peer 

Your report is a bit difficult to read but if I understand correctly
you have a single pg_createcluster command that fails. Unfortunately
this is a 3rd party program which is not part of PostgreSQL
distribution. I found a web page that seems to contain emails of its
maintainers [1] but I recommend re-checking it with your local `man
pg_createcluster` or `pg_createcluster --help`.

From what I can tell you are basically trying to create a database
(a.k.a. cluster). I'm surprised to see that a 3rd party program is
needed for this. You may consider doing the same with the tools
provided by PostgreSQL. The documentation [2] will tell you how to do
this, or alternatively take a look at this script [3].

If you discover any problems with our tools and/or find a way to
reproduce your issue with them please let us know.

[1]: https://manpages.ubuntu.com/manpages/trusty/man8/pg_createcluster.8.html
[2]: https://www.postgresql.org/docs/
[3]: https://github.com/afiskon/pgscripts/blob/master/single-install.sh

--
Best regards,
Aleksander Alekseev

Hi again,

> If you discover any problems with our tools and/or find a way to
> reproduce your issue with them please let us know.

On second look your report just doesn't make sense:

```
[USER@HOST ~]$ psql -p 54372 -U $(whoami) osm -c "CREATE EXTENSION hstore;"
CREATE EXTENSION
2024-11-13 12:58:52 osm2pgsql version 1.11.0
2024-11-13 12:58:52 Database version: 17.0 (Ubuntu 17.0-1.pgdg24.10+1)
2024-11-13 12:58:52 PostGIS version: 3.5
2024-11-13 12:58:52 Storing properties to table
'"public"."osm2pgsql_properties"'.
2024-11-13 12:58:52 ERROR: Unable to open flatnode file
'/mnt/faststorage/flatnodes/flatnode-osmdata': No such file or
directory
[USER@HOST ~]$ osm2pgsql --create --database osm --port 54372 --cache
80000 --number-processes 8 Téléchargements/planet-241028.osm.pbf -k
--slim --flat-nodes /mnt/faststorage/flatnodes/flatnode-osmdata
2024-11-13 12:59:55 osm2pgsql version 1.11.0
2024-11-13 12:59:55 Database version: 17.0 (Ubuntu 17.0-1.pgdg24.10+1)
2024-11-13 12:59:55 PostGIS version: 3.5
```

Here you show some output of osm2pgsql before executing it.

You will have to provide a better bug report if you expect any help
from the community. Ideally in terms "I execute steps 1 2 and 3 using
the following tools from the PostgreSQL distribution. On step 3 I get
the result X while the expected result is Y". You'll have to figure
the rest by yourself, sorry.

--
Best regards,
Aleksander Alekseev

On 11/13/24 20:01, Elie Gedeon wrote:
> Hi,
> ...
>
> 2024-11-13 18:15:15 Creating geometry index on table 'planet_osm_line'...
> 2024-11-13 18:32:22 Creating osm_id index on table 'planet_osm_line'...
> WARNING: page verification failed, calculated checksum 27154 but
> expected 30568
> 2024-11-13 18:44:44 Creating geometry index on table 'planet_osm_polygon'...
> 2024-11-13 19:18:28 Creating osm_id index on table 'planet_osm_polygon'...
> WARNING: page verification failed, calculated checksum 63824 but
> expected 53140
> 2024-11-13 19:36:44 Done postprocessing on table 'planet_osm_ways' in
> 7358s (2h 2m 38s)
> 2024-11-13 19:36:44 Done postprocessing on table 'planet_osm_rels' in
> 572s (9m 32s)
> 2024-11-13 19:36:44 All postprocessing on table 'planet_osm_point' done
> in 1852s (30m 52s).
> 2024-11-13 19:36:44 All postprocessing on table 'planet_osm_roads' done
> in 1334s (22m 14s).
> 2024-11-13 19:36:44 ERROR: Database error: ERROR: invalid page in block
> 25652861 of relation base/16385/8057509
> CONTEXT: parallel worker
> 

This suggests you probably have storage issues - notice the 'page
verification failed' warnings, complaining about checksum errors. I
don't know why osm2pgsql doesn't fail right away, maybe it's not
checking the result of that command, or something like that

The most likely explanation for PG14 working is that it doesn't have
checksums enabled. In which case it may have the same issues, it just
doesn't notice that.

Are there any suspicious I/O errors in dmesg, or something like that? I
don't know which filesystem you use, but maybe try with something that
has thorough checksumming (like zfs), or check mount options.


regards

-- 
Tomas Vondra