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