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(at)postgresql(dot)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