Thank you, Justin - On Sat, Mar 30, 2024 at 4:33 AM Justin Clift <jus...@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
