Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rqVZe-00Cnn3-Fe for pgsql-general@arkaria.postgresql.org; Sat, 30 Mar 2024 10:04:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rqVZd-007jJl-HE for pgsql-general@arkaria.postgresql.org; Sat, 30 Mar 2024 10:04:29 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rqVZd-007jJc-0G for pgsql-general@lists.postgresql.org; Sat, 30 Mar 2024 10:04:29 +0000 Received: from mail-ed1-x52f.google.com ([2a00:1450:4864:20::52f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rqVZY-007Enu-F2 for pgsql-general@lists.postgresql.org; Sat, 30 Mar 2024 10:04:27 +0000 Received: by mail-ed1-x52f.google.com with SMTP id 4fb4d7f45d1cf-56c0a249bacso3299248a12.1 for ; Sat, 30 Mar 2024 03:04:25 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1711793064; x=1712397864; darn=lists.postgresql.org; h=cc:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=3ezMVtHHSikBUnk9HuwpzAw32guuQu/TljGUwYi3Skg=; b=cUVvDIEpBiZejypV4xbEBOICnXTBZMjMJFV1sGiPGIvFF59aWBwf+ldnY2S0YU2wGO tl/ps7tF6ij/CFB5lecO9LbcXbNewtFdkliQGNXLSGMEpxkrEJDWlougKVgeFM970bB9 esyJBb3xrVFvtop8L6QG05DLjLgea57WPD5ef7F27+btawfKCVRHdDDA/3w/XA0CBlq3 Pyh4o+G79nPyT5OPEHfISotKsVfF6h078IZdFLZDiBK/Z4ySHCPpMQzt0e70ODEWkt8M F/gCfg7Dxsiak5KI22JCHl+aSQaeL9IQJiudaCyK6zEh/G3jsxMIec2FQZSc3yHVxgR0 HtwQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1711793064; x=1712397864; h=cc:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=3ezMVtHHSikBUnk9HuwpzAw32guuQu/TljGUwYi3Skg=; b=UFeom+ISmpPG4RF2B+qYBe1XY4sIRNoj6vw3x9xjGCCp/5OvNImimW8x0mM1PLLkJk 9O+z8wgAYuzrOBJ+ztLHqpZTPfSGEn97glV3goZl9dsGgcXdgoBDMOHPqTnB8arXWTC5 tn/Wr8Ow+CayNjzMLOJp7sfFRSdur14hhbW+FMG9bInlIyTLJDxI4+lJX9B8bLCb//d6 ZQH9YPAT6FIkplkx0vTvgRKNWjYhncBMChmlZPVn5JYBHr94APKAlJ3W7UVhlgpyvTch yYxmJjFWKRbp88YvwNQgocrjU8jDIsKvyh2RSty8+GEk93rZb2ctl8ez+vsbWnA9ZhL2 7RAw== X-Gm-Message-State: AOJu0Yz6BZtqPVjnAF6V0zxFdqRcd5OG9h5hqaN/brQH4JKYJ1c0l6vT wDIlliPUrqrnXLuOsCBxHfnKkkyzq52c3IVg+d6KZRX4jDaBZBO0+5vX6547AmybUZxX7xZPnOj Ny0EFSDCLMB0xQBcBeFw5CuTEBXgtLbr7iID8xSnvTL0= X-Google-Smtp-Source: AGHT+IFIUL25KYVu2yrfyGaIeUGdoSaAl5hOa4rMTqOeJh1hak2Fqu/Rs28HHfvnyPXVSMMAmH5d7AbaqfkZitXjjDc= X-Received: by 2002:a50:a40c:0:b0:568:be6d:e5e5 with SMTP id u12-20020a50a40c000000b00568be6de5e5mr3056149edb.37.1711793063579; Sat, 30 Mar 2024 03:04:23 -0700 (PDT) MIME-Version: 1.0 References: <98df75338710b3cb63817f3db532cdf6@postgresql.org> In-Reply-To: <98df75338710b3cb63817f3db532cdf6@postgresql.org> From: Alexander Farber Date: Sat, 30 Mar 2024 11:04:11 +0100 Message-ID: Subject: Re: Please recommend postgresql.conf improvements for osm2pgsql loading Europe Cc: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000007bdcb10614dde1ae" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000007bdcb10614dde1ae Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thank you, Justin - On Sat, Mar 30, 2024 at 4:33=E2=80=AFAM Justin Clift 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 =3D 1GB" >> $PGDATA/postgresql.conf && \ echo "work_mem =3D 50MB" >> $PGDATA/postgresql.conf && \ echo "maintenance_work_mem =3D 10GB" >> $PGDATA/postgresql.conf && \ echo "autovacuum_work_mem =3D 2GB" >> $PGDATA/postgresql.conf && \ echo "wal_level =3D minimal" >> $PGDATA/postgresql.conf && \ echo "checkpoint_timeout =3D 60min" >> $PGDATA/postgresql.conf && \ echo "max_wal_size =3D 10GB" >> $PGDATA/postgresql.conf && \ echo "checkpoint_completion_target =3D 0.9" >> $PGDATA/postgresql.conf && \ echo "max_wal_senders =3D 0" >> $PGDATA/postgresql.conf && \ echo "random_page_cost =3D 1.0" >> $PGDATA/postgresql.conf && \ echo "password_encryption =3D scram-sha-256" >> $PGDATA/postgresql.conf && \ echo "fsync =3D off" >> $PGDATA/postgresql.conf && \ pg_ctl start && \ createuser --username=3Dpostgres $PGUSER && \ createdb --username=3Dpostgres --encoding=3DUTF8 --owner=3D$PGUSER $PGDATABASE && \ psql --username=3Dpostgres $PGDATABASE --command=3D"ALTER USER $PGUSER = WITH PASSWORD '$PGPASSWORD';" && \ psql --username=3Dpostgres $PGDATABASE --command=3D'CREATE EXTENSION IF= NOT EXISTS postgis;' && \ psql --username=3Dpostgres $PGDATABASE --command=3D'CREATE EXTENSION IF= NOT EXISTS hstore;' && \ osm2pgsql --username=3D$PGUSER --database=3D$PGDATABASE --create --cache=3D60000 --hstore --latlong /data/map.osm.pbf && \ rm -f /data/map.osm.pbf && \ pg_ctl stop && \ echo "fsync =3D on" >> $PGDATA/postgresql.co= nf && \ 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 =3D on will override the former, right? Best regards Alex --0000000000007bdcb10614dde1ae Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Th= ank you, Justin -

On Sat, Mar 30, 2024 at 4:33=E2=80=AFAM Justin Clift <<= a href=3D"mailto:justin@postgresql.org">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/78= 243530/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= =C2=A0
and later report back on any improvements:

RUN= set -eux && \
=C2=A0 =C2=A0 pg_ctl init && \
=C2=A0 =C2=A0 echo "shared_buffers =3D 1GB"=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 >> $PGDATA/postgresql.conf = && \
=C2=A0 =C2=A0 echo "work_mem =3D 50MB"=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0&g= t;> $PGDATA/postgresql.conf && \
=C2=A0 =C2=A0 echo &q= uot;maintenance_work_mem =3D 10GB"=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0&g= t;> $PGDATA/postgresql.conf && \
=C2=A0 =C2=A0 echo &q= uot;autovacuum_work_mem =3D 2GB"=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0>> $PGDATA/postgresql.conf && \
=C2=A0 =C2=A0 ec= ho "wal_level =3D minimal"=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0>> $PGDATA/postgresql.conf && \
=
=C2=A0 =C2=A0 echo "checkpoint_timeout =3D 60min"=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 >> $PGDATA/postgresql.conf && \
=C2=A0 =C2=A0 echo "max_wal_size =3D 10GB"=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0>> $PGDATA/postgresql.co= nf && \
=C2=A0 =C2=A0 echo "checkpoint_completion_ta= rget =3D 0.9"=C2=A0 >> $PGDATA/postgresql.conf && \
=C2=A0 =C2=A0 echo "max_wal_senders =3D 0"=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0>> $PGDATA/postgresql.co= nf && \
=C2=A0 =C2=A0 echo "random_page_cost =3D 1.0= "=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 >> $PGDATA/pos= tgresql.conf && \
=C2=A0 =C2=A0 echo "password_encry= ption =3D scram-sha-256" >> $PGDATA/postgresql.conf && \=
=C2=A0 =C2=A0 echo "fsync =3D off"=C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 >> $PGDATA/postgresql.conf && \
=C2=A0 =C2=A0 p= g_ctl start && \=C2=A0
=C2=A0 =C2=A0 createuser --usernam= e=3Dpostgres $PGUSER && \
=C2=A0 =C2=A0 createdb --userna= me=3Dpostgres --encoding=3DUTF8 --owner=3D$PGUSER $PGDATABASE && \<= /div>
=C2=A0 =C2=A0 psql --username=3Dpostgres $PGDATABASE --command=3D= "ALTER USER $PGUSER WITH PASSWORD '$PGPASSWORD';" &&a= mp; \
=C2=A0 =C2=A0 psql --username=3Dpostgres $PGDATABASE --comm= and=3D'CREATE EXTENSION IF NOT EXISTS postgis;' && \
<= div>=C2=A0 =C2=A0 psql --username=3Dpostgres $PGDATABASE --command=3D'C= REATE EXTENSION IF NOT EXISTS hstore;' && \
=C2=A0 = =C2=A0 osm2pgsql --username=3D$PGUSER --database=3D$PGDATABASE --create --c= ache=3D60000 --hstore --latlong /data/map.osm.pbf && \
= =C2=A0 =C2=A0 rm -f /data/map.osm.pbf && \
=C2=A0 =C2=A0 = pg_ctl stop && \
=C2=A0 =C2=A0 echo "fsync =3D on&qu= ot;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 >> $PGDATA/postgresql.conf && \
=C2=A0 =C2=A0 echo '# TYPE DATABASE USER ADDRESS METHOD'= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 > $PGDATA/pg_hba= .conf && \
=C2=A0 =C2=A0 echo "local all postgres pe= er"=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0>> $PGDATA/pg_hba.conf && \
=C2=A0 =C2=A0 echo "local $PGDATABASE $PGUSER=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0scram-sha-256" >> $PGDATA/pg_hba.conf &a= mp;& \
=C2=A0 =C2=A0 echo "host=C2=A0 $PGDATABASE $PGUSE= R 0.0.0.0/0 scram-sha-256" >> $= PGDATA/pg_hba.conf

The later fsync =3D on will override the former, = right?

Best regards
Alex

=C2=A0
--0000000000007bdcb10614dde1ae--