From: Elie Gedeon <elie@gedeon.li>
To: pgsql-bugs@lists.postgresql.org
Subject: Regression from postgresql14 to postgresql17 with postgis (osm2pgsql)
Date: Wed, 13 Nov 2024 20:01:44 +0100
Message-ID: <fb9f522d-e041-44b7-ac24-f4e9c6574160@gedeon.li> (raw)
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 :
[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'--pgoption
maintenance_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'--pgoption work_mem='50MB'-- --data-checksums
--lc-messages=C --auth-host=scram-sha-256 --auth-local=peer
Creating new PostgreSQL cluster 17/osmdata ...
/usr/lib/postgresql/17/bin/initdb -D /mnt/faststorage/postgres
--no-instructions --data-checksums --lc-messages=C
--auth-host=scram-sha-256 --auth-local=peer
Les fichiers de ce système de bases de données appartiendront à
l'utilisateur « postgres ».
Le processus serveur doit également lui appartenir.
L'instance sera initialisée avec cette configuration de locale :
fournisseur de locale : libc
LC_COLLATE: fr_FR.UTF-8
LC_CTYPE: fr_FR.UTF-8
LC_MESSAGES: C
LC_MONETARY: fr_FR.UTF-8
LC_NUMERIC: fr_FR.UTF-8
LC_TIME: fr_FR.UTF-8
L'encodage par défaut des bases de données a été configuré en conséquence
avec « UTF8 ».
La configuration de la recherche plein texte a été initialisée à « french ».
Les sommes de contrôle des pages de données sont activées.
correction des droits sur le répertoire existant
/mnt/faststorage/postgres... ok
création des sous-répertoires... ok
sélection de l'implémentation de la mémoire partagée dynamique...posix
sélection de la valeur par défaut pour « max_connections »... 100
sélection de la valeur par défaut pour « shared_buffers »... 128MB
sélection du fuseau horaire par défaut... Europe/Paris
création des fichiers de configuration... ok
lancement du script bootstrap...ok
exécution de l'initialisation après bootstrap... ok
synchronisation des données sur disque... ok
Ver Cluster Port Status Owner Data directory Log file
17 osmdata 54372 down postgres /mnt/faststorage/postgres
/var/log/postgresql/postgresql-17-osmdata.log
[USER@HOST ~]$ sudo systemctl start postgresql@17-osmdata
[USER@HOST ~]$ sudo -u postgres createuser -p 54372 --createdb
--pwprompt --superuser "$(whoami)"
Saisir le mot de passe pour le nouveau rôle :
Saisir le mot de passe à nouveau :
[USER@HOST ~]$ createdb --owner $(whoami) --port 54372 --encoding=UTF8 osm
[USER@HOST ~]$ psql -p 54372 -U $(whoami) osm -c "CREATE EXTENSION postgis;"
CREATE EXTENSION
[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
2024-11-13 12:59:55 Storing properties to table
'"public"."osm2pgsql_properties"'.
2024-11-13 12:59:55 Setting up table 'planet_osm_point'
2024-11-13 12:59:55 Setting up table 'planet_osm_line'
2024-11-13 12:59:55 Setting up table 'planet_osm_polygon'
2024-11-13 12:59:55 Setting up table 'planet_osm_roads'
2024-11-13 17:33:56 Reading input files done in 16441s (4h 34m 1s).
2024-11-13 17:33:56 Processed 9475372292 nodes in 1006s (16m 46s) - 9419k/s
2024-11-13 17:33:56 Processed 1059436515 ways in 7897s (2h 11m 37s) - 134k/s
2024-11-13 17:33:56 Processed 12649514 relations in 7538s (2h 5m 38s) - 2k/s
2024-11-13 17:33:57 Clustering table 'planet_osm_line' by geometry...
2024-11-13 17:33:57 Clustering table 'planet_osm_point' by geometry...
2024-11-13 17:34:05 Clustering table 'planet_osm_roads' by geometry...
2024-11-13 17:34:05 Clustering table 'planet_osm_polygon' by geometry...
2024-11-13 17:34:05 Done postprocessing on table 'planet_osm_nodes' in 0s
2024-11-13 17:34:05 Building index on table 'planet_osm_rels'
2024-11-13 17:34:05 Building index on table 'planet_osm_ways'
2024-11-13 17:46:53 Creating geometry index on table 'planet_osm_point'...
2024-11-13 17:50:01 Creating geometry index on table 'planet_osm_roads'...
2024-11-13 17:55:54 Creating osm_id index on table 'planet_osm_roads'...
2024-11-13 17:56:02 Analyzing table 'planet_osm_roads'...
2024-11-13 18:03:53 Creating osm_id index on table 'planet_osm_point'...
2024-11-13 18:04:48 Analyzing table 'planet_osm_point'...
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
Working with postgresql14
[USER@HOST ~]$ sudo pg_createcluster 14 osmdata
--datadir=/mnt/faststorage/postgres --port=54342 --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' --pgoption
maintenance_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' --pgoption
work_mem='50MB' -- --data-checksums --lc-messages=C
--auth-host=scram-sha-256 --auth-local=peer
Creating new PostgreSQL cluster 14/osmdata ...
/usr/lib/postgresql/14/bin/initdb -D /mnt/faststorage/postgres
--no-instructions --data-checksums --lc-messages=C
--auth-host=scram-sha-256 --auth-local=peer
Les fichiers de ce système de bases de données appartiendront à
l'utilisateur « postgres ».
Le processus serveur doit également lui appartenir.
L'instance sera initialisée avec les locales
COLLATE: fr_FR.UTF-8
CTYPE: fr_FR.UTF-8
MESSAGES: C
MONETARY: fr_FR.UTF-8
NUMERIC: fr_FR.UTF-8
TIME: fr_FR.UTF-8
L'encodage par défaut des bases de données a été configuré en conséquence
avec « UTF8 ».
La configuration de la recherche plein texte a été initialisée à « french ».
Les sommes de contrôle des pages de données sont activées.
correction des droits sur le répertoire existant
/mnt/faststorage/postgres... ok
création des sous-répertoires... ok
sélection de l'implémentation de la mémoire partagée dynamique...posix
sélection de la valeur par défaut pour max_connections... 100
sélection de la valeur par défaut pour shared_buffers... 128MB
sélection du fuseau horaire par défaut... Europe/Paris
création des fichiers de configuration... ok
lancement du script bootstrap...ok
exécution de l'initialisation après bootstrap... ok
synchronisation des données sur disque... ok
Ver Cluster Port Status Owner Data directory Log file
14 osmdata 54342 down postgres /mnt/faststorage/postgres
/var/log/postgresql/postgresql-14-osmdata.log
[USER@HOST ~]$ sudo systemctl start postgresql@14-osmdata
[USER@HOST ~]$ sudo -u postgres createuser -p 54342 --createdb
--pwprompt --superuser "$(whoami)"
could not change directory to "/home/phi": Permission non accordée
Enter password for new role:
Enter it again:
[USER@HOST ~]$ createdb --owner $(whoami) --port 54342 --encoding=UTF8 osm
[USER@HOST ~]$ psql -p 54342 -U $(whoami) osm -c "CREATE EXTENSION postgis;"
CREATE EXTENSION
[USER@HOST ~]$ psql -p 54342 -U $(whoami) osm -c "CREATE EXTENSION hstore;"
CREATE EXTENSION
[USER@HOST ~]$ osm2pgsql --create --database osm --port 54342 --cache
80000 --number-processes 8 Téléchargements/planet-241028.osm.pbf -k
--slim --flat-nodes /mnt/faststorage/flatnodes/flatnode-osmdata
2024-11-12 01:12:40 osm2pgsql version 1.11.0
2024-11-12 01:12:40 Database version: 14.13 (Ubuntu 14.13-1.pgdg24.10+1)
2024-11-12 01:12:40 PostGIS version: 3.5
2024-11-12 01:12:40 Storing properties to table
'"public"."osm2pgsql_properties"'.
2024-11-12 01:12:40 Setting up table 'planet_osm_point'
2024-11-12 01:12:40 Setting up table 'planet_osm_line'
2024-11-12 01:12:40 Setting up table 'planet_osm_polygon'
2024-11-12 01:12:40 Setting up table 'planet_osm_roads'
2024-11-12 07:20:17 Reading input files done in 22057s (6h 7m 37s).
2024-11-12 07:20:17 Processed 9475372292 nodes in 1086s (18m 6s) - 8725k/s
2024-11-12 07:20:17 Processed 1059436515 ways in 8056s (2h 14m 16s) - 132k/s
2024-11-12 07:20:17 Processed 12649514 relations in 12915s (3h 35m 15s)
- 979/s
2024-11-12 07:20:17 Clustering table 'planet_osm_point'by geometry...
2024-11-12 07:20:17 Clustering table 'planet_osm_line'by geometry...
2024-11-12 07:20:22 Clustering table 'planet_osm_polygon'by geometry...
2024-11-12 07:20:22 Clustering table 'planet_osm_roads'by geometry...
2024-11-12 07:20:23 Done postprocessing on table 'planet_osm_nodes'in 0s
2024-11-12 07:20:23 Building index on table 'planet_osm_ways'
2024-11-12 07:20:23 Building index on table 'planet_osm_rels'
2024-11-12 07:34:53 Creating geometry index on table 'planet_osm_point'...
2024-11-12 07:45:05 Creating geometry index on table 'planet_osm_roads'...
2024-11-12 07:57:07 Creating osm_id index on table 'planet_osm_roads'...
2024-11-12 07:57:21 Analyzing table 'planet_osm_roads'...
2024-11-12 08:24:33 Creating geometry index on table 'planet_osm_line'...
2024-11-12 08:26:12 Creating osm_id index on table 'planet_osm_point'...
2024-11-12 08:27:13 Analyzing table 'planet_osm_point'...
2024-11-12 09:16:22 Creating osm_id index on table 'planet_osm_line'...
2024-11-12 09:18:05 Analyzing table 'planet_osm_line'...
2024-11-12 09:29:04 Creating geometry index on table 'planet_osm_polygon'...
2024-11-12 09:30:36 Done postprocessing on table 'planet_osm_ways'in
7812s (2h 10m 12s)
2024-11-12 09:30:36 Done postprocessing on table 'planet_osm_rels'in
715s (11m 55s)
2024-11-12 09:30:36 All postprocessing on table 'planet_osm_point'done
in 4020s (1h 7m 0s).
2024-11-12 09:30:36 All postprocessing on table 'planet_osm_line'done in
7075s (1h 57m 55s).
2024-11-12 11:15:31 Creating osm_id index on table 'planet_osm_polygon'...
2024-11-12 11:18:32 Analyzing table 'planet_osm_polygon'...
2024-11-12 11:18:36 All postprocessing on table 'planet_osm_polygon'done
in 14298s (3h 58m 18s).
2024-11-12 11:18:36 All postprocessing on table 'planet_osm_roads'done
in 2226s (37m 6s).
2024-11-12 11:18:36 Storing properties to table
'"public"."osm2pgsql_properties"'.
2024-11-12 11:18:36 osm2pgsql took 36355s (10h 5m 55s) overall.
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: elie@gedeon.li, pgsql-bugs@lists.postgresql.org
Subject: Re: Regression from postgresql14 to postgresql17 with postgis (osm2pgsql)
In-Reply-To: <fb9f522d-e041-44b7-ac24-f4e9c6574160@gedeon.li>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox