Thread
-
Please recommend postgresql.conf improvements for osm2pgsql loading Europe
Alexander Farber <alexander.farber@gmail.com> — 2024-03-29T19:53:08Z
Good evening, I use the following postgresql.conf in my Dockerfile ( the full version at https://stackoverflow.com/a/78243530/165071 ), when loading a 28 GByte large europe-latest.osm.pbf into PostgreSQL 16 with PostGIS extension: echo "shared_buffers = 1GB" >> $PGDATA/postgresql.conf && \ echo "work_mem = 50MB" >> $PGDATA/postgresql.conf && \ echo "maintenance_work_mem = 10GB" >> $PGDATA/postgresql.conf && \ echo "autovacuum_work_mem = 2GB" >> $PGDATA/postgresql.conf && \ echo "wal_level = minimal" >> $PGDATA/postgresql.conf && \ echo "checkpoint_timeout = 60min" >> $PGDATA/postgresql.conf && \ echo "max_wal_size = 10GB" >> $PGDATA/postgresql.conf && \ echo "checkpoint_completion_target = 0.9" >> $PGDATA/postgresql.conf && \ echo "max_wal_senders = 0" >> $PGDATA/postgresql.conf && \ echo "random_page_cost = 1.0" >> $PGDATA/postgresql.conf && \ echo "password_encryption = scram-sha-256" >> $PGDATA/postgresql.conf && \ And after/during osm2pgsql is loading the data into the database, the following messages are displayed by PostgreSQL: 2024-03-29T14:50:19.6909027Z 2024-03-29 14:50:19 Processed 3355283586 nodes in 432s (7m 12s) - 7767k/s 2024-03-29T14:50:19.6909478Z 2024-03-29 14:50:19 Processed 406646166 ways in 2720s (45m 20s) - 150k/s 2024-03-29T14:50:19.6909785Z 2024-03-29 14:50:19 Processed 7265827 relations in 672s (11m 12s) - 11k/s 2024-03-29T14:50:20.0292733Z [0m [91m2024-03-29 14:50:20 node cache: stored: 3355283586(100.00%), storage efficiency: 56.37% (dense blocks: 268264, sparse nodes: 1877553733), hit rate: 100.00% 2024-03-29T14:50:20.0293430Z 2024-03-29 14:50:20 Clustering table 'planet_osm_line' by geometry... 2024-03-29T14:50:20.0293787Z 2024-03-29 14:50:20 Clustering table 'planet_osm_point' by geometry... 2024-03-29T14:50:20.1056438Z [0m [91m2024-03-29 14:50:20 Clustering table 'planet_osm_polygon' by geometry... 2024-03-29T14:50:20.1056819Z 2024-03-29 14:50:20 Clustering table 'planet_osm_roads' by geometry... 2024-03-29T15:15:27.3834184Z [0m [91m2024-03-29 15:15:24 Creating geometry index on table 'planet_osm_roads'... 2024-03-29T15:15:56.7850293Z [0m2024-03-29 15:15:56.784 UTC [344] LOG: skipping vacuum of "planet_osm_roads" --- lock not available 2024-03-29T15:16:56.7864755Z 2024-03-29 15:16:56.785 UTC [347] LOG: skipping vacuum of "planet_osm_roads" --- lock not available 2024-03-29T15:17:56.7891503Z 2024-03-29 15:17:56.788 UTC [350] LOG: skipping vacuum of "planet_osm_roads" --- lock not available 2024-03-29T15:18:16.5830638Z [91m2024-03-29 15:18:16 Analyzing table 'planet_osm_roads'... 2024-03-29T15:18:18.1485666Z [0m [91m2024-03-29 15:18:18 All postprocessing on table 'planet_osm_roads' done in 1678s (27m 58s). 2024-03-29T15:22:17.1463669Z [0m [91m2024-03-29 15:22:17 Creating geometry index on table 'planet_osm_point'... 2024-03-29T15:22:56.8860848Z [0m2024-03-29 15:22:56.885 UTC [365] LOG: skipping vacuum of "planet_osm_point" --- lock not available 2024-03-29T15:23:56.8890669Z 2024-03-29 15:23:56.888 UTC [368] LOG: skipping vacuum of "planet_osm_point" --- lock not available 2024-03-29T15:24:56.9521355Z 2024-03-29 15:24:56.951 UTC [371] LOG: skipping vacuum of "planet_osm_point" --- lock not available 2024-03-29T15:25:56.9166127Z 2024-03-29 15:25:56.915 UTC [374] LOG: skipping vacuum of "planet_osm_point" --- lock not available 2024-03-29T15:26:57.4606148Z 2024-03-29 15:26:57.460 UTC [377] LOG: skipping vacuum of "planet_osm_point" --- lock not available 2024-03-29T15:27:56.9555832Z 2024-03-29 15:27:56.955 UTC [380] LOG: skipping vacuum of "planet_osm_point" --- lock not available 2024-03-29T15:28:56.9718398Z 2024-03-29 15:28:56.971 UTC [383] LOG: skipping vacuum of "planet_osm_point" --- lock not available 2024-03-29T15:29:56.9912768Z 2024-03-29 15:29:56.990 UTC [386] LOG: skipping vacuum of "planet_osm_point" --- lock not available 2024-03-29T15:30:57.1482580Z 2024-03-29 15:30:57.147 UTC [389] LOG: skipping vacuum of "planet_osm_point" --- lock not available 2024-03-29T15:31:01.2674499Z [91m2024-03-29 15:31:01 Analyzing table 'planet_osm_point'... 2024-03-29T15:31:02.3310950Z [0m [91m2024-03-29 15:31:02 All postprocessing on table 'planet_osm_point' done in 2442s (40m 42s). 2024-03-29T15:41:13.1544481Z [0m [91m2024-03-29 15:41:13 Creating geometry index on table 'planet_osm_line'... 2024-03-29T15:41:57.2434689Z [0m2024-03-29 15:41:57.242 UTC [422] LOG: skipping vacuum of "planet_osm_line" --- lock not available 2024-03-29T15:42:57.2519273Z 2024-03-29 15:42:57.251 UTC [425] LOG: skipping vacuum of "planet_osm_line" --- lock not available 2024-03-29T15:43:57.2626514Z 2024-03-29 15:43:57.262 UTC [428] LOG: skipping vacuum of "planet_osm_line" --- lock not available 2024-03-29T15:44:57.2531401Z 2024-03-29 15:44:57.252 UTC [431] LOG: skipping vacuum of "planet_osm_line" --- lock not available 2024-03-29T15:45:57.2515330Z 2024-03-29 15:45:57.251 UTC [434] LOG: skipping vacuum of "planet_osm_line" --- lock not available 2024-03-29T15:46:35.3182906Z 2024-03-29 15:46:35.317 UTC [28] LOG: checkpoint starting: time 2024-03-29T15:46:57.2509511Z 2024-03-29 15:46:57.250 UTC [437] LOG: skipping vacuum of "planet_osm_line" --- lock not available 2024-03-29T15:47:58.3610817Z 2024-03-29 15:47:58.360 UTC [440] LOG: skipping vacuum of "planet_osm_line" --- lock not available 2024-03-29T15:48:57.2897759Z 2024-03-29 15:48:57.289 UTC [443] LOG: skipping vacuum of "planet_osm_line" --- lock not available 2024-03-29T15:49:57.9056709Z 2024-03-29 15:49:57.905 UTC [446] LOG: skipping vacuum of "planet_osm_line" --- lock not available 2024-03-29T15:49:59.6678449Z [91m2024-03-29 15:49:59 Analyzing table 'planet_osm_line'... 2024-03-29T15:50:09.6412981Z [0m [91m2024-03-29 15:50:09 All postprocessing on table 'planet_osm_line' done in 3589s (59m 49s). 2024-03-29T15:54:38.1250032Z [0m [91m2024-03-29 15:54:38 Creating geometry index on table 'planet_osm_polygon'... 2024-03-29T15:54:57.5225406Z [0m2024-03-29 15:54:57.522 UTC [461] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available 2024-03-29T15:55:57.4751735Z 2024-03-29 15:55:57.474 UTC [464] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available 2024-03-29T15:56:57.4952251Z 2024-03-29 15:56:57.494 UTC [467] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available 2024-03-29T15:57:57.6161474Z 2024-03-29 15:57:57.615 UTC [470] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available 2024-03-29T15:58:57.5342965Z 2024-03-29 15:58:57.533 UTC [473] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available 2024-03-29T15:59:57.5535374Z 2024-03-29 15:59:57.553 UTC [476] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available 2024-03-29T16:00:57.5727969Z 2024-03-29 16:00:57.572 UTC [479] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available 2024-03-29T16:01:57.5920798Z 2024-03-29 16:01:57.591 UTC [482] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available 2024-03-29T16:02:18.0375710Z 2024-03-29 16:02:18.036 UTC [449] LOG: automatic vacuum of table "osm_database.public.planet_osm_line": index scans: 0 2024-03-29T16:02:18.0376138Z pages: 0 removed, 4766720 remain, 4766720 scanned (100.00% of total) 2024-03-29T16:02:18.0376345Z tuples: 0 removed, 118178562 remain, 0 are dead but not yet removable 2024-03-29T16:02:18.0378017Z removable cutoff: 18260, which was 20 XIDs old when operation ended 2024-03-29T16:02:18.0378243Z frozen: 0 pages from table (0.00% of total) had 0 tuples frozen 2024-03-29T16:02:18.0378408Z index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed 2024-03-29T16:02:18.0382989Z index "planet_osm_line_way_idx": pages: 577427 in total, 0 newly deleted, 0 currently deleted, 0 reusable 2024-03-29T16:02:18.0383168Z avg read rate: 61.347 MB/s, avg write rate: 0.013 MB/s 2024-03-29T16:02:18.0383306Z buffer usage: 4766334 hits, 5344899 misses, 1175 dirtied 2024-03-29T16:02:18.0383451Z WAL usage: 2 records, 0 full page images, 417 bytes 2024-03-29T16:02:18.0383600Z system usage: CPU: user: 10.94 s, system: 19.27 s, elapsed: 680.67 s 2024-03-29T16:02:57.6121504Z 2024-03-29 16:02:57.611 UTC [485] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available 2024-03-29T16:03:57.8261200Z 2024-03-29 16:03:57.825 UTC [488] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available 2024-03-29T16:04:57.6506943Z 2024-03-29 16:04:57.650 UTC [491] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available 2024-03-29T16:05:57.6702697Z 2024-03-29 16:05:57.669 UTC [494] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available 2024-03-29T16:06:57.6731793Z 2024-03-29 16:06:57.672 UTC [497] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available 2024-03-29T16:07:57.6929376Z 2024-03-29 16:07:57.692 UTC [500] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available 2024-03-29T16:08:57.7118933Z 2024-03-29 16:08:57.711 UTC [503] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available 2024-03-29T16:09:27.9176268Z 2024-03-29 16:09:27.917 UTC [461] LOG: automatic vacuum of table "osm_database.pg_toast.pg_toast_3528119": index scans: 0 2024-03-29T16:09:27.9177696Z pages: 0 removed, 1122287 remain, 1122287 scanned (100.00% of total) 2024-03-29T16:09:27.9178122Z tuples: 0 removed, 5101444 remain, 0 are dead but not yet removable 2024-03-29T16:09:27.9178409Z removable cutoff: 18279, which was 1 XIDs old when operation ended 2024-03-29T16:09:27.9178607Z new relfrozenxid: 18263, which is 3 XIDs ahead of previous value 2024-03-29T16:09:27.9180150Z frozen: 0 pages from table (0.00% of total) had 0 tuples frozen 2024-03-29T16:09:27.9180404Z index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed 2024-03-29T16:09:27.9180572Z avg read rate: 7.433 MB/s, avg write rate: 10.076 MB/s 2024-03-29T16:09:27.9180728Z buffer usage: 1416563 hits, 828117 misses, 1122600 dirtied 2024-03-29T16:09:27.9182167Z WAL usage: 1122288 records, 35 full page images, 66502016 bytes 2024-03-29T16:09:27.9182354Z system usage: CPU: user: 4.05 s, system: 7.38 s, elapsed: 870.39 s 2024-03-29T16:09:57.7317530Z 2024-03-29 16:09:57.731 UTC [506] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available 2024-03-29T16:10:57.7507241Z 2024-03-29 16:10:57.750 UTC [509] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available 2024-03-29T16:11:57.7701269Z 2024-03-29 16:11:57.769 UTC [512] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available 2024-03-29T16:12:57.7790805Z 2024-03-29 16:12:57.778 UTC [515] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available 2024-03-29T16:13:57.7991903Z 2024-03-29 16:13:57.798 UTC [518] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available 2024-03-29T16:14:03.8618112Z [91m2024-03-29 16:14:03 Analyzing table 'planet_osm_polygon'... 2024-03-29T16:14:04.5346120Z [0m [91m2024-03-29 16:14:04 All postprocessing on table 'planet_osm_polygon' done in 5024s (1h 23m 44s). 2024-03-29T16:14:04.5612319Z [0m [91m2024-03-29 16:14:04 osm2pgsql took 8849s (2h 27m 29s) overall. 2024-03-29T16:14:07.3275177Z [0m [91m+ rm -f /data/map.osm.pbf 2024-03-29T16:14:07.3309732Z [0m [91m+ pg_ctl stop 2024-03-29T16:14:07.3321751Z [0m2024-03-29 16:14:07.331 UTC [27] LOG: received fast shutdown request 2024-03-29T16:14:07.3369440Z waiting for server to shut down....2024-03-29 16:14:07.336 UTC [27] LOG: aborting any active transactions 2024-03-29T16:14:07.3378562Z 2024-03-29 16:14:07.337 UTC [27] LOG: background worker "logical replication launcher" (PID 33) exited with exit code 1 2024-03-29T16:14:07.4013492Z 2024-03-29 16:14:07.400 UTC [28] LOG: checkpoint complete: wrote 24 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=1652.023 s, sync=0.034 s, total=1652.084 s; sync files=227, longest=0.007 s, average=0.001 s; distance=721 kB, estimate=9467 kB; lsn=0/6519650, redo lsn=0/1FCCA88 2024-03-29T16:14:07.4014432Z 2024-03-29 16:14:07.400 UTC [28] LOG: shutting down 2024-03-29T16:14:07.4048807Z 2024-03-29 16:14:07.404 UTC [28] LOG: checkpoint starting: shutdown immediate 2024-03-29T16:14:20.1091481Z ............2024-03-29 16:14:20.108 UTC [28] LOG: checkpoint complete: wrote 101114 buffers (77.1%); 0 WAL file(s) added, 0 removed, 5 recycled; write=0.366 s, sync=12.305 s, total=12.708 s; sync files=19, longest=3.837 s, average=0.648 s; distance=70963 kB, estimate=70963 kB; lsn=0/65196C8, redo lsn=0/65196C8 2024-03-29T16:14:20.1897519Z 2024-03-29 16:14:20.189 UTC [27] LOG: database system is shut down 2024-03-29T16:14:20.2497852Z done 2024-03-29T16:14:20.2498193Z server stopped Is anybody please able to spot any improvements I could apply to the postgresql.conf config values at the top of my mail, that could reduce the loading time of almost 2 hours? Thank you for any hints Alex -
Re: Please recommend postgresql.conf improvements for osm2pgsql loading Europe
Justin Clift <justin@postgresql.org> — 2024-03-30T03:33:42Z
On 2024-03-30 05:53, Alexander Farber wrote: <snip> > I use the following postgresql.conf in my Dockerfile > ( the full version at https://stackoverflow.com/a/78243530/165071 ), > when loading a 28 GByte large europe-latest.osm.pbf <snip> > Is anybody please able to spot any improvements I could > apply to the postgresql.conf config values at the top of my mail, > that could reduce the loading time of almost 2 hours? Not specific conf file improvements, but for an initial data load have you done things like turning off fsync(), deferring index creating until after the data load finishes, and that kind of thing? You don't want fsync() off when you're using the database in production, but for long data load scenarios it seems like it'd be a decent fit. With .pbf files, from skimming over how they're described here: https://wiki.openstreetmap.org/wiki/PBF_Format ... they don't seem to be optimised for loading into a database. (?) It kind of looks like they'd be stored into individual records, which probably means they'd be getting imported as individual INSERT statements rather than something that's optimised for bulk loading. :( Regards and best wishes, Justin Clift
-
Re: Please recommend postgresql.conf improvements for osm2pgsql loading Europe
Alexander Farber <alexander.farber@gmail.com> — 2024-03-30T10:04:11Z
Thank you, Justin - On Sat, Mar 30, 2024 at 4:33 AM Justin Clift <justin@postgresql.org> wrote: > On 2024-03-30 05:53, Alexander Farber wrote: > > I use the following postgresql.conf in my Dockerfile > > ( the full version at https://stackoverflow.com/a/78243530/165071 ), > > when loading a 28 GByte large europe-latest.osm.pbf > > Not specific conf file improvements, but for an initial data load > have you done things like turning off fsync(), deferring index > creating until after the data load finishes, and that kind of thing? > I will try the following commands in my Dockerfile then and later report back on any improvements: RUN set -eux && \ pg_ctl init && \ echo "shared_buffers = 1GB" >> $PGDATA/postgresql.conf && \ echo "work_mem = 50MB" >> $PGDATA/postgresql.conf && \ echo "maintenance_work_mem = 10GB" >> $PGDATA/postgresql.conf && \ echo "autovacuum_work_mem = 2GB" >> $PGDATA/postgresql.conf && \ echo "wal_level = minimal" >> $PGDATA/postgresql.conf && \ echo "checkpoint_timeout = 60min" >> $PGDATA/postgresql.conf && \ echo "max_wal_size = 10GB" >> $PGDATA/postgresql.conf && \ echo "checkpoint_completion_target = 0.9" >> $PGDATA/postgresql.conf && \ echo "max_wal_senders = 0" >> $PGDATA/postgresql.conf && \ echo "random_page_cost = 1.0" >> $PGDATA/postgresql.conf && \ echo "password_encryption = scram-sha-256" >> $PGDATA/postgresql.conf && \ echo "fsync = off" >> $PGDATA/postgresql.conf && \ pg_ctl start && \ createuser --username=postgres $PGUSER && \ createdb --username=postgres --encoding=UTF8 --owner=$PGUSER $PGDATABASE && \ psql --username=postgres $PGDATABASE --command="ALTER USER $PGUSER WITH PASSWORD '$PGPASSWORD';" && \ psql --username=postgres $PGDATABASE --command='CREATE EXTENSION IF NOT EXISTS postgis;' && \ psql --username=postgres $PGDATABASE --command='CREATE EXTENSION IF NOT EXISTS hstore;' && \ osm2pgsql --username=$PGUSER --database=$PGDATABASE --create --cache=60000 --hstore --latlong /data/map.osm.pbf && \ rm -f /data/map.osm.pbf && \ pg_ctl stop && \ echo "fsync = on" >> $PGDATA/postgresql.conf && \ echo '# TYPE DATABASE USER ADDRESS METHOD' > $PGDATA/pg_hba.conf && \ echo "local all postgres peer" >> $PGDATA/pg_hba.conf && \ echo "local $PGDATABASE $PGUSER scram-sha-256" >> $PGDATA/pg_hba.conf && \ echo "host $PGDATABASE $PGUSER 0.0.0.0/0 scram-sha-256" >> $PGDATA/pg_hba.conf The later fsync = on will override the former, right? Best regards Alex -
Re: Please recommend postgresql.conf improvements for osm2pgsql loading Europe
Alexander Farber <alexander.farber@gmail.com> — 2024-03-30T18:07:38Z
Turning fsync = off has resulted in no noticable build time reduction for my Dockerfile with OSM Europe data, but still thank you for the suggestion! >
-
Re: Please recommend postgresql.conf improvements for osm2pgsql loading Europe
Justin Clift <justin@postgresql.org> — 2024-03-31T07:31:28Z
On 2024-03-31 04:07, Alexander Farber wrote: > Turning fsync = off has resulted in no noticable build time reduction > for > my Dockerfile with OSM Europe data, but still thank you for the > suggestion! No worries. :) With this import you're doing, is it something that will be repeated a lot with the exact same data set, or is this a once off thing? If it's something that'll be repeated a lot (maybe part of some automated process?), then it might be worth making a backup / snapshot / something of the database after the import has completed. With a backup or snapshot in place (depends on the storage you're using), you could potentially load things from that backup / snapshot (etc) instead of having to do the import all over again each time. Regards and best wishes, Justin Clift
-
Re: Please recommend postgresql.conf improvements for osm2pgsql loading Europe
hector vass <hector.vass@gmail.com> — 2024-03-31T15:55:02Z
On Sat, 30 Mar 2024, 10:04 Alexander Farber, <alexander.farber@gmail.com> wrote: > Thank you, Justin - > > On Sat, Mar 30, 2024 at 4:33 AM Justin Clift <justin@postgresql.org> > wrote: > >> On 2024-03-30 05:53, Alexander Farber wrote: >> > I use the following postgresql.conf in my Dockerfile >> > ( the full version at https://stackoverflow.com/a/78243530/165071 ), >> > when loading a 28 GByte large europe-latest.osm.pbf >> >> Not specific conf file improvements, but for an initial data load >> have you done things like turning off fsync(), deferring index >> creating until after the data load finishes, and that kind of thing? >> > > I will try the following commands in my Dockerfile then > and later report back on any improvements: > > RUN set -eux && \ > pg_ctl init && \ > echo "shared_buffers = 1GB" >> $PGDATA/postgresql.conf > && \ > echo "work_mem = 50MB" >> $PGDATA/postgresql.conf > && \ > echo "maintenance_work_mem = 10GB" >> $PGDATA/postgresql.conf > && \ > echo "autovacuum_work_mem = 2GB" >> $PGDATA/postgresql.conf > && \ > echo "wal_level = minimal" >> $PGDATA/postgresql.conf > && \ > echo "checkpoint_timeout = 60min" >> $PGDATA/postgresql.conf > && \ > echo "max_wal_size = 10GB" >> $PGDATA/postgresql.conf > && \ > echo "checkpoint_completion_target = 0.9" >> $PGDATA/postgresql.conf > && \ > echo "max_wal_senders = 0" >> $PGDATA/postgresql.conf > && \ > echo "random_page_cost = 1.0" >> $PGDATA/postgresql.conf > && \ > echo "password_encryption = scram-sha-256" >> $PGDATA/postgresql.conf > && \ > echo "fsync = off" >> > $PGDATA/postgresql.conf && \ > pg_ctl start && \ > createuser --username=postgres $PGUSER && \ > createdb --username=postgres --encoding=UTF8 --owner=$PGUSER > $PGDATABASE && \ > psql --username=postgres $PGDATABASE --command="ALTER USER $PGUSER > WITH PASSWORD '$PGPASSWORD';" && \ > psql --username=postgres $PGDATABASE --command='CREATE EXTENSION IF > NOT EXISTS postgis;' && \ > psql --username=postgres $PGDATABASE --command='CREATE EXTENSION IF > NOT EXISTS hstore;' && \ > osm2pgsql --username=$PGUSER --database=$PGDATABASE --create > --cache=60000 --hstore --latlong /data/map.osm.pbf && \ > rm -f /data/map.osm.pbf && \ > pg_ctl stop && \ > echo "fsync = on" >> > $PGDATA/postgresql.conf && \ > echo '# TYPE DATABASE USER ADDRESS METHOD' > > $PGDATA/pg_hba.conf && \ > echo "local all postgres peer" >> > $PGDATA/pg_hba.conf && \ > echo "local $PGDATABASE $PGUSER scram-sha-256" >> > $PGDATA/pg_hba.conf && \ > echo "host $PGDATABASE $PGUSER 0.0.0.0/0 scram-sha-256" >> > $PGDATA/pg_hba.conf > > The later fsync = on will override the former, right? > > Best regards > Alex > > > 2hrs sounds reasonable for Europe, it's a big place in terms of osm data and osm2pgsql is doing processing to convert to geometry objects prior to doing anything on the Postgresql side. If you examine the --log--sql output for a small test country you can see what it does in terms of the postgresql. osm2pgsql gives options to trim the output to only what you need (so if you don't want waterways, traffic features, parking places or places of worship etc.. why load them) Hopefully you have found the excellent geofabrik https://download.geofabrik.de/ source for osm data. Rather than load this data afresh each update cycle you would be better off simply loading the changes so the .osc files or ... osm osmosis will create the equivalent of a diff file for you Looks like you are already using osm2psql's recommended postgresql.config settings, I'd be surprised if this was way off. Getting as close to tin rather than virtual machines and containers will also help, lots of io going on here. If you are only interested in the geography you might consider geofabrik's shapefile available for many countries, they have already done some of the work for you. Apologies if you are already a long way down this route & just asking about the final stage of loading the osm2pgsql output to Postgresql but however well you do here I would only expect small marginal gains.
-
Re: Please recommend postgresql.conf improvements for osm2pgsql loading Europe
Alexander Farber <alexander.farber@gmail.com> — 2024-04-01T00:37:50Z
Thank you for the insights