Thank you, Justin -
On Sat, Mar 30, 2024 at 4:33 AM Justin Clift <justin@xxxxxxxxxxxxxx> 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?
The later fsync = on will override the former, right?
Best regards
Alex
