This is a static archive of our old OpenStreetMap Help Site. Please post any new questions and answers at community.osm.org.

PostgreSql .config parameter when using Osm2pgsql –append

I have already imported the OSM Planet to the database. And I want to update the database regularly. The update command I use is osm2pgsql --append -slim with 6GB cache. However, the update process is still too slow.

Hardware: 32G memory, 1.7TB SSD.

Database config:

shared_buffers = 2GB
work_mem = 1000MB
effective_io_concurrency = 2
fsync = on
synchronous_commit = off
full_page_writes = off
checkpoint_segments = 100
checkpoint_completion_target = 0.9
autovacuum = on
effective_cache_size = 4GB
maintenance_work_mem = 2GB
max_worker_process = 12

On average it took 1 hour to process 6 hours worth of new OSM updates. (Screenshot is the log printed by the script openstreetmap-tiles-update-expire. Inside the script, it calls osm2pgsql --append -slim) alt text

I remember when bulk loading OSM Planet to the database, a lot of articles suggest modifying postgresql.conf

Such as:

fsync = off (import only!)

autovacuum = off (import only!)

Since the database is keep updating, should I change the postgresql config settings back to these import settings to increase performance?

postgresql osm2pgsql mapping

asked 02 Dec '20, 20:38

Yingcai
11●2●2●5
accept rate: 0%

edited 04 Dec '20, 14:41

Could you indicate what hardware this is running on and your original import parameters?

(03 Dec '20, 06:47) SimonPoole ♦

(for completeness - that screenshot looks like output from openstreetmap-tiles-update-expire)

(03 Dec '20, 22:02) SomeoneElse ♦

Thanks @simonPoole and @SomeoneElse. The post has been updated.

(04 Dec '20, 14:42) Yingcai

One Answer:

I am not sure what screen shot is about, this doesn't look like osm2pgsql output!?

There is some advice on how to tune your database for osm2pgsql in the osm2pgsql manual at https://osm2pgsql.org/doc/manual.html#tuning-the-postgresql-server .

answered 03 Dec '20, 09:27

Jochen Topf
5.2k●5●50●74
accept rate: 31%

The screenshot is the log printed by the script openstreetmap-tiles-update-expire (mod_tile https://github.com/SomeoneElseOSM/mod_tile/tree/switch2osm ). Inside the script, it calls osm2pgsql --append -slim

(04 Dec '20, 19:53) Yingcai