Re: Please recommend postgresql.conf improvements for osm2pgsql loading Europe

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