public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alexander Farber <[email protected]>
Cc: [email protected]
Subject: Re: Please recommend postgresql.conf improvements for osm2pgsql loading Europe
Date: Sat, 30 Mar 2024 11:04:11 +0100
Message-ID: <CAADeyWgKVA4BdJu7L+9Z7rXW06vMRhiLfQP1MixwCshOGpmZ7Q@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAADeyWibmmfvBhJS2P2mPUOLx57WJ0h+qM5xBQPqFehh-Sna4g@mail.gmail.com>
<[email protected]>
Thank you, Justin -
On Sat, Mar 30, 2024 at 4:33 AM Justin Clift <[email protected]> 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
view thread (6+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Please recommend postgresql.conf improvements for osm2pgsql loading Europe
In-Reply-To: <CAADeyWgKVA4BdJu7L+9Z7rXW06vMRhiLfQP1MixwCshOGpmZ7Q@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox