About build_exposure_ancillary.sh

What is build_exposure_ancillary.sh?

Drew extracted and refactored parts of add_data.sh so that the initial portion of the process called "exposure ancillary" (before DSRA or PSRA) are imported and

Input

• OpenDRR/boundaries-db etc.?

Output

/usr/src/app/opendrr-exposure-ancillary.dump

-rw-r--r--  1 root root 9702448904 Mar  9 19:37 opendrr-exposure-ancillary.dump

which is to be compressed into a 7-Zip multi-volume archive and uploaded as release assets:

foka@debian-e431:~/NRCan/OpenDRR/opendrr-api$ time eatmydata docker cp opendrr-api-python-opendrr-1:/usr/src/app/opendrr-exposure-ancillary.dump /tmp/

real	2m33.600s
user	0m4.034s
sys	0m27.869s

which is about 64 MB/s? Not sure if that is fast or slow. (WD SA510 SATA SSD, 1TB)

I should probably use a Docker volume so that

UID and GID

$ docker run -it --rm postgis/postgis:15-3.3 grep postgres /etc/passwd
postgres:x:999:999::/var/lib/postgresql:/bin/bash

where 999 is set in the https://hub.docker.com/_/postgres

Disk space usage

When max_wal_size is set to 8GB and checkpoint_timeout left unchanged at 5min:

root@debian-e431:/var/log# du -csh /var/lib/docker/volumes/*
0	/var/lib/docker/volumes/backingFsBlockDev
8.0K	/var/lib/docker/volumes/efe89e64abcbc08734e25f70fcad046caad521648da6d7fee6ac81b56f2377be
48K	/var/lib/docker/volumes/metadata.db
9.1G	/var/lib/docker/volumes/opendrr-api_opendrr-scripts
58G	/var/lib/docker/volumes/opendrr-api_pg-data
67G	total

$ docker exec -it opendrr-api-db-opendrr-1 du -ch /var/lib/postgresql/data
4.0K	/var/lib/postgresql/data/pg_notify
4.0K	/var/lib/postgresql/data/pg_twophase
4.0K	/var/lib/postgresql/data/pg_serial
4.0K	/var/lib/postgresql/data/pg_logical/snapshots
4.0K	/var/lib/postgresql/data/pg_logical/mappings
16K	/var/lib/postgresql/data/pg_logical
51G	/var/lib/postgresql/data/base/16384
19M	/var/lib/postgresql/data/base/16385
7.2M	/var/lib/postgresql/data/base/4
12K	/var/lib/postgresql/data/base/pgsql_tmp
7.3M	/var/lib/postgresql/data/base/5
7.4M	/var/lib/postgresql/data/base/1
51G	/var/lib/postgresql/data/base
4.0K	/var/lib/postgresql/data/pg_commit_ts
4.0K	/var/lib/postgresql/data/pg_wal/archive_status
7.4G	/var/lib/postgresql/data/pg_wal
12K	/var/lib/postgresql/data/pg_multixact/members
12K	/var/lib/postgresql/data/pg_multixact/offsets
28K	/var/lib/postgresql/data/pg_multixact
556K	/var/lib/postgresql/data/global
4.0K	/var/lib/postgresql/data/pg_dynshmem
4.0K	/var/lib/postgresql/data/pg_stat
4.0K	/var/lib/postgresql/data/pg_stat_tmp
12K	/var/lib/postgresql/data/pg_xact
12K	/var/lib/postgresql/data/pg_subtrans
4.0K	/var/lib/postgresql/data/pg_replslot
4.0K	/var/lib/postgresql/data/pg_snapshots
4.0K	/var/lib/postgresql/data/pg_tblspc
58G	/var/lib/postgresql/data
58G	total

WAL size:

foka@debian-e431:~/NRCan/OpenDRR/opendrr-api$ docker exec -it opendrr-api-db-opendrr-1 psql
psql (15.2 (Debian 15.2-1.pgdg110+1))
Type "help" for help.

postgres=# SELECT pg_size_pretty(pg_current_wal_insert_lsn() - '0/00000000'::pg_lsn);
 pg_size_pretty 
----------------
 47 GB
(1 row)

Let’s try max_wal_size of 256GB and checkpoint_timeout of 1d and see what happens!

Sample run log

See https://gist.githubusercontent.com/anthonyfok/32ab32ab6735970514654ddac95818bd/raw/f71322c170c91c8d3dac7647b002344dce25ee74/opendrr-api_build_exposure_ancillary_2023-03-09.log