Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tBVHZ-004HDc-DA for pgsql-bugs@arkaria.postgresql.org; Thu, 14 Nov 2024 08:32:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tBVHW-002156-Ng for pgsql-bugs@arkaria.postgresql.org; Thu, 14 Nov 2024 08:32:51 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tBIcp-00G7gQ-D8 for pgsql-bugs@lists.postgresql.org; Wed, 13 Nov 2024 19:02:00 +0000 Received: from 10.mo575.mail-out.ovh.net ([46.105.79.203]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tBIcg-001hzY-7E for pgsql-bugs@lists.postgresql.org; Wed, 13 Nov 2024 19:01:58 +0000 Received: from director3.ghost.mail-out.ovh.net (unknown [10.109.176.215]) by mo575.mail-out.ovh.net (Postfix) with ESMTP id 4XpXhB0kn5z1gdY for ; Wed, 13 Nov 2024 19:01:45 +0000 (UTC) Received: from ghost-submission-5b5ff79f4f-87g2f (unknown [10.110.101.166]) by director3.ghost.mail-out.ovh.net (Postfix) with ESMTPS id 692D81FE87 for ; Wed, 13 Nov 2024 19:01:45 +0000 (UTC) Received: from gedeon.li ([37.59.142.96]) by ghost-submission-5b5ff79f4f-87g2f with ESMTPSA id ERSbOJj3NGdVeQIAipwbHQ (envelope-from ) for ; Wed, 13 Nov 2024 19:01:45 +0000 Authentication-Results: garm.ovh; auth=pass (GARM-96R0011c965380-001c-4c73-97c0-d2fb0a423762, D1C9CF0C83CFAD3113C3C5A8CBF4B7B8AEC0589A) smtp.auth=elie@gedeon.li X-OVh-ClientIp: 93.31.68.103 Content-Type: multipart/alternative; boundary="------------66cyuVaOt5DAjZvWpvsH0HUN" Message-ID: Date: Wed, 13 Nov 2024 20:01:44 +0100 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird To: pgsql-bugs@lists.postgresql.org Content-Language: en-US From: Elie Gedeon Subject: Regression from postgresql14 to postgresql17 with postgis (osm2pgsql) X-Ovh-Tracer-Id: 17841291399319932217 X-VR-SPAMSTATE: OK X-VR-SPAMSCORE: 0 X-VR-SPAMCAUSE: gggruggvucftvghtrhhoucdtuddrgeefuddrvddtgdduudelucetufdoteggodetrfdotffvucfrrhhofhhilhgvmecuqfggjfdpvefjgfevmfevgfenuceurghilhhouhhtmecuhedttdenucenucfjughrpegtkfffgggfvffhufesrgdtreertddvjeenucfhrhhomhepgfhlihgvucfivgguvghonhcuoegvlhhivgesghgvuggvohhnrdhliheqnecuggftrfgrthhtvghrnhepgeeiiefffefhhfetkeeugeduueefjeeigefhvedvgeehtdeiheelheejtdevuefgnecuffhomhgrihhnpehophgvnhhsthhrvggvthhmrghprdhorhhgnecukfhppeduvdejrddtrddtrddupdelfedrfedurdeikedruddtfedpfeejrdehledrudegvddrleeinecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehinhgvthepuddvjedrtddrtddruddpmhgrihhlfhhrohhmpegvlhhivgesghgvuggvohhnrdhlihdpnhgspghrtghpthhtohepuddprhgtphhtthhopehpghhsqhhlqdgsuhhgsheslhhishhtshdrphhoshhtghhrvghsqhhlrdhorhhgpdfovfetjfhoshhtpehmohehjeehmgdpmhhouggvpehsmhhtphhouhht DKIM-Signature: a=rsa-sha256; bh=Mcy5gawUw1hoj7JeNidjMKbduBnhIJegvF3ul0fy+ug=; c=relaxed/relaxed; d=gedeon.li; h=From; s=ovhmo5035911-selector1; t=1731524506; v=1; b=IeqPIoKY7HZIF9fJHN64cnzRgl5qXpNSLrusTauzPRm5ednlxhzlrrDRZjO+7GzlE9EKjkVE HAZfRiTI8ffz7ulrMSL+EOClLD4I6704IO+O8a0jFjba0GrfoeaIJwq49zXOJGBM0FwgIozyXV8 Dm8CXwZ3ykVRurKd+2D37Iwbn5DWYwQlJGmEavgroEIA12yunk6/QgetL/CWkjbWsaOV8IUFNZH J6i/vHqC3cqJE0OGF3K4Faw9bb3LrfJJCF9KKwqDzW74d0YiaP3HXR6A6GU+IClTQIeTKTqjg7H vBTv2M1qe89QuDc2yvV0E4Cu50I7BADaiMa2ty3EWXJ1A== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------66cyuVaOt5DAjZvWpvsH0HUN Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 --------------66cyuVaOt5DAjZvWpvsH0HUN Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

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

--------------66cyuVaOt5DAjZvWpvsH0HUN--